主从不同步故障案例一-原创手记-慕课网

故障原因：

开发人员在主库上修改了一张表的结构，加了一个字段，从库由于各种原因没有同步过去，导致主从同步失败。

故障处理：

1、查看在从库上查看同步状态：

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.100.100

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 917648

Relay_Log_File: mysqld-relay-bin.000017

Relay_Log_Pos: 778406

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: No

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema,test,mysql,information_schema,performance_schema,test

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 1054

Last_Error: Error 'Unknown column 'js_code' in 'field list'' on query. Default database: 'web_platform'. Query: 'update act_id set js_code='确保该页面的PV埋点已经上报;__tj(node,snode,cid,w,'''','''','''');说明:remark;' where act_id=11'

Skip_Counter: 0

Exec_Master_Log_Pos: 906099

Relay_Log_Space: 790258

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: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 1054

Last_SQL_Error: Error 'Unknown column 'js_code' in 'field list'' on query. Default database: 'web_platform'. Query: 'update act_id set js_code='确保该页面的PV埋点已经上报;__tj(node,snode,cid,w,'''','''','''');说明:remark;' where act_id=11'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

2、从上面可以看出表act_id的js_code字段在从库上没有，可以对比一下主库和从库这张表的结构：

查看主库：

mysql> desc act_id;

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

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

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

11 rows in set (0.00 sec)

查看从库：

mysql> desc act_id;

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

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

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

10 rows in set (0.00 sec)

3、在正式处理故障之前要停掉主库和从库的slave进程（我的环境是做了双向同步，即主主同步）

mysql> slave stop;

Query OK, 0 rows affected (0.12 sec)

4、在从库上加缺少的字段：

alter table act_id add js_code text not Null AFTER fields;

5、启动从库的slave进程，查看主从状态：

mysql> slave start;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

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

Slave_IO_State: Waiting for master to send event

Master_Host: 10.10.100.100

Master_User: slave

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysql-bin.000009

Read_Master_Log_Pos: 917648

Relay_Log_File: mysqld-relay-bin.000117

Relay_Log_Pos: 253

Relay_Master_Log_File: mysql-bin.000009

Slave_IO_Running: Yes

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB: mysql,information_schema,performance_schema,test,mysql,information_schema,performance_schema,test

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: 917648

Relay_Log_Space: 556

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: 0

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 0

Last_IO_Error:

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

6、查看从库的master信息，在主库上从新change

从库上：

mysql> show master status \G

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

File: mysql-bin.000009

Position: 235

Binlog_Do_DB:

Binlog_Ignore_DB: mysql,information_schema,performance_schema,test,mysql,information_schema,performance_schema,test

1 row in set (0.00 sec)

主库上：

mysql> change master to master_host="10.10.100.101", master_port=3306, master_user='slave', master_password='123456', master_log_file='mysql-bin.000009', master_log_pos=235;

1 row in set (0.00 sec)

7、对比主库和从库这两张表的结构和数据是否一样：

主库：

mysql> show create table act_id;

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

| Table | Create Table |

| act_id | CREATE TABLE `act_id` (

`act_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(512) NOT NULL,

`fields` varchar(512) NOT NULL,

`js_code` text NOT NULL,

`remark` varchar(512) NOT NULL,

`create_user` varchar(256) NOT NULL,

`duty_user_name` varchar(256) NOT NULL,

`duty_user_email` varchar(256) DEFAULT NULL,

`duty_user_phone` varchar(256) DEFAULT NULL,

`create_time` bigint(20) NOT NULL,

`update_time` bigint(20) NOT NULL,

PRIMARY KEY (`act_id`),

UNIQUE KEY `act_id_UNIQUE` (`act_id`)

) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> desc act_id;

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

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

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

11 rows in set (0.00 sec)

从库：

mysql> show create table act_id;

| Table | Create Table |

| act_id | CREATE TABLE `act_id` (

`act_id` int(10) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(512) NOT NULL,

`fields` varchar(512) NOT NULL,

`js_code` text NOT NULL,

`remark` varchar(512) NOT NULL,

`create_user` varchar(256) NOT NULL,

`duty_user_name` varchar(256) NOT NULL,

`duty_user_email` varchar(256) DEFAULT NULL,

`duty_user_phone` varchar(256) DEFAULT NULL,

`create_time` bigint(20) NOT NULL,

`update_time` bigint(20) NOT NULL,

PRIMARY KEY (`act_id`),

UNIQUE KEY `act_id_UNIQUE` (`act_id`)

) ENGINE=MyISAM AUTO_INCREMENT=100 DEFAULT CHARSET=utf8 |

1 row in set (0.00 sec)

mysql> desc act_id;

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

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

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

11 rows in set (0.00 sec)

处理完成。

故障案例主从不同步数据库