mysql主从宕机恢复步骤
在生产环境中经常会出现slave出现错误,从而发生主从同步故障,此时就需要人工干预了。以下是小生整理出的一个回复思路,欢迎大佬指导,分享更好的方法。
宕机恢复分为几种情况:
1.从库数据一致性要求低
2.从库数据一致性要求高
从库数据要求一致性低:
这种情况比较好解决,由于对数据一致性要求比较低,我们可以先把slave起来从而达到热备份的效果(因为之前有做过全量备份和增量备份,所以不用担心数据丢失)。
以目前master对应的pos作为slave起始的pos。
MariaDB [db1]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.11
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: matser11.000002
Read_Master_Log_Pos: 1486
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 630
Relay_Master_Log_File: matser11.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: 1146
Last_Error: Error 'Table 'db1.name' doesn't exist' on query. Default database: 'db1'. Query: 'insert into name values(1,"haha")'
Skip_Counter: 0
Exec_Master_Log_Pos: 919
Relay_Log_Space: 1493
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: 1146
Last_SQL_Error: Error 'Table 'db1.name' doesn't exist' on query. Default database: 'db1'. Query: 'insert into name values(1,"haha")'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 11
1 row in set (0.00 sec)
ERROR: No query specified
1.将业务主库上锁,阻止对数据的更新
MariaDB [db1]> SET AUTOCOMMIT=0;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> lock tables name read;
Query OK, 0 rows affected (0.00 sec)
MariaDB [db1]> show master status;
+-----------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------