mysql主从宕机恢复步骤
在生产环境中经常会出现slave出现错误，从而发生主从同步故障，此时就需要人工干预了。以下是小生整理出的一个回复思路，欢迎大佬指导，分享更好的方法。
宕机恢复分为几种情况：
1.从库数据一致性要求低
2.从库数据一致性要求高
从库数据要求一致性低：
这种情况比较好解决，由于对数据一致性要求比较低，我们可以先把slave起来从而达到热备份的效果(因为之前有做过全量备份和增量备份，所以不用担心数据丢失)。
以目前master对应的pos作为slave起始的pos。
1MariaDB [db1]> show slave status\G; 2*************************** 1. row *************************** 3 Slave_IO_State: Waiting for master to send event 4 Master_Host: 192.168.1.11 5 Master_User: repluser 6 Master_Port: 3306 7 Connect_Retry: 60 8 Master_Log_File: matser11.000002 9 Read_Master_Log_Pos: 1486 10 Relay_Log_File: mariadb-relay-bin.000002 11 Relay_Log_Pos: 630 12 Relay_Master_Log_File: matser11.000002 13 Slave_IO_Running: Yes 14 Slave_SQL_Running: No 15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 1146 22 Last_Error: Error 'Table 'db1.name' doesn't exist' on query. Default database: 'db1'. Query: 'insert into name values(1,"haha")' 23 Skip_Counter: 0 24 Exec_Master_Log_Pos: 919 25 Relay_Log_Space: 1493 26 Until_Condition: None 27 Until_Log_File: 28 Until_Log_Pos: 0 29 Master_SSL_Allowed: No 30 Master_SSL_CA_File: 31 Master_SSL_CA_Path: 32 Master_SSL_Cert: 33 Master_SSL_Cipher: 34 Master_SSL_Key: 35 Seconds_Behind_Master: NULL 36Master_SSL_Verify_Server_Cert: No 37 Last_IO_Errno: 0 38 Last_IO_Error: 39 Last_SQL_Errno: 1146 40 Last_SQL_Error: Error 'Table 'db1.name' doesn't exist' on query. Default database: 'db1'. Query: 'insert into name values(1,"haha")' 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 11 431 row in set (0.00 sec) 44 45ERROR: No query specified 46
1.将业务主库上锁，阻止对数据的更新
1MariaDB [db1]> SET AUTOCOMMIT=0; 2Query OK, 0 rows affected (0.00 sec) 3MariaDB [db1]> lock tables name read; 4Query OK, 0 rows affected (0.00 sec) 5MariaDB [db1]> show master status; 6+-----------------+----------+--------------+------------------+ 7| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | 8+-----------------+----------+--------------+------------------+ 9| matser11.000002 | 1486 | | | 10+-----------------+----------+--------------+------------------+ 111 row in set (0.00 sec)
2.回到从库重新做主从
1MariaDB [db1]> stop slave ; 2Query OK, 0 rows affected (0.00 sec) 3 4MariaDB [db1]> reset slave all; 5Query OK, 0 rows affected (0.01 sec) 6 7MariaDB [db1]> show slave status\G; 8Empty set (0.00 sec) 9 10ERROR: No query specified 11MariaDB [db1]> change master to master_host="192.168.1.11",master_user="repluser",master_password="123qqq...A",master_log_file="matser11.000002",master_log_pos=1486; 12Query OK, 0 rows affected (0.01 sec) 13MariaDB [db1]> start slave ; 14Query OK, 0 rows affected (0.00 sec) 15 16MariaDB [db1]> show slave status\G; 17*************************** 1. row *************************** 18 Slave_IO_State: Waiting for master to send event 19 Master_Host: 192.168.1.11 20 Master_User: repluser 21 Master_Port: 3306 22 Connect_Retry: 60 23 Master_Log_File: matser11.000002 24 Read_Master_Log_Pos: 1486 25 Relay_Log_File: mariadb-relay-bin.000002 26 Relay_Log_Pos: 528 27 Relay_Master_Log_File: matser11.000002 28 Slave_IO_Running: Yes 29 Slave_SQL_Running: Yes 30 Replicate_Do_DB: 31 Replicate_Ignore_DB: 32 Replicate_Do_Table: 33 Replicate_Ignore_Table: 34 Replicate_Wild_Do_Table: 35 Replicate_Wild_Ignore_Table: 36 Last_Errno: 0 37 Last_Error: 38 Skip_Counter: 0 39 Exec_Master_Log_Pos: 1486 40 Relay_Log_Space: 824 41 Until_Condition: None 42 Until_Log_File: 43 Until_Log_Pos: 0 44 Master_SSL_Allowed: No 45 Master_SSL_CA_File: 46 Master_SSL_CA_Path: 47 Master_SSL_Cert: 48 Master_SSL_Cipher: 49 Master_SSL_Key: 50 Seconds_Behind_Master: 0 51Master_SSL_Verify_Server_Cert: No 52 Last_IO_Errno: 0 53 Last_IO_Error: 54 Last_SQL_Errno: 0 55 Last_SQL_Error: 56 Replicate_Ignore_Server_Ids: 57 Master_Server_Id: 11 581 row in set (0.00 sec)
１）使用LOCK TALBES虽然可以给InnoDB加表级锁，但必须说明的是，表锁不是由InnoDB存储引擎层管理的，而是由其上一层ＭySQL Server负责的，仅当autocommit=0、innodb_table_lock=1（默认设置）时，InnoDB层才能知道MySQL加的表锁，ＭySQL Server才能感知InnoDB加的行锁，这种情况下，InnoDB才能自动识别涉及表级锁的死锁；否则，InnoDB将无法自动检测并处理这种死锁。
２）在用LOCAK TABLES对InnoDB锁时要注意，要将AUTOCOMMIT设为0，否则ＭySQL不会给表加锁；事务结束前，不要用UNLOCAK TABLES释放表锁，因为UNLOCK TABLES会隐含地提交事务；COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁，必须用UNLOCK TABLES释放表锁，正确的方式见如下语句。
1MariaDB [db1]> COMMIT; 2Query OK, 0 rows affected (0.00 sec) 3 4MariaDB [db1]> unlock tables; 5Query OK, 0 rows affected (0.00 sec)
这种方法小生并不推荐
skip掉相关错误
停止slave服务
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
开启slave服务并查看状态
1MariaDB [db1]> stop slave; 2Query OK, 0 rows affected (0.00 sec) 3 4MariaDB [db1]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; 5Query OK, 0 rows affected (0.00 sec) 6 7MariaDB [db1]> start slave; 8Query OK, 0 rows affected (0.00 sec) 9 10MariaDB [db1]> show slave status\G; 11*************************** 1. row *************************** 12 Slave_IO_State: Waiting for master to send event 13 Master_Host: 192.168.1.11 14 Master_User: repluser 15 Master_Port: 3306 16 Connect_Retry: 60 17 Master_Log_File: matser11.000001 18 Read_Master_Log_Pos: 1195 19 Relay_Log_File: mariadb-relay-bin.000003 20 Relay_Log_Pos: 528 21 Relay_Master_Log_File: matser11.000001 22 Slave_IO_Running: Yes 23 Slave_SQL_Running: Yes 24 Replicate_Do_DB: 25 Replicate_Ignore_DB: 26 Replicate_Do_Table: 27 Replicate_Ignore_Table: 28 Replicate_Wild_Do_Table: 29 Replicate_Wild_Ignore_Table: 30 Last_Errno: 0 31 Last_Error: 32 Skip_Counter: 0 33 Exec_Master_Log_Pos: 1195 34 Relay_Log_Space: 2057 35 Until_Condition: None 36 Until_Log_File: 37 Until_Log_Pos: 0 38 Master_SSL_Allowed: No 39 Master_SSL_CA_File: 40 Master_SSL_CA_Path: 41 Master_SSL_Cert: 42 Master_SSL_Cipher: 43 Master_SSL_Key: 44 Seconds_Behind_Master: 0 45Master_SSL_Verify_Server_Cert: No 46 Last_IO_Errno: 0 47 Last_IO_Error: 48 Last_SQL_Errno: 0 49 Last_SQL_Error: 50 Replicate_Ignore_Server_Ids: 51 Master_Server_Id: 11 521 row in set (0.00 sec)
这里跳过的是一个事务。当然，也可以跳过多个事务，但要谨慎，毕竟，你并不知道跳过的是什么事务。
建议：可反复执行上述步骤，仔细查看导致从库不能同步的语句。有的时候，阻止从库的事务太多，这种方法就显得略为低效。
可分析主库日志的事务，来确定SQL_SLAVE_SKIP_COUNTER的合适值
数据库一致性要求高
这种情况比较难受，因为生产环境还在继续，你必须以最快的方式恢复到最新的数据。
旧数据可用：
查看宕机时的偏移量或者最后一条sql语句(可根据时间查看)，并找出原因，加以解决
1MariaDB [db1]> show slave status\G; 2*************************** 1. row *************************** 3 Slave_IO_State: Waiting for master to send event 4 Master_Host: 192.168.1.11 5 Master_User: repluser 6 Master_Port: 3306 7 Connect_Retry: 60 8 Master_Log_File: matser11.000001 9 Read_Master_Log_Pos: 1385 10 Relay_Log_File: mariadb-relay-bin.000003 11 Relay_Log_Pos: 528 12 Relay_Master_Log_File: matser11.000001 13 Slave_IO_Running: Yes 14 Slave_SQL_Running: No 15 Replicate_Do_DB: 16 Replicate_Ignore_DB: 17 Replicate_Do_Table: 18 Replicate_Ignore_Table: 19 Replicate_Wild_Do_Table: 20 Replicate_Wild_Ignore_Table: 21 Last_Errno: 1146 22 Last_Error: Error 'Table 'db1.name' doesn't exist' on query. Default database: 'db1'. Query: 'insert into name values(13,"lala")' 23 Skip_Counter: 0 24 Exec_Master_Log_Pos: 1195 25 Relay_Log_Space: 2247 26 Until_Condition: None 27 Until_Log_File: 28 Until_Log_Pos: 0 29 Master_SSL_Allowed: No 30 Master_SSL_CA_File: 31 Master_SSL_CA_Path: 32 Master_SSL_Cert: 33 Master_SSL_Cipher: 34 Master_SSL_Key: 35 Seconds_Behind_Master: NULL 36Master_SSL_Verify_Server_Cert: No 37 Last_IO_Errno: 0 38 Last_IO_Error: 39 Last_SQL_Errno: 1146 40 Last_SQL_Error: Error 'Table 'db1.name' doesn't exist' on query. Default database: 'db1'. Query: 'insert into name values(13,"lala")' 41 Replicate_Ignore_Server_Ids: 42 Master_Server_Id: 11 431 row in set (0.00 sec) 44 45ERROR: No query specified 46[root@slave mysql]# mysqlbinlog mariadb-relay-bin.000003 47/*!*/; 48# at 595 49#200821 19:28:18 server id 11 end_log_pos 1358 Query thread_id=14 exec_time=0 error_code=0 50use `db1`/*!*/; 51SET TIMESTAMP=1598009298/*!*/; 52insert into name values(13,"lala") 53/*!*/; 54# at 691 55#200821 19:28:18 server id 11 end_log_pos 1385 Xid = 512 56COMMIT/*!*/; 57DELIMITER ; 58# End of log file 59ROLLBACK /* added by mysqlbinlog */; 60/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/; 61/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
将业务主库上锁，阻止对数据的更新
1MariaDB [db1]> SET AUTOCOMMIT=0; 2Query OK, 0 rows affected (0.00 sec) 3MariaDB [db1]> lock tables name read; 4Query OK, 0 rows affected (0.00 sec)
将缺失数据导出
1[root@master mysql]# date 22020年 08月 21日 星期五 19:57:42 CST 3[root@master mysql]# mysqlbinlog --start-datetime="20-08-21 19:28:18" --stop-datetime="20--8-21 19:57:42" matser11.000001 > data.sql 4[root@master mysql]# scp data.sql root@192.168.1.12:/root
恢复数据并查看
1[root@slave mysql]# mysql -uroot -p < /root/data.sql 2[root@slave mysql]# mysql 3....
恢复主从(这里最好重做主从，由于我的是缺失库导致sql线程失败，所以重启slave就ok了)
1MariaDB [db1]> stop slave; 2MariaDB [db1]> start slave; 3MariaDB [db1]> show slave status\G; 4*************************** 1. row *************************** 5 Slave_IO_State: Waiting for master to send event 6 Master_Host: 192.168.1.11 7 Master_User: repluser 8 Master_Port: 3306 9 Connect_Retry: 60 10 Master_Log_File: matser11.000001 11 Read_Master_Log_Pos: 5185 12 Relay_Log_File: mariadb-relay-bin.000004 13 Relay_Log_Pos: 1098 14 Relay_Master_Log_File: matser11.000001 15 Slave_IO_Running: Yes 16 Slave_SQL_Running: Yes 17 Replicate_Do_DB: 18 Replicate_Ignore_DB: 19 Replicate_Do_Table: 20 Replicate_Ignore_Table: 21 Replicate_Wild_Do_Table: 22 Replicate_Wild_Ignore_Table: 23 Last_Errno: 0 24 Last_Error: 25 Skip_Counter: 0 26 Exec_Master_Log_Pos: 5185 27 Relay_Log_Space: 5097 28 Until_Condition: None 29 Until_Log_File: 30 Until_Log_Pos: 0 31 Master_SSL_Allowed: No 32 Master_SSL_CA_File: 33 Master_SSL_CA_Path: 34 Master_SSL_Cert: 35 Master_SSL_Cipher: 36 Master_SSL_Key: 37 Seconds_Behind_Master: 0 38Master_SSL_Verify_Server_Cert: No 39 Last_IO_Errno: 0 40 Last_IO_Error: 41 Last_SQL_Errno: 0 42 Last_SQL_Error: 43 Replicate_Ignore_Server_Ids: 44 Master_Server_Id: 11 451 row in set (0.00 sec) 46 47ERROR: No query specified
解开主库的锁
1MariaDB [db1]> COMMIT; 2Query OK, 0 rows affected (0.00 sec) 3 4MariaDB [db1]> unlock tables; 5Query OK, 0 rows affected (0.00 sec)
旧数据不可用：
清空原来的主从
同上
使用最新一次的全量备份和增量备份先恢复大量数据。
1[root@slave mysql]# mysql -uroot -p < /mysql/all/data.sql 2[root@slave mysql]# mysql -uroot -p < /mysql/name/data.sql
最后再合适的时机将业务主库上锁，阻止对数据的更新。
同上
利用binlog日志导出剩余的数据进行恢复。(此时的起始值应该是上次增量备份结束的时间)
同上
恢复主从
同上
解开主库的锁
优化方案
什么？运维小哥没做备份？(不想混了？)嫌麻烦？
那强烈推荐PXC集群。