MySQL 8.0: changing configuration easily and cloud friendly !

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.

Subscribe to Blog via Email

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

9 Comments

  1. Hi Lefred,

    Such cool features, the way our compliance work is by tagging services with database features

    ls -l /etc/mysql/*.cnf | awk ‘{ print $9 }’
    /etc/mysql/aria.cnf
    /etc/mysql/audit.cnf
    /etc/mysql/backup.cnf
    /etc/mysql/compressbinlog.cnf
    /etc/mysql/compressinnodb.cnf
    /etc/mysql/groupreplication.cnf
    /etc/mysql/innodb.cnf
    /etc/mysql/loggeneral.cnf
    /etc/mysql/logs.cnf
    /etc/mysql/logslow.cnf
    /etc/mysql/logsqlerror.cnf
    /etc/mysql/lowercasetable.cnf
    /etc/mysql/multidomains.cnf
    /etc/mysql/my.cnf
    /etc/mysql/myrock.cnf
    /etc/mysql/mysqlgtid.cnf
    /etc/mysql/network.cnf
    /etc/mysql/nodoublewrite.cnf
    /etc/mysql/nodurable.cnf
    /etc/mysql/nologslaveupdates.cnf
    /etc/mysql/noquerycache.cnf
    /etc/mysql/optimizer.cnf
    /etc/mysql/path.cnf
    /etc/mysql/pfs.cnf
    /etc/mysql/rpl_ptr.cnf
    /etc/mysql/security.cnf
    /etc/mysql/semisync.cnf
    /etc/mysql/sharedpool.cnf
    /etc/mysql/smallredolog.cnf
    /etc/mysql/sphinx.cnf
    /etc/mysql/spider.cnf
    /etc/mysql/sqlmodeoracle.cnf
    /etc/mysql/sqlmodeunstrict.cnf
    /etc/mysql/ssl.cnf
    /etc/mysql/threadpool.cnf
    /etc/mysql/tokudb.cnf
    /etc/mysql/wsrep.cnf

    Then we have a set of symlink if the tag is present in compliance

    ls -l /etc/mysql/rc.d/ | awk ‘{ print $9 $10 $11}’

    01_backup.cnf->../backup.cnf
    01_innodb.cnf->../innodb.cnf
    01_semisync.cnf->../semisync.cnf
    01_sphinx.cnf->../sphinx.cnf
    01_ssl.cnf->../ssl.cnf
    01_threadpool.cnf->../threadpool.cnf
    50_sqlmodeunstrict.cnf->../sqlmodeunstrict.cnf
    99_logslow.cnf->../logslow.cnf
    99_lowercasetable.cnf->../lowercasetable.cnf
    99_nodoublewrite.cnf->../nodoublewrite.cnf
    99_noquerycache.cnf->../noquerycache.cnf
    99_path.cnf->../path.cnf
    99_smallredolog.cnf->../smallredolog.cnf

    This can lead to duplicate variables applied based on the last found for the order by symlink , I’m curious to see if those features would work in such valid configuration case ??

    Thanks for sharing !

  2. This is an awesome feature and definitely going to make many DBAs lives so much easier…. Two questions though, does the SET PERSIST persist multiple reboots and what would be the best way to remove a persisted variable if I no longer wanted it to persist?

  3. SET PERSIST does survive multiple reboots. To remove a persisted variable we have RESET PERSIST. Please see below example:
    SET PERSIST innodb_buffer_pool_size=10*1024*1024;
    RESET PERSIST innodb_buffer_pool_size;
    If you want to remove all persisted variables simply do RESET PERSIST;

  9. […] RESTARTコマンドが実装されました。この目的は、SQL接続でのMySQLのリモートマネジメントができるようにすることであり、例えば動的でないシステム変数をSET PERSISTで変更し、RESTARTする、ということができます。Frederic Descampsによる、MySQL 8.0: changing configuration easily and cloud friendly !のブログ記事をご覧ください。 […]

Leave a Reply

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

As MySQL Community Manager, I am an employee of Oracle and the views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

You can find articles I wrote on Oracle’s blog.

Continue