Install MySQL 8 on Linux with lower_case_table_names = 1

MySQL stores several files on disk. Even in MySQL 8 where the data dictionary is stored in InnoDB tables, there are still all the tablespace files. Different file system behave differently, and one particular challenge is case sensitivity. On Microsoft Windows, the case does not matter, on Linux the case is important, and on macOS the case of the file names is preserved but the operating system by default makes it look like it is case insensitive.

Which convention that is the correct depends on your personal preference and use case. Between case sensitivity and case insensitivity, it basically boils down to whether mydb, MyDB, and MYDB should be the same identifier or three different ones. Since MySQL originally relied on the file system for its data dictionary, the default was to rely on the case sensitivity of the file system. The option lower_case_table_names was introduced to override the behaviour. The most common use is to set lower_case_table_names to 1 on Linux to introduce case insensitive schema and table names.

Dolphin with lower_case_table_names

This blog will first discuss how lower_case_table_names work in MySQL 8 – it is not the same as in earlier versions. Then it will be shown how MySQL 8 can be initialized on Linux to use case insensitive identifiers.

Advice

To use case insensitive identifiers in MySQL 8, the main thing is that you must set lower_case_table_names = 1 in your MySQL configuration file before you initialize the data directory (this happens on the first start when using systemd).

MySQL 8 and lower_case_table_names

In MySQL 8, it is no longer allowed to change the value of the lower_case_table_names option after the data directory has been initialized. This is a safety feature – as described in the reference manual:

It is prohibited to start the server with a lower_case_table_names setting that is different from the setting used when the server was initialized. The restriction is necessary because collations used by various data dictionary table fields are based on the setting defined when the server is initialized, and restarting the server with a different setting would introduce inconsistencies with respect to how identifiers are ordered and compared.

https://dev.mysql.com/doc/refman/en/server-system-variables.html#sysvar_lower_case_table_names

If you try to start MySQL 8 with a different value of lower_case_table_names than MySQL was initialized, you will get an error like (from the MySQL error log):

2019-04-14T03:57:19.095459Z 1 [ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
2019-04-14T03:57:19.097773Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2019-04-14T03:57:19.098425Z 0 [ERROR] [MY-010119] [Server] Aborting
2019-04-14T03:57:20.784893Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.15)  MySQL Community Server - GPL.

So what are the steps to initialize MySQL 8 with lower_case_table_names = 1? Let’s go through them.

Installing MySQL 8 with Case Insensitive Identifier Names

There are several ways to install MySQL 8 on Linux. The steps that will be shown here are:

  1. Install the MySQL repository.
  2. Remove previous installations of MySQL or one of its forks.
  3. Clean the data directory.
  4. Install MySQL 8.
  5. Initialize with lower_case_table_names = 1.

The example commands are from Oracle Linux 7 and also works on Red Hat Enterprise Linux (RHEL) 7 and CentOS 7. The MySQL YUM repository will be used. On other Linux distributions the steps will in general be different, but related.

1. Install the MySQL Repository

MySQL provides repositories for several Linux distributions for the Community Edition. You can download the repository definition files from MySQL Community Downloads. The files can also be accessed directly. For this example the YUM repository definition will be downloaded using wget and then installed using yum:

shell$ wget https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
--2019-04-14 12:28:31--  https://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm
...
HTTP request sent, awaiting response... 200 OK
Length: 25892 (25K) [application/x-redhat-package-manager]
Saving to: ‘mysql80-community-release-el7-2.noarch.rpm’

100%[===========================================================>] 25,892      --.-K/s   in 0.01s   

2019-04-14 12:28:33 (1.76 MB/s) - ‘mysql80-community-release-el7-2.noarch.rpm’ saved [25892/25892]

shell$ yum install mysql80-community-release-el7-2.noarch.rpm
...

Dependencies Resolved

=====================================================================================================
 Package                      Arch      Version     Repository                                  Size
=====================================================================================================
Installing:
 mysql80-community-release    noarch    el7-2       /mysql80-community-release-el7-2.noarch     31 k

Transaction Summary
=====================================================================================================
Install  1 Package

Total size: 31 k
Installed size: 31 k
Is this ok [y/d/N]: y
Downloading packages:
...
  Installing : mysql80-community-release-el7-2.noarch                                            1/1 
  Verifying  : mysql80-community-release-el7-2.noarch                                            1/1 

Installed:
  mysql80-community-release.noarch 0:el7-2                                                           

Complete!

You can now remove the previous installation (if present) and its files.

2. Remove Previous Installations

MySQL or one of its forks may have been installed beforehand. This may even happen as a dependency of another package. You should never have more than one MySQL or fork installed using the package system (yum or rpm on Oracle Linux, RHEL, and CentOS).

Tip

If you need to install different versions of MySQL side by side, use the tarball distributions.

You want to uninstall the existing packages in such a way that you do not remove the programs that depend on it – otherwise you will have to re-install those later. One option is to use the rpm command with the --nodeps option. On Oracle Linux 7, RHEL 7, and CentOS 7 this may look like:

shell$ rpm -e --nodeps mariadb-server-5.5.56-2.el7.x86_64 mariadb-5.5.56-2.el7.x86_64 mariadb-libs-5.5.56-2.el7.x86_64

You can find out which packages are installed using rpm -qa and pass the output through grep to search for the packages of interest.

The next step is to clean out any existing files left behind.

3. Clean the Data Directory

In order to be able to initialize MySQL in step 5., the data directory must be empty. You can choose to use a non-default location for the data directory, or you can re-use the default location which use /var/lib/mysql. If you want to preserve your old data directory, make sure you back it up first!

Warning

