CentOS 6 setting MySQL is case-insensitive

Today, the company server, Tomcat to read the data in MySQL, incredibly can not read the success, looked at the next Tomcat log, found that there is a sentence contains the letter of the case, in order to solve this problem, online search, incredibly is to let MySQL ignore case, But our company's MySQL is done by the master and subordinate, in order not to affect the master-slave environment and data in MySQL, we gave the boss in advance, said to stop the Web services, Tomcat services and MySQL services 2 hours, in a tense atmosphere we finally finished, Here's how to modify the MySQL ignore case operation.

System: CentOS 6.x

Software version: MySQL 5.5.x tomcat-6.x
1. Stop Web services and Tomcat first
Here do not say how to stop, is a engaged in the operation of the dimension should know.

2. Stop MySQL master-slave sync
First, stop slave from the server, then lock the main database, do not let the data write again, and then back up the data in the main database.
Flush tables with read lock; The primary database locks the table and does not allow the data to be written again.
Show master status; View the status of the primary database and record the values of FILE and Position.
Mysqldump-u Root-p db|gzip>/root/sqlbak/db.sql.gz back up the SQL data in the primary database.
Slave stop; Stopping slave from the database

3. Modify the MySQL master-slave configuration file, so that it ignores case

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

To add under Mysqld, do not add the wrong place, save and restart MySQL.

PS: Here I want to say, lower_case_table_names This parameter can not be modified in the MySQL command line, if you modify 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

Description Lower_case_table_names This parameter, can only be added in the MY.CNF configuration, can not be modified in the MySQL command line.

4. Restore Master-Slave synchronization
First in the Lord, look at the state of the primary database and then execute it from the database according to the state of the master:

Change Master to master_host= ' 192.168.10.151 ', master_user= ' dbmysql ', master_password= ' 123456 ', master_log_file= ' Master-bin.000001 ', master_log_pos=107;
Start slave;
show slave statusg;

If you synchronize, and then cancel the lock status of the primary database, the command is as follows:

Unlock tables;

5. To verify
Random in the main inserted data or modify the point of data, if there is synchronization, that explains the master and subordinate normal, if not synchronized, check whether there is an error from the database.