RTFM.WIKI

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

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

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


linux:mysql:mysql_tnt

MySQL - tips'n'tricks

Разное

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

query_cache_size

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

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

query_cache_type=0

Для полного отключения нужно выставить размер кэша равным нулю

query_cache_size=0

Ротация slow query 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

А вообще читайте правильные блоги, а не всякие сомнительные wiki.

wtf 1 2 3

Вытащить базу из --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

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

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-модель используется при высокоуровневом (концептуальном) проектировании баз данных. С её помощью можно выделить ключевые сущности и обозначить связи, которые могут устанавливаться между этими сущностями. (wikipedia)

Подробнее о ER-модели можно почитать здесь.

Оказывается даже phpmyadmin такое умеет.

MySQL Workbench очень даже хорош. Пример работы WB.

Еще есть Valentina Studio и DbSchema

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

Как изменить размер лога 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

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

$ 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)

Логи

Как включить логи в 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%';

Мало кто знает, но есть еще чудесный лог авторизаций

Обсуждение

Ваш комментарий. Вики-синтаксис разрешён:
 
linux/mysql/mysql_tnt.txt · Последнее изменение: 2021/10/19 16:56 — dx