How to Change the Value of lower_case_table_names in MySQL 8(Doc ID 2597775.1)

APPLIES TO:

MySQL Server - Version 8.0 and later
Information in this document applies to any platform.

GOAL

Change the value of lower_case_table_names in MySQL 8.

SOLUTION

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.
Note: If you are using Group Replication or InnoDB Cluster, you must perform this procedure for the whole cluster at the same time as all nodes must have the same value for lower_case_table_names.
 
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.
Note: This step is extremely important!
 
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.