mysql主从复制数据不同步处理方法 |

吴老二 2019年12月14日20:46:57 评论 1,204 次浏览

以下主要是针对主从复制过程中出现的数据不同步处理方法记录，第一种处理方法就是跳过，第二种方法就是删掉从库的数据从头开始同步，两种同步方法各有利弊。第一种跳过方法，跳过错误的地方，从最新的日志开始同步，这个时候就会丢失从错误发送时，到最新的日志间隔的数据丢失的情况，如果这段时间的日志没有增加这个方法当然是最快捷最简单的，不过如果中间增加了很多数据，这个时候跳过去的这段数据就会在从的服务器上不存在，数据丢失。这种方法是不可取的，如果你的主服务器是数据不多，或者说你的从服务器有多个，那就可以把从服务器从生产环境上去掉，从主的第一个日志开始同步或者把主的数据备份一下，记录一下主mysql的binlog，然后在还原在根据备份的binlog做同步，这样不但简单也快捷。出现问题不同，处理方式不同，下面我就针对这两种的处理方式做一下实验记录：

实验环境

主机名	主机IP	角色
wulaoer_mysql01	10.211.55.130	主服务
wulaoer_mysql02	10.211.55.131	从服务01
wulaoer_mysql03	10.211.55.132	从服务02

我们要把主从复制搭建起来，才能进行下面的实验，这里就不搭建了，可以参考一下mysql的主从复制，里面只需要做到主从复制配置即可，后面的不需要，下面我的三台服务器已经做好了主从复制，看一下

#wulaoer_mysql02
mysql> show slave status\G;
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 10.211.55.130
				  Master_User: wulaoer
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000006
		  Read_Master_Log_Pos: 258843
			   Relay_Log_File: wulaoer_server02-relay-bin.000007
				Relay_Log_Pos: 259006
		Relay_Master_Log_File: mysql-bin.000006
			 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: 258843
			  Relay_Log_Space: 504767
			  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
				  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
			 Master_Info_File: /usr/local/mysql/var/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
		   Master_Retry_Count: 86400
				  Master_Bind: 
	  Last_IO_Error_Timestamp: 
	 Last_SQL_Error_Timestamp: 
			   Master_SSL_Crl: 
		   Master_SSL_Crlpath: 
		   Retrieved_Gtid_Set: 
			Executed_Gtid_Set: 
				Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified
#wulaoer_mysql03
mysql> show slave status\G;
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 10.211.55.130
				  Master_User: wulaoer
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000006
		  Read_Master_Log_Pos: 258843
			   Relay_Log_File: wulaoer_serve03-relay-bin.000008
				Relay_Log_Pos: 283
		Relay_Master_Log_File: mysql-bin.000006
			 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: 258843
			  Relay_Log_Space: 259352
			  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
				  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
			 Master_Info_File: /usr/local/mysql/var/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
		   Master_Retry_Count: 86400
				  Master_Bind: 
	  Last_IO_Error_Timestamp: 
	 Last_SQL_Error_Timestamp: 
			   Master_SSL_Crl: 
		   Master_SSL_Crlpath: 
		   Retrieved_Gtid_Set: 
			Executed_Gtid_Set: 
				Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql主从复制环境已经做好了，不过目前是同步的环境，我要把这个环境弄成数据不同步的状态才能进行下面的实验，下面是我的处理方法，我有两个从库在wulaoer_mysql02从库上创建一个表，然后在主库上创建一个表，这样就会出现wulaoer_mysql02同步失败的情况。

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| Course         |
| Score2         |
| Teacher        |
| wulaoer       |
+----------------+
4 rows in set (0.00 sec)

mysql> drop tables wulaoer;        #因为这个表关联其他表，所以需要设置一下。
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails
mysql> SET FOREIGN_KEY_CHECKS = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> drop tables wulaoer;
Query OK, 0 rows affected (0.00 sec)

mysql> SET FOREIGN_KEY_CHECKS = 1; #删除后还需要改回来
Query OK, 0 rows affected (0.00 sec)

从库wulaoer_mysql02上面已经复制删除日志，同步到完成，test.wulaoer表也删除了，下面我在wulaoer_mysql02库上创建一个wulaoer表，然后在看一下同步信息。

