Архив метки: mysql

FreeBSD сброс пароля root к MySQL

1. Останавливаем службу MySQL:

/usr/local/etc/rc.d # ./mysql-server stop

2. Запускаем службу с опцией —skip-grant-tables

mysqld_safe —skip-grant-tables &

3. Подключаемся с серверу MySQL при помощи клиента mysql:

mysql -u root
4. Вводим новый пароль для root:

mysql> use mysql;
mysql> update user set password=PASSWORD(«NEW-ROOT-PASSWORD») where User=’root’;
mysql> flush privileges;
mysql> quit

5. Останавливаем сервер MySQL:

/etc/init.d/mysql stop
6.Запускаем MySQL-сервер и логинимся с новым паролем:

/usr/local/etc/rc.d # ./mysql-server start
mysql -u root -p

Коварный UNION..

Иной раз не знаешь где затупишь. Казалось бы простой запрос с использованием UNION, а потратил кучу времени, чтобы выяснить, почему он иногда не корректно отрабатывает.

Было:

$sql="select * from (select amount,local_date as date,managers.fio as tar_id,'enter' as type from payments inner join managers on payments.mod_person=managers.person_id where agrm_id='$agrm_id' UNION  "
 . "SELECT amount,DATE_ADD(period, INTERVAL 1 DAY) as date,tarifs.descr as tarif,'inet' as type FROM rentcharge inner join tarifs on rentcharge.tar_id=tarifs.tar_id where rentcharge.agrm_id='$agrm_id' and amount<>0 UNION "
 . "select usbox_charge.amount,usbox_charge.charge_date,tarifs.descr as tarif,'tv' from usbox_charge inner join usbox_services on usbox_charge.serv_id=usbox_services.serv_id inner join tarifs on tarifs.tar_id=usbox_services.tar_id where usbox_charge.amount<>0 and usbox_charge.agrm_id='$agrm_id') as res order by date";

Стало:

$sql="select * from (select amount,local_date as date,managers.fio as tar_id,'enter' as type from payments inner join managers on payments.mod_person=managers.person_id where agrm_id='$agrm_id' UNION ALL "
 . "SELECT amount,DATE_ADD(period, INTERVAL 1 DAY) as date,tarifs.descr as tarif,'inet' as type FROM rentcharge inner join tarifs on rentcharge.tar_id=tarifs.tar_id where rentcharge.agrm_id='$agrm_id' and amount<>0 UNION ALL "
 . "select usbox_charge.amount,usbox_charge.charge_date,tarifs.descr as tarif,'tv' from usbox_charge inner join usbox_services on usbox_charge.serv_id=usbox_services.serv_id inner join tarifs on tarifs.tar_id=usbox_services.tar_id where usbox_charge.amount<>0 and usbox_charge.agrm_id='$agrm_id') as res order by date";

В чем соль? Оператор UNION без опции ALL оказывается обладает свойствами группировки одинаковых значений в запросе. Например у меня, при одинаковых одновременных значениях даты и суммы происходила группировка этих строчек в одну…

MySQL в консоли. Шпаргалка

mysql -uusername -ppassword -hhost
create database db_name; - создаем новую бд с названием db_name
show databases; - просмотр имеющихся бд
use db_name; - выбрать бд с именем db_name
show tables; - просмотр всех таблиц
show columns from table_name; - просмотр колонок и их свойств
create table table_name (`id` int(11) AUTO_INCREMENT,`name` varchar(255)); - создание таблицы с колонками id и name
alter table table_name add column1 int(11); - добавить колонку в таблицу
alter table table_name drop column1; - удалить колонку из таблицы
SELECT * FROM table_name;
SET NAMES utf8; - чаще для линуксовых консолей
SET NAMES cp866; - для cmd windows

Важно! Обязателен разделитель команд ;

Сортировка в MySQL кириллицы utf8-general-ci

загруженное (1)Дано: MySQL 5.1.72, таблицы созданные в utf8-general-ci, установка соединения вызывается следующей функцией:

function connect($host,$name,$pass,$base){
 $this->idsqlconnection=new mysqli($host,$name,$pass,$base);
 if (mysqli_connect_errno()) {
 $serr=mysqli_connect_error();
 die("Error connect to Mysql or select base: $serr");
 } 
 $result = mysqli_query($this->idsqlconnection,"SET NAMES utf8_general_ci"); 
 mysqli_set_charset($this->idsqlconnection, "utf8_general_ci");
 
 }

И хоть ты тресни, запрос вида:

$SQL = "SELECT * FROM places WHERE orgid='$orgid' AND active=1 ORDER BY name";
 $result = $sqlcn->ExecuteSQL( $SQL ) or die("Не могу выбрать список помещений!".mysqli_error($sqlcn->idsqlconnection));

Сортировался по непонятно каким критериям. Потеряв на поиск причины  несколько часов, перепробовав кучу «рецептов», пришлось заворачивать сортировку запросов в команду binary(), т.е. вот так:

$SQL = "SELECT * FROM places WHERE orgid='$orgid' AND active=1 ORDER BY binary(name)";

Костыль конечно.. И сортирует различая большие и маленькие буквы. Но таки лучше так чем никак. Ищу лучшее решение.

Шпаргалка по работе c MySQL

Вот сегодня столкнулся с неожиданным глюком — импортированный из одной версии phpmyadmin дамп в упор не захотел загружаться в другую версию phpmyadmin, хотя версия mysql одинаковая. Вот тут то и пригодился доступ на сервер по ssh и знание основ работы с консолью. А так, вот небольшая шпаргалка по работе с mysql из консоли:

Работа с бекапами

Делаем бекап
mysqldump -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql

Создаём структуру базы без данных
mysqldump —no-data — u USER -pPASSWORD DATABASE > /path/to/file/schema.sql

Если нужно сделать дамп только одной или нескольких таблиц
mysqldump -u USER -pPASSWORD DATABASE TABLE1 TABLE2 TABLE3 > /path/to/file/dump_table.sql

Создаём бекап и сразу его архивируем
mysqldump -u USER -pPASSWORD DATABASE | gzip > /path/to/outputfile.sql.gz

Создание бекапа с указанием его даты
mysqldump -u USER -pPASSWORD DATABASE | gzip > `date +/path/to/outputfile.sql.%Y%m%d.%H%M%S.gz`

Заливаем бекап в базу данных
mysql -u USER -pPASSWORD DATABASE < /path/to/dump.sql

Заливаем архив бекапа в базу
gunzip < /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE или так zcat /path/to/outputfile.sql.gz | mysql -u USER -pPASSWORD DATABASE Создаём новую базу данных mysqladmin -u USER -pPASSWORD create NEWDATABASE Удобно использовать бекап с дополнительными опциями -Q -c -e, т.е. mysqldump -Q -c -e -u USER -pPASSWORD DATABASE > /path/to/file/dump.sql, где:
-Q оборачивает имена обратными кавычками
-c делает полную вставку, включая имена колонок
-e делает расширенную вставку. Итоговый файл получается меньше и делается он чуть быстрее

Для просмотра списка баз данных можно использовать команду:
mysqlshow -u USER -pPASSWORD

А так же можно посмотреть список таблиц базы:
mysqlshow -u USER -pPASSWORD DATABASE

часть текста стырена с http://habrahabr.ru/post/105954/

UPDATE 26/02/2014
Удаление всех таблиц в базе данных, 3 способа:

mysql --silent --skip-column-names -u[USERNAME] -p[PASSWORD] [DATABASE] -e "show tables" | \
gawk '{print "drop table " $1 ";"}' | mysql -u [USERNAME] -p[PASSWORD] [DATABASE]
mysqldump -u[USERNAME] -p[PASSWORD] --add-drop-table --no-data [DATABASE] | \
grep ^DROP | mysql -u[USERNAME] -p[PASSWORD] [DATABASE]
mysql --silent --skip-column-names --execute="show tables" --user=gallery_admin --password=pass gallery3 | awk '{print "drop table " $1 ";"}' | mysql --user=gallery_admin --password=pass gallery3