lower_case_table_names 可以引發mysql replication 不成功

很早以前配置過一個mysql的replication, 跑的很好，突然客戶的mysql database replicaiton失效了

重新配置replication也無效

[crhk@_Slave bin]$ cat mysqld_slave.sh
#!/bin/sh
/usr/bin/mysqld_safe --skip-slave-start &
[crhk@_Slave bin]$

------------------------------------slave----------------------------------
[root@_Slave bin]# ./mysqld_slave.sh
[root@_Slave bin]# Starting mysqld daemon with databases from /var/lib/mysql

 

[root@_Slave mysql]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.45-log Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show slave status;
Empty set (0.00 sec)
mysql>
mysql> change master to
    -> master_host='A.B.C.D',
    -> master_port=3306,
    -> master_user='repl',
    -> master_password='password',
    -> master_log_file='mysql-bin.000031',
    -> master_log_pos=201937405;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log
mysql>

將同步所需文件全部刪除

rm /var/lib/mysql/relay-log.info 
rm /var/lib/mysql/master.info 
rm /var/run/mysqld/*

重新做，問題依然

只能跟蹤log了

。。。。

100301 22:26:38  mysqld started
100301 22:26:38 [Warning] /usr/libexec/mysqld: ignoring option '--innodb_file_per_table' due to invalid value 'ON'
100301 22:26:38 [Warning] /usr/libexec/mysqld: ignoring option '--innodb_file_per_table' due to invalid value 'ON'
100301 22:26:39  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
100301 22:26:39  InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 8 2665319758.
InnoDB: Doing recovery: scanned up to log sequence number 8 2665319758
InnoDB: Last MySQL binlog file position 0 3092, file name /var/lib/mysql/mysql-bin.000037
100301 22:26:39  InnoDB: Started; log sequence number 8 2665319758
100301 22:26:39 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.45-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution
100301 22:27:47 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.000037' at position 3268, relay log '/var/run/mysqld/mysqld-relay-bin.000001' position: 4
100301 22:27:47 [Note] Slave I/O thread: connected to master 'repl@172.16.12.29:3306',  replication started in log 'mysql-bin.000037' at position 3268
100301 22:27:47 [ERROR] Slave: Error 'Table 'databasename.GuessFall' doesn't exist' on query. Default database: 'databasename'. Query: 'insert into GuessFall (GuessFallId,UserId,StockCode,State,StartPrice,EndPrice,CreateDate,StopDate) values ('ff52146c-81f6-4334-8920-ef3f059ff3e8','b27e0e77-9cf2-aa9b-e040-10ac0d0c2f1f', '02777', 0, -1, 0.000, '2010-03-01 22:14:40','2010-03-01 22:14:40')', Error_code: 1146
100301 22:27:47 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysql-bin.000037' position 3268

。。。。

發現插入不了

這是開始懷疑master/slave 的配置問題不一致了

diff一下，果然，slave庫中少了

lower_case_table_names=1

修改之slave my.cnf文件，重新配置，正常了

Myql 官方解釋：

http://dev.mysql.com/doc/refman/5.0/en/identifier-case-sensitivity.html

簡而言之就是 寫入不區分大小寫

0  --名字對大小寫敏感 ，default

1 - -table name on disk是小寫， 名字對大小寫敏感

2--存儲根據create table or create database 的語句，mysql 查詢的時候轉換小寫，對大小寫不敏感

教訓：

1. replication 方式下的 master /slave的環境配置一致很重要，

2.lower_case_table_names 這類常用參數，應該在database配置的時候設置好了，避免不必要的改動。