mysql> show slave status\G;
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 10.211.55.130
				  Master_User: wulaoer
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000006
		  Read_Master_Log_Pos: 258964
			   Relay_Log_File: wulaoer_server02-relay-bin.000007
				Relay_Log_Pos: 259127
		Relay_Master_Log_File: mysql-bin.000006
			 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: 258964
			  Relay_Log_Space: 504888
			  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
				  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
			 Master_Info_File: /usr/local/mysql/var/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
		   Master_Retry_Count: 86400
				  Master_Bind: 
	  Last_IO_Error_Timestamp: 
	 Last_SQL_Error_Timestamp: 
			   Master_SSL_Crl: 
		   Master_SSL_Crlpath: 
		   Retrieved_Gtid_Set: 
			Executed_Gtid_Set: 
				Auto_Position: 0
1 row in set (0.00 sec)

目前还没有报错，下面在主库(wulaoer_mysql01)上也创建一个wulaoer表，在看一下wulaoer_mysql02同步信息

mysql> use test;
Database changed
mysql> CREATE TABLE wulaoer(
	-> sno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT"学号",
	-> sname VARCHAR(20) NOT NULL COMMENT"学生姓名",
	-> ssex VARCHAR(20) NOT NULL COMMENT"学生性别",
	-> sbirthday datetime COMMENT"学生出生年月",
	-> class VARCHAR(20) COMMENT"学生所在班级"
	-> );
Query OK, 0 rows affected (0.00 sec)

在同一个库下创建了同一个名字的表，在看一下wulaoer_mysql02同步信息.

mysql> show slave status\G;
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 10.211.55.130
				  Master_User: wulaoer
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000006
		  Read_Master_Log_Pos: 259314
			   Relay_Log_File: wulaoer_server02-relay-bin.000007
				Relay_Log_Pos: 259127
		Relay_Master_Log_File: mysql-bin.000006
			 Slave_IO_Running: Yes
			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: 1050
				   Last_Error: Error 'Table 'wulaoer' already exists' on query. Default database: 'test'. Query: 'CREATE TABLE wulaoer(
sno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT"学号",

sname VARCHAR(20) NOT NULL COMMENT"学生姓名",

ssex VARCHAR(20) NOT NULL COMMENT"学生性别",

sbirthday datetime COMMENT"学生出生年月",

class VARCHAR(20) COMMENT"学生所在班级"

)'
				 Skip_Counter: 0
		  Exec_Master_Log_Pos: 258964
			  Relay_Log_Space: 505238
			  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: 1050
			   Last_SQL_Error: Error 'Table 'wulaoer' already exists' on query. Default database: 'test'. Query: 'CREATE TABLE wulaoer(
sno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT"学号",

sname VARCHAR(20) NOT NULL COMMENT"学生姓名",

ssex VARCHAR(20) NOT NULL COMMENT"学生性别",

sbirthday datetime COMMENT"学生出生年月",

class VARCHAR(20) COMMENT"学生所在班级"

)'
  Replicate_Ignore_Server_Ids: 
			 Master_Server_Id: 1
				  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
			 Master_Info_File: /usr/local/mysql/var/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State: 
		   Master_Retry_Count: 86400
				  Master_Bind: 
	  Last_IO_Error_Timestamp: 
	 Last_SQL_Error_Timestamp: 191214 15:02:58
			   Master_SSL_Crl: 
		   Master_SSL_Crlpath: 
		   Retrieved_Gtid_Set: 
			Executed_Gtid_Set: 
				Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

同步信息出现错误，在test库上创建不了表，就是把主库的表删除或者从库的表删除都不能解决这个问题，就是把主库从库都删除也不能解决，下面我们使用第一种方法做跳过，这个时候删除不删除表对于同步意义不大，那我就把从库的表删除了，错误依然存在，那我就跳过去，在没有跳过去之前我在主库的表中加了一些数据。

