024、xtrabackup搭建主从、主从故障演练
xtrabackup搭建主从
1、清除环境
从库执行：
mysql> stop slave;
Query OK, 0 rows affected (0.20 sec)
mysql> reset slave all;
Query OK, 0 rows affected (0.03 sec)2、主库使用xtrabackup备份
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user=root --password=root --socket=/tmp/mysql.sock /u01/bakdata/xtrabackup/full_202105123、主库使用xtrabackup恢复，目的是追加binlog中备份时产生的数据
[root@localhost ~]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=root --socket=/tmp/mysql.sock --apply-log /u01/bakdata/xtrabackup/full_20210512/4、将备份传送到从库
[root@localhost ~]# scp -r /u01/bakdata/xtrabackup/full_20210512 192.168.100.112:/u01/bakdata/xtrabackup/full_202105125、从库恢复，重新指定新的数据文件
[root@localhost ~]# pkill mysql
[root@localhost ~]# mv /u01/data/mysql/ /u01/data/mysql_old
[root@localhost ~]# cp -r /u01/bakdata/xtrabackup/full_20210512/ /u01/data/mysql
[root@localhost ~]# chown -R mysql:mysql /u01/data/mysql6、主库赋权，从库搭建主库
mysql> grant replication slave on *.* to 'uslave'@'192.168.100.%' identified by 'uslave';
Query OK, 0 rows affected (0.01 sec)从库搭建主从时，position号从下面的文件中查找：
[root@localhost ~]# cat /u01/bakdata/xtrabackup/full_20210512/xtrabackup_binlog_info
mysql-bin.000013 337 e1fe2aff-afc8-11eb-a7c9-08002765b4fe:1-582若是之前开启了GTID复制，会报错：
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.111',MASTER_USER='uslave',MASTER_PASSWORD='uslave',MASTER_PORT=3306,MASTER_LOG_FILE='mysql_bin.000005',MASTER_LOG_POS=191;
ERROR 1776 (HY000): Parameters MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE and RELAY_LOG_POS cannot be set when MASTER_AUTO_POSITION is active.解决办法：
mysql> change master to MASTER_AUTO_POSITION=0;
Query OK, 0 rows affected (0.11 sec)
mysql> CHANGE MASTER TO MASTER_HOST='192.168.100.111',MASTER_USER='uslave',MASTER_PASSWORD='uslave',MASTER_PORT=3306,MASTER_LOG_FILE='mysql_bin.000005',MASTER_LOG_POS=191;
Query OK, 0 rows affected, 2 warnings (0.04 sec)7、启动主从，查看主从状态
mysql> start slave;
Query OK, 0 rows affected (0.21 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.111
Master_User: uslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000013
Read_Master_Log_Pos: 1010
Relay_Log_File: localhost-relay-bin.000002
Relay_Log_Pos: 987
Relay_Master_Log_File: mysql-bin.000013
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: 1010
Relay_Log_Space: 1195
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: 3
Master_UUID: e1fe2aff-afc8-11eb-a7c9-08002765b4fe
Master_Info_File: /u01/data/mysql/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: e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-585
Executed_Gtid_Set: e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-585
Auto_Position: 0
1 row in set (0.03 sec)主从故障
参数：sql_slave_skip_counter，当主从同步错误时，若已确定从库数据与主库一致，可以设置该参数，跳过一个事务。
mysql> set global sql_slave_skip_counter=1;1、主键冲突，错误代码1062
创建表，含有主键，并插入测式数据：
mysql> create table t(id int not null auto_increment,primary key(id));
Query OK, 0 rows affected (0.10 sec)
mysql> insert into t select 1;
Query OK, 1 row affected (0.07 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 2;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 3;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)查看从库同步之后，向从库插入数据：
mysql> select *from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
+----+
3 rows in set (0.00 sec)
mysql> insert into t values(4);
Query OK, 1 row affected (0.03 sec)主库并不会同步从库的数据，此时向主库插入从库已存在的数据：
mysql> insert into t values(4);
Query OK, 1 row affected (0.01 sec)从库查看主从状态：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.111
Master_User: uslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 1477
Relay_Log_File: localhost-relay-bin.000006
Relay_Log_Pos: 1452
Relay_Master_Log_File: mysql-bin.000014
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: 1062
Last_Error: Could not execute Write_rows event on table test.t; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000014, end_log_pos 1446
Skip_Counter: 0
Exec_Master_Log_Pos: 1242
Relay_Log_Space: 2098
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: 1062
Last_SQL_Error: Could not execute Write_rows event on table test.t; Duplicate entry '4' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's master log mysql-bin.000014, end_log_pos 1446
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: e1fe2aff-afc8-11eb-a7c9-08002765b4fe
Master_Info_File: /u01/data/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: 210513 11:19:18
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-592
Executed_Gtid_Set: bebea676-b309-11eb-bcff-0800275fc998:1,
e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-591
Auto_Position: 0
1 row in set (0.00 sec)以上可以查看到报错信息：Duplicate entry '4' for key 'PRIMARY'
解决方法：在从库手动删除重复的数据，或者使用percona的工具：percona-toolkit
[root@localhost ~]# pt-slave-restart -uroot -proot
2021-05-13T11:38:21 p=...,u=root localhost-relay-bin.000006 1452 1062
^CExiting on SIGINT.
--命令启动后，ctrl+c停止即可。再次查看主从状态，已恢复正常：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.111
Master_User: uslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 1477
Relay_Log_File: localhost-relay-bin.000006
Relay_Log_Pos: 1687
Relay_Master_Log_File: mysql-bin.000014
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: 1477
Relay_Log_Space: 2098
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: 3
Master_UUID: e1fe2aff-afc8-11eb-a7c9-08002765b4fe
Master_Info_File: /u01/data/mysql/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: e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-592
Executed_Gtid_Set: bebea676-b309-11eb-bcff-0800275fc998:1,
e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-592
Auto_Position: 0
1 row in set (0.00 sec)2、少数据，错误代码1032
从库删除一条数据，主库再执行更新：
mysql> select * from t;
+----+
| id |
+----+
| 1 |
| 2 |
| 3 |
| 4 |
+----+
4 rows in set (0.11 sec)
mysql> delete from t where id=4;
Query OK, 1 row affected (0.17 sec)mysql> update t set id=44 where id=4;
Query OK, 1 row affected (0.11 sec)
Rows matched: 1 Changed: 1 Warnings: 0从库查看主从状态：
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.111
Master_User: uslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 1718
Relay_Log_File: localhost-relay-bin.000006
Relay_Log_Pos: 1687
Relay_Master_Log_File: mysql-bin.000014
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: 1032
Last_Error: Could not execute Update_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000014, end_log_pos 1687
Skip_Counter: 0
Exec_Master_Log_Pos: 1477
Relay_Log_Space: 2339
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: 1032
Last_SQL_Error: Could not execute Update_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000014, end_log_pos 1687
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
Master_UUID: e1fe2aff-afc8-11eb-a7c9-08002765b4fe
Master_Info_File: /u01/data/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: 210513 14:59:36
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-593
Executed_Gtid_Set: bebea676-b309-11eb-bcff-0800275fc998:1-2,
e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-592
Auto_Position: 0
1 row in set (0.00 sec)查看报错信息：
Last_SQL_Error: Could not execute Update_rows event on table test.t; Can't find record in 't', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000014, end_log_pos 1687
根据报错信息，在主库分析binlog日志，通过日志挖掘，找出报错的数据，手动在从库插入：
[root@localhost ~]# mysqlbinlog --no-defaults -v -v --base64-output=decode-rows /u01/data/mysql/mysql-bin.000014 > /u01/1.txt[root@localhost ~]# vi /u01/1.txt
……
BEGIN
/*!*/;
# at 1597
#210513 14:59:35 server id 3 end_log_pos 1641 CRC32 0xc91e217f Table_map: `test`.`t` mapped to number 73
# at 1641
#210513 14:59:35 server id 3 end_log_pos 1687 CRC32 0x030fb6af Update_rows: table id 73 flags: STMT_END_F
### UPDATE `test`.`t`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### SET
### @1=44 /* INT meta=0 nullable=0 is_null=0 */
# at 1687
#210513 14:59:35 server id 3 end_log_pos 1718 CRC32 0x06591243 Xid = 43
COMMIT/*!*/;mysql> insert into t values(44);
Query OK, 1 row affected (0.07 sec)然后再使用percona的工具解决：
[root@localhost ~]# pt-slave-restart -uroot -proot
2021-05-13T15:12:44 p=...,u=root localhost-relay-bin.000006 1687 1032
^CExiting on SIGINT.
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.111
Master_User: uslave
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000014
Read_Master_Log_Pos: 1718
Relay_Log_File: localhost-relay-bin.000007
Relay_Log_Pos: 354
Relay_Master_Log_File: mysql-bin.000014
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: 1718
Relay_Log_Space: 2339
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: 3
Master_UUID: e1fe2aff-afc8-11eb-a7c9-08002765b4fe
Master_Info_File: /u01/data/mysql/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: e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-593
Executed_Gtid_Set: bebea676-b309-11eb-bcff-0800275fc998:1-3,
e1fe2aff-afc8-11eb-a7c9-08002765b4fe:583-593
Auto_Position: 0
1 row in set (0.00 sec)3、主从server id一致，错误号1593
解决方法：从库修改/etc/my.cnf中server id号，然后重启mysql服务。
4、主从uuid号一致
解决方法：从库删除/u01/data/mysql/auto.cnf，并重启mysql服务。
3和4问题经常因为克隆主机搭建主从时出现，所以，搭建主从时，尽量不要克隆主机。
浙公网安备 33010602011771号