Important: If you want to keep your old data files, make sure you back them up before proceeding! All existing files will be permanently lost during this step.

The data directory may have been removed in step 2., but if it has not, you can remove it using the following command:

shell$ rm -rf /var/lib/mysql

Optionally, you can also remove the error log, and if you store files outside the data directory (for example the binary log files or InnoDB log files), you should also remove those. The error log is located in /var/log/; for other files, you will need to check your configuration file (usually /etc/my.cnf).

You are now ready to install the MySQL 8.

4. Install MySQL 8

You can choose between several packages and patch releases (maintenance releases). It is recommended to install the latest patch release. You can see from the release notes which release is the latest. By default, yum will also install the latest release. Which packages you want to install depends on your requirements. The MySQL reference manual includes a list of the available packages with a description of what they include.

In this example, the following packages will be installed:

  • mysql-community-client: Client applications such as the mysql command-line client.
  • mysql-community-common: Some common files for MySQL programs.
  • mysql-community-libs: Shared libraries using the latest version of the API.
  • mysql-community-libs-compat: Shared libraries using the version of the API corresponding to what RPM packages from the Oracle Linux/RHEL/CentOS repositories that depend on MySQL uses. For Oracle Linux 7, RHEL 7, and CentOS 7 this means version 18 (e.g. libmysqlclient.so.18).
  • mysql-community-server: The actual MySQL Server.
  • mysql-shell: MySQL Shell – the second generation command-line client with devops support. This RPM is not listed in the above reference as it is not part of the MySQL Server RPM bundle, however when using the MySQL YUM repository, it can be installed in the same way as the other RPMs.

The yum command thus becomes:

shell$ yum install mysql-community-{client,common,libs,libs-compat,server} mysql-shell
...

Dependencies Resolved

=====================================================================================================
 Package                           Arch         Version            Repository                   Size
=====================================================================================================
Installing:
 mysql-community-client            x86_64       8.0.15-1.el7       mysql80-community            25 M
 mysql-community-common            x86_64       8.0.15-1.el7       mysql80-community           566 k
 mysql-community-libs              x86_64       8.0.15-1.el7       mysql80-community           2.2 M
 mysql-community-libs-compat       x86_64       8.0.15-1.el7       mysql80-community           2.1 M
 mysql-community-server            x86_64       8.0.15-1.el7       mysql80-community           360 M
 mysql-shell                       x86_64       8.0.15-1.el7       mysql-tools-community       9.0 M

Transaction Summary
=====================================================================================================
Install  6 Packages

Total download size: 400 M
Installed size: 1.8 G
Is this ok [y/d/N]: y
Downloading packages:
...
Retrieving key from file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Importing GPG key 0x5072E1F5:
 Userid     : "MySQL Release Engineering <mysql-build@oss.oracle.com>"
 Fingerprint: a4a9 4068 76fc bd3c 4567 70c8 8c71 8d3b 5072 e1f5
 Package    : mysql80-community-release-el7-2.noarch (installed)
 From       : /etc/pki/rpm-gpg/RPM-GPG-KEY-mysql
Is this ok [y/N]: y
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : mysql-community-common-8.0.15-1.el7.x86_64                                        1/6 
  Installing : mysql-community-libs-8.0.15-1.el7.x86_64                                          2/6 
  Installing : mysql-community-client-8.0.15-1.el7.x86_64                                        3/6 
  Installing : mysql-community-server-8.0.15-1.el7.x86_64                                        4/6 
  Installing : mysql-community-libs-compat-8.0.15-1.el7.x86_64                                   5/6 
  Installing : mysql-shell-8.0.15-1.el7.x86_64                                                   6/6 
  Verifying  : mysql-community-libs-compat-8.0.15-1.el7.x86_64                                   1/6 
  Verifying  : mysql-community-common-8.0.15-1.el7.x86_64                                        2/6 
  Verifying  : mysql-community-server-8.0.15-1.el7.x86_64                                        3/6 
  Verifying  : mysql-shell-8.0.15-1.el7.x86_64                                                   4/6 
  Verifying  : mysql-community-client-8.0.15-1.el7.x86_64                                        5/6 
  Verifying  : mysql-community-libs-8.0.15-1.el7.x86_64                                          6/6 

Installed:
  mysql-community-client.x86_64 0:8.0.15-1.el7   mysql-community-common.x86_64 0:8.0.15-1.el7       
  mysql-community-libs.x86_64 0:8.0.15-1.el7     mysql-community-libs-compat.x86_64 0:8.0.15-1.el7  
  mysql-community-server.x86_64 0:8.0.15-1.el7   mysql-shell.x86_64 0:8.0.15-1.el7                  

Complete!

Notice how the GPG key for the MySQL YUM repository is downloaded, and you are requested to verify it is the correct key. This happens, because it is the first time the repository is used. You can also manually add the GPG key using the instructions in Signature Checking Using GnuPG.

You are now ready to the final step: configuring and starting MySQL Server for the first time.

5. Initialize with lower_case_table_names = 1

As mentioned in the introduction to this blog, you need to ensure that lower_case_table_names is configured when MySQL initializes its data directory. When you use systemd to start MySQL, it will happen automatically when you start MySQL with an empty data directory. This means, you should update the MySQL configuration file with the desired value of lower_case_table_names before the first start.

The default location for the MySQL configuration file is /etc/my.cnf. Open this file with your favourite editor and ensure the line lower_case_table_names = 1 is listed in the [mysqld] group:

[mysqld]
lower_case_table_names = 1

Optionally, you can make other changes to the configuration as needed.

Tip

Other than a few capacity settings such as innodb_buffer_pool_size and the configuration of the InnoDB redo logs, the default configuration is a good starting point for most installations.

