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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Understanding the Different Configuration files used for MySQL Server
  2. MySQL Cluster Point-In-Time Recovery (PITR)
  3. MySQL Server 8.0 – How to create a REPLICATION SLAVE using MEB
  4. How To Restore an NDBCluster Backup Using ndb_restore
  5. How To Create a Local Yum Repository for MySQL Enterprise Packages
  6. Beginners Guide to MySQL Data Types
  7. How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment
  8. How to Rollback RPM Upgrade of the MySQL Server
  9. MySQL : how to set (change) user password
  10. How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary