mysql主从同步配置-出现的问题_this operation cannot be performed with a running

1、ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SL

mysql配置主从复制出现以下异常:

slave服务器执行命令
	CHANGE MASTER TO MASTER_HOST='192.168.116.101', MASTER_USER='root', 
	MASTER_PASSWORD='Root1234', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=107;

出现异常信息
	ERROR 1198 (HY000): This operation cannot be performed with a running slave; run STOP SLAVE first

解决方案
	进入slave服务器mysql执行命令：STOP slave;
	在执行上面CHANGE MASTER命令
	成功后再执行命令：START slave;
1
2
3
4
5
6
7
8
9
10
11
12
13

2、This operation cannot be performed with a running slave io thread; run STOP SLAVE IO_THREAD FOR CHANNEL ‘’ first

解决办法:
1.先关闭当前的slave;

mysql> stop slave;
2.检查主服务器的配置信息:

vi /etc/my.cnf
# 文件末尾查看追加
log-bin=master-bin 
binlog-format=ROW 
server-id=1		   
binlog-do-db=msb   
1
2
3
4
5
6

3.查看主的状态信息,在主MySQL下:

mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000009 |      154 | msb          |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
1
2
3
4
5
6
7

记住Position,完成后重新关联:

change master to master_host='192.168.182.111',master_user='root',master_password='123456',master_port=3306,
master_log_file='master-bin.000001',master_log_pos=154;
1
2

查看slave状态：

发现Slave_SQL_Running: No,这地方必须是两个yes才可以同步。

执行:

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 
START SLAVE;
1
2
3

再查看slave状态:

slave_sql_running和Slave_IO_Running，分别负责slave mysql进程和与主机的io通信全部是yes,成功!