====== 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 ==== Недостаточно поставить query_cache_type=0 Для полного отключения [[https://www.percona.com/blog/2016/11/11/is-your-query-cache-really-disabled/|нужно выставить размер кэша равным нулю]] query_cache_size=0 ==== Ротация 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'' /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 } ==== Дамп utf8mb4 ==== mysqldump --default-character-set=utf8mb4 -u username -p database > dump.sql Для старых версий MySQL нужно использовать ключ совместимости mysqldump -u username -p --compatible=mysql40 database > dump.sql mysqldump -u username -p --compatible=mysql323 database > dump.sql my.cnf [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 А вообще читайте [[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'' sed -n -e '/CREATE DATABASE.*db_name/,/CREATE DATABASE/p' all_databases.sql > db_name.sql ==== Тюнинг MySQL — thread_cache_size ==== Параметр ''thread_cache_size'' играет немаловажную роль в производительности нагруженного MySQL-сервера. В некоторых случаях можно увеличить производительность на 30-50%. Этот параметр указывает количество тредов, уходящих в кеш при отключении клиента. При новом подключении тред используется из кеша, что позволяет экономить ресурсы при больших нагрузках. Выявить необходимость оптимизации параметра ''thread_cache_size'' можно следующим путем: mysqladmin -u root -p extended-status | grep Threads Вывод | Threads_cached | 13 | | Threads_connected | 28 | | Threads_created | 120461 | | Threads_running | 1 | Если значение ''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 диаграмму имеющейся базы ====
Модель сущность-связь (ER-модель) — модель данных, позволяющая описывать концептуальные схемы предметной области. ER-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями. ([[https://ru.wikipedia.org/wiki/ER-модель|wikipedia]])
Подробнее о 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 МБ mysql -e "show variables like 'innodb_log_file_size'" +----------------------+---------+ | Variable_name | Value | +----------------------+---------+ | innodb_log_file_size | 5242880 | +----------------------+---------+ Останавливаем MySQL сервер # /etc/init.d/mysqld stop Изменяем/добавляем параметр в конфигурационном файле ''/etc/my.cnf'' [mysqld] innodb_log_file_size = 64M Переименовываем существующие лог файлы mv /var/lib/mysql/ib_logfile0 /var/lib/mysql/ib_logfile0_bak mv /var/lib/mysql/ib_logfile1 /var/lib/mysql/ib_logfile1_bak Запускаем MySQL сервер # /etc/init.d/mysqld start Проверяем лог файл # 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! ==== Секретная фича в 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]] ==== Изменить пароль пользователю ==== $ 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; ==== 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 # service mysql stop Запускаем **mysqld_safe** с опцией **--skip-grant-tables** # 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 Подключаемся к MySQL с правами root. Т.к. MySQL запущен в безопасном режиме, то пароль запрошен не будет # mysql -u root Выбираем базу данных mysql mysql> use mysql; Устанавливаем новый пароль для пользователя root mysql> update user set password=PASSWORD("Новый_MySQL_root_пароль") where User='root'; Перезагружаем привилегии mysql> flush privileges; Отключаемся от MySQL mysql> quit; Останавливаем и вновь запускаем MySQL демон /etc/init.d/mysql stop && /etc/init.d/mysql start или # ps aufx | grep mysql | awk '{print $2}' | xargs kill -9 или # killall -9 mysqld_safe ==== Как очистить историю командной строки mysql (~/.mysql_history) ==== http://www.cyberciti.biz/faq/howto-clear-mysql-command-history/ ==== Логи ==== Как включить логи в MySQL? Добавить в ''/etc/my.cnf'' [mysqld] log-bin log log-error log-slow-queries Файлы будут созданы автоматически в том же каталоге где лежат сами базы (обычно /var/lib/mysql) Если нужно указать другой путь к лог файлу [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 Создадим файлы и выставим права # 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... Проверяем установленные переменные ''general_log'', ''general_log_file'', ''log'', ''log_error'', mysql> show variables like '%log%'; Мало кто знает, но есть еще чудесный [[https://www.percona.com/blog/2012/12/28/auditing-login-attempts-in-mysql/|лог авторизаций]]