MySQL configuration variables are a set of server system variables used to configure the operation and behavior of the server. In this blog post, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.
We will explain three different ways for setting the configuration variables based on your use-case. Configuration variables that can be set at run time are called Dynamic variables and those that need a MySQL server restart to take effect are called Non-Dynamic variables.
Setting MySQL Configuration Variables |
|---|
1: Set the Configuration for the Current Lifespan of a Running MySQL Server
Most of the MySQL configurations are dynamic in nature and can be set at run time using the SET command. This means that the changes are not persistent and will be lost if MySQL server restarts. This is useful to test the behavior of your config change before making it permanent.
For both MySQL 5.7 and 8.0, you can accomplish this by using the command SET GLOBAL
Example:
mysql> set global max_connect_errors=10000;
2: Set and Persist the Configuration Change across MySQL Restarts
Once you are happy with the configuration change settings, you would want to make it permanent.
In MySQL 5.7, you would need to do this in 2 steps:
- Set the run time configuration setting using the command SET GLOBAL
mysql> set global max_connect_errors=10000;
- Save this change in your my.cnf file by updating the existing entry for max_connect_errors or by adding a new one.
This has become much easier in MySQL 8.0. You can do this in a single step using the command SET PERSIST
mysql> set persist max_connect_errors=10000;How to Set Configuration Variables - MySQL 5.7 vs MySQL 8.0Click To Tweet
This will set the run-time value for the configuration and also persist the change by saving it in the file mysqld-auto.cnf that exists in the data directory. This is a json file and you will now see the following entries in the file.
{
"Version": 1,
"mysql_server": {
"max_connect_errors": {
"Value": "10000",
"Metadata": {
"Timestamp": 1581135119397374,
"User": "sgroot",
"Host": "localhost"
}
}
}
}
Note: The configuration settings present in mysqld-auto.cnf always overrides the values present in the my.cnf file. So, any further changes you make in the my.cnf file for the variable “max_connect_errors” do not take effect. This may be confusing for those transitioning from MySQL 5.7 as they may be used to storing all their settings in my.cnf
3: Setting Configuration Variables that are not Dynamic
Some of the configuration variables cannot be set at run time and would require a MySQL restart to take effect.
In MySQL 5.7, you would make an entry for these variables in your my.cnf file and restart MySQL server for it to take effect. Example for such a variable is innodb_log_file_size.
In MySQL 8.0, you can run a command called SET PERSIST ONLY which will make an entry in mysqld-auto.cnf.
Example:
mysql> set persist_only innodb_log_file_size=134217728;
It is also possible to restart the MySQL server from the command line using the RESTART command. This will cause the changed value of innodb_log_file_size to take effect.
Note: The RESTART command works only if MySQL is managed using external programs like systemd or mysqld_safe. Please see more details on this here.
Otherwise , RESTART command fails with a message like the following.
mysql> RESTART; ERROR 3707 (HY000): Restart server failed (mysqld is not managed by supervisor process).
MySQL Configuration Management across Multiple Servers
Managing MySQL configuration in source-replica environments is a tedious process if you have to manage multiple clusters that are running different MySQL versions. This is where a managed solution like ScaleGrid would be beneficial.
The ScaleGrid UI console can be used to review the current settings of various configuration variables or set their values.
ScaleGrid can recognize when a configuration setting is non-dynamic and will warn the user if a MySQL restart is required for the value to take effect. ScaleGrid will also make a backup of the current my.cnf file before applying any new configuration changes.
In source-replica environments, ScaleGrid changes the configuration settings in a rolling fashion one server at a time. If a non-dynamic variable needs to be set, ScaleGrid performs a failover of the current master to minimize the downtime that is involved if MySQL has to be otherwise restarted.
Visit the link below find out more about the different features of the ScaleGrid MySQL hosting solution.
