189 8069 5689
这篇文章主要介绍“MySQL Innodb恢复的方法教程”，在日常操作中，相信很多人在MySQL Innodb恢复的方法教程问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”MySQL Innodb恢复的方法教程”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！
芦溪网站制作公司哪家好，找创新互联公司！从网页设计、网站建设、微信开发、APP开发、响应式网站等网站项目制作，到程序开发，运营维护。创新互联公司于2013年开始到现在10年的时间，我们拥有了丰富的建站经验和运维经验，来保证我们的工作的顺利进行。专注于网站建设就选创新互联公司。
MySQL · 引擎特性 · InnoDB 崩溃恢复过程
enum {
SRV_FORCE_IGNORE_CORRUPT = 1, /*!< let the server run even if it
detects a corrupt page */
SRV_FORCE_NO_BACKGROUND = 2, /*!< prevent the main thread from
running: if a crash would occur
in purge, this prevents it */
SRV_FORCE_NO_TRX_UNDO = 3, /*!< do not run trx rollback after
recovery */
SRV_FORCE_NO_IBUF_MERGE = 4, /*!< prevent also ibuf operations:
if they would cause a crash, better
not do them */
SRV_FORCE_NO_UNDO_LOG_SCAN = 5, /*!< do not look at undo logs when
starting the database: InnoDB will
treat even incomplete transactions
as committed */
SRV_FORCE_NO_LOG_REDO = 6 /*!< do not do the log roll-forward
in connection with recovery */};innodb中的 3个lsn
innodb的lsn和oracle的scn一样，是一个重要的概念。比如
在flush list中正是是使用low lsn作为链表的条件
参考buf_page_t中的lsn_t oldest_modification;
在checkpoint中记录的也是lsn
参考宏
#define LOG_CHECKPOINT_NO 0#define LOG_CHECKPOINT_LSN 8#define LOG_CHECKPOINT_OFFSET 16#define LOG_CHECKPOINT_LOG_BUF_SIZE 24
在物理文件中每个块最后的刷新lsn
参考宏FIL_PAGE_LSN
在写日志落盘的时候也是以lsn为标准的
参考函数log_write_up_to
实际上lsn就是表示的日志量的字节数，是一个累加的值，在5.7中表现为:
/* Type used for all log sequence number storage and arithmetics */typedef ib_uint64_t lsn_t;
及一个8字节非负的整数。最大值及2的64次方。有了这种物理上概念，lsn很容易换算为当前日志的偏移量。
下面描述一下和检查点相关的几个lsn
ibdata第一个块FIL中的lsn(flush lsn):ibdata的26后面8字节是在innodb 干净关闭的时候进行更新的，如果不正常关闭不会进行写入(FIL_PAGE_FILE_FLUSH_LSN)
redolog中MLOG_CHECKPOINT的lsn: redolog MLOG_CHECKPOINT lsn的写入是在每次checkpoint的时候同步写入的.干净关闭会更新。
redolog header中的lsn:是在每次checkpoint的时候异步写入的在MLOG_CHECKPOINT写入之后.干净关闭会更新。
我们表示为lsn1/lsn2/lsn3
正常关闭3个lsn是相等的，如果非正常关闭innodb,lsn1不会更新，因此lsn3必然不和lsn1相等，则判定需要进行carsh recovery。
if (checkpoint_lsn != flush_lsn) {
...... if (!recv_needed_recovery) {
ib::info() << "The log sequence number " << flush_lsn
<< " in the system tablespace does not match"
" the log sequence number " << checkpoint_lsn
<< " in the ib_logfiles!"; //出现这个警告说明需要恢复了，因为没有干净的关闭数据库，那么flush_lsn一定比checkpoint_lsn小
if (srv_read_only_mode) {
ib::error() << "Can't initiate database"
" recovery, running in read-only-mode.";
log_mutex_exit(); return(DB_READ_ONLY);
}
recv_init_crash_recovery(); //初始化
}
}MLOG_CHECKPOINT
log_checkpoint 函数由master线程调用，以及关闭数据库的时候调用，不断的向redo log中写入MLOG_CHECKPOINT和MLOG_FILE_NAME
log_group_checkpoint 写入checkpoint信息到log header
#0 mtr_t::commit_checkpoint (this=0x7fff761fb830, checkpoint_lsn=697558445) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/mtr/mtr0mtr.cc:592#1 0x0000000001ceb4b9 in fil_names_clear (lsn=697558445, do_write=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/fil/fil0fil.cc:7067#2 0x0000000001a521cc in log_checkpoint (sync=true, write_always=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1927#3 0x0000000001b856f2 in srv_master_do_idle_tasks () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2596#4 0x0000000001b85b6b in srv_master_thread (arg=0x0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2744#5 0x0000003f74807aa1 in start_thread () from /lib64/libpthread.so.0#6 0x0000003f740e8bcd in clone () from /lib64/libc.so.6
正常innodb运行的情况下 checkpoint是由master线程触发。我们知道脏数据通过page clean线程和lru manager线程是在不断写盘的，那么在进行异常重启的的时候我们必须要知道一个恢复的起点，但是这个起点是不能记录在内存中必要固化到磁盘，恢复的时候读取这个点以后的redo进行恢复，而checkpoint就是完成这个事情下面是checkpoint的执行流程。
正常情况下master会每秒进行检查点其作用有:(参考log_checkpoint函数)
1、检查是否有自上次检查点以来的脏数据写盘了
2、如果有则在redo里面会为每个修改过的文件写入MLOG_FILE_NAME,完成后写入一个总的MLOG_CHECKPOINT(参考fil_names_clear函数)
MLOG_FILE_NAME主要记录至上次检查点以来更改过的数据文件
MLOG_CHECKPOINT主要记录检查点的lsn
3、如果有则在redo header中写入相应的检查点信息包含(异步写)(参考log_group_checkpoint函数)
Log sequence number 697794162
Log flushed up to 697794162
Pages flushed up to 697794162
Last checkpoint at 697794153
697794162-697794153 = 9 刚好是MLOG_CHECKPOINT的长度
oldest_lsn <= log_sys->last_checkpoint_lsn + SIZE_OF_MLOG_CHECKPOINT
SIZE_OF_MLOG_CHECKPOINT=9
redo的写入有6个途径:
1、master 线程每秒调用 栈帧(可能是idle可能是active 和检测是否需要插入缓存合并有关)
2、master 线程每秒checkpoint调用 (可能是idle可能是active 和检测是否需要插入缓存合并有关)
3、page clean 线程调用 栈帧 /* Force the log to the disk before writing the modified block */
4、主线程commit 调用 栈帧
5、innodb shutdown
6、redo buffer不足
1、master线程调用 栈帧(可能是idle可能是active 和检测是否需要插入缓存合并有关)
#0 log_group_write_buf (group=0x33f29f8, buf=0x7fffa5b38000 "\200\024", len=512, pad_len=0, start_lsn=697764864, new_data_offset=166) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145#1 0x0000000001a50f95 in log_write_up_to (lsn=697765068, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493#2 0x0000000001a51163 in log_buffer_sync_in_background (flush=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1553#3 0x0000000001b84bd1 in srv_sync_log_buffer_in_background () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2312#4 0x0000000001b85666 in srv_master_do_idle_tasks () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2586#5 0x0000000001b85b6b in srv_master_thread (arg=0x0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2744
2、master 线程checkpoint调用 (可能是idle可能是active 和检测是否需要插入缓存合并有关)
#0 log_group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\200\024\002", len=1024, pad_len=0, start_lsn=697789952, new_data_offset=139) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145#1 0x0000000001a50f95 in log_write_up_to (lsn=697790725, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493#2 0x0000000001a52247 in log_checkpoint (sync=true, write_always=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1934#3 0x0000000001b856f2 in srv_master_do_idle_tasks () at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2596#4 0x0000000001b85b6b in srv_master_thread (arg=0x0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/srv/srv0srv.cc:2744
3、page clean 线程调用 栈帧 /* Force the log to the disk before writing the modified block */
#0 log_group_write_buf (group=0x33f29f8, buf=0x7fffa5a38000 "\200\024\002", len=13312, pad_len=1024, start_lsn=697778176, new_data_offset=468) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1145#1 0x0000000001a50f95 in log_write_up_to (lsn=697790015, flush_to_disk=true) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/log/log0log.cc:1493#2 0x0000000001c704c7 in buf_flush_write_block_low (bpage=0x7fffc0cae940, flush_type=BUF_FLUSH_LIST, sync=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1035#3 0x0000000001c70cea in buf_flush_page (buf_pool=0x33247d8, bpage=0x7fffc0cae940, flush_type=BUF_FLUSH_LIST, sync=false) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1237#4 0x0000000001c717f4 in buf_flush_try_neighbors (page_id=..., flush_type=BUF_FLUSH_LIST, n_flushed=0, n_to_flush=25) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1466#5 0x0000000001c71b57 in buf_flush_page_and_try_neighbors (bpage=0x7fffc0cae940, flush_type=BUF_FLUSH_LIST, n_to_flush=25, count=0x7fffa02867c0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1558#6 0x0000000001c72862 in buf_do_flush_list_batch (buf_pool=0x33247d8, min_n=25, lsn_limit=18446744073709551615) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1846#7 0x0000000001c72cb6 in buf_flush_batch (buf_pool=0x33247d8, flush_type=BUF_FLUSH_LIST, min_n=25, lsn_limit=18446744073709551615) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:1926#8 0x0000000001c73104 in buf_flush_do_batch (buf_pool=0x33247d8, type=BUF_FLUSH_LIST, min_n=25, lsn_limit=18446744073709551615, n_processed=0x7fffa0286938) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:2071#9 0x0000000001c734ee in buf_flush_lists (min_n=25, lsn_limit=18446744073709551615, n_processed=0x7fffa02869c8) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:2202#10 0x0000000001c76a97 in buf_flush_page_cleaner_coordinator (arg=0x0) at /root/mysql5.7.14/percona-server-5.7.14-7/storage/innobase/buf/buf0flu.cc:3362
4、主线程commit 调用 栈帧