Setting MySQL Configuration Variables - MySQL 5.7 vs MySQL 8.0
In this article, we will explain the differences in managing the configuration variables between MySQL 5.7 and MySQL 8.0.
Join the DZone community and get the full member experience.Join For Free
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
- 2: Set and Persist the Configuration Change across MySQL Restarts
- 3: Setting Configuration Variables that are not Dynamic
- MySQL Configuration Management across Multiple Servers
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
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
- 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
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.
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
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. An 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.
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.
Otherwise, RESTART command fails with a message like the following.
Published at DZone with permission of Anton Bjorkman. See the original article here.
Opinions expressed by DZone contributors are their own.