Now, you can start MySQL:

shell$ systemctl start mysqld

This will take a little time as it includes initializing the data directory. Once MySQL has started, you can retrieve the temporary password for the root account from the MySQL error log:

shell$ grep 'temporary password' /var/log/mysqld.log 
2019-04-14T03:29:00.122862Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: aLxwMUQr%7C,

The temporary password is randomly generated during the initialization to avoid MySQL being left with a known default password. Use this temporary password to log in and set your permanent root password:

shell$ mysql --user=root --host=localhost --password
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.15

Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> ALTER USER root@localhost IDENTIFIED BY 'n3w_$tr0ng_P@s$word';
Query OK, 0 rows affected (0.12 sec)

By default for RPM installations, MySQL has the password validation component installed using the MEDIUM strength policy. This means you will need to use a relatively strong password.

You can now verify that MySQL uses case insensitive schema and table identifiers:

mysql> SELECT @@global.lower_case_table_names;
+---------------------------------+
| @@global.lower_case_table_names |
+---------------------------------+
|                               1 |
+---------------------------------+
1 row in set (0.00 sec)

mysql> SELECT VARIABLE_SOURCE, VARIABLE_PATH
         FROM performance_schema.variables_info
        WHERE VARIABLE_NAME = 'lower_case_table_names';
+-----------------+---------------+
| VARIABLE_SOURCE | VARIABLE_PATH |
+-----------------+---------------+
| GLOBAL          | /etc/my.cnf   |
+-----------------+---------------+
1 row in set (0.01 sec)

mysql> CREATE SCHEMA db1;
Query OK, 1 row affected (0.03 sec)

mysql> use DB1;
Database changed
mysql> CREATE TABLE t1 (id int unsigned NOT NULL PRIMARY KEY);
Query OK, 0 rows affected (0.47 sec)

mysql> INSERT INTO T1 VALUES (1);
Query OK, 1 row affected (0.05 sec)

mysql> SELECT * FROM t1;
+----+
| id |
+----+
|  1 |
+----+
1 row in set (0.01 sec)

The query in lines 9-11 queries what the source of the value of the lower_case_table_names option is. This shows that the value of 1 (from the previous query) is picked up from the /etc/my.cnf file. The rest of the queries show how the db1 schema and the db1.t1 table can be accessed both using lower and upper case.

That is it. Now you can use MySQL Server without having to remember which case was used when a schema object was created.

Reduce MySQL Core Dump Size by Excluding the InnoDB Buffer Pool

When things go horrible wrong and a process crashes, one of the most powerful things to investigate the cause of the crash is a core dump. As the amount of memory allocated to processes such as MySQL has increased – in some cases approaching 1TiB of memory – enabling core dumps can cause problems of their own. MySQL Server 8.0.14 and later supports an option to reduce the size of the core dump which will be discussed in this blog.

Typically the largest single user of memory for MySQL is the InnoDB buffer pool. This is used to cache the data and indexes for tables using the InnoDB storage engine (the default). It is rarely important to know what is stored in the InnoDB buffer pool when investigating a crash, so a good way to reduce the core dump size is to exclude the buffer pool. In MySQL 8.0.14 innodb_buffer_pool_in_core_file configuration option was added for this purpose. It defaults to ON which means the buffer pool is included in the core dumps (same behaviour as in older releases).

Contribution

Thanks for Facebook for the contribution that implemented the innodb_buffer_pool_in_core_file option.

Let’s look a bit more into core dumps, how to enable them, why you may want to exclude the InnoDB buffer pool, and an example.

Information

The innodb_buffer_pool_in_core_file option only applies on Linux with kernel 3.4 and later. For this reason the rest of the blog assumes Linux is used.

Enabling Core Dumps

Core dumps can be enabled by including the core-file option in the MySQL configuration. It is disabled by default. However, on Linux/Unix it is in general not enough to enable core-file to get a core dump in case of a crash as the system will also limit the size of a core dump. Typically this limit is 0 by default.

The details of the steps required to enable core dumps depends on the Linux distribution used. The following will discuss the core-file option and the limit on the core size. However, the exact steps required may differ for your system and possibly include additional steps.

Enabling Core Dumps in MySQL

As mentioned, the option in MySQL to enable core dumps is core-file. This can only be set using the MySQL configuration file (my.cnf or my.ini). For example:

[mysqld]
core-file

After you have updated the configuration file, the change will take effect the next time MySQL is restarted.

Core Size Limit

Linux includes various limitations on the resources a process can use. This helps improve the stability of the system as a whole, but the default limits may be too restrictive for something like a database host where one process uses most of the resources. One of the limitations is the size of the core dump and typically the default limit is 0.

You can verify the current limits for your process using the proc file system, for example (assuming only a single mysqld process on the system):

shell$ sudo cat /proc/$(pidof mysqld)/limits
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             10240                12288                processes 
Max open files            10240                65536                files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       15611                15611                signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us

There are various ways to change the limits for MySQL and which one to use depends on how you start MySQL. If you start MySQL from the command-line, you can simply use the ulimit command first:

shell$ ulimit -c unlimited                                                                                                                                                                                                        

# Start MySQL
                                                                                                                                               
shell$ sudo cat /proc/$(pidof mysqld)/limits | grep 'core file size'
Max core file size        unlimited            unlimited            bytes

This has set the limit for the core file size to unlimited.

Warning

Do not blindly set the size to unlimited. If you configure the limit too high, you may run out of disk space preventing MySQL from restarting.

You can also configure the limit in the /etc/security/limits.conf file or in a new file in /etc/security/limits.d/. This is a better way to persist a setting and allows you for example to configure the limits for a given user. For example to set the core dump file size to unlimited for the mysql user:

