Set mysql to case-insensitive in centos 6

In linux, mysql is case-insensitive. If you want mysql to be case-insensitive, you can modify lower_case_table_names in my. ini.

On the company's server today, tomcat could not read data from mysql. After checking the tomcat log, it found that the data contains a uppercase/lowercase letter, to solve this problem, I searched the internet to make mysql case-insensitive. However, our company's mysql is master-slave. In order not to affect the master-slave environment and mysql DATA, we said hello to the boss in advance, saying we had to stop the web service, tomcat service, and mysql service for two hours. In a tense atmosphere, we finally got it done, the following describes how to modify the case-insensitive mysql operation.

System: centos 6.x

Software Version: mysql 5.5.x tomcat-6.x
1. Stop the web service and tomcat
Here we will not talk about how to stop it. It should be known to all O & M personnel.

2. Stop mysql master-slave Synchronization
Stop the slave from the server first, and then lock the master database, so that the data is not written in again, and then back up the data in the master database.
Flush tables with read lock; the master database locks the table and prevents data from being written.
Show master status; view the status of the master database and record the value of FILE and Position.
Mysqldump-u root-p db | gzip>/root/sqlbak/db. SQL .gz back up SQL data in the primary database.
Slave stop; stop slave from the database

3. Modify the mysql Master/Slave configuration file to ignore the case sensitivity.

Vi/etc/my. cnf
# The MySQL server
[Mysqld]
Set-variable = lower_case_table_names = 1

To add it under mysqld, do not add an error. Save and restart mysql.

Ps: Here I want to say that the lower_case_table_names parameter cannot be modified in the mysql command line. If you modify it in the mysql command line, as follows:
Set GLOBAL lower_case_table_names = 1;
Set session;

If you execute these two commands, mysql will report the following error:

Error: Variable 'lower' _ case_table_names 'is a read only variable

It indicates that the lower_case_table_names parameter can only be added in my. cnf configuration and cannot be modified in the mysql command line.

4. Restore master-slave Synchronization
First, check the status of the primary database in the primary database, and then execute the following commands from the database based on the primary status:

Change master to master_host = '192. 168.10.151 ', master_user = 'dbmysql', master_password = '000000', master_log_file = 'master-bin.000001', master_log_pos = 192;
Start slave;
Show slave statusG;

If the master database is locked after synchronization, run the following command:

Unlock tables;

5. Verify
Insert data entries or modify data entries in the master node. If synchronization starts, the master and slave nodes are normal. If synchronization fails, check whether errors are reported in the slave database.