mysql主从宕机恢复步骤

Wesley13
• 阅读 505

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 |
+-----------------+----------+--------------+------------------+
| matser11.000002 |     1486 |              |                  |
+-----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

2.回到从库重新做主从

MariaDB [db1]> stop slave ;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> reset slave all;
Query OK, 0 rows affected (0.01 sec)

MariaDB [db1]> show slave status\G;
Empty set (0.00 sec)

ERROR: No query specified
MariaDB [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;
Query OK, 0 rows affected (0.01 sec)
MariaDB [db1]> start slave ;
Query OK, 0 rows affected (0.00 sec)

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: 528
        Relay_Master_Log_File: matser11.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: 1486
              Relay_Log_Space: 824
              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: 11
1 row in set (0.00 sec)

​ 1)使用LOCK TALBES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层MySQL Server负责的,仅当autocommit=0、innodb_table_lock=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL Server才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。

​ 2)在用LOCAK TABLES对InnoDB锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCAK TABLES释放表锁,因为UNLOCK TABLES会隐含地提交事务;COMMIT或ROLLBACK产不能释放用LOCAK TABLES加的表级锁,必须用UNLOCK TABLES释放表锁,正确的方式见如下语句。

MariaDB [db1]> COMMIT;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> unlock tables;
Query OK, 0 rows affected (0.00 sec)

这种方法小生并不推荐

skip掉相关错误

停止slave服务

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

开启slave服务并查看状态

MariaDB [db1]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> start slave;
Query OK, 0 rows affected (0.00 sec)

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.000001
          Read_Master_Log_Pos: 1195
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 528
        Relay_Master_Log_File: matser11.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: 1195
              Relay_Log_Space: 2057
              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: 11
1 row in set (0.00 sec)

这里跳过的是一个事务。当然,也可以跳过多个事务,但要谨慎,毕竟,你并不知道跳过的是什么事务。

建议:可反复执行上述步骤,仔细查看导致从库不能同步的语句。有的时候,阻止从库的事务太多,这种方法就显得略为低效。

可分析主库日志的事务,来确定SQL_SLAVE_SKIP_COUNTER的合适值

数据库一致性要求高

这种情况比较难受,因为生产环境还在继续,你必须以最快的方式恢复到最新的数据。

旧数据可用:

查看宕机时的偏移量或者最后一条sql语句(可根据时间查看),并找出原因,加以解决

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.000001
          Read_Master_Log_Pos: 1385
               Relay_Log_File: mariadb-relay-bin.000003
                Relay_Log_Pos: 528
        Relay_Master_Log_File: matser11.000001
             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(13,"lala")'
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1195
              Relay_Log_Space: 2247
              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(13,"lala")'
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 11
1 row in set (0.00 sec)

ERROR: No query specified
[root@slave mysql]# mysqlbinlog mariadb-relay-bin.000003 
/*!*/;
# at 595
#200821 19:28:18 server id 11  end_log_pos 1358     Query    thread_id=14    exec_time=0    error_code=0
use `db1`/*!*/;
SET TIMESTAMP=1598009298/*!*/;
insert into name values(13,"lala")
/*!*/;
# at 691
#200821 19:28:18 server id 11  end_log_pos 1385     Xid = 512
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

将业务主库上锁,阻止对数据的更新

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)

将缺失数据导出

[root@master mysql]# date
2020年 08月 21日 星期五 19:57:42 CST
[root@master mysql]# mysqlbinlog --start-datetime="20-08-21 19:28:18"  --stop-datetime="20--8-21 19:57:42" matser11.000001 > data.sql
[root@master mysql]# scp data.sql root@192.168.1.12:/root

恢复数据并查看

[root@slave mysql]# mysql -uroot -p < /root/data.sql
[root@slave mysql]# mysql
....

恢复主从(这里最好重做主从,由于我的是缺失库导致sql线程失败,所以重启slave就ok了)

MariaDB [db1]> stop slave;
MariaDB [db1]> start slave;
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.000001
          Read_Master_Log_Pos: 5185
               Relay_Log_File: mariadb-relay-bin.000004
                Relay_Log_Pos: 1098
        Relay_Master_Log_File: matser11.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: 5185
              Relay_Log_Space: 5097
              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: 11
1 row in set (0.00 sec)

ERROR: No query specified

解开主库的锁

MariaDB [db1]> COMMIT;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> unlock tables;
Query OK, 0 rows affected (0.00 sec)
旧数据不可用:

清空原来的主从

​ 同上

使用最新一次的全量备份和增量备份先恢复大量数据。

[root@slave mysql]# mysql -uroot -p < /mysql/all/data.sql
[root@slave mysql]# mysql -uroot -p < /mysql/name/data.sql

最后再合适的时机将业务主库上锁,阻止对数据的更新。

​ 同上

利用binlog日志导出剩余的数据进行恢复。(此时的起始值应该是上次增量备份结束的时间)

​ 同上

恢复主从

​ 同上

解开主库的锁

优化方案

什么?运维小哥没做备份?(不想混了?)嫌麻烦?

那强烈推荐PXC集群。

点赞
收藏
评论区
推荐文章
blmius blmius
3年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
Stella981 Stella981
3年前
KVM调整cpu和内存
一.修改kvm虚拟机的配置1、virsheditcentos7找到“memory”和“vcpu”标签,将<namecentos7</name<uuid2220a6d1a36a4fbb8523e078b3dfe795</uuid
Easter79 Easter79
3年前
Twitter的分布式自增ID算法snowflake (Java版)
概述分布式系统中,有一些需要使用全局唯一ID的场景,这种时候为了防止ID冲突可以使用36位的UUID,但是UUID有一些缺点,首先他相对比较长,另外UUID一般是无序的。有些时候我们希望能使用一种简单一些的ID,并且希望ID能够按照时间有序生成。而twitter的snowflake解决了这种需求,最初Twitter把存储系统从MySQL迁移
Wesley13 Wesley13
3年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
3年前
00:Java简单了解
浅谈Java之概述Java是SUN(StanfordUniversityNetwork),斯坦福大学网络公司)1995年推出的一门高级编程语言。Java是一种面向Internet的编程语言。随着Java技术在web方面的不断成熟,已经成为Web应用程序的首选开发语言。Java是简单易学,完全面向对象,安全可靠,与平台无关的编程语言。
Stella981 Stella981
3年前
Django中Admin中的一些参数配置
设置在列表中显示的字段,id为django模型默认的主键list_display('id','name','sex','profession','email','qq','phone','status','create_time')设置在列表可编辑字段list_editable
Wesley13 Wesley13
3年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Python进阶者 Python进阶者
9个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这