mysql    soft    core    unlimited
mysql    hard    core    unlimited

However, on distributions using systemd (including Oracle Linux 7, Red Hat Enterprise Linux (RHEL) 7, and CentOS 7), systemd completely ignores /etc/security/limits.conf. Instead you need to use the service file for the process. For MySQL this is the /usr/lib/systemd/system/mysqld.service file, or if you have multiple instances on one host, /usr/lib/systemd/system/mysqld@.service. If you use Debian, replace mysqld with mysql. The option to set in the service file is LimitCore, for example:

[Service]
LimitCore = infinity

If you use mysqld_safe to start MySQL, you can use the core-file-size option (in the [mysqld_safe] group in the MySQL configuration file or on the command-line) to change the limit. This requires that the hard limit is high enough for the requested limit.

Problems with Core Dumps

Over time the amount of memory allocated to MySQL has grown. This particularly applies to the InnoDB buffer pool – as databases handle more data, there is also a greater need to cache the most used data and indexes in memory. Today, some MySQL instances have total memory allocations approaching 1TiB.

When a core dump happens, the memory allocated to the process is written out to disk. For processes using a large amount of memory, this may take some time. Furthermore, there is a possibility that the system may run out of disk, particularly if repeated crashes occur. The disk space usage can particularly be an issue when MySQL is running as a service as it is likely the core file in that case is written to the data directory. From a support perspective, it is also less than trivial to share a core dump that is several hundred gigabytes large.

Warning

Remember that the core dump is a snapshot of the process memory. This means that any data loaded into memory will be present in the core dump. This is particularly something to consider if you store sensitive data in your database. Even if you have transparent data encryption (TDE) enabled, the data in memory will be decrypted!

Excluding the InnoDB buffer pool from the core dump does reduce the amount of data in the core dump, but some data will still be included, for example because it is stored in memory for sorting or as part of a query result.

The InnoDB buffer pool is in most cases the largest contributor – it can easily contribute 75% or more of the total core dump size. So, excluding the buffer pool can significantly reduce the size of the core dump. I will give an example at the end of the blog.

Example

To finish off this blog, let’s look at an example. In this case, MySQL has been configured with innodb_buffer_pool_size = 2G and data has been loaded, so most of the buffer is in use:

mysql> SELECT SUM(FREE_BUFFERS)
         FROM information_schema.INNODB_BUFFER_POOL_STATS;
+-------------------+
| SUM(FREE_BUFFERS) |
+-------------------+
|                 0 |
+-------------------+
1 row in set (0.02 sec)

And from the output of top

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
11142 mysql     20   0 4121468 2.261g      0 S   4.0 58.6   2:38.50 mysqld 

When MySQL is killed with signal 11 (segmentation fault), a core dump is created. Without innodb_buffer_pool_in_core_file, the core dump is almost 3 gigabytes (see output at the end of the blog).

If the new feature is enabled by adding innodb_buffer_pool_in_core_file to the configuration file or by persisting it

mysql> SET PERSIST innodb_buffer_pool_in_core_file = OFF;
Query OK, 0 rows affected (0.02 sec)

The change takes effect immediately. The core dump will now be much smaller – around the size of the buffer pool smaller:

shell$ ls -lh /var/lib/mysql/core.*
-rw-------. 1 mysql mysql 2.8G Feb 11 20:30 /var/lib/mysql/core.11142
-rw-------. 1 mysql mysql 759M Feb 11 20:47 /var/lib/mysql/core.14456

Here core.11142 is with innodb_buffer_pool_in_core_file enabled and core.14456 with the option disabled.

Tip

Want to know more? There are more details in the reference manual at Excluding Buffer Pool Pages from Core Files and in the blog from the server team: MySQL 8.0: Excluding the Buffer Pool from a Core File.

MySQL 8.0: Persisted Variables

MySQL 8.0 introduced a new feature that allows you to persist configuration changes from inside MySQL. Previously you could execute SET GLOBAL to change the configuration at runtime, but you needed to update your MySQL configuration file in order to persist the change. In MySQL 8.0 you can skip the second step. This blog discuss how this works and how to backup and restore the configuration.

Using SET PERSIST to set a variable and the persisted_variables table in the Performance Schema to get a list of persisted variables.
Using SET PERSIST to set a variable and the persisted_variables table in the Performance Schema to get a list of persisted variables.

Persisting Variables

You persist changes with either the SET PERSIST or SET PERSIST_ONLY statement. The different is that SET PERSIST_ONLY only updates the configuration whereas SET PERSIST essentially combines SET GLOBAL and SET PERSIST_ONLY.

Information

Some variables such as innodb_buffer_pool_instances can only use PERSIST_ONLY, i.e. it requires a restart to make the changes take effect. Still others, such as datadir, requires cryptographically signed SET statements which are available in MySQL 8.0.14 and later.

mysqld-auto.cnf and variables_info

The persisted variables are stored in the file mysqld-auto.cnf located in the data directory using the JSON format. It includes more information than just the persisted value. It also includes information such as who made the change and when. An example file is:

shell$ cat mysqld-auto.cnf 
{ "Version" : 1 , "mysql_server" : { "sort_buffer_size" : { "Value" : "32768" , "Metadata" : { "Timestamp" : 1534230053297668 , "User" : "root" , "Host" : "localhost" } } , "join_buffer_size" : { "Value" : "131072" , "Metadata" : { "Timestamp" : 1534230072956789 , "User" : "root" , "Host" : "localhost" } } , "mysql_server_static_options" : { "slave_parallel_type" : { "Value" : "LOGICAL_CLOCK" , "Metadata" : { "Timestamp" : 1534230099583642 , "User" : "root" , "Host" : "localhost" } } } } }

