Miscellaneous

Changing Configuration Settings in MySQL

Changing configuration settings in MySQL wasn’t always easy. Of course it’s possible to change things (hopefully), but keeping track of everything is not always obvious. This is where configuration management systems like puppet, chef, ansible, … excels in making our life easier.

/etc/my.cnf

With MySQL 8.0, we worked in making your life easier (and the life of configuration management systems easier too).

Let’s first illustrate the problematic very naively:

As you can see, we can modify this configuration variable, but after a restart of MySQL, this change is lost, this behavior, of course is something known by all MySQL DBAs. The usual solution is then to also modify the configuration file (/etc/my.cnf in most cases).

SET PERSIST

We have added a new feature allowing to modify a configuration variable and make the change persistent:

Above, we can see that the change persists even after a restart. We have added SET PERSIST that will change the variable and store it somewhere so value will survive a crash or a restart. Also I hope you noticed a new feature: RESTART (since MySQL 8.0.4). Yes, before when you had to restart MySQL, you had to do it via an access to the operating system or using a GUI made available for you. This is the case when you use the cloud for example. The RESTART command is way more convenient.

SET PERSIST & RESTART

Below you can see a full example of how convenient these two new features in MySQL 8.0 are for the Cloud:

So you could see that it’s possible to change a READ_ONLY variable that will be modified after a restart and perform all this without quitting the MySQL Client. (using SET PERSIST_ONLY)

To change such variable extra privileges are needed:

  • SYSTEM_VARIABLES_ADMIN
  • PERSIST_RO_VARIABLES_ADMIN

If you are curious to know where the changes are stored, in MySQL datadir, there is new file mysqld-auto.cnf that store all the changes in JSON format:

What’s next ?

In the next version, we will also keep track of who, where and when the change was made:

And as now, it will be possible to use a performance_schema table to query such information from the MySQL Client (after restart):

mysql> select * from performance_schema.variables_info where variable_source like 'PERSISTED'G
*************************** 1. row ***************************
VARIABLE_NAME: innodb_buffer_pool_size
VARIABLE_SOURCE: PERSISTED
VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf
MIN_VALUE: 5242880
MAX_VALUE: 9223372036854775807
SET_TIME: 2018-01-16 18:59:25
SET_USER: root
SET_HOST: localhost
*************************** 2. row ***************************
VARIABLE_NAME: innodb_log_file_size
VARIABLE_SOURCE: PERSISTED
VARIABLE_PATH: /var/lib/mysql/mysqld-auto.cnf/mysqld-auto.cnf
MIN_VALUE: 4194304
MAX_VALUE: 18446744073709551615
SET_TIME: 2018-01-16 18:58:47
SET_USER: root
SET_HOST: localhost

I think these two new features when combined will really improve MySQL usability in the Cloud.

And of course, now the question we could ask ourselves is what is the best way to configure your MySQL server? There is not yet a best practice, and as almost always when talking about MySQL, it depends…

My own suggestion would be to have the initial configuration (after install and first tuning) in /etc/my.cnf and then set everything from the client (even with configuration management systems). But this is my own opinion.

Source link

Leave a Reply

Your email address will not be published. Required fields are marked *