MySQL备份与恢复精要-CSDN博客

本文深入探讨MySQL备份与恢复策略，包括冷备、热备、逻辑备份与物理备份的不同方式，以及xtrabackup与binlog在数据量大时的应用。详细解析mysqldump、mysqlpump及mydumper的备份原理，阐述DML、DDL操作对备份的影响，提供全备与增量备份的对比，并介绍binlog2sql与全备+mysqlbinlog在任意时间点恢复中的作用。

一、备份与恢复

MySQL备份与恢复
    备份
        备份方式
            是否停业务
                冷备：停掉业务或DB进行备份(copy、rsync)
                热备：不停业务的情况下进行备份
                    逻辑备份：导出SQL脚本进行备份
                        mysqldump：只支持单线程工作
                        mysqlpump：并行的最小粒度是单个数据库对象，对于每张表的导出只能是单个线程的
                        mydumper：支持对单表多个线程备份，参数-r
                        select into outfile
                    物理备份：通过拷贝文件进行备份
                        xtrabackup+binlog
            是否拷贝所有数据
                全量备份：某一时刻整个数据库快照
                增量备份：首次增量基于全量，后续增量可选择基于全量/增量
            保存位置
                本地备份
                远程备份
        备份原理
            mysqldump、mysqlpump、mydumper、xtrabackup备份原理
            InnoDB Crash Recovery
                MySQLD Crash Recovery和InnoDB Crash Recovery的区别
                redo大小调整：5.6以上直接修改my.cnf；5.6以下在apply-log前修改backup-my.cnf中的innodb_log_file%
            DML、DDL操作对备份的影响
                DML操作可能导致mysqldump、mysqlpump备份的non-InnoDB表不一致
                DDL操作可能导致InnoDB表开启一致性快照事务之后，如果表结构定义发生改变，事务将无法对该表执行查询
        备份方案选择
            数据量小：逻辑备份
            数据量大：xtrabackup+binlog，日常备份使用方案
    恢复
        备份时刻恢复
            物理备份->copy-back；逻辑备份->mysql、myloader、source
        任意时间点恢复
            binlog2sql实现对误操作的闪回
            全备+mysqlbinlog
            高级技术
                原始节点做master，恢复节点做slave
                copy binlog 2 relay-log利用sql_thread应用日志
                binlog伪装成master，利用io_thread读取日志恢复

View Code

对于数据量超大的情况，建议结合Delayed Replication。其实最好是做分库分表，控制单实例的大小●-●

二、坑坑洼洼

2.1、source dump.sql的数据不能复制到从库
GTID环境，使用mysqldump --single-transaction --master-data=2 -B dbname >xxx.sql，然后source xxx.sql到另一组主从复制环境的主库，发现主库有数据，从库却没有数据
原因：备份选项没有指定--set-gtid-purged=off，该选项影响是否在输出文件添加 set @@global.gtid_purged、set @@session.sql_log_bin=0语句
如果备份用于还原数据/搭建从库，--set-gtid-purged=on(输出带set gtid_purged，sql_log_bin=0)，还原过程不产生binlog
如果备份用于新主，--set-gtid-purged=off(输出不带set gtid_purged，sql_log_bin=0)，还原过程产生binlog
GTID环境，没有指定--set-gtid-purged参数时(默认为AUTO)，备份输出文件会加上set语句，load备份不会记录binlog，因此从库就获取不到数据啦●-●
2.2、mysqldump对MyISAM表没做一致性备份
mysqldump --single-transaction --master-data=2 -B dbname >xxx.sql
UNLOCK TABLE~SELECT * FROM 之间如果对MyISAM表进行DML操作，会导致备份出来的数据与show master status不一致
START TRANSACTION WITH CONSISTENT SNAPSHOT子句开启一个一致性快照事务之后，如果表结构发生改变，事务将无法对该表执行查询
2.3、XtraBackup只备份InnoDB表也要加FTWRL
XtraBackup借助InnoDB Crash Recovery机制来备份InnoDB表，对于MyISAM的备份，还得施加FTWRL以保证一致性备份。同时，XtraBackup是在施加FTWRL期间备份InnoDB表对应的.frm文件。可以通过innobackupex --include=databasename.tablename/xtrabackup --tables=databasename.tablename >back.log 2>&1查看日志输出
扩展阅读：MySQL备份可能遇到的坑

转载于:https://www.cnblogs.com/Uest/p/9098750.html