mysql: полезные команды и настройки
Настройки
Список настроечных параметров и их значения
mysqld --verbose --help
Управление пользователями
Список пользователей
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> DROP USER 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
Maatkit - расширенные возможности MySQL
Adminer - удобная замена phpMyAdmin, всего один php-файл.
Другие sql-команды
Замена в поле определенной подстроки на другую
mysql> UPDATE table SET field=replace(field, 'original string', 'new string');
Вставка данных из одной таблицы в другую
mysql> INSERT INTO table1 (fld1, fld2) SELECT table2.fld1, table2.fld2 FROM table2 WHERE table2.fld2 > 7;
Просмотр структуры таблицы blog_posts
mysql> DESCRIBE blog_posts;
Просмотр sql-запроса на создание структуры таблицы blog_posts
mysql> SHOW CREATE TABLE blog_posts;
Мониторинг и статистика
Список всех баз данных
mysql> SHOW DATABASES;
Список всех таблиц в выбранной базе данных
mysql> SHOW TABLES;
Статистика по работе сервера
mysql> SHOW GLOBAL STATUS;
Mytop - простое средство мониторинга Mysql
Remote MySQL Performance And Query Monitoring
Install Innotop to Monitor MySQL Server Performance
Install Mtop (MySQL Database Server Monitoring)
Оптимизация баз данных
При помощи команды 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.
Изменение строки приглашения (prompt)
В ~/.my.cnf добавим
[client]
default-character-set = 'utf8'
pager = 'less -n -i -S'
prompt = '\u@\h [\d] > '
user = 'root'
password = 'secret'
Эти настройки были найдены на просторах инета, но автора, к сожалению, не помню. Делают эти настройки следующие
устанавливаем кодировку по-умолчанию в utf8
используем less для вывода результатов запроса
меняем строку приглашения, \u - пользователь, \h - хост, \d - база данных
указываем логин и пароль для 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
Начиная с версии 5.5.30 при дампе возможно получить следующие сообщение
Warning: Skipping the data of table mysql.event. Specify the --events option explicitly.
или
mysqldump: Couldn't execute 'show events': Cannot proceed because system tables used by Event Scheduler were found damaged at server start (1577)
Для исправления этого надо обновить базы и перезапустить MySQL
sudo mysql_upgrade -u root -h localhost -p --verbose
sudo service mysql restart
Изменение кодировка для текстового поля
Список полей для таблицы table с информацией о поле, в том числе и кодировка поля
SHOW FULL COLUMNS FROM table;
Меняем charset для поля field
ALTER TABLE table MODIFY field VARCHAR(255) CHARACTER SET utf8;
Изменение кодировка при импорте с дампа
Определение кодировки файла
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
MySQL + Python
Иногда при компиляция MySQL-python может выскочить такая ошибка configure: error: mysql_config executable not found, это значит, что не установлен пакет libmysqlclient15-dev. Под Ubuntu устанавливается так
sudo apt-get install libmysqlclient15-dev
Перенос директории с данным (data directory)
Останавливаем MySQL
service mysqld stop
Копируем существующею директорию с данными в новое место
mkdir -p /path/new/dir
sudo chown -R mysql:mysql /path/new/dir
sudo cp -R -p /var/lib/mysql /path/new/dir
Укажем в my.cnf путь к новой директории, секция mysqld
# sudo vim /etc/mysql/my.cnf
[mysqld]
datadir=/path/new/dir/mysql
Запускаем MySQL
service mysqld start
Дополнительное чтиво
Стратегия восстановления поврежденной таблицы в MySQL
Настройка и оптимизация MySQL сервера
Постраничная навигация с MySQL при большом количестве записей
MySQL Performance real life Tips and Tricks
MyIsam Vs Innodb Vs Memory – Mysql Storage Engine Comparison
http://proft.me/2011/07/19/mysql-poleznye-komandy-i-nastrojki/
| 