Since it is JSON, it is easy to reformat to make easier to read, for example:

shell$ cat mysqld-auto.cnf | python -m json.tool
{
    "Version": 1,
    "mysql_server": {
        "join_buffer_size": {
            "Metadata": {
                "Host": "localhost",
                "Timestamp": 1534230072956789,
                "User": "root"
            },
            "Value": "131072"
        },
        "mysql_server_static_options": {
            "slave_parallel_type": {
                "Metadata": {
                    "Host": "localhost",
                    "Timestamp": 1534230099583642,
                    "User": "root"
                },
                "Value": "LOGICAL_CLOCK"
            }
        },
        "sort_buffer_size": {
            "Metadata": {
                "Host": "localhost",
                "Timestamp": 1534230053297668,
                "User": "root"
            },
            "Value": "32768"
        }
    }
}

This information is also available from the performance_schema.variables_info table:

mysql> SELECT VARIABLE_NAME, VARIABLE_SOURCE, sys.format_path(VARIABLE_PATH) AS Path,
              SET_TIME, SET_USER, SET_HOST
         FROM performance_schema.variables_info
  WHERE VARIABLE_NAME IN ('join_buffer_size', 'slave_parallel_type', 'sort_buffer_size');
+---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+
| VARIABLE_NAME       | VARIABLE_SOURCE | Path                                      | SET_TIME                   | SET_USER | SET_HOST  |
+---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+
| join_buffer_size    | DYNAMIC         |                                           | 2018-08-14 17:08:15.526750 | root     | localhost |
| slave_parallel_type | PERSISTED       | @@datadir/mysqld-auto.cnf/mysqld-auto.cnf | 2018-08-14 17:01:39.583642 | root     | localhost |
| sort_buffer_size    | PERSISTED       | @@datadir/mysqld-auto.cnf                 | 2018-08-14 17:00:53.297668 | root     | localhost |
+---------------------+-----------------+-------------------------------------------+----------------------------+----------+-----------+
3 rows in set (0.36 sec)

Notice that the source for join_buffer_size is DYNAMIC whereas the two other variables have the source set to PERSISTED. Why? After all they all three existed in the mysqld-auto.cnf file. DYNAMIC means that the variable was changes since the last restart either using SET GLOBAL or SET PERSIST. Another thing to be aware of is that variables changed with SET PERSIST_ONLY will not show up in variables_info until after the next restart. I will soon get back to show a way to get the variables that have been persisted in one way or another.

Backup and Restore

As a simple way to back up the configuration is simply copy the mysqld-auto.cnf file to a safe location. Similarly, you can restore the configuration by copying it back.

However, what if you want most of the configuration but not everything or you want to edit some of the values? In that case you need another way of exporting the configuration as you should not manually edit mysqld-auto.cnf.

Warning

Do not edit the mysqld-auto.cnf file manually. It should only be changed with SET PERSIST and SET PERSIST_ONLY. If there are any errors in the file, MySQL will refuse to start.

Fortunately as it turns out, it is easy to export all persisted variables. The table performance_schema.persisted_variables includes all variables that has either been read from mysqld-auto.cnf or has been changed with SET PERSIST or SET PERSIST_ONLY since the last restart. The table include the persisted values. For example:

mysql> SELECT * FROM performance_schema.persisted_variables;
+---------------------+----------------+
| VARIABLE_NAME       | VARIABLE_VALUE |
+---------------------+----------------+
| sort_buffer_size    | 32768          |
| join_buffer_size    | 131072         |
| slave_parallel_type | LOGICAL_CLOCK  |
+---------------------+----------------+
3 rows in set (0.01 sec)

This can be used to create SET statements that can be used to recreate the configuration on another instance. For example:

SELECT CONCAT('SET PERSIST_ONLY ', VARIABLE_NAME, ' = ',
              IF(VARIABLE_VALUE REGEXP '^([0-9]+|ON|OFF|YES|NO)$',
                 VARIABLE_VALUE,
                 QUOTE(VARIABLE_VALUE)), ';'
             ) AS SetStmt
  FROM performance_schema.persisted_variables;

Using the mysql command-line client, you can avoid the column names and table format by using the --skip-column-names and --batch options:

shell$ mysql --skip-column-names --batch \
             -e "SELECT CONCAT('SET PERSIST_ONLY ', VARIABLE_NAME, ' = ', IF(VARIABLE_VALUE REGEXP '^([0-9]+|ON|OFF|YES|NO)$', VARIABLE_VALUE, QUOTE(VARIABLE_VALUE)), ';') FROM performance_schema.persisted_variables;" \
             > config.sql

Note

On Microsoft Windows ensure everything is on one line and the backslashes are removed.

Now the file config.sql contains an export of the persisted variables:

shell$ cat config.sql 
SET PERSIST_ONLY sort_buffer_size = 32768;
SET PERSIST_ONLY join_buffer_size = 131072;
SET PERSIST_ONLY slave_parallel_type = 'LOGICAL_CLOCK';

This example creates SET PERSIST_ONLY statement as those will work with all persistable variables. When you replay the SET statements, it will require a restart of MySQL for the changes to take effect. If you want to use SET PERSIST where possible, then you need to take into consideration whether the variable support SET PERSIST.  A list of variables that require SET PERSIST_ONLY are included at the end.

The configuration can now be restored as:

mysql> SOURCE config.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> RESTART;
Query OK, 0 rows affected (0.00 sec)

PERSIST_ONLY Variables

As promised, I will conclude with a list of persistable variables that only supports SET PERSIST_ONLY. As of MySQL 8.0.12 without any plugins installed, the variables are:

