MySQL实例恢复

爱被打了一巴掌 2022-05-13 03:50 171阅读 0赞

MySQL实例在异常宕机重启后，会自动启动实例恢复。由于MySQL为多引擎数据库，所以需要说明的是MySQL实例恢复，实质上指的是对事务进行恢复，即对innodb恢复。本文简要描述mysql实例恢复的步骤，并通过具体演示来感受mysql实例恢复的过程。

## 一、MySQL实例 ##

MySQL实例就是mysqld后台进程以及多个线程再加上内存分配  
![这里写图片描述][70]

## 二、MySQL实例恢复的步骤 ##

![这里写图片描述][70 1]

## 三、InnoDB恢复过程 ##

InnoDB崩溃恢复包括几个步骤：

#### 1、应用重做日志 ####

重做日志应用程序是第一步，在实例初始化期间执行，此时不接受任何连接。如果在关机或崩溃时，所有更改都从缓冲池刷新到表空间（ibdata *和* .ibd文件），那么重做日志应用程序可以跳过。如果启动时缺少重做日志文件，InnoDB会跳过重做日志应用。即使数据丢失，也不建议删除重做日志以加快恢复过程。仅在干净关闭后才被视为一个选项执行，删除重做日志是可以接受的，innodb\_fast\_shutdown设置为0或1。

#### 2、回滚未完成的事务 ####

在崩溃时处于活动状态(未提交)的任何事务都将回滚。回滚未完成的事务所花费的时间可能是事务在中断之前处于活动状态的时间长度三倍或四倍，具体取决于服务器负载。无法取消正在回滚的事务。在极端情况下，回滚可能需要特别长的时间，也可能很快，取决于innodb\_force\_recovery设置为3或更高值。

#### 3、更改缓冲区合并 ####

将更改缓冲区（系统表空间的一部分）中的更改应用于二级索引的叶页，因为索引页被读取到缓冲池。

#### 4、清除非活动事物 ####

删除任何标记已删除记录，那些对活动事务不再可见的记录。重做日志应用之后的步骤不依赖于重做日志（除了用于记录重做日志）并且正常处理并行执行。其中，只有不完整的回滚事务对于崩溃恢复是特殊的。插入缓冲区合并和清除是在正常处理期间执行。

#### 5、尽快接受客户端请求，减少宕机时间 ####

作为崩溃恢复的一部分，在服务器崩溃，InnoDB回滚任何未提交的事务或在XA PREPARE状态下的事务。回滚由后台线程执行，与来自新连接的事务并行执行。在回滚操作完成之前，新连接可能会遇到与已恢复事务的锁定冲突。在大多数情况下，即使MySQL服务器在繁重的活动中被意外杀死，恢复过程自动发生，DBA不需要任何操作。如果是硬件失败或严重的系统错误导致InnoDB数据损坏，MySQL可能会拒绝启动。

## 四、演示实例恢复 ##

