MySQL备份流程及优化

2017-06-22 10:22 Kevin.hhl 阅读(518) 评论(0) 收藏举报

1.Flush table with read lock 的认识：

Flush table with read lock(简称: FTWRL) 有哪些问题：

例如让DBA头大的long query，FTWRL需要等待所有的表没有引用后关闭所有表，long query 导致FTWRL无法执行成功。这也是经常备份被hung住，有可能导致备份失败。

2.老版本如何获得一致性的备份：

2.1 mysqldump

早期使用mysqldump 来备份，xtrabackup 成为当前主流Inoodb＋MyiSAM热备工具。

从MySQL 5.1开始：在最后备份非事务引擎表的时候为了获得一致性备份需要在MySQL上获得 Flush table with read lock，有可能在压力大的库或者有事务没有正常关闭的连接在，会导致备份无法因超时后导致备份失败。还有如果在master 上备份会导致加FTWRL到最后这段时间会堵塞所有的DML，Master这段时间变成只读，长短取决于非事务表的数量和大小。

percona server 从5.6.16-64.0开始优化了mysqldump备份，引入轻量级锁，lock tables for backup 和 lock binlog for backup。mysqldump 增加参数：--lock-for-backup

关于Backup lock和Binlog lock 详细介绍：https://www.percona.com/doc/percona-server/5.6/management/backup_locks.html#interaction-with-other-global-locks

LOCK TABLES FOR BACKUP
... copy .frm, MyISAM, CSV, etc. ...
LOCK BINLOG FOR BACKUP
UNLOCK TABLES
... get binlog coordinates ...
... wait for redo log copying to finish ...
UNLOCK BINLOG

参考：https://www.percona.com/doc/percona-server/5.6/management/backup_locks.html#interaction-with-other-global-locks

2.2 xtrabackup:

version<2.2.0 :也是加FTWRL，和老版本mysqldump类似。老版本的改进：Improved FLUSH TABLES WITH READ LOCK handling：https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/improved_ftwrl.html。

version>=2.2.0 引入轻量级锁，lock tables for backup; 和 lock binlog for backup;

xtrabackup物理备份流程变化如下：

修改之前(<2.2.0 )的流程：

1.get Redo LSN

2.copy 系统表空间+事务引擎表的数据文件+后台子进程(IBACKUP)拷贝Redo

3.FLUSH TABLES WITH READ LOCK

4.copy 所有 *.frm文件，非事务引擎表(MyISAM、ARCHIVE等)数据＋索引文件

5.Get the binary log coordinates(坐标/位点)

6.finalize the background copy of REDO log

7.unlock tables;

修改之后(>=2.2.0 )的流程：

1.get Redo LSN

2.copy 系统表空间+事务引擎表的数据文件+后台子进程(IBACKUP)拷贝Redo

3.LOCK TABLES FOR BACKUP

4.copy 所有 *.frm文件，非事务引擎表(MyISAM、ARCHIVE等)数据＋索引文件

5.LOCK BINLOG FOR BACKUP

6.finalize the background copy of REDO log

7.UNLOCK TABLES;

8.get the binary log coordinates(坐标/位点)

9.UNLOCK BINLOG

3.优化的目标

3.1 引入备份锁的优势

LOCK TABLES FOR BACKUP:

作用：获取一致性数据

a)禁止非事务引擎(非InnoDB)表写入(也即DML)。

b)禁止所有表的DDL。

优点：

a)不会被大查询阻塞。

b)不会堵塞innodb表的读取和更新，这点非常重要，对于业务表全部是并innodb的情况，则备份过程中DML完全不受损。

LOCK BINLOG FOR BACKUP:

作用：获取一致性位点。

a)禁止对binlog的位点操作(不允许DML、DDL)

优点：

a)时间短，对db的影响很小。

下面是xtrabackup-2.2.4的备份主要log：

>> log scanned up to (1844037)
xtrabackup: Creating suspend file '/data/mybackup/2017-06-20_18-36-55/xtrabackup_suspended_2' with pid '31195'
170620 18:36:59  innobackupex: Continuing after ibbackup has suspended
170620 18:36:59  innobackupex: Executing LOCK TABLES FOR BACKUP...
170620 18:36:59  innobackupex: Backup tables lock acquired
170620 18:36:59  innobackupex: Starting to backup non-InnoDB tables and files
innobackupex: in subdirectories of '/data/servers/data/my3306/data'
innobackupex: Backing up files '/data/servers/data/my3306/data/performance_schema/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}'
 (53 files)
>> log scanned up to (1844045)
innobackupex: Backing up files '/data/servers/data/my3306/data/mysql/*.{frm,isl,MYD,MYI,MAD,MAI,MRG,TRG,TRN,ARM,ARZ,CSM,CSV,opt,par}' (74 files)
innobackupex: Backing up file '/data/servers/data/my3306/data/hhl/c1.frm'
innobackupex: Backing up file '/data/servers/data/my3306/data/hhl/t2.frm'
innobackupex: Backing up file '/data/servers/data/my3306/data/hhl/p1.frm'
innobackupex: Backing up file '/data/servers/data/my3306/data/hhl/db.opt'
innobackupex: Backing up file '/data/servers/data/my3306/data/hhl/api_order.frm'
innobackupex: Backing up file '/data/servers/data/my3306/data/hhl/t1.frm'
170620 18:36:59  innobackupex: Finished backing up non-InnoDB tables and files
170620 18:36:59  innobackupex: Executing LOCK BINLOG FOR BACKUP...
170620 18:36:59  innobackupex: Executing FLUSH ENGINE LOGS...
170620 18:36:59  innobackupex: Waiting for log copying to finish
xtrabackup: The latest check point (for incremental): '1844037'
xtrabackup: Stopping log copying thread.
.>> log scanned up to (1844045)
xtrabackup: Creating suspend file '/data/mybackup/2017-06-20_18-36-55/xtrabackup_log_copied' with pid '31195'
xtrabackup: Transaction log of lsn (1844037) to (1844045) was copied.
170620 18:37:00  innobackupex: Executing UNLOCK BINLOG
170620 18:37:00  innobackupex: Executing UNLOCK TABLES
170620 18:37:00  innobackupex: All tables unlocked

刷新页面返回顶部

Kevin.hhl