有三台mysql A B C
其中A 和 B 已经做了主从,又把B做成了C的主,然后问题是,B上说什么也不
记录bin-log,所以C就一直不能从B上同步到数据,B能从A上顺利同步到数据
。
找了半天资料,终于找到解决办法,在B的配置文件my.cnf 加上一句
log-slave-updates
即可。
这样配置了之后,B能同步A,但是C连接不到B,提示:
Last_IO_Error: error connecting to master 'repl2@192.168.1.119:3307'
- retry-time: 60 retries: 86400
找不到毛病在哪,重复了好几次,都不行。
搞定了,查看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: 1130
160512 8:40:53 [Note] Error reading relay log event: slave SQL
thread was killed
160512 8:40:53 [Note] Slave I/O thread killed while connecting to
master
160512 8:40:53 [Note] Slave I/O thread exiting, read up to log
'B_Master.000003', position 340
160512 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: 4
160512 8:42:29 [ERROR] Slave I/O: error connecting to master
'repl2@192.168.1.119:3307' - retry-time: 60 retries: 86400,
Error_code: 1130
160512 8:48:09 [Note] usr/local/mysql/bin/mysqld: Normal shutdown
C连接不到B,再试着直接C登录B:
[root@localCoohx ~]# mysql -u repl2 -h192.168.1.119 -P 3307 -
pmysqlslave
ERROR 1130 (HY000): Host '192.168.1.118' is not allowed to connect
to this MySQL server
问题就出在这了,突然想起来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)
之后就连接成功了。