Mysql production environment ____ master-slave synchronization repair case _ MySQL

I. hardware environment Master: DellR720Intel (R) Xeon (R) CPUE5-2640v2 @ 200GHzMEM64G, disk4 * 25SAS network 4 * Gigabit Slave: DellR720Intel (R) Xeon (R) CPUE5-2640v2 I. hardware environment

Master: Dell R720 Intel (R) Xeon (R) CPU E5-2640 v2 @ 2.00 GHz

MEM 64 GB, disk 4*2.5 SAS Network 4 * Gigabit

Slave: Dell R720 Intel (R) Xeon (R) CPU E5-2640 v2 @ 2.00 GHz

MEM 64 GB, disk 4*2.5 SAS Network 4 * Gigabit

II. software environment

System software:

Master: cento5.8

Slave: cento5.8

Database software: mysql-5.5.10

III. symptom

3.1 receive an alarm and discover problems

On July 22, 2014, the system received an alarm about mysql master-Slave synchronization. log on to Slave and useShow slavestatus \ G;The result is as follows. the error code is 1146 and the error description is"Database name. table name does not exist. INSERT statement"

3.2 analyze and solve problems

There is an error description in the preceding slave, and the table does not exist. We need further verification. execute show databases on the slave; check that the inventory is found in; 2. continue to enter the command,

Use database name;

Show tables;

The table also exists. since all tables exist, why does the following error occur: "The table does not exist?" think, check, and google. a similar situation exists, but the solution is not universal.

Calm down and look back at the error prompt carefully. There are new discoveries. The table names in the error prompt are in upper case and the table names in the actual database are in lower case. Okay, verify it,

Select * from database name. table name; the table name is also in uppercase. after execution is completed, the error message is the same as the error message "The table does not exist ".

Select * from database name. table name; table name in lower case. the execution is complete, and the correct result is output, 2.

Find the cause to solve the problem.

Solution:

Stop slave;

Show slavestatus \ G;

Clone A New secureCRT connection and edit the my. cnf configuration file,

Add a row under the [mysqld] Node: lower_case_table_names = 1

Save and exit.

/Etc/init. d/mysqldrestart

Return to the database operation command line and execute start slave; show slave status \ G; enable synchronization. the error message disappears and the synchronization is restored.

Slave_IO_Running: Yes

Slave_ SQL _Running: Yes

Log on to the master and find that the my. cnf configuration of the master contains lower_case_table_names = 1.

The final cause is as follows: lower_case_table_names = 1 is missing in my. cnf configuration on the slave end.