mysql 主从通信异常_mysql主从同步异常

查看主从状态

主服务器

mysql> show master status;

+------------------+-----------+--------------+------------------+-------------------+

| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

+------------------+-----------+--------------+------------------+-------------------+

| mysql-bin.000467 | 509113942 |              |                  |                   |

+------------------+-----------+--------------+------------------+-------------------+

从服务器

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Waiting for master to send event

Master_Host: 192.168.11.11

Master_User: repluser

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000467

Read_Master_Log_Pos: 537072113

Relay_Log_File: mysql-relay-bin.001357

Relay_Log_Pos: 789610157

Relay_Master_Log_File: mysql-bin.000454

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1060

Last_Error: Error 'Duplicate column name 'AES_TEL1'' on query. ....

原因：

错误的认为从库是主库，在从库做了alter table 修改表结构，之后发现是从库后又在主库上做了该操作，主库做完操作后从库也会执行这个语句，但是由于从库上已经做了修改，所以主从线程更改表失败，就会看到从库状态如上面所示，手动解决这个问题：

mysql>set global sql_slave_skip_counter=1;

mysql>start slave;

告诉从库，让它忽略掉这个错误继续执行同步。

参考：https://www.cnblogs.com/cobbliu/p/4227183.html