• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

Changing the value of lower_case_table_names in MySQL 8

by admin

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.

Filed Under: mysql

Some more articles you might also be interested in …

  1. How to configure resource groups for MySQL Server running on Linux
  2. How to Troubleshoot InnoDB Lock Issues
  3. MySQL: how to figure out which session holds which table level or global read locks
  4. MySQL Error “Too many connections” and how to resolve it
  5. What is the MySQL Enterprise Monitor?
  6. How MySQL Enterprise Backup (MEB) uses locking while making a backup
  7. MySQL: Identify what user and thread are holding on to a meta data lock that is preventing other queries from running
  8. What is the purpose of “system user” in MySQL Replication
  9. MySQL: How to Set Account Resource Limits
  10. Understanding mysqlcheck and myisamchk utilities

You May Also Like

Primary Sidebar

Recent Posts

  • powertop Command Examples in Linux
  • powertop: command not found
  • powerstat: command not found
  • powerstat Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright