Every MySQL should have these variables set …

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
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.


 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 |

21235301| a55faa32-c832-22e8-b6fb-e51f15b76554 |
+———–+————-+——+———–+————————————–+

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;

Make sure you add to my.cnf to you do not lose binlog and position at a restart. It will default to FILE otherwise.

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     |        | 
+-------------------+---------------------+------+-----+---------+-------+

Turn off log_queries_not_using_indexes until needed

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


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

https://dev.mysql.com/doc/refman/5.6/en/innodb-online-ddl-operations.html

Side note — USE mysql_config_editor

Previous blog post about this is here

https://anothermysqldba.blogspot.com/2013/05/mysql-users-grants-mysqlconfigeditor.html

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

via Planet MySQL
Every MySQL should have these variables set …