[root@centos7 ~]# more /etc/redhat-release 
    CentOS Linux release 7.2.1511 (Core) 
    
    (root@localhost)[(none)]> show variables like 'version'; 
    +---------------+------------+
    | Variable_name | Value |
    +---------------+------------+
    | version | 5.7.23-log |
    +---------------+------------+
    
    (root@localhost)[(none)]> drop table if exists sakila.t20;
    
    (root@localhost)[(none)]> create table sakila.t20(id int,descr varchar(20));
    Query OK, 0 rows affected (0.02 sec)
    
    (root@localhost)[(none)]> insert into sakila.t20 values(1,'Instrecovery');
    Query OK, 1 row affected (0.00 sec)
    
    (root@localhost)[(none)]> set autocommit=0;
    Query OK, 0 rows affected (0.00 sec)
    
    (root@localhost)[(none)]> insert into sakila.t20 values(2,'lost');
    Query OK, 1 row affected (0.01 sec)
    
    [root@centos7 ~]# ps -ef|grep mysqld
    mysql 6012 1 0 21:56 ? 00:00:01 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
    root 6819 5007 0 22:12 pts/2 00:00:00 grep --color=auto mysqld
    [root@centos7 ~]# 
    [root@centos7 ~]# kill -9 6012
    
    mysqld会自动重启，观察日志的输出情况
    [root@centos7 ~]# tail -fn 100 /var/lib/mysql/mysqld.log 
    2018-08-17T22:13:58.162282+08:00 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
    2018-08-17T22:13:58.163716+08:00 0 [Note] /usr/sbin/mysqld (mysqld 5.7.23-log) starting as process 6902 ...
    2018-08-17T22:13:58.169230+08:00 0 [Note] InnoDB: PUNCH HOLE support available
    2018-08-17T22:13:58.169343+08:00 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
    2018-08-17T22:13:58.169363+08:00 0 [Note] InnoDB: Uses event mutexes
    2018-08-17T22:13:58.169371+08:00 0 [Note] InnoDB: GCC builtin __atomic_thread_fence() is used for memory barrier
    2018-08-17T22:13:58.169377+08:00 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
    2018-08-17T22:13:58.169383+08:00 0 [Note] InnoDB: Using Linux native AIO
    2018-08-17T22:13:58.169973+08:00 0 [Note] InnoDB: Number of pools: 1
    2018-08-17T22:13:58.170188+08:00 0 [Note] InnoDB: Using CPU crc32 instructions
    2018-08-17T22:13:58.172706+08:00 0 [Note] InnoDB: Initializing buffer pool, total size = 128M, instances = 1, chunk size = 128M
    2018-08-17T22:13:58.184610+08:00 0 [Note] InnoDB: Completed initialization of buffer pool
    2018-08-17T22:13:58.187623+08:00 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().
    2018-08-17T22:13:58.202938+08:00 0 [Note] InnoDB: Highest supported file format is Barracuda.
    2018-08-17T22:13:58.206243+08:00 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 831040248 --检查点位置，检点点位置之后的日志需要确认是否重放或回滚
    2018-08-17T22:13:58.206338+08:00 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 831040257 --日志位置
    2018-08-17T22:13:58.206363+08:00 0 [Note] InnoDB: Database was not shutdown normally! --提示数据库异常关闭
    2018-08-17T22:13:58.206372+08:00 0 [Note] InnoDB: Starting crash recovery. --开始崩溃恢复，以下提示一个事务在undo，需要回滚
    2018-08-17T22:13:58.221492+08:00 0 [Note] InnoDB: 1 transaction(s) which must be rolled back or cleaned up in total 1 row operations to undo
    2018-08-17T22:13:58.221564+08:00 0 [Note] InnoDB: Trx id counter is 38144 --下一行提示binlog位置
    2018-08-17T22:13:58.223102+08:00 0 [Note] InnoDB: Last MySQL binlog file position 0 1004, file name mysqlbin.000012
    2018-08-17T22:13:58.358681+08:00 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1" --移除及生成临时表空间
    2018-08-17T22:13:58.358732+08:00 0 [Note] InnoDB: Creating shared tablespace for temporary tables
    2018-08-17T22:13:58.358824+08:00 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
    2018-08-17T22:13:58.374256+08:00 0 [Note] InnoDB: Starting in background the rollback of uncommitted transactions
    2018-08-17T22:13:58.374350+08:00 0 [Note] InnoDB: Rolling back trx with id 37646, 1 rows to undo --开始回滚
    2018-08-17T22:13:58.384470+08:00 0 [Note] InnoDB: Rollback of trx with id 37646 completed --回滚完成
    2018-08-17T22:13:58.384553+08:00 0 [Note] InnoDB: Rollback of non-prepared transactions completed
    2018-08-17T22:13:58.385412+08:00 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
    2018-08-17T22:13:58.387835+08:00 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
    2018-08-17T22:13:58.387882+08:00 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
    2018-08-17T22:13:58.389416+08:00 0 [Note] InnoDB: Waiting for purge to start --purge线程清理回滚段信息
    2018-08-17T22:13:58.441475+08:00 0 [Note] InnoDB: 5.7.23 started; log sequence number 831040257
    2018-08-17T22:13:58.443981+08:00 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
    2018-08-17T22:13:58.444282+08:00 0 [Note] Plugin 'FEDERATED' is disabled.
    2018-08-17T22:13:58.454856+08:00 0 [Note] Recovering after a crash using /var/lib/mysql/mysqlbin
    2018-08-17T22:13:58.455101+08:00 0 [Note] Starting crash recovery...
    2018-08-17T22:13:58.455185+08:00 0 [Note] Crash recovery finished. -- 完成所有崩溃恢复
    2018-08-17T22:13:58.462891+08:00 0 [Note] Found ca.pem, server-cert.pem and server-key.pem in data directory. Trying to enable SSL support using them.
    2018-08-17T22:13:58.463297+08:00 0 [Warning] CA certificate ca.pem is self signed.
    2018-08-17T22:13:58.466277+08:00 0 [Note] Server hostname (bind-address): '*'; port: 3306
    2018-08-17T22:13:58.466650+08:00 0 [Note] IPv6 is available.
    2018-08-17T22:13:58.466734+08:00 0 [Note] - '::' resolves to '::';
    2018-08-17T22:13:58.466830+08:00 0 [Note] Server socket created on IP: '::'.
    2018-08-17T22:13:58.482404+08:00 0 [Note] Event Scheduler: Loaded 0 events
    2018-08-17T22:13:58.482703+08:00 0 [Note] /usr/sbin/mysqld: ready for connections. --开始对外提供服务
    Version: '5.7.23-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL)
    2018-08-17T22:13:59.473531+08:00 0 [Note] InnoDB: Buffer pool(s) load completed at 180817 22:13:59
    
    [root@centos7 ~]# mysql
    
    (root@localhost)[(none)]> select * from sakila.t20;
    +------+--------------+
    | id | descr |
    +------+--------------+
    | 1 | Instrecovery |
    +------+--------------+

[70]: /images/20220513/a22dbab8b07e48fcaf2f990c3fb81b65.png
[70 1]: /images/20220513/87a0fab22d8a4316b90c4da835508ba6.png