mysql> INSERT INTO wulaoer values(108,"曾华","男",19770901,95033);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(105,"匡明","男",19751002,95031);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(107,"王丽","女",19760123,95033);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(101,"李军","男",19760220,95033);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(109,"王芳","女",19750210,95031);
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO wulaoer values(103,"陆君","男",19740603,95031);
Query OK, 1 row affected (0.00 sec)

在解决错误之前我们需要看一下主库的binlog日志节点，然后根据节点在做同步。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 |   261201 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

在从库上根据主库的这个日志节点做同步

mysql> stop slave;    #设置之前先关闭中继，然后在根据日志连接
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.211.55.130', MASTER_USER='wulaoer', MASTER_PASSWORD='wulaoer', MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=261201;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

mysql> start slave;   #设置好读取节点后，在开启中继。
Query OK, 0 rows affected (0.00 sec)

检查一下wulaoer_mysql02的同步信息

mysql> show slave status\G;
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 10.211.55.130
				  Master_User: wulaoer
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000006
		  Read_Master_Log_Pos: 261201
			   Relay_Log_File: wulaoer_server02-relay-bin.000002
				Relay_Log_Pos: 283
		Relay_Master_Log_File: mysql-bin.000006
			 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: 261201
			  Relay_Log_Space: 467
			  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
				  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
			 Master_Info_File: /usr/local/mysql/var/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
		   Master_Retry_Count: 86400
				  Master_Bind: 
	  Last_IO_Error_Timestamp: 
	 Last_SQL_Error_Timestamp: 
			   Master_SSL_Crl: 
		   Master_SSL_Crlpath: 
		   Retrieved_Gtid_Set: 
			Executed_Gtid_Set: 
				Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

检测表是否存在

mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| Course         |
| Score2         |
| Teacher        |
+----------------+
3 rows in set (0.00 sec)

表不存在，说明数据丢失，如果再出现问题到解决问题之间没有数据增加，那就不讲了，

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> set global sql_slave_skip_counter=10;  #意思是从当前位置跳过10个event
Query OK, 0 rows affected (0.00 sec)

mysql> start slave;
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.211.55.130
				  Master_User: wulaoer
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000006
		  Read_Master_Log_Pos: 261550
			   Relay_Log_File: wulaoer_server02-relay-bin.000003
				Relay_Log_Pos: 283
		Relay_Master_Log_File: mysql-bin.000006
			 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: 9
		  Exec_Master_Log_Pos: 261550
			  Relay_Log_Space: 979
			  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
				  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
			 Master_Info_File: /usr/local/mysql/var/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
		   Master_Retry_Count: 86400
				  Master_Bind: 
	  Last_IO_Error_Timestamp: 
	 Last_SQL_Error_Timestamp: 
			   Master_SSL_Crl: 
		   Master_SSL_Crlpath: 
		   Retrieved_Gtid_Set: 
			Executed_Gtid_Set: 
				Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

下面看一下第二种方法，里面有数据，把丢失的数据也同步过来，还是stduent2表，在没有解决问题之前我先看一下主库的binlog，然后在从库创建一个表wulaoer，再在主库上创建wulaoer然后在表wulaoer中增加一些数据，就会出现同步错误。我在主库中增加了一些数据，下面看带有数据的恢复方法。

这是主库的信息

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       538 |
| mysql-bin.000002 |       143 |
| mysql-bin.000003 |       143 |
| mysql-bin.000004 |      3165 |
| mysql-bin.000005 |    500633 |
| mysql-bin.000006 |    266857 |
+------------------+-----------+
6 rows in set (0.00 sec)

mysql> CREATE TABLE wulaoer(
	-> sno VARCHAR(20) NOT NULL PRIMARY KEY COMMENT"学号",
	-> sname VARCHAR(20) NOT NULL COMMENT"学生姓名",
	-> ssex VARCHAR(20) NOT NULL COMMENT"学生性别",
	-> sbirthday datetime COMMENT"学生出生年月",
	-> class VARCHAR(20) COMMENT"学生所在班级"
	-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT INTO wulaoer values(108,"曾华","男",19770901,95033);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(105,"匡明","男",19751002,95031);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(107,"王丽","女",19760123,95033);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(101,"李军","男",19760220,95033);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(109,"王芳","女",19750210,95031);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO wulaoer values(103,"陆君","男",19740603,95031);
Query OK, 1 row affected (0.00 sec)

在从库上，根据日志来继续同步，不过这个日志之前的数据就会没有了，那我就从最同步出现问题的这个点做同步那就会重新同步，不过这个点一定要准确，如果失败后，那就失败之前的数据没了，如果日志提前就会出现覆盖不了的情况，所以要自己把握一下。不过在问题发送时从库有做其他操作要把自己操作的内容删除了在做同步，或者把从库的所有数据都删除在做，如果有多个从建议从新同步，数据量比较大。也可以把主库的数据备份一下，还原到从库上，然后在根据备份的日志点做同步不过数据量大比较麻烦。下面是把从节点的所有库删除然后在继续同步的，这种情况数据不大的情况下还可以，如果几百T那就分库了。

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='10.211.55.130', MASTER_USER='wulaoer', MASTER_PASSWORD='wulaoer', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=538;
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

验证一下从库wulaoer_mysql02的数据是否正常。

mysql> select * from wulaoer;
ERROR 1046 (3D000): No database selected
mysql> show slave status\G;
*************************** 1. row ***************************
			   Slave_IO_State: Waiting for master to send event
				  Master_Host: 10.211.55.130
				  Master_User: wulaoer
				  Master_Port: 3306
				Connect_Retry: 60
			  Master_Log_File: mysql-bin.000006
		  Read_Master_Log_Pos: 269691
			   Relay_Log_File: wulaoer_server02-relay-bin.000007
				Relay_Log_Pos: 269854
		Relay_Master_Log_File: mysql-bin.000006
			 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: 269691
			  Relay_Log_Space: 770691
			  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
				  Master_UUID: 13fe5e61-1d96-11ea-a91d-000c299d5dbc
			 Master_Info_File: /usr/local/mysql/var/master.info
					SQL_Delay: 0
		  SQL_Remaining_Delay: NULL
	  Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
		   Master_Retry_Count: 86400
				  Master_Bind: 
	  Last_IO_Error_Timestamp: 
	 Last_SQL_Error_Timestamp: 
			   Master_SSL_Crl: 
		   Master_SSL_Crlpath: 
		   Retrieved_Gtid_Set: 
			Executed_Gtid_Set: 
				Auto_Position: 0
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| jumpserver         |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.01 sec)

mysql> use test;
Database changed
mysql> select * from wulaoer;
+------+--------+------+---------------------+-------+
| sno  | sname  | ssex | sbirthday           | class |
+------+--------+------+---------------------+-------+
| 101  | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 103  | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 105  | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 107  | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 108  | 曾华   | 男   | 1977-09-01 00:00:00 | 95033 |
| 109  | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 110  | 曾一   | 男   | 1977-09-01 00:00:00 | 95033 |
| 111  | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 1110 | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 1111 | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 112  | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 113  | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
| 114  | 王芳   | 女   | 1975-02-10 00:00:00 | 95031 |
| 115  | 陆君   | 男   | 1974-06-03 00:00:00 | 95031 |
| 116  | 曾一   | 男   | 1977-09-01 00:00:00 | 95033 |
| 117  | 匡明   | 男   | 1975-10-02 00:00:00 | 95031 |
| 118  | 王丽   | 女   | 1976-01-23 00:00:00 | 95033 |
| 119  | 李军   | 男   | 1976-02-20 00:00:00 | 95033 |
+------+--------+------+---------------------+-------+
18 rows in set (0.00 sec)

数据验证正常，实验测试成功。扩展部分查看binlog日志内容

show binlog events;    #终端查看binlog日志
show binlog events in 'mysql-bin.000002';   #终端查看mysql-bin.000002日志
mysqlbinlog --no-defaults --database=geeRunner --start-datetime="2017-09-17 07:21:09" --stop-datetime="2017-09-19 07:59:50" binlogs.000080 | more  #本地查看某一段时间的binlog日志

以上内容仅供参考，所有知识点已经实验成功。

实验环境

您可以选择一种方式赞助本站

支付宝扫一扫赞助

微信钱包扫描赞助

发表评论取消回复

登录 注册 找回密码

登录注册找回密码