MySQL实例恢复

版权声明:本文为博主原创文章,欢迎扩散,扩散请务必注明出处。 https://blog.csdn.net/robinson_0612/article/details/82588176

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

一、MySQL实例

MySQL实例就是mysqld后台进程以及多个线程再加上内存分配

二、MySQL实例恢复的步骤

三、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 |
+------+--------------+

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • Oracle 实例恢复

    Oracle实例失败多为实例非一致性关闭所致,通常称为崩溃(crash)。实例失败的结果等同于shutdown abort。

    Leshami
  • myloader恢复mysql数据库示例

        mydumper是针对mysql数据库备份的一个轻量级第三方的开源工具,备份方式为逻辑备份。它支持多线程,备份速度远高于原生态的mysqldump以及众...

    Leshami
  • MYSQL数据库恢复案例分享

    本次分享的案例是关于存储的数据恢复,存储上RAID崩溃导致存储无法启动。存储内部共有6台以上虚拟机,其中LINUX虚拟机3台为客户重要数据。 工程师初步分析得出...

    北亚数据安全与救援
  • mysql binlog恢复数据实战

    数据库恢复的先决条件是,定时备份数据库,缩小binlog恢复范围.首先我们备份测试数据库数据:

    仙士可
  • Mysql误删恢复

    在配置文件中加入 log-bin 配置,表示启用binlog,如果没有给定值,写成 log-bin=,则默认名称为主机名。(注:名称若带有小数点,则只取第一个小...

    剑行者
  • MySQL 数据恢复

    前两天因为没注意的误操作, 直接把某个数据表清掉了, 心慌慌. 怪自己学艺不精, 当时整了一下午也没把数据找回来. 当晚回来闭关研究, 终于在凌晨1点多整出来了...

    烟草的香味
  • mysql备份与恢复

    程序员同行者
  • mysql数据恢复 转

    binlog 基本认识     MySQL的二进制日志可以说是MySQL最重要的日志了,它记录了所有的DDL和DML(除了数据查询语句)语句,以事件形式记录,...

    wuweixiang
  • 恢复mysql数据库

    show binlog events in 'mysql-bin.000002' 查看指定文件

    用户1437675

扫码关注云+社区

领取腾讯云代金券