Detailed explanation of the pitfalls of recording lower_case_table_names in MySQL

1 Cause

After the project migrated the database and restarted, an error message was displayed saying "T_AAA table does not exist", but the table can be seen in the database and has data

2 Problem Analysis

Reinstall the system and database to confirm that the system and database are clean. Eliminate the system and database as the cause. Use the same method to restore the data backup two days ago and one day ago. The project still cannot be started. Eliminate the cause of the data content. Use mysqldump and export SQL files to restore the data one day ago. The project still cannot be started. Eliminate the cause of the recovery method.

The above methods are basically participated by operation and maintenance personnel. After the technical director participated in observing the project error, he suddenly found that it was a problem with the upper and lower case of the table name. The cause was finally determined through testing:
Use the rpm package to install the database. Set lower_case_table_names to 0 during automatic initialization. The table name is case sensitive, lowercase in the database and uppercase in the code.

3 Solutions

• Delete the initialized database, i.e. base_dir, data_dir, etc.
• Reinitialize the database and set lower_case_table_names to 1
• Restore backup data

4 Conclusion

For MySQL 8.0 and above, the lower-case-table-names parameter can only be set during initialization, and cannot be achieved by modifying my.cnf (adding configuration to my.cnf will result in an error)

To initialize the database, you do not need to uninstall the entire MySQL and reinstall it. You only need to use the mysqld command.

The user group of base_dir should be mysql. The command under Linux is:

chown -R mysql.mysql MySQL data path (i.e. base_dir)

Recursively create directories under Linux

mkdir -p mysql/lib/mysql-files

MySQL Reinitialization

mysqld -initialize --lower-case-table-names=1

For MySQL 8.0 and above, you need to create a user first and then grant permissions

create user 'test'@'%' identified by 'test';
-- with option can grant permissions to other users grant all privileges on test.* to 'test'@'%' with option;

This is the end of this article about the pitfalls of recording lower_case_table_names in MySQL. For more relevant MySQL lower_case_table_names content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!