Ноябрь 29th, 2013 
Evgeniy Kamenev Оптимизация параметров Mysql с помощью скриптов
Mysqltuner
Mysqlprimer
1. mysqltuner.pl (http://blog.mysqltuner.com/)
2. tuning-primer.sh (http://www.day32.com/MySQL/)
Логирование медленных запросов + запросов не использующих индексы
|
1 2 3 4 5 6 7 |
[mysqld] # логгируем запросы длиннее 5 секунд long_query_time = 5 log-slow-queries = /var/log/mysqld-slow-query.log # логгируем запросы, не использующие индексы log-queries-not-using-indexes |
Увеличение количества открытых MySQL файлов
Часто при использовании Inndb MySQL открывает очень много файлов и сваливается, т.к. упирается в лимит (1024 открытых файла). Текущее использование открытых файлов можно узнать так:
|
1 |
# lsof -u mysql | wc –l |
Если же оно приближается к 1024, то стоит в блок [mysqld] файла my.cnf добавить следующее:
|
1 |
open_files_limit = 8192 |
Увеличение количества MySQL-подключений
|
1 |
MariaDB [(none)]> show variables like 'max_connections'; |
|
1 2 3 4 5 |
+-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ |
|
1 |
MariaDB [(none)]> set global max_connections=300; |
|
1 |
# nano /etc/my.cnf |
|
1 2 |
[mysqld] max_connectons = 300 |
Просмотр справки по команде show,create user,create table
|
1 |
mysql> help show |
|
1 |
mysql> help create user |
|
1 |
mysql> help create table |
Просмотр имен таблиц из базы joomla_on имеющих в составе своего имени слово “users”
|
1 |
mysql>show tables from joomla_on like '%users%'; |
Просмотр баз данных , имена которых содержат слово mysql
|
1 |
mysql>show databases like '%mysql%'; |
Просмотр типов привилегий
|
1 |
mysql>show privileges; |
Просмотр структуры таблицы databasename.tablename
|
1 |
mysql>describe databasename.tablename; |
Просмотр общей информации о текущем сеансе MySQL
|
1 |
mysql>status; |
Просмотр расширенной информации о состоянии MySQL
|
1 |
mysql>show status; |
Просмотр текущей базы данных
|
1 |
mysql>select database(); |
Просмотр установленной версии MySQL
|
1 |
mysql>select version(); |
Просмотр текущего пользователя
|
1 |
mysql>select current_user(); |
Просмотр существующих таблиц в базе данных databasename.
|
1 |
mysq>show tables from databasename.tablename; |
Просмотр клиентских подключений к MySQL-серверу и их количества
|
1 |
mysql>show processlist; |
Просмотр всех опций mysql-утилиты
|
1 |
# mysql --help | less |
(здесь же указано, где MySQL-сервер ищет свой файл конфигурации)
Запись всех выполняемых команд и возвращаемых после их выполнения данных в файл
|
1 |
mysql> tee /tmp/testtee.txt; |
Остановка записи в файл
|
1 |
mysql> notee; |
MySQLAdmin
Просмотр статуса сервера
|
1 |
# mysqladmin status -p123456789 |
Просмотр расширенной информации о состоянии MySQL
|
1 |
# mysqladmin -p123456789 extended-status |
Смена пароля MySQL-пользователя
|
1 |
# mysqladmin –p<old_password> password |
|
1 2 |
New password: Confirm new password: |
Перезагрузка таблиц привилегий
|
1 |
# mysqladmin -p123456789 reload |
Обновление всех кешей и файлов журналов
|
1 |
# mysqladmin -p123456789 refresh |
Просмотр информации о версии сервера
|
1 |
# mysqladmin -p123456789 version |
Просмотр всех активных процессов сервера
|
1 |
# mysqladmin -p123456789 processlist |
Проверка работает ли MySQL.
|
1 |
# mysqladmin -p123456789 ping |
Список всех команд
|
1 |
# mysqladmin -p123456789 --help |
Завершение(уничтожение) процесса/ов по его ID
|
1 |
# mysqladmin -p123456789 kill IDprocess IDprocess … |
Использование базы данных information_schema
|
1 |
MariaDB [information_schema]> use information_schema; |
|
1 |
Database changed |
просмотр всех привилегий для всех пользователей
|
1 |
MariaDB [information_schema]> select GRANTEE, PRIVILEGE_TYPE FROM user_privileges; |
Просмотр списка активных клиентских подключений(аналог show processlist;)
|
1 |
MariaDB [information_schema]> use information_schema; |
|
1 |
Database changed |
|
1 |
MariaDB [information_schema]> select ID, USER, HOST, DB, STATE from PROCESSLIST; |
|
1 2 3 4 5 6 |
+----+------+-----------+--------------------+-----------+ | ID | USER | HOST | DB | STATE | +----+------+-----------+--------------------+-----------+ | 4 | root | localhost | information_schema | executing | +----+------+-----------+--------------------+-----------+ 1 row in set (0.03 sec) |
Установка значение переменных(использование команды SET)
Переменные, которым присваиваются значения при помощи команды SET могут быть настроены глобально для всех сеансов или только для текущего сеанса
Для установки глобального значения используем ключевое слово GLOBAL
Для установки значения для текущего сеанса используем ключевое слово SESSION
Если не указано никакого ключевого слова, то система по умолчанию будет считать, что указано ключевое слово SESSION
|
1 |
MariaDB [(none)]> set tmp_table_size=25000000; |
|
1 |
MariaDB [(none)]> show variables like '%tmp_table_size%'; |
|
1 2 3 4 5 |
+----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 25000000 | +----------------+----------+ |
Делаем переподключение и проверяем ,что значение вернулось на исходное
|
1 |
MariaDB [(none)]> \r |
|
1 2 |
Connection id: 7 Current database: *** NONE *** |
|
1 |
MariaDB [(none)]> show variables like '%tmp_table_size%'; |
|
1 2 3 4 5 6 |
+----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.00 sec) |
Установка глобальной переменной
|
1 2 |
MariaDB [(none)]> set GLOBAL tmp_table_size=25000000; Query OK, 0 rows affected (0.00 sec) |
текущее значение переменной остается без изменений до переподключения
MariaDB [(none)]> show variables like ‘%tmp_table_size%’;
|
1 2 3 4 5 6 |
+----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 16777216 | +----------------+----------+ 1 row in set (0.00 sec) |
Переподключаемся к MySQL
|
1 2 3 |
MariaDB [(none)]> \r Connection id: 8 Current database: *** NONE *** |
MariaDB [(none)]> show variables like ‘%tmp_table_size%’;
|
1 2 3 4 5 6 |
+----------------+----------+ | Variable_name | Value | +----------------+----------+ | tmp_table_size | 25000000 | +----------------+----------+ 1 row in set (0.00 sec) |
|
1 |
MariaDB [(none)]> show variables like '%long_query_time%'; |
|
1 2 3 4 5 6 |
+-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 5.000000 | +-----------------+----------+ 1 row in set (0.00 sec) |
|
1 |
MariaDB [(none)]> set long_query_time=20; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
Просмотр системных переменных через команду SELECT
Глобальных переменных
|
1 |
MariaDB [(none)]> SELECT @@GLOBAL.long_query_time; |
|
1 2 3 4 5 6 |
+--------------------------+ | @@GLOBAL.long_query_time | +--------------------------+ | 5.000000 | +--------------------------+ 1 row in set (0.00 sec) |
Сессионных переменных
|
1 |
MariaDB [(none)]> SELECT @@SESSION .long_query_time; |
Либо
|
1 |
MariaDB [(none)]> SELECT long_query_time; |
|
1 2 3 4 5 6 |
+---------------------------+ | @@SESSIOn.long_query_time | +---------------------------+ | 20.000000 | +---------------------------+ 1 row in set (0.00 sec) |
Вывод сразу нескольких переменных(как глобальных, так и сессионных)
|
1 |
MariaDB [mysql]> select @@slow_query_log_file, @@global.long_query_time; |
|
1 2 3 4 5 |
+--------------------------------+--------------------------+ | @@slow_query_log_file | @@global.long_query_time | +--------------------------------+--------------------------+ | /var/log/mysqld-slow-query.log | 5.000000 | +--------------------------------+--------------------------+ |
Просмотр переменных состояния с помощью SHOW STATUS
|
1 |
MariaDB [mysql]> show status like '%connection%'; |
|
1 2 3 4 5 6 7 |
+----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Connections | 20 | | Max_used_connections | 2 | +----------------------+-------+ 2 rows in set (0.00 sec) |
Создание пользователя
|
1 |
MariaDB [(none)]> create user 'winxp'@'192.168.1.%' identified by '123456789'; |
|
1 |
MariaDB [(none)]> flush privileges; |
Если не указывать имя хоста, с которого нужно разрешить подключение пользователя, то будет установлен символ % , который означает подключение с любого хоста
Если не указывать пароль с которым разрешено подключаться пользователю, то пользователю разрешено будет подключаться с пустым паролем.
Просмотр привилегий пользователя
|
1 |
MariaDB [(none)]> show grants for 'winxp'@'192.168.1.%'; |
|
1 2 3 4 5 6 |
+----------------------------------------------------------------------------------------------------------------+ | Grants for winxp@192.168.1.% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'winxp'@'192.168.1.%' IDENTIFIED BY PASSWORD '*CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D' | +----------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
Права USAGE устанавливается автоматически при создании пользователя. На само деле оно ничего не позволяет пользователю, кроме как подключаться к MySQL.
Выдача права SELECT пользователю winxp на все таблицы базы joomla_on
|
1 |
MariaDB [(none)]> grant select on joomla_on.* to 'winxp'@'192.168.1.%'; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
Перезагрузка таблиц привилегий
|
1 |
MariaDB [(none)]> flush privileges; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
|
1 |
MariaDB [(none)]> show grants for 'winxp'@'192.168.1.%'; |
|
1 2 3 4 5 6 7 |
+----------------------------------------------------------------------------------------------------------------+ | Grants for winxp@192.168.1.% | +----------------------------------------------------------------------------------------------------------------+ | GRANT USAGE ON *.* TO 'winxp'@'192.168.1.%' IDENTIFIED BY PASSWORD '*CC67043C7BCFF5EEA5566BD9B1F3C74FD9A5CF5D' | | GRANT SELECT ON `joomla_on`.* TO 'winxp'@'192.168.1.%' | +----------------------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) |
Удаление права Select для пользователя winxp в базе базе joomla_on
|
1 |
MariaDB [(none)]> revoke select on joomla_on.* from 'winxp'@'192.168.1.%'; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
Удаление всех прав пользователя
|
1 |
MariaDB [(none)]> revoke all privileges, grant option from 'winxp'@'192.168.1.%'; |
|
1 |
Query OK, 0 rows affected (14.22 sec) |
Удаление пользователя(предварительно не обходимо удалить у него все права)
|
1 |
MariaDB [(none)]> drop user 'winxp'@'192.168.1.%'; |
|
1 |
Query OK, 0 rows affected (3.77 sec) |
Выдача всех прав на базу joomla_on для пользователя winxp,подключенного с сети 192.168.1.0/24 с паролем 123456789
|
1 |
MariaDB [(none)]> grant all on joomla_on.* to 'winxp'@'192.168.1.%' identified by '123456789'; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
Присваивание нулевого количества привилегий
|
1 |
MariaDB [(none)]> grant usage on joomla_on.* to 'winxp'@'192.168.1.%' identified by '123456789'; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
Смена пароля для существующего пользователя
|
1 |
MariaDB [(none)]> set password for 'winxp'@'192.168.1.%' = PASSWORD('new_password'); |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
Смена пароля для текущего пользователя(под которым подключен к MySQL)
|
1 |
MariaDB [(none)]> set password = PASSWORD('new_password'); |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
Принцип проверки подлинности паролей MySQL
Пароли хранятся в графе Password таблицы привилегий mysql.user и шифруются посредством MYSQL-функции PASSWORD(). Когда какой-либо пользователь входит на сервер MySQL, введя при этом пароль, MySQL сначала шифрует строку этого пароля при помощи функции PASSWORD(), а затем сравнивает получившийся результат со значением в графе Password записи с информацией о пользователе в таблице user.
Если эти значения совпадут(либо,если другие правила доступа позволят), то пользователь получит доступ. Если же они не совпадут, в доступе будет отказано.
Восстановление забытого пароля root для MySQL
|
1 |
# /etc/init.d/mysqld stop |
|
1 |
# /usr/bin/mysqld_safe --skip-grant-tables --skip-networking |
для Ubuntu mysqld
|
1 |
--skip-grant-tables |
|
1 2 |
131118 20:55:55 mysqld_safe Logging to '/var/log/mysqld.log'. 131118 20:55:55 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql |
В другой консоли
|
1 |
# mysql |
|
1 |
MariaDB [(none)]> |
|
1 |
MariaDB [(none)]> UPDATE mysql.user SET password=PASSWORD("newpassword") WHERE User='root'; |
|
1 |
# /etc/init.d/mysqld stop |
|
1 |
Stopping mysqld: [ OK ] |
|
1 |
# /etc/init.d/mysqld start |
|
1 |
Starting mysqld: [ OK ] |
Журнал ошибок
Определение местонахождения журнала ошибок
|
1 |
MariaDB [(none)]> show variables like '%log_error%'; |
|
1 2 3 4 5 |
+---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | log_error | /var/log/mysqld.log | +---------------+---------------------+ |
Общий журнал запросоов
|
1 |
MariaDB [(none)]> show variables like '%general%'; |
|
1 2 3 4 5 6 |
+------------------+---------------+ | Variable_name | Value | +------------------+---------------+ | general_log | OFF | | general_log_file | centos642.log | +------------------+---------------+ |
Данный файл по умолчанию имеет имя hostname.log и располагается в MySQL-каталоге data.
Его имя и расположение можно переопределить с помощью переменной general_log_file
Журнал медленных запросов
Записываем все запросы, продолжительность выполнения которых превышает установленное количество времени(определяемое значением переменной long_query_time)
( в данном случае более 5 секунд)
|
1 |
MariaDB [(none)]> show variables like '%slow_query_log%'; |
|
1 2 3 4 5 6 |
+---------------------+--------------------------------+ | Variable_name | Value | +---------------------+--------------------------------+ | slow_query_log | ON | | slow_query_log_file | /var/log/mysqld-slow-query.log | +---------------------+--------------------------------+ |
|
1 |
MariaDB [(none)]> show variables like '%long_query%'; |
|
1 2 3 4 5 |
+-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 5.000000 | +-----------------+----------+ |
Двоичный(бинарный) журнал
Запись всех команд, которые изменяют табличные данные
|
1 |
(insert,replace,delete,grant,revoke,update,create table,drop table) |
может сохранятся в двоичном журнале, который обеспечивает более эффективный формат хранения данных и позволяет фиксировать более крупные объемы информации.
|
1 |
MariaDB [(none)]> show variables like 'log_bin'; |
|
1 2 3 4 5 |
+---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+ |
После активации использования двоичного журнала, его содержимое можно посмотреть с помощью утилиты mysqlbinlog
|
1 |
# mysqlbinlog /var/lib/mysql/centos642.000001 |
|
1 2 3 |
…………………………………………… create database testbase …………………………………………… |
|
1 |
MariaDB [(none)]> show master logs; |
|
1 2 3 4 5 6 |
+------------------+-----------+ | Log_name | File_size | +------------------+-----------+ | centos642.000001 | 336 | +------------------+-----------+ 1 row in set (0.00 sec) |
Если необходимо выполнить запросы, которые не должно быть записаны в двоичный журнал, тогда устанавливаем сессионную переменную sql_log_bin в значение 0
|
1 |
MariaDB [(none)]> set sql_log_bin=0; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
После чего все запросы, выполненные в этом сеансе, не будут записываться в двоичный журнал
|
1 |
MariaDB [(none)]> drop database testbase; |
|
1 |
Query OK, 0 rows affected (0.00 sec) |
|
1 |
# mysqlbinlog /var/lib/mysql/centos642.000001 |
|
1 |
create database testbase |
Теперь можно восстановить данные/структуру из бинарного журнала
#Восстановление с бинарного журнала
|
1 |
# mysqlbinlog /var/lib/mysql/centos642.000001 | mysql -u root –p123456789 |
проверка
|
1 |
# mysql -u root -p123456789 -e "show databases" | grep testbase |
|
1 |
testbase |
Проверка таблиц
При работающем MySQL-сервере используем команду check table
|
1 |
MariaDB [(none)]> check table joomla_on.hzesp_languages; |
При остановленном MySQL-сервере для таблиц с механизмом хранения данных MyISAM-таблиц можно использовать утилиту myisamchk указывая для проверки файл *.MYI(где содержатся индексы), а не файл MYD(где содержится инфорамция(данные))
|
1 |
# myisamchk /var/lib/mysql/joomla_on/hzesp_languages.MYI |
Восстановление таблиц
Для MyISAM-таблиц
|
1 |
# myisamchk --recover|-safe-recover|-quick /path/to/table/*.MYI |
Опции восстановления
|
1 |
--recover |
– ремонт и восстановление(стандартное восстановление)
|
1 |
--safe-recover |
– безопасный режим восстановления (медленнее тщательное восстановление)
|
1 |
--quick |
– быстрое восстановление(контролируются только индексные файлы, но не файлы данных)
|
1 |
# myisamchk --recover /var/lib/mysql/joomla_on/hzesp_languages.MYI |
repair table
|
1 |
repair table <tablename> [quick|extended] |
|
1 |
MariaDB [(none)]> repair table joomla_on.hzesp_languages; |
Оптимизация таблиц
|
1 |
MariaDB [(none)]> optimize table joomla_on.hzesp_languages; |
Проверка, исправление ошибок и оптимизация всех баз данных
|
1 |
# mysqlcheck -u root -p --auto-repair --optimize --all-databases |
Резервоное копирование и восстановление описано в отдельной статье. Использование mysqldump
Подсчет размера баз данных
|
1 |
mysql> SELECT table_schema "database_name", sum( data_length + index_length )/1024/1024 "Data Base Size in MB" FROM information_schema.TABLES GROUP BY table_schema; |
Подсчет размера конкретной базы данных
|
1 |
mysql> SELECT table_schema `Database`, Round(Sum(data_length + index_length) / 1024 / 1024, 1) `Size in MB` FROM information_schema.TABLES where table_schema = "database_name"; |
Подсчет размера таблиц в базе данных mydatabase
|
1 |
mysql> SELECT TABLE_NAME, table_rows, data_length, index_length, round(((data_length + index_length) / 1024 / 1024),2) "Size in MB" FROM information_schema.TABLES WHERE table_schema = "mydatabase"; |
Подсчет размера конкретной таблицы
|
1 |
mysql> SELECT table_name AS `Table`, round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` FROM information_schema.TABLES WHERE table_schema = "database_name" AND table_name = "table_name"; |
Подсчет контрольной суммы таблицы
Эта команда особенно полезна, если у вас настроена MySQL-репликация и вы хотите убедиться что данные согласованы, проверив идентичность таблиц на master и slave.
Во время подсчета контрольной суммы таблица блокируется на чтение (read lock). Чем больше таблица, тем больше времени это может занять.
|
1 |
mysql> CHECKSUM TABLE название_таблицы; |
Подсчет количества строк в таблице
|
1 |
mysql> SELECT COUNT(*) FROM table_name; |
Опубликовано в рубрике 
Метки: