MySQL学习笔记--主从复制故障重置操作

来源：互联网发布：淘宝反恶联盟编辑：程序博客网时间：2024/06/05 22:53

一. 观察复制同步

前面已经完成简单的复制同步的配置，下面我们再进行一些测试观察

1. 先在master库进行一些操作，

--建库建表并插入些记录

mysql> create database licz;

Query OK, 1 row affected (0.00sec)

mysql> use licz;

Database changed

mysql> create table t1(idint,str varchar(30));

Query OK, 0 rows affected (0.10sec)

mysql> insert into t1 select1,'Hello MySQL!!';

Query OK, 1 row affected (0.02sec)

Records: 1 Duplicates: 0 Warnings: 0

--查看所日志（或用show binarylogs命令）

mysql> show master logs;

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

| Log_name | File_size |

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

| master-bin.000001 | 550 |

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

1 row in set (0.00 sec)

--切换日志

mysql> flush logs;

Query OK, 0 rows affected (0.08sec)

mysql> show master logs;

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

| Log_name | File_size |

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

| master-bin.000001 | 598 |

| master-bin.000002 | 120 |

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

2 rows in set (0.00 sec)

mysql> insert into t1 select2,'Happy new year!';

Query OK, 1 row affected (0.01sec)

Records: 1 Duplicates: 0 Warnings: 0

--查看master当前状态

mysql> show master status;

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

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

| master-bin.000002 | 345 | | | |

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

1 row in set (0.00 sec)

--查看二进制日志里有哪些事件

mysql> show binlog events\G; #查看第一个日志文件的内容

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

Log_name: master-bin.000001

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 120

Info: Server ver: 5.6.28-log, Binlog ver: 4

*************************** 2.row ***************************

Log_name: master-bin.000001

Pos: 120

Event_type: Query

Server_id: 1

End_log_pos: 214

Info: create database licz

*************************** 3.row ***************************

Log_name: master-bin.000001

Pos: 214

Event_type: Query

Server_id: 1

End_log_pos: 327

Info: use `licz`; create table t1(idint,str varchar(30))

*************************** 4.row ***************************

Log_name: master-bin.000001

Pos: 327

Event_type: Query

Server_id: 1

End_log_pos: 406

Info: BEGIN

*************************** 5.row ***************************

Log_name: master-bin.000001

Pos: 406

Event_type: Query

Server_id: 1

End_log_pos: 519

Info: use `licz`; insert into t1 select1,'Hello MySQL!!'

*************************** 6.row ***************************

Log_name: master-bin.000001

Pos: 519

Event_type: Xid

Server_id: 1

End_log_pos: 550

Info: COMMIT /* xid=127 */

*************************** 7.row ***************************

Log_name: master-bin.000001

Pos: 550

Event_type: Rotate

Server_id: 1

End_log_pos: 598

Info: master-bin.000002;pos=4

7 rows in set (0.00 sec)

ERROR:

No query specified

mysql> show binlog events in 'master-bin.000002'\G; #查看指定日志文件的内容

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

Log_name: master-bin.000002

Pos: 4

Event_type: Format_desc

Server_id: 1

End_log_pos: 120

Info: Server ver: 5.6.28-log, Binlogver: 4

*************************** 2.row ***************************

Log_name: master-bin.000002

Pos: 120

Event_type: Query

Server_id: 1

End_log_pos: 199

Info: BEGIN

*************************** 3.row ***************************

Log_name: master-bin.000002

Pos: 199

Event_type: Query

Server_id: 1

End_log_pos: 314

Info: use `licz`; insert into t1 select2,'Happy new year!'

*************************** 4.row ***************************

Log_name: master-bin.000002

Pos: 314

Event_type: Xid

Server_id: 1

End_log_pos: 345

Info: COMMIT /* xid=131 */

4 rows in set (0.00 sec)

ERROR:

No query specified

2. 在Slave端查看复制同步情况

--查看同步内容，可以看到slave已经同步完成

mysql> use licz;

Database changed

mysql> select * from t1;

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

| id | str |

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

| 1 | Hello MySQL!! |

| 2 | Happy new year! |

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

2 rows in set (0.00 sec)

--查看slave库的状态

mysql> show slave status\G;

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

Slave_IO_State: Waiting formaster to send event

Master_Host: host8

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000002

Read_Master_Log_Pos: 345

Relay_Log_File:slave_relay_bin.000005

Relay_Log_Pos: 509

Relay_Master_Log_File:master-bin.000002

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

Relay_Log_Space: 730

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:d3cf4fc0-aaca-11e5-a73b-000c29a2e5a2

Master_Info_File:/var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has readall 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

二. 模拟同步故障并重置同步

1. 模拟同步故障

下面来模拟主从同步故障案例

--先在Slave从库创建一个表

mysql> create table t2(idint);

Query OK, 0 rows affected (0.08sec)

--再在Master主库创建一个同名的表

mysql> create table t2(idint);

Query OK, 0 rows affected (0.08sec)

此时可以看到主从库已经停止同步，由于从库已经存在此表产生了数据库的一不致

--查看Slave状态

mysql> show slave status\G;

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

Slave_IO_State: Waiting formaster to send event

Master_Host: host8

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File:master-bin.000002

Read_Master_Log_Pos: 442

Relay_Log_File:slave_relay_bin.000005

Relay_Log_Pos: 509

Relay_Master_Log_File:master-bin.000002

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 't2'already exists' on query. Default database: 'licz'. Query: 'create table t2(idint)'

Skip_Counter: 0

Exec_Master_Log_Pos: 345

Relay_Log_Space: 827

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 't2'already exists' on query. Default database: 'licz'. Query: 'create table t2(idint)'

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

Master_UUID:d3cf4fc0-aaca-11e5-a73b-000c29a2e5a2

Master_Info_File:/var/lib/mysql/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: 151229 14:16:35

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

2. 重置主从同步

为从重新使主从进行同步，解决办法之一就是重置Master和slave复制

--查看Master状态

mysql> show master status\G;

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

File: master-bin.000002

Position: 657

Binlog_Do_DB:

Binlog_Ignore_DB:

Executed_Gtid_Set:

1 row in set (0.00 sec)

ERROR:

No query specified

--首先在Slave删除和主库名字冲突的表,停止并重置Slave

mysql> drop table t2;

Query OK, 0 rows affected (0.04sec)

mysql> stop slave;

Query OK, 0 rows affected (0.03sec)

mysql> reset slave;

Query OK, 0 rows affected (0.13sec)

--然后在Master端删除此表，并重置Master

mysql> drop table t2;

Query OK, 0 rows affected (0.12sec)

mysql> reset master;

Query OK, 0 rows affected (0.09sec)

--此时再查看Master状态，发现binlog又从头开始轮循了

mysql> show master status;

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

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

| master-bin.000001 | 120 | | | |

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

1 row in set (0.00 sec)

--启动slave

mysql> start slave;

Query OK, 0 rows affected (0.08sec)

--查看Slave状态

mysql> show slave status\G;

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

Slave_IO_State: Waiting formaster to send event

Master_Host: host8

Master_User: repl_user

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: master-bin.000001

Read_Master_Log_Pos: 120

Relay_Log_File:slave_relay_bin.000003

Relay_Log_Pos: 284

Relay_Master_Log_File:master-bin.000001

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

Relay_Log_Space: 457

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:d3cf4fc0-aaca-11e5-a73b-000c29a2e5a2

Master_Info_File:/var/lib/mysql/master.info

SQL_Delay: 0

SQL_Remaining_Delay: NULL

Slave_SQL_Running_State: Slave has readall 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)

此时重置后主从就可以开始同步了！

0 0