RTFM.WIKI

Эта страница только для чтения. Вы можете посмотреть её исходный текст, но не можете его изменить. Сообщите администратору, если считаете, что это неправильно.
====== MySQL - tips'n'tricks ======

{{:linux:mysql:mysql_logo.png?nolink |}}

См. также [[linux:mysql:mysql_errors|MySQL - коллекция ошибок и фиксов]]

===== Разное =====

  * [[http://grimoire.ca/mysql/choose-something-else|Do not pass this way again]]
  * [[https://www.facebook.com/notes/mysql-at-facebook/my-mysql-is-faster-than-your-mysql/10151250402570933|My MySQL is faster than your MySQL]]
  * http://seyferseed.ru/ru/php/soglasovanie-vremenny-h-zon-mezhdu-prilozheniem-i-mysql.html#sthash.8yuF5FCG.SZUVEbml.dpbs

===== HowTo - Как сделать xyz? =====

==== query_cache_size ====

[[https://oracle-patches.com/db/mysql/как-грамотно-настроить-query_cache_size-в-mysql-и-mariadb|Как грамотно настроить query_cache_size в MySQL и MariaDB]]

==== Как выключить query_cache ====

Недостаточно поставить

<code>query_cache_type=0</code>

Для полного отключения [[https://www.percona.com/blog/2016/11/11/is-your-query-cache-really-disabled/|нужно выставить размер кэша равным нулю]]

<code>query_cache_size=0</code>

==== Ротация slow query logs ====

[[https://oguya.ch/posts/2016-04-13-safely-rotating-mysql-slow-logs/|Safely Rotating MySQL Slow Query Logs]]

Пример файла ''/etc/logrotate.d/mysql-slow-logs''

<code>
/var/lib/mysql/mysql-slow.log {
    size 1G
    dateext
    compress
    missingok
    rotate 20
    notifempty
    delaycompress
    sharedscripts
    nocopytruncate
    create 660 mysql mysql
    postrotate
        /usr/bin/mysql -e 'select @@global.slow_query_log into @sq_log_save; set global slow_query_log=off; select sleep(5); FLUSH SLOW LOGS; select sleep(10); set global slow_query_log=@sq_log_save;'
    endscript
    rotate 150
}
</code>

==== Дамп utf8mb4 ====

<code>mysqldump --default-character-set=utf8mb4 -u username -p database > dump.sql</code>

Для старых версий MySQL нужно использовать ключ совместимости

<code>mysqldump -u username -p --compatible=mysql40 database > dump.sql</code>

<code>mysqldump -u username -p --compatible=mysql323 database > dump.sql</code>

my.cnf

<code>
[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
</code>

А вообще читайте [[https://mathiasbynens.be/notes/mysql-utf8mb4#backup|правильные блоги]], а не всякие сомнительные wiki.

==== wtf 1 2 3 ====

  * [[http://singlemaltsites.com/2014/07/shell-script-to-dump-all-mysql-databases-into-separate-files/|Shell Script to Dump all MySQL Databases Into Separate Files]]

==== Вытащить базу из --all-databases ====

Достать базу **db_name** если бэкап сделан ''mysqldump --all-databases''

<code>sed -n -e '/CREATE DATABASE.*db_name/,/CREATE DATABASE/p' all_databases.sql > db_name.sql</code>

==== Тюнинг MySQL — thread_cache_size ====

Параметр ''thread_cache_size'' играет немаловажную роль в производительности нагруженного MySQL-сервера. В некоторых случаях можно увеличить производительность на 30-50%.

Этот параметр указывает количество тредов, уходящих в кеш при отключении клиента. При новом подключении тред используется из кеша, что позволяет экономить ресурсы при больших нагрузках.

Выявить необходимость оптимизации параметра ''thread_cache_size'' можно следующим путем:

<code>mysqladmin -u root -p extended-status | grep Threads</code>

Вывод

<code>
| Threads_cached    | 13     |
| Threads_connected | 28     |
| Threads_created   | 120461 |
| Threads_running   | 1      |
</code>

Если значение ''Threads_created'' значительно больше, чем ''Threads_cached'' (на тысячи), значит ''thread_cache_size'' или слишком мал, или вовсе отключен.

Оптимальное значение ''thread_cache_size'', при котором ''Threads_created'' держится на приемлемом уровне, колеблется от 8 до 100, в зависимости от нагрузки и количества памяти.

Рекомендуется постепенно повышать значение ''thread_cache_size'' и наблюдать за ''Threads_created'' до тех пор, пока ''Threads_created'' будет незначительно больше ''Threads_cached''.

==== Соответствие версий MySQL-MariaDB ====

^ MySQL ^ MariaDB ^
| 5.5 | 5.5 |
| 5.6 | 10.0/10.1 |
| 5.7 | 10.2 |

Также пригодится

[[https://mariadb.com/kb/en/library/differences-in-mariadb-in-debian-and-ubuntu/|Differences in MariaDB in Debian (and Ubuntu)]]

**System variables**
^ Variable ^ MariaDB in Debian ^ Standard MariaDB ^ Notes ^
| character_set_server | utf8mb4 | latin1 | Debian sets a default character set that can support emojis etc. | 
| collation_server | utf8mb4_general_ci | latin1_swedish_ci |

**Options**
^ Option ^ MariaDB in Debian ^ Standard MariaDB ^ Notes ^
| plugin-load-add | auth_socket.so | - | Debian enables the UNIX_SOCKET Authentication Plugin plugin by default, allowing passwordless login. |
==== Как создать ER диаграмму имеющейся базы ====

<blockquote>Модель сущность-связь (ER-модель) — модель данных, позволяющая описывать концептуальные схемы предметной области. ER-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями. ([[https://ru.wikipedia.org/wiki/ER-модель|wikipedia]])</blockquote>

Подробнее о ER-модели можно почитать [[http://citforum.ru/cfin/prcorpsys/infsistpr_09.shtml|здесь]].

Оказывается даже [[http://sforsuresh.in/generating-erd-using-phpmyadmin/|phpmyadmin такое умеет]].

[[http://wb.mysql.com/|MySQL Workbench]] очень даже хорош. [[https://www.javierrguez.com/en/entity-relationship-diagram-generate-an-existing-mysql-database-with-mysql-workbench/|Пример работы]] WB.

Еще есть [[https://www.valentina-db.com/en/valentina-studio-overview|Valentina Studio]] и [[http://www.dbschema.com/download.html|DbSchema]] 

==== Как перенести myisam или innodb на другой сервер ====

  * https://dev.mysql.com/doc/refman/5.6/en/innodb-migration.html
  * http://serverfault.com/questions/367255/linux-mysql-is-it-safe-to-copy-mysql-db-files-with-cp-command-from-one-db-to

==== Как изменить размер лога innodb (innodb_log_file_size) ====

По-умолчанию установлен размер 5 МБ

<code>
mysql -e "show variables like 'innodb_log_file_size'"
+----------------------+---------+
| Variable_name        | Value   |
+----------------------+---------+
| innodb_log_file_size | 5242880 | 
+----------------------+---------+
</code>

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

<code># /etc/init.d/mysqld stop</code>

Изменяем/добавляем параметр в конфигурационном файле ''/etc/my.cnf''

<code>
[mysqld]
innodb_log_file_size = 64M
</code>

Переименовываем существующие лог файлы

<code>
mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0_bak
mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1_bak
</code>

Запускаем MySQL сервер

<code># /etc/init.d/mysqld start</code>

Проверяем лог файл

<code>
# tail -n 100 /var/log/mysqld.log # для CentOS
...
130730 13:17:35  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 64 MB
InnoDB: Database physically writes the file full: wait...
130730 13:17:35  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 64 MB
InnoDB: Database physically writes the file full: wait...
130730 13:17:37 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
</code>

==== Секретная фича в Percona/MySQL 5.6 ====

[[https://www.percona.com/blog/2013/09/05/my-favorite-secret-mysql-5-6-feature/|Automatic InnoDB transaction log file size change]]

==== Изменить пароль пользователю ====

<code>
$ mysql -h localhost -u root -p

mysql> UPDATE mysql.user SET Password=PASSWORD(‘new_db_password’) WHERE User=’db_user_name’ AND Host=’db_host’;
mysql> FLUSH PRIVILEGES;
mysql> quit;
</code>

==== Max memory usage ====

Обычно считаюат так:

''Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + (read_buffer_size + sort_buffer_size) X max_connections''

Более продвинутый вариант:

''Maximum MySQL Memory Usage = innodb_buffer_pool_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) X max_connections)''

==== Как сбросить root пароль для MySQL ====

https://loginroot.com/recover-mysql-root-password/

Останавливаем MySQL

<code># service mysql stop</code>

Запускаем **mysqld_safe** с опцией **--skip-grant-tables**

<code>
# mysqld_safe --skip-grant-tables &
140606 13:47:58 mysqld_safe Logging to '/var/log/mysqld.log'.
140606 13:47:58 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
140606 13:47:59 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
</code>

Подключаемся к MySQL с правами root. Т.к. MySQL запущен в безопасном режиме, то пароль запрошен не будет

<code># mysql -u root</code>

Выбираем базу данных mysql

<code>mysql> use mysql;</code>

Устанавливаем новый пароль для пользователя root

<code>mysql> update user set password=PASSWORD("Новый_MySQL_root_пароль") where User='root';</code>

Перезагружаем привилегии

<code>mysql> flush privileges;</code>

Отключаемся от MySQL

<code>mysql> quit;</code>

Останавливаем и вновь запускаем MySQL демон

<code>
/etc/init.d/mysql stop && /etc/init.d/mysql start
или
# ps aufx | grep mysql | awk '{print $2}' | xargs kill -9
или
# killall -9  mysqld_safe
</code>

==== Как очистить историю командной строки mysql (~/.mysql_history) ====

http://www.cyberciti.biz/faq/howto-clear-mysql-command-history/

==== Логи ====

Как включить логи в MySQL?

Добавить в ''/etc/my.cnf''

<code>
[mysqld]
log-bin
log
log-error
log-slow-queries
</code>

Файлы будут созданы автоматически в том же каталоге где лежат сами базы (обычно /var/lib/mysql)

Если нужно указать другой путь к лог файлу

<code>
[mysqld]
log-bin          = /var/log/mysql-bin.log
log              = /var/log/mysql.log
log-error        = /var/log/mysql-error.log
log-slow-queries = /var/log/mysql-slowquery.log
</code>

Создадим файлы и выставим права
<code>
# touch /var/log/mysql.log
# chown mysql:mysql /var/log/mysql.log
# touch /var/log/mysql.error.log
# chown mysql:mysql /var/log/mysql-error.log

etc...
</code>

Проверяем установленные переменные ''general_log'', ''general_log_file'', ''log'', ''log_error'', 

<code>mysql> show variables like '%log%';</code>

Мало кто знает, но есть еще чудесный [[https://www.percona.com/blog/2012/12/28/auditing-login-attempts-in-mysql/|лог авторизаций]]