Changing the value of lower_case_table_names in MySQL 8

The only time you are allowed to change the setting of lower_case_table_names is in connection with initializing the MySQL data directory (mysqld –initialize). So you need to create a logical backup, then completely re-initialize MySQL, then restore the backup.

An example of the steps that can be used are:

1. Put the database in read only mode, for example:

mysql> SET GLOBAL super_read_only = ON;
Query OK, 0 rows affected (0.00 sec)

It is also recommended to shut down the application.

2. Verify that there are no duplicate schema or table names:

mysql> SELECT LOWER(SCHEMA_NAME), COUNT(*)
       FROM information_schema.SCHEMATA
       GROUP BY LOWER(SCHEMA_NAME)
       HAVING COUNT(*) > 1;
Empty set (0.00 sec)

mysql> SELECT TABLE_SCHEMA, LOWER(TABLE_NAME), COUNT(*)
       FROM information_schema.TABLES
       GROUP BY TABLE_SCHEMA, LOWER(TABLE_NAME)
       HAVING COUNT(*) > 1;
Empty set (0.02 sec)

If any duplicate identifiers are found, then you cannot change lower_case_table_names without first renaming one of the duplicates.

3. Create a logical backup using mysqlpump or mysqldump. If you use mysqlpump, make sure to include the –users option to include the users and their grants.

4. Verify you can restore the backup and that the restored database work as expected.

5. Shut down MySQL.

6. Delete all files under datadir and any other directories you may use to MySQL files. Tip: Make a copy of all the files before deleting.

7. Update my.cnf to include the new value for lower_case_table_names.

8. Re-initialize MySQL using the –initialize option with mysqld, for example:

shell$ mysqld --defaults-file=/etc/my.cnf --initialize

9. Once re-initialized and the root password has been changed, restore the backup.

10. Verify everything is working as expected.