Solve the problem of mysql capitalization

background

mysql in Linux system is case sensitive by default, that is, user and USER are two different tables, and this parameter cannot be modified later. If you modify it, you need to clear the data. The cost is very high, and the latest version does not For the improvement of this problem, I can’t think of the purpose of this design, because the actual situation is that a database may be shared by many systems, some need to be case sensitive, some don’t. If this problem is not found in the early stage, it will be rebuilt in the later stage. To set up a database for data migration, apart from killing mysql, I can't think of any other reason for oracle to design this way.

solution

• backup database

We will initialize the database later, and all data will be lost after initialization, so we need to make a backup in advance

mysqldump -uroot -p 数据库名称 >db_name.sql

It is recommended to backup each database

• Modify the configuration file

Modify the configuration file /etc/mysql/my.cnf and add the following configuration

lower_case_table_names=1

• Modify the data file directory

datadir         = /data/mysql

/data/mysql must be empty

• Close the database and execute the following command to initialize

mysqld  --lower_case_table_names=1 --initialize-insecure --basedir=/usr/ --datadir=/data/mysql

• All data will be lost after restarting the database, we need to re-create the database and database user

# 仅供参考
create database db_name;
update user set host='%' where user='root';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password;
alter user 'root'@'%' identified by 'mysql';
GRANT ALL PRIVILEGES ON db_name.* TO 'root'@'%';

• Reimport data

mysql -uroot -p  db_name < db_name.sql