вторник, 2 августа 2011 г.

Mysql: полезные команды и настройки




Управление пользователями


Как получить список пользователей


mysql> SELECT User,Host FROM mysql.user;

Как узнать права у пользователя root@localhost


mysql> SHOW GRANTS FOR root@localhost;

Как создать нового пользователя


mysql> CREATE USER 'user'@'localhost' IDENTIFIED BY 'secret';

Как добавить привилегии для всех таблиц БД dbname пользователю 'user'@'localhost'


mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX ON dbname.* TO 'user'@'localhost';

Как добавить все привилегии для всех таблиц БД dbname пользователю 'user'@'localhost'


mysql> GRANT ALL PRIVILEGES ON dbname.* TO 'user'@'localhost';

Как удалить права пользователя 'user'@'localhost' для БД dbname:


mysql> REVOKE ALL ON dbname.* FROM 'user'@'localhost';

Как перезагрузить привилегии


mysql> FLUSH PRIVILEGES;

Как сменить пароль root


$ mysqladmin -uroot password 'secret'

Управление базой данных


Как создать базу данных


mysql> CREATE DATABASE dbname COLLATE utf8_general_ci;

Как создать базу данных из консоли


$ mysqladmin -u root -p create dbname

Как удалить базу данных из консоли


mysqladmin -u root -p drop dbname


Другие sql-команды


Как заменить в поле определенной подстроки на другую


mysql> UPDATE FIRMS SET field=replace(field, 'original string', 'new string');

Мониторинг и статистика


Как получить список всех баз данных


mysql> SHOW DATABASES;

Как получить список всех таблиц в выбранной базе данных


mysql> SHOW TABLES;

Как посмотреть статистику по работе сервера


mysql> SHOW GLOBAL STATUS;

Mytop - простое средство мониторинга Mysql


Оптимизация баз данных


При помощи команды mysqlcheck можно выполнять проверку, оптимизацию и исправление ошибок.


Как проверить на ошибки БД dbname


$ mysqlcheck -p dbname

Как восстановить и оптимизировать все БД


$ mysqlcheck -Aor -p

Описание аргументов


-p – использовать пароль


-A – проверять все базы данных


-r – ремонтировать БД


-o – оптимизировать БД


Скрипт простой оптимизации БД, можно добавить в крон для выполнение раз в сутки


mysqlcheck --repair --analyze --optimize --all-databases --auto-repair -u root -pSECRET

Установим mysqltuner, который показывает статистику по работающему mysql-серверу и дает полезные советы по оптимизации.


cd /usr/local/bin
wget http://mysqltuner.pl/mysqltuner.pl
chmod +x mysqltuner.pl

Запустим


/usr/local/bin/mysqltuner.pl

При запуске скрипт спросит логин и пароль для root'a. После корректной авторизации будет выведена статистика и разные советы по правке конфигурационного файла MySQL.


Дамп (резервная копия)


Как сделать дамп базы данных


$ mysqldump -uroot -p dbname > dump.sql

Как сделать дамп выбранных баз


$ mysqldump -uroot -p -B dbname1 dbname2 > dump.sql

Как сделать дамп всех баз


$ mysqldump -uroot -p -A > dump.sql

Как сделать дамп только структуры, без данных


$ mysqldump -uroot -p --no-data dbname > database.sql

Другие опции


--add-drop-table - добавляет команду DROP TABLE перед каждой командой CREATE TABLE


--add-locks - добавляет команду LOCK TABLES перед выполнением и UNLOCK TABLE после выполнения каждого дампа таблицы


--no-create-db, -n - не добавлять команду CREATE DATABASE, которая добавляется при использовании параметров --databases и --all-databases


--no-data, -d - дампить только структуру таблиц


--no-create-info, -t - не создавать команду CREATE TABLE


--skip-comments - не выводить комментарии.


--compact - использовать компактный формат


--create-options - добавляет дополнительную информацию о таблице в команду CREATE TABLE: тип, значение AUTO_INCREMENT и т.д. Не нужные опции можно вырезать с помощью sed.


--extended-insert, -e - применение команды INSERT с многострочным синтаксисом (повышает компактность и быстродействие операторов ввода)


--tables - дампить только таблицы из списка, следующего за этим параметром, разделитель - пробел


Как применить дамп


$ mysql -uroot -p dbname1 < dump.sql

Как изменить кодировку при импорте с дампа


Как определить кодировку файла


file --mime-encoding dump.sql

Как конвертировать из кодировки latin1 в utf8


mysqldump --add-drop-table -uroot -p dbname | replace CHARSET=latin1 CHARSET=utf8 | iconv -f latin1 -t utf8 | mysql -uroot -p dbname

Как восстановить root-пароль


$ service mysqld stop
$ mysqld_safe --skip-grant-tables &
$ mysql

mysql> UPDATE mysql.user SET Password=PASSWORD('secret') WHERE User='root';
mysql> FLUSH PRIVILEGES;

$ service mysqld restart

1 комментарий:

Анонимный комментирует...

Отлично! Спасибо!

Отправить комментарий