Configuring MySQL 8
MySQL has two types of parameters:
Static, which takes effect after restarting MySQL server Dynamic, which can be changed online without restarting MySQL server works in version 5.7 and later.
Variables can be set through the following:
- Config file
- Startup script
- Using SET command
- Persisting Configuration Parameters
Using config file:
The config file can be found in /etc/my.cnf (RHL and CENTOS) and /etc/mysql/my.cnf(Debian), you can edit this file in your choice of editor.
The configuration file has the below sections, the related parameter should be kept under them.
- [mysql]: Section is read by the mysql command-line client
- [client]: Section is read by all connecting clients (including mysql cli)
- [mysqld]: Section is read by the mysqlserver
- [mysqldump]: The section is read by the backup utility called mysqldump
- [mysqld_safe]: Read by the mysqld_safeprocess (MySQL Server Startup Script)
[root@localhost ~]# mysql -e "select @@server_id;" +-------------+ | @@server_id | +-------------+ | 1 |
change the server-id parameter to 2 and restart the mysql server
#vim /etc/my.cnf server-id=2 (edit this parameter in config file) #sudo systemctl restart mysqld [root@localhost ~]# mysql -e "select @@server_id;" +-------------+ | @@server_id | +-------------+ | 2|
Using parameters with the startup script:
Suppose you wish to start MySQL using a startup script and not through systemd,
especially for testing or for some temporary change. You can pass the variables to the script
rather than changing it in the config file
# /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/usr/local/mysql/data/centos7.err --pidfile=/usr/local/mysql/data/centos7.pid --init-file=/tmp/mysql-init &
You can see that the –init-file parameter is passed to the server. The server executes the SQL statements in that file before starting
Using global and session variables:
There are two types of variables based on the scope of the variable:
Global: Applies to all the new connections
Session: Applies only to the current connection (session)
I will use sort_buffer_size for this example because this has global and session-level scope, so this will explain the scope very well.
mysql> SET GLOBAL sort_buffer_size = 512 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> SELECT @@sort_buffer_size; +--------------------+ | @@sort_buffer_size | +--------------------+ | 262144 | +--------------------+ 1 row in set (0.00 sec)
It didn’t change because the sort_buffer size has both global and session-level scope and the global is just a default for new sessions. We were using the same client connection all the way so with the same global we changed the default for new sessions but our session is not new, our session started before the change of the default. So we can use the Select @@global.sort_buffer_size to query the global value
mysql> SELECT @@global.sort_buffer_size; +---------------------------+ | @@global.sort_buffer_size | +---------------------------+ | 524288 | +---------------------------+ 1 row in set (0.00 sec) mysql> SELECT @@global.sort_buffer_size, @@session.sort_buffer_size; +---------------------------+----------------------------+ | @@global.sort_buffer_size | @@session.sort_buffer_size | +---------------------------+----------------------------+ | 524288 | 262144 | +---------------------------+----------------------------+ 1 row in set (0.00 sec)
Persisting Configuration Parameters:
check the sort_buffer_size from the performance schema, the session variables and the global variables table.
mysql> SELECT * FROM performance_schema.session_variables WHERE variable_name LIKE 'sort_buffer_size'; +------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | sort_buffer_size | 262144 | +------------------+----------------+ 1 row in set (0.01 sec) mysql> SELECT * FROM performance_schema.global_variables WHERE variable_name LIKE 'sort_buffer_size'; +------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | sort_buffer_size | 262144 | +------------------+----------------+ 1 row in set (0.01 sec)
Both are the same as we haven’t change it yet. Let check that this value is coming from.
mysql> SELECT * FROM performance_schema.variables_info WHERE variable_name LIKE 'sort_buffer_size'; +------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ | sort_buffer_size | GLOBAL | /etc/my.cnf | 32768 | 18446744073709551615 | NULL | NULL | NULL | +------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ 1 row in set (0.01 sec)
So we can see that the variable is coming from the /etc/my.cnf and the VARIABLE_SOURCE value is GLOBAL as this is not changed yet session picked the global value upon connection.
Let’s change the Global sor_buffer_size and see what will be the result.
mysql> SET GLOBAL sort_buffer_size = 512 * 1024; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM performance_schema.variables_info WHERE variable_name LIKE 'sort_buffer_size'; +------------------+-----------------+---------------+-----------+----------------------+----------------------------+----------+-----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +------------------+-----------------+---------------+-----------+----------------------+----------------------------+----------+-----------+ | sort_buffer_size | DYNAMIC | | 32768 | 18446744073709551615 | 2020-08-09 11:04:01.681846 | root | localhost | +------------------+-----------------+---------------+-----------+----------------------+----------------------------+----------+-----------+ 1 row in set (0.01 sec)
Now as we can see that source is dynamic, SET_TIME has been changed and it’s the root user that has made the changes.
Let’s check a variable that is not in my.cnf like wait timeout.
mysql> SELECT * FROM performance_schema.variables_info WHERE variable_name LIKE 'wait_timeout'; +---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+ | wait_timeout | COMPILED | | 1 | 31536000 | NULL | NULL | NULL | +---------------+-----------------+---------------+-----------+-----------+----------+----------+----------+ 1 row in set (0.00 sec)
You can see that the variable source, in this case, it is COMPILED which means that we are using the server’s default value. Let’s change it to something else.
mysql> SET GLOBAL wait_timeout=100; Query OK, 0 rows affected (0.00 sec)
Now if we check again the source will be dynamic, there we go.
mysql> SELECT * FROM performance_schema.variables_info WHERE variable_name LIKE 'wait_timeout'; +---------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +---------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+ | wait_timeout | DYNAMIC | | 1 | 31536000 | 2020-08-09 11:08:57.537268 | root | localhost | +---------------+-----------------+---------------+-----------+-----------+----------------------------+----------+-----------+ 1 row in set (0.01 sec)
Let’s make this persistent with the PERSIST command. NOTE we haven’t added these changes in my.cnf file
mysql> SET PERSIST wait_timeout=100; Query OK, 0 rows affected (0.00 sec)
Exit from MySQL client and check that the setting is indeed in mysqld-auto.cnf.
[root@localhost ~]# sudo cat /var/lib/mysql/mysqld-auto.cnf
{ "Version" : 1 , "mysql_server" : { "mandatory_roles" : { "Value" : "dbt3_read23" , "Metadata" : { "Timestamp" : 1592984803211900 , "User" : "root" , "Host" : "localhost" } } , "wait_timeout" : { "Value" : "100" , "Metadata" : { "Timestamp" : 1596960656007284 , "User" : "root" , "Host" : "localhost" } } } }
Restart the mysql server and check the path from the wait_timeout variable value is coming, it will be coming from /var/lib/mysql/mysqld-auto.cnf as shown below.
mysql> SELECT * FROM performance_schema.variables_info WHERE variable_name LIKE 'wait_timeout'; +---------------+-----------------+--------------------------------+-----------+-----------+----------------------------+----------+-----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +---------------+-----------------+--------------------------------+-----------+-----------+----------------------------+----------+-----------+ | wait_timeout | PERSISTED | /var/lib/mysql/mysqld-auto.cnf | 1 | 31536000 | 2020-08-09 11:10:56.007284 | root | localhost | +---------------+-----------------+--------------------------------+-----------+-----------+----------------------------+----------+-----------+ 1 row in set (0.01 sec)
If you check the value for ‘sort_buffer_size’ it’s still the same as we didn’t change it’s value, still coming from the same path and source is GLOBAL.
mysql> SELECT * FROM performance_schema.variables_info WHERE variable_name LIKE 'sort_buffer_size'; +------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ | VARIABLE_NAME | VARIABLE_SOURCE | VARIABLE_PATH | MIN_VALUE | MAX_VALUE | SET_TIME | SET_USER | SET_HOST | +------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ | sort_buffer_size | GLOBAL | /etc/my.cnf | 32768 | 18446744073709551615 | NULL | NULL | NULL | +------------------+-----------------+---------------+-----------+----------------------+----------+----------+----------+ 1 row in set (0.00 sec)