MySQL主从同步：
有三台mysql A B C
其中A 和 B 已经做了主从，又把B做成了C的主，然后问题是，B上说什么也不记录bin-log，所以C就一直不能从B上同步到数据，B能从A上顺利同步到数据。
解决办法：
在B的配置文件my.cnf 加上一句
log-slave-updates
AB主从已设置好，问题出在BC之间
B上：mysql>grant replication slave on *.* to 'repl2'@'192.168.1.118' identified by 'mysqlslave'mysql> flush table with read clock;mysql> show master status;
+-----------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-----------------+----------+--------------+------------------+| B_Master.000002 | 524565 | db1,db2 | mysql,db1 |
+-----------------+----------+--------------+------------------+1 row in set (0.00 sec)123456789123456789
C上：vim /usr/local/mysql_slave/my.cnf
修改如下：
server-id = 2 //区别于主的id相同在主上已经配置过，注释下面的配置#指定要同步的库 #replicate-do-db=db2 #忽略不同步的库#replicate-ignore-db=mysql1234567812345678
重启生效：/etc/init.d/mysqldslave restartmysql> change master to master_host='192.168.1.119',master_port=3307,master_user='repl2',master_password='mysqlslave',master_log_file='B_Master.000002',master_log_pos=524565;11
C连接B时报错：
这样配置了之后，B能同步A，但是C连接不到B，提示：Last_IO_Error: error connecting to master 'repl2@192.168.1.119:3307' - retry-time: 60 retries: 8640011
查看C错误日志：Version: '5.1.73' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)160512 8:40:29 [ERROR] Slave I/O: error connecting to master 'repl2@192.168.1.119:3307' - retry-time: 60 retries: 86400, Error_code: 1130160512 8:40:53 [Note] Error reading relay log event: slave SQL thread was killed160512 8:40:53 [Note] Slave I/O thread killed while connecting to master160512 8:40:53 [Note] Slave I/O thread exiting, read up to log 'B_Master.000003', position 340160512 8:42:14 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='192.168.1.119', master_port='3307', master_log_file='B_Master.000003', master_log_pos='340'. New state master_host='192.168.1.119', master_port='3307', master_log_file='B_Master.000004', master_log_pos='338'.160512 8:42:29 [Note] Slave SQL thread initialized, starting replication in log 'B_Master.000004' at position 338, relay log './localCoohx-relay-bin.000001' position: 4160512 8:42:29 [ERROR] Slave I/O: error connecting to master 'repl2@192.168.1.119:3307' - retry-time: 60 retries: 86400, Error_code: 1130160512 8:48:09 [Note] /usr/local/mysql/bin/mysqld: Normal shutdown123456789101112123456789101112
C连接不到B，再试着直接C登录B：[root@localCoohx ~]# mysql -u repl2 -h192.168.1.119 -P 3307 -pmysqlslaveERROR 1130 (HY000): Host '192.168.1.118' is not allowed to connect to this MySQL server1212
问题就出在这了，突然想起来B没有给C授权远程登录，于是在B上：mysql> grant all on *.* to 'repl2'@'192.168.1.118' identified by 'mysqlslave';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)12341234
之后就连接成功了。
————————————————–
由于之前mysql 从机的IP发生变化，所以需要BC之间从新设置主从：
B 192.168.1.112 C:192.168.1.118
B上： 重新给作为Slave的C授权mysql> grant replication slave on *.* to 'repl2'@192.168.1.118 identified by 'mysqlslave';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
mysql> flush tables with read lock; //让事务show master status锁死表的读mysql> show master status;
+--------------+----------+--------------+------------------+| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+| coohx.000002 | 333 | db1 | mysql |
+--------------+----------+--------------+------------------+1 row in set (0.00 sec)12345678910111234567891011
C上：mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> change master to master_host='192.168.1.112',master_port=3307,master_user='repl2',master_password='mysqlslave',master_log_file='B_Master.000028',master_log_pos=106;ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log C上重新设置主信息失败.
查看错误日志： 160529 13:15:52 [Note] /usr/local/mysql/bin/mysqld: ready for connections.
Version: '5.1.73' socket: '/tmp/mysql.sock' port: 3306 MySQL Community Server (GPL)160529 13:40:50 [ERROR] Failed to open the relay log './localCoohx-relay-bin.000004' (relay_log_pos 549)160529 13:40:50 [ERROR] Could not find target log during relay log initialization160529 13:45:38 [ERROR] Failed to open the relay log './localCoohx-relay-bin.000004' (relay_log_pos 549)160529 13:45:38 [ERROR] Could not find target log during relay log initialization160529 13:52:06 [ERROR] Failed to open the relay log './localCoohx-relay-bin.000004' (relay_log_pos 549)160529 13:52:06 [ERROR] Could not find target log during relay log initialization160529 13:52:18 [ERROR] Failed to open the relay log './localCoohx-relay-bin.000004' (relay_log_pos 549)160529 13:52:18 [ERROR] Could not find target log during relay log initialization160529 14:00:21 [ERROR] Failed to open the relay log './localCoohx-relay-bin.000004' (relay_log_pos 549)160529 14:00:21 [ERROR] Could not find target log during relay log initialization123456789101112131415161718192021222324123456789101112131415161718192021222324
localCoohx-relay-bin.000004 这是上一次C同步B后生成的relay重演文件，后来由于改过主机名，所以作废，于是删掉这些同步文件，继续执行：mysql> change master to master_host='192.168.1.112',master_port=3307,master_user='repl2',master_password='mysqlslave',master_log_file='B_Master.000028',master_log_pos=106;
ERROR 1201 (HY000): Could not initialize master info structure; more error messages can be found in the MySQL error log1212
还是出错，查看slave状态：mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.119 Master_User: repl2 Master_Port: 3307 Connect_Retry: 60 Master_Log_File: B_Master.000005 Read_Master_Log_Pos: 405 Relay_Log_File: localCoohx-relay-bin.000004 Relay_Log_Pos: 549 Relay_Master_Log_File: B_Master.000005 Slave_IO_Running: No Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 405 Relay_Log_Space: 0 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULLMaster_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:1 row in set (0.00 sec)12345678910111213141516171819202122232425262728293031323334353637383940411234567891011121314151617181920212223242526272829303132333435363738394041
显示为上一次主从同步时主的信息，主B的IP已经改变，所以重新设置slave-C，但是失败，
解决办法：mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset slave; //这是关键一步！！！Query OK, 0 rows affected (0.03 sec)1234512345mysql> change master to master_host='192.168.1.112',master_port=3307,master_user='repl2',master_password='mysqlslave',master_log_file='B_Master.000028',master_log_pos=106;
Query OK, 0 rows affected (0.09 sec)
mysql> slave start;
Query OK, 0 rows affected (0.05 sec)12341234mysql> show slave status\G;*************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.112 Master_User: repl2 Master_Port: 3307 Connect_Retry: 60 Master_Log_File: B_Master.000028 Read_Master_Log_Pos: 106 Relay_Log_File: Coohx_slave-relay-bin.000002 Relay_Log_Pos: 250 Relay_Master_Log_File: B_Master.000028 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 106 Relay_Log_Space: 411 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error:1 row in set (0.00 sec)12345678910111213141516171819202122232425262728293031323334353637383940411234567891011121314151617181920212223242526272829303132333435363738394041
成功连接到主，进行同步。
补充：
查看某一账户权限
mysql> show grants for ‘repl2’@’192.168.1.119’;
+——————————————————————————————————————————+
| Grants for repl2@192.168.1.119 |
+——————————————————————————————————————————+
| GRANT REPLICATION SLAVE ON . TO ‘repl2’@’192.168.1.119’ IDENTIFIED BY PASSWORD ‘*EBB378AC232302FF0D4565FFF9F42577B1C04625’ |
+——————————————————————————————————————————+
1 row in set (0.00 sec)
总结：在同一台mysql服务器上第二次设置从服务器时，应先关掉上一次的主从连接，reset清空以前的master信息，然后重新设置新的slave。
即：stop
reset
change master123123
扫一扫
633
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
