RTFM.WIKI

Ordnung muß sein. Ordnung über alles (18+)

Инструменты пользователя

Инструменты сайта

Вы находитесь здесь: RTFM вики - база знаний про Linux/Windows/OSX, системное администрирование и прочее айти » Меню категории Linux » Меню категории MySQL » MySQL - tips'n'tricks
Stylesheet conf/userstyle.css not found, please contact the developer of "dokuwiki_2024" template.
linux:mysql:mysql_tnt

Различия

Показаны различия между двумя версиями страницы.

Ссылка на это сравнение

linux:mysql:mysql_tnt [2019/03/27 18:32] – внешнее изменение 127.0.0.1linux:mysql:mysql_tnt [2021/10/19 16:56] (текущий) – dx
Строка 1: Строка 1:
 +====== 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/|лог авторизаций]]
Сделано в 🇷🇺 / Размещено в NQhost / Работает на DokuWiki
ipv6 ready CC BY NC ND