mysql 互為主從複制常見問題

報錯： 1） change master導緻的： Last_IO_Error: error connecting to master - retry-time: 60 retries 2） 在沒有解鎖的情況下停止slave程序： stop slave; ERROR 1192 (HY000): Cant execute the given command because you have active locked tables

報錯：

1）

change master導緻的：

              Last_IO_Error: error connecting to master - retry-time: 60  retries

2）    

在沒有解鎖的情況下停止slave程序：

     > stop slave;

ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

3）

change master文法錯誤，落下逗号

mysql> change master to

    -> master_host='IP'

    -> master_user='USER',

    -> master_password='PASSWD',

    -> master_log_file='mysql-bin.000002',

    -> master_log_pos=106;

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'master_user='USER',

master_password='PASSWD',

master_log_file='mysql-bin.000002' at line 3

4）

在沒有停止slave程序的情況下change master

mysql> change master to master_host=‘IP', master_user='USER', master_password='PASSWD', master_log_file='mysql-bin.000001',master_log_pos=106;

ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

5）

用change master語句指定同步位置出錯，修改master_log_file和master_log_pos參數：

Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'bogus data in log event', Error_code: 1236

6 )

A B的server-id相同：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids;

these ids must be different for replication to work (or the --replicate-same-server-id option must be used on

slave but this does not always make sense; please check the manual before using it).

檢視server-id

mysql> show variables like 'server_id';

手動修改server-id

mysql> set global server_id=2; #此處的數值和my.cnf裡設定的一樣就行

mysql> slave start;

7）

change master之後: Could not initialize master info structure, more error messages can be found in the MySQL error log

 1.mysql> reset slave; # 重點就是這行

 2.mysql> CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=107; # 請依照自行環境設定

 3.mysql> start slave; # 就正常了

重新設定 slave, MASTER_LOG_FILE 和 MASTER_LOG_POS 會被清空, 是以需要重新設定：

本文轉自奔跑在路上部落格51CTO部落格，原文連結http://blog.51cto.com/qiangsh/1555067如需轉載請自行聯系原作者

qianghong000