RTFM.WIKI

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

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

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


Боковая панель


Навигация

Линкшэринг

ALARM!

Добавить новую страницу

You are not allowed to add pages
linux:mysql:mysql_tnt


MySQL - tips'n'tricks

Разное

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

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

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

This website uses cookies. By using the website, you agree with storing cookies on your computer. Also you acknowledge that you have read and understand our Privacy Policy. If you do not agree leave the website.More information about cookies

Discussion

Enter your comment. Wiki syntax is allowed:
 
linux/mysql/mysql_tnt.txt · Последнее изменение: 2019/03/27 18:32 (внешнее изменение)