So over the years, we all learn more and more about what we like and use often in MySQL.
Currently, I step in and out of a robust about of different systems. I love it being able to see how different companies use MySQL. I also see several aspect and settings that often get missed. So here are a few things I think should always be set and they not impact your MySQL database.
At a high level:
Move the Slow log to a table
Now you can truncate it or dump it or whatever you like to do with this data easily also.
Note variable values into your my.cnf file to enable upon restart.
Set report_host_name
+-----------+-------------+------+-----------+--------------------------------------+
Set master & slaves to use tables
Make sure you add to my.cnf to you do not lose binlog and position at a restart. It will default to FILE otherwise.
All data is available in tables now and easily stored with backups
Turn off log_queries_not_using_indexes until needed
Currently, I step in and out of a robust about of different systems. I love it being able to see how different companies use MySQL. I also see several aspect and settings that often get missed. So here are a few things I think should always be set and they not impact your MySQL database.
At a high level:
- >Move the Slow log to a table
- Set report_host_name
- Set master & slaves to use tables
- Turn off log_queries_not_using_indexes until needed
- Side note -- USE ALGORITHM=INPLACE
- Side note -- USE mysql_config_editor
- Side note -- USE mysql_upgrade --upgrade-system-tables
Move the Slow log to a table
This is a very simple process with a great return. YES you can use Percona toolkit to analyze the slow logs. However, I like being able to query against the table and find duplicate queries or by times and etc with a simple query call.
mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select @@slow_query_log,@@sql_log_off;
+------------------+---------------+
| @@slow_query_log | @@sql_log_off |
+------------------+---------------+
| 1 | 0 |
+------------------+---------------+
mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)
mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)
mysql> set GLOBAL slow_query_log=0;
Query OK, 0 rows affected (0.04 sec)
mysql> set GLOBAL sql_log_off=1;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE mysql.slow_log ENGINE = MyISAM;
Query OK, 0 rows affected (0.39 sec)
mysql> set GLOBAL slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> set GLOBAL sql_log_off=0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL log_output = 'TABLE';
Query OK, 0 rows affected (0.00 sec)
mysql> SET GLOBAL log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
mysql> select @@slow_launch_time;
+--------------------+
| @@slow_launch_time |
+--------------------+
| 2 |
+--------------------+
1 row in set (0.00 sec)
mysql> SELECT SLEEP(10);
+-----------+
| SLEEP(10) |
+-----------+
| 0 |
+-----------+
1 row in set (9.97 sec)
mysql> select count(*) from mysql.slow_log;
+----------+
| count(*) |
+----------+
| 1 |
+----------+
1 row in set (0.00 sec)
mysql> select * from mysql.slow_log\G
*************************** 1. row ***************************
start_time: 2019-03-27 18:02:32
user_host: klarson[klarson] @ localhost []
query_time: 00:00:10
lock_time: 00:00:00
rows_sent: 1
rows_examined: 0
db:
last_insert_id: 0
insert_id: 0
server_id: 502
sql_text: SELECT SLEEP(10)
thread_id: 16586457
Now you can truncate it or dump it or whatever you like to do with this data easily also.
Note variable values into your my.cnf file to enable upon restart.
Set report_host_name
This is a simple my.cnf file edit in all my.cnf files but certainly the slaves my.cnf files. On a master.. this is just set for when it ever gets flipped and becomes a slave.
21235301| a55faa32-c832-22e8-b6fb-e51f15b76554 |
report_host = <hostname> <or whatever you want to call it>
This allows you from the master to do
mysql> show slave hosts;
+-----------+-------------+------+-----------+--------------------------------------+
| Server_id | Host | Port | Master_id | Slave_UUID |
+-----------+-------------+------+-----------+--------------------------------------+
| 21235302 | <hostname> | 3306 | +-----------+-------------+------+-----------+--------------------------------------+
Set master & slaves to use tables
mysql> show variables like '%repository';
+---------------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | FILE |
| relay_log_info_repository | FILE |
+---------------------------+-------+
mysql_slave> stop slave;
mysql_slave> SET GLOBAL master_info_repository = 'TABLE';
mysql_slave> SET GLOBAL relay_log_info_repository = 'TABLE';
mysql_slave> start slave;
- master-info-repository =TABLE
- relay-log-info-repository =TABLE
mysql> show variables like '%repository';
---------------------+-------+
| Variable_name | Value |
+---------------------------+-------+
| master_info_repository | TABLE |
| relay_log_info_repository | TABLE |
+---------------------------+-------+
All data is available in tables now and easily stored with backups
mysql> desc mysql.slave_master_info;
+------------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+---------------------+------+-----+---------+-------+
| Number_of_lines | int(10) unsigned | NO | | NULL | |
| Master_log_name | text | NO | | NULL | |
| Master_log_pos | bigint(20) unsigned | NO | | NULL | |
| Host | char(64) | YES | | NULL | |
| User_name | text | YES | | NULL | |
| User_password | text | YES | | NULL | |
| Port | int(10) unsigned | NO | | NULL | |
| Connect_retry | int(10) unsigned | NO | | NULL | |
| Enabled_ssl | tinyint(1) | NO | | NULL | |
| Ssl_ca | text | YES | | NULL | |
| Ssl_capath | text | YES | | NULL | |
| Ssl_cert | text | YES | | NULL | |
| Ssl_cipher | text | YES | | NULL | |
| Ssl_key | text | YES | | NULL | |
| Ssl_verify_server_cert | tinyint(1) | NO | | NULL | |
| Heartbeat | float | NO | | NULL | |
| Bind | text | YES | | NULL | |
| Ignored_server_ids | text | YES | | NULL | |
| Uuid | text | YES | | NULL | |
| Retry_count | bigint(20) unsigned | NO | | NULL | |
| Ssl_crl | text | YES | | NULL | |
| Ssl_crlpath | text | YES | | NULL | |
| Enabled_auto_position | tinyint(1) | NO | | NULL | |
| Channel_name | char(64) | NO | PRI | NULL | |
| Tls_version | text | YES | | NULL | |
| Public_key_path | text | YES | | NULL | |
| Get_public_key | tinyint(1) | NO | | NULL | |
+------------------------+---------------------+------+-----+---------+-------+
27 rows in set (0.05 sec)
mysql> desc mysql.slave_relay_log_info;
+-------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+---------------------+------+-----+---------+-------+
| Number_of_lines | int(10) unsigned | NO | | NULL | |
| Relay_log_name | text | NO | | NULL | |
| Relay_log_pos | bigint(20) unsigned | NO | | NULL | |
| Master_log_name | text | NO | | NULL | |
| Master_log_pos | bigint(20) unsigned | NO | | NULL | |
| Sql_delay | int(11) | NO | | NULL | |
| Number_of_workers | int(10) unsigned | NO | | NULL | |
| Id | int(10) unsigned | NO | | NULL | |
| Channel_name | char(64) | NO | PRI | NULL | |
+-------------------+---------------------+------+-----+---------+-------+
This was shown above also. This is a valid variable .. but depending on application it can load a slow log with useless info. Some tables might have 5 rows in it, you use it for some random drop down and you never put an index on it. With this enabled every time you query that table it gets logged. Now.. I am a big believer in you should put an index on it anyway. But focus this variable when you are looking to troubleshoot and optimize things. Let it run for at least 24hours so you get a full scope of a system if not a week.
mysql> SET GLOBAL log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)
To turn on
Note variable values into your my.cnf file to enable upon restart.
Side note -- USE ALGORITHM=INPLACE
mysql> SET GLOBAL log_queries_not_using_indexes=0;
Query OK, 0 rows affected (0.00 sec)
To turn on
mysql> SET GLOBAL log_queries_not_using_indexes=1;
Query OK, 0 rows affected (0.00 sec)
Note variable values into your my.cnf file to enable upon restart.
Side note -- USE ALGORITHM=INPLACE
OK this is not a variable but more of a best practice. You should already be using EXPLAIN before you run a query, This shows you the query plan and lets you be sure all syntax is valid. I have seen more than once a Delete query executed without an WHERE by mistake. So 1st always use EXPLAIN to double check what you plan to do. Not the other process you should always do is try to use an ALGORITHM=INPLACE or ALGORITHM=COPY when altering tables.
mysql> ALTER TABLE TABLE_DEMO ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> ALTER TABLE TABLE_DEMO ALGORITHM=INPLACE, ADD INDEX `datetime`(`datetime`);
Query OK, 0 rows affected (1.49 sec)
Records: 0 Duplicates: 0 Warnings: 0
A list of online DLL operations is here
Side note -- USE mysql_config_editor
Side note -- USE mysql_config_editor
Previous blog post about this is here
The simple example
mysql_config_editor set --login-path=local --host=localhost --user=root --password
Enter password:
# mysql_config_editor print --all
[local]
user = root
password = *****
host = localhost
# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
# mysql --login-path=local
Welcome to the MySQL monitor.
# mysql --login-path=local -e 'SELECT NOW()';
Side note -- USE mysql_upgrade --upgrade-system-tables
Don't forget to use mysql_upgrade after you actually upgrade.
This is often forgotten and leads to issues and errors at start up. You do not have to run upgrade across every table that exists though. The focus of most upgrade are the system tables. So at the very least focus on those.
mysql_upgrade --login-path=local --upgrade-system-tables
