Различия

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

Посмотреть различия двумя колонкамивнутри текста Go

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

2021/10/19 16:56 dx 2019/03/27 18:32 внешнее изменениеGo		2021/10/19 16:56 dx 2019/03/27 18:32 внешнее изменениеGo
linux:mysql:mysql_tnt [2019/03/27 18:32] – внешнее изменение 127.0.0.1		linux: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/|лог авторизаций]]