back_log
binlog_gtid_simple_recovery
disabled_storage_engines
disconnect_on_expired_password
ft_max_word_len
ft_min_word_len
ft_query_expansion_limit
innodb_adaptive_hash_index_parts
innodb_api_disable_rowlock
innodb_api_enable_binlog
innodb_api_enable_mdl
innodb_autoinc_lock_mode
innodb_buffer_pool_chunk_size
innodb_buffer_pool_instances
innodb_doublewrite
innodb_flush_method
innodb_force_recovery
innodb_ft_aux_table
innodb_ft_cache_size
innodb_ft_min_token_size
innodb_ft_server_stopword_table
innodb_ft_sort_pll_degree
innodb_ft_total_cache_size
innodb_ft_user_stopword_table
innodb_log_file_size
innodb_log_files_in_group
innodb_monitor_disable
innodb_monitor_enable
innodb_monitor_reset
innodb_monitor_reset_all
innodb_numa_interleave
innodb_open_files
innodb_page_cleaners
innodb_purge_threads
innodb_read_io_threads
innodb_rollback_on_timeout
innodb_sort_buffer_size
innodb_sync_array_size
innodb_tmpdir
innodb_use_native_aio
innodb_write_io_threads
large_pages
log_slave_updates
log_syslog
log_syslog_facility
log_syslog_include_pid
log_syslog_tag
lower_case_table_names
max_digest_length
metadata_locks_cache_size
metadata_locks_hash_instances
myisam_mmap_size
myisam_recover_options
mysqlx_bind_address
mysqlx_port
mysqlx_port_open_timeout
mysqlx_socket
mysqlx_ssl_ca
mysqlx_ssl_capath
mysqlx_ssl_cert
mysqlx_ssl_cipher
mysqlx_ssl_crl
mysqlx_ssl_crlpath
mysqlx_ssl_key
ngram_token_size
old
open_files_limit
performance_schema
performance_schema_digests_size
performance_schema_error_size
performance_schema_events_stages_history_long_size
performance_schema_events_stages_history_size
performance_schema_events_statements_history_long_size
performance_schema_events_statements_history_size
performance_schema_events_transactions_history_long_size
performance_schema_events_transactions_history_size
performance_schema_events_waits_history_long_size
performance_schema_events_waits_history_size
performance_schema_max_cond_classes
performance_schema_max_digest_length
performance_schema_max_file_classes
performance_schema_max_file_handles
performance_schema_max_memory_classes
performance_schema_max_mutex_classes
performance_schema_max_rwlock_classes
performance_schema_max_socket_classes
performance_schema_max_sql_text_length
performance_schema_max_stage_classes
performance_schema_max_statement_classes
performance_schema_max_statement_stack
performance_schema_max_thread_classes
performance_schema_session_connect_attrs_size
rbr_exec_mode
relay_log_recovery
relay_log_space_limit
report_host
report_password
report_port
report_user
skip_name_resolve
skip_show_database
slave_skip_errors
ssl_cipher
table_open_cache_instances
thread_handling
thread_stack
tls_version

Changes to Options and Variables in MySQL 5.6

With MySQL 5.6 just gone GA, I thought it would be good to take a look at the changes in options and variables that comes with the new release.

First of all, several of the existing options have get new default values. As James Day already have written a good post about that in his blog, I will refer to that instead of going through the changes. For a general overview of the new features and improvements, the recent blogs by Rob Young and Peter Saitsev are good starting points together with the What is New in MySQL 5.6 page in the Reference Manual are good places to start.

Instead I will focus a little on the new options that has been introduced. The first thing to note is that a in the current 5.5. release (5.5.30) there are 323 variables whereas 5.6 GA (5.6.10) returns 440 rows.

MySQL 5.5.29> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES;
+----------+
| COUNT(*) |
+----------+
|      323 |
+----------+
1 row in set (0.04 sec)

MySQL 5.6.10> SELECT COUNT(*) FROM information_schema.GLOBAL_VARIABLES;
+----------+
| COUNT(*) |
+----------+
|      440 |
+----------+
1 row in set (0.02 sec)

Note

This post is written using the Enterprise versions with the semi-synchronous replication plugins enabled in both versions plus the memcached and password validation plugins in 5.6.

Actually the number of new variables is not 117 but 129 as 12 variables have been removed in 5.6.

So what are all of these 129 new variables good for? Actually there is a good chance that you will never need to touch many of them as the default value is good enough, they simply have been added to provide the value of options already present in 5.5 but not exposed through SHOW GLOBAL VARIABLES, or that they are for features you are not using. If we try to group the new variables the distribution comes out as:

FeatureNew Variables
Global Transaction IDs5
Other Replication19
Memcached Plugin6
Validate Password Plugin6
Other Security Related5
InnoDB54
Optimizer Traces5
Performance Schema15
Exposing Previously Existing Variables2
Other12

New Variables in MySQL 5.6

The 54 new InnoDB variables span a number of different changes and additions such as:

  • New adaptive flushing algorithm
  • Buffer Pool dumps to disk and restore
  • Support for additional checksum algorithms
  • Improvements for compression
  • Full text indexes
  • New monitoring options (the information_schema.metrics table)
  • Configurable page size
  • Persistent statistics
  • Undo logs improvements
  • And more …

For reference I have added a list of the new variables with the release they were introduced and the default value (additionally innodb_print_all_deadlocks is also new, but that was also added to 5.5.30):

+--------------------------------------------------------+--------------+------------------------------------------------------------------------+
| Variable_name                                          | From Version | Default (Linux)                                                        |
+--------------------------------------------------------+--------------+------------------------------------------------------------------------+
| bind_address                                           | 5.6.1        | *                                                                      |
| binlog_checksum                                        | 5.6.2        | NONE                                                                   |
| binlog_max_flush_queue_time                            | 5.6.6        | 0                                                                      |
| binlog_order_commits                                   | 5.6.6        | ON                                                                     |
| binlog_row_image                                       | 5.6.2        | FULL                                                                   |
| binlog_rows_query_log_events                           | 5.6.2        | OFF                                                                    |
| core_file                                              | 5.6.2        | OFF                                                                    |
| daemon_memcached_enable_binlog                         | 5.6.6        | OFF                                                                    |
| daemon_memcached_engine_lib_name                       | 5.6.6        | innodb_engine.so                                                       |
| daemon_memcached_engine_lib_path                       | 5.6.6        |                                                                        |
| daemon_memcached_option                                | 5.6.6        |                                                                        |
| daemon_memcached_r_batch_size                          | 5.6.6        | 1                                                                      |
| daemon_memcached_w_batch_size                          | 5.6.6        | 1                                                                      |
| default_authentication_plugin                          | 5.6.6        | MYSQL_NATIVE_PASSWORD                                                  |
| default_tmp_storage_engine                             | 5.6.2        | InnoDB                                                                 |
| end_markers_in_json                                    | 5.6.5        | OFF                                                                    |
| enforce_gtid_consistency                               | 5.6.9        | OFF                                                                    |
| eq_range_index_dive_limit                              | 5.6.5        | 10                                                                     |
| explicit_defaults_for_timestamp                        | 5.6.6        | OFF                                                                    |
| gtid_executed                                          | 5.6.9        |                                                                        |
| gtid_mode                                              | 5.6.5        | OFF                                                                    |
| gtid_next                                              | 5.6.5        | AUTOMATIC                                                              |
| gtid_purged                                            | 5.6.9        |                                                                        |
| host_cache_size                                        | 5.6.5        | 128                                                                    |
| ignore_db_dirs                                         | 5.6.3        |                                                                        |
| innodb_adaptive_flushing_lwm                           | 5.6.6        | 10                                                                     |
| innodb_adaptive_max_sleep_delay                        | 5.6.3        | 0                                                                      |
| innodb_api_bk_commit_interval                          | 5.6.7        | 5                                                                      |
| innodb_api_disable_rowlock                             | 5.6.6        | OFF                                                                    |
| innodb_api_enable_binlog                               | 5.6.6        | OFF                                                                    |
| innodb_api_enable_mdl                                  | 5.6.6        | OFF                                                                    |
| innodb_api_trx_level                                   | 5.6.6        | 0                                                                      |
| innodb_buffer_pool_dump_at_shutdown                    | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_dump_now                            | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_filename                            | 5.6.3        | ib_buffer_pool                                                         |
| innodb_buffer_pool_load_abort                          | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_load_at_startup                     | 5.6.3        | OFF                                                                    |
| innodb_buffer_pool_load_now                            | 5.6.3        | ON                                                                     |
| innodb_change_buffer_max_size                          | 5.6.2        | 25                                                                     |
| innodb_checksum_algorithm                              | 5.6.3        | innodb                                                                 |
| innodb_cmp_per_index_enabled                           | 5.6.7        | OFF                                                                    |
| innodb_compression_failure_threshold_pct               | 5.6.7        | 5                                                                      |
| innodb_compression_level                               | 5.6.7        | 6                                                                      |
| innodb_compression_pad_pct_max                         | 5.6.7        | 50                                                                     |
| innodb_disable_sort_file_cache                         | 5.6.4        | OFF                                                                    |
| innodb_flush_log_at_timeout                            | 5.6.6        | 1                                                                      |
| innodb_flush_neighbors                                 | 5.6.3        | 1                                                                      |
| innodb_flushing_avg_loops                              | 5.6.6        | 30                                                                     |
| innodb_ft_cache_size                                   | 5.6.4        | 32M                                                                    |
| innodb_ft_enable_diag_print                            | 5.6.4        | OFF                                                                    |
| innodb_ft_enable_stopword                              | 5.6.4        | ON                                                                     |
| innodb_ft_max_token_size                               | 5.6.4        | 84                                                                     |
| innodb_ft_min_token_size                               | 5.6.4        | 3                                                                      |
| innodb_ft_num_word_optimize                            | 5.6.4        | 2000                                                                   |
| innodb_ft_server_stopword_table                        | 5.6.4        | NULL                                                                   |
| innodb_ft_sort_pll_degree                              | 5.6.4        | 2                                                                      |
| innodb_ft_user_stopword_table                          | 5.6.4        | NULL                                                                   |
| innodb_io_capacity_max                                 | 5.6.6        | 2000                                                                   |
| innodb_lru_scan_depth                                  | 5.6.3        | 1024                                                                   |
| innodb_max_dirty_pages_pct_lwm                         | 5.6.6        | 0                                                                      |
| innodb_max_purge_lag_delay                             | 5.6.5        | 0                                                                      |
| innodb_monitor_disable                                 | 5.6.2        |                                                                        |
| innodb_monitor_enable                                  | 5.6.2        |                                                                        |
| innodb_monitor_reset                                   | 5.6.2        |                                                                        |
| innodb_monitor_reset_all                               | 5.6.2        |                                                                        |
| innodb_online_alter_log_max_size                       | 5.6.6        | 128M                                                                   |
| innodb_optimize_fulltext_only                          | 5.6.4        | OFF                                                                    |
| innodb_page_size                                       | 5.6.4        | 16k                                                                    |
| innodb_print_all_deadlocks                             | 5.6.2        | OFF                                                                    |
| innodb_read_only                                       | 5.6.7        | OFF                                                                    |
| innodb_sort_buffer_size                                | 5.6.4        | 1M                                                                     |
| innodb_stats_auto_recalc                               | 5.6.6        | ON                                                                     |
| innodb_stats_persistent                                | 5.6.6        | ON                                                                     |
| innodb_stats_persistent_sample_pages                   | 5.6.2        | 20                                                                     |
| innodb_stats_transient_sample_pages                    | 5.6.2        | 8                                                                      |
| innodb_sync_array_size                                 | 5.6.3        | 1                                                                      |
| innodb_undo_directory                                  | 5.6.3        | .                                                                      |
| innodb_undo_logs                                       | 5.6.3        | 128                                                                    |
| innodb_undo_tablespaces                                | 5.6.3        | 0                                                                      |
| log_bin_basename                                       | 5.6.1        |                                                                        |
| log_bin_index                                          | 5.6.1        |                                                                        |
| log_bin_use_v1_row_events                              | 5.6.6        | OFF                                                                    |
| log_throttle_queries_not_using_indexes                 | 5.6.5        | 0                                                                      |
| master_info_repository                                 | 5.6.2        | FILE                                                                   |
| master_verify_checksum                                 | 5.6.2        | OFF                                                                    |
| metadata_locks_hash_instances                          | 5.6.8        | 8                                                                      |
| optimizer_trace                                        | 5.6.3        | enabled=off,one_line=off                                               |
| optimizer_trace_features                               | 5.6.3        | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselec |
| optimizer_trace_limit                                  | 5.6.3        | 1                                                                      |
| optimizer_trace_max_mem_size                           | 5.6.3        | 16k                                                                    |
| optimizer_trace_offset                                 | 5.6.3        | -1                                                                     |
| performance_schema_accounts_size                       | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_digests_size                        | 5.6.5        | -1 (autosized)                                                         |
| performance_schema_events_stages_history_long_size     | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_events_stages_history_size          | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_events_statements_history_long_size | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_events_statements_history_size      | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_hosts_size                          | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_max_socket_classes                  | 5.6.3        | 10                                                                     |
| performance_schema_max_socket_instances                | 5.6.3        | -1 (autosized)                                                         |
| performance_schema_max_stage_classes                   | 5.6.3        | 100                                                                    |
| performance_schema_max_statement_classes               | 5.6.3        | 100                                                                    |
| performance_schema_session_connect_attrs_size          | 5.6.6        | -1 (autosized)                                                         |
| performance_schema_setup_actors_size                   | 5.6.1        | 100                                                                    |
| performance_schema_setup_objects_size                  | 5.6.1        | 100                                                                    |
| performance_schema_users_size                          | 5.6.3        | -1 (autosized)                                                         |
| relay_log_basename                                     | 5.6.2        | %{datadir}%{hostname}-relay-bin                                        |
| relay_log_info_repository                              | 5.6.2        | FILE                                                                   |
| server_id_bits                                         | 5.6.6        | 32                                                                     |
| server_uuid                                            | 5.6.0        |                                                                        |
| sha256_password_private_key_path                       | 5.6.6        | private_key.pem                                                        |
| sha256_password_public_key_path                        | 5.6.6        | public_key.pem                                                         |
| slave_allow_batching                                   | 5.6.6        | OFF                                                                    |
| slave_checkpoint_group                                 | 5.6.3        | 512                                                                    |
| slave_checkpoint_period                                | 5.6.3        | 300                                                                    |
| slave_parallel_workers                                 | 5.6.3        | 0                                                                      |
| slave_pending_jobs_size_max                            | 5.6.3        | 1k                                                                     |
| slave_rows_search_algorithms                           | 5.6.6        | TABLE_SCAN,INDEX_SCAN                                                  |
| slave_sql_verify_checksum                              | 5.6.1        | ON                                                                     |
| ssl_crl                                                | 5.6.3        |                                                                        |
| ssl_crlpath                                            | 5.6.3        |                                                                        |
| table_open_cache_instances                             | 5.6.6        | 1                                                                      |
| tx_read_only                                           | 5.6.5        | OFF                                                                    |
| validate_password_dictionary_file                      | 5.6.6        |                                                                        |
| validate_password_length                               | 5.6.6        | 8                                                                      |
| validate_password_mixed_case_count                     | 5.6.6        | 1                                                                      |
| validate_password_number_count                         | 5.6.6        | 1                                                                      |
| validate_password_policy                               | 5.6.10       | MEDIUM                                                                 |
| validate_password_special_char_count                   | 5.6.6        | 1                                                                      |
+--------------------------------------------------------+--------------+------------------------------------------------------------------------+

Note that while the default values are for an installation on Linux, most will also apply to other platforms. See also the Reference Manual.

For good measure here is a list of the variables that have been removed in 5.6:

  • engine_condition_pushdown – deprecated in 5.5.3, use optimizer_switch instead.
  • have_csv – use SHOW ENGINES or information_schema.ENGINES instead.
  • have_innodb – use SHOW ENGINES or information_schema.ENGINES instead.
  • have_ndbcluster – use SHOW ENGINES or information_schema.ENGINES instead.
  • have_partitioning – use SHOW ENGINES or information_schema.ENGINES instead.
  • log – deprecated in 5.1.29, use general_log instead.
  • log_slow_queries – deprecated in 5.1.29, use slow_query_log instead.
  • max_long_data_size – deprecated in 5.5.11, is now automatically controlled by max_allowed_packet.
  • rpl_recovery_rank – previously unused.
  • sql_big_tables – hasn’t really been needed since 3.23.2.
  • sql_low_priority_updates – Use low_priority_updates instead.
  • sql_max_join_size