数据库备份

港控/mmm° 2022-04-12 20:10 249阅读 0赞

# 逻辑备份–mysqldump #

## 原理 ##

逻辑备份，通过select语句，将要备份的所有数据选出来， 产生一组能够被执行以再现原始数据库对象定义和表数据的SQL语句。

## 参数 ##

\-A 倒出全部数据库  
\-B test1 test2 倒出指定的数据库  
\-t emp\_range 倒出指定数据库（test）中的制定表（emp\_range）  
\-d 导出表结构  
\-uroot -p -S /tmp/mysql3306.sock -p表示手动输入密码  
–add-drop-database 添加drop databasw的作用是如果会的数据库已存在，则drop已存在的数据库后再恢复  
–add-drop-table(默认on) 在数据表创建之前添加drop table语句，默认为打开状态。添加drop table的作用是如果恢复的表格已存在，则drop已存在的表格之后，再恢复.  
–skip-add-drop-table(database) 若要跳过drop table或drop database 可以加选项  
–single-transaction 可以实现不加锁一致性备份,在备份的时候,备份的链接会显示的开启RR模式,  
–hex-blob 备份二进制的字段的时候,需要添加 blob  
–ignore-table=name 跳过指定的表不备份  
\-x lock-all-tables  
\-l, Lock all tables for read.  
–master-data  
添加change master到备份文件内部 binlog-file binlog-pos  
head -n 35 /tmp/all.sql  
1 添加change master语句  
2 \#change master带注释  
eg mysqldump -S /tmp/mysql.sock --master-data=2 --single-transaction -B test1 > /tmp/all.sql  
–set-gtid-purged 默认打开  
–set-gtid-purged =off 如果只是单纯备份的话,不是做主从目的备份,可以关闭.

*  示例  
    mysqldump -u -p -h -P --master-data=2 -A --single transaction >/tmp/backup\_20180723.sql
 *  逻辑备份的优缺点
    
    1.  优点 简单易读
    2.  缺点 恢复时间长，数据量越大，回复时间越长,只能单线程备份恢复
    3.  如果没有主从,关闭session binlog

# 物理备份–xtrabackup #

## 备份过程 ##

*  innobackupex 在启动后，会先 fork 一个进程，启动 xtrabackup进程，然后就等待 xtrabackup 备份完 ibd 数据文件；  
    xtrabackup 在备份 InnoDB 相关数据时，是有2种线程的，1种是 redo 拷贝线程，负责拷贝 redo 文件，1种是 ibd 拷贝线程，负责拷贝 ibd 文件；redo 拷贝线程只有一个，在 ibd 拷贝线程之前启动，在 ibd 线程结束后结束。xtrabackup 进程开始执行后，先启动 redo 拷贝线程，从最新的 checkpoint 点开始顺序拷贝 redo 日志；然后再启动 ibd 数据拷贝线程，在 xtrabackup 拷贝 ibd 过程中，innobackupex 进程一直处于等待状态（等待文件被创建）。
 *  xtrabackup 拷贝完成idb后，通知 innobackupex（通过创建文件 xtrabackup\_suspended\_2），同时自己进入等待（redo 线程仍然继续拷贝）;
 *  innobackupex 收到 xtrabackup 通知后，执行FLUSH TABLES WITH READ LOCK (FTWRL)，取得一致性位点，然后开始备份非 InnoDB 文件（包括 frm、MYD、MYI、CSV、opt、par等）。拷贝非 InnoDB 文件过程中，因为数据库处于全局只读状态，如果在业务的主库备份的话，要特别小心，非 InnoDB 表（主要是MyISAM）比较多的话整库只读时间就会比较长，这个影响一定要评估到。
 *  当 innobackupex 拷贝完所有非 InnoDB 表文件后，通知 xtrabackup（通过删文件 xtrabackup\_suspended\_2） ，同时自己进入等待（等待另一个文件被创建）；
 *  xtrabackup 收到 innobackupex 备份完非 InnoDB 通知后，就停止 redo 拷贝线程，然后通知 innobackupex redo log 拷贝完成（通过创建文件 xtrabackup\_log\_copied）；
 *  innobackupex 收到 redo 备份完成通知后，就开始解锁，执行 UNLOCK TABLES；
 *  最后 innobackupex 和 xtrabackup 进程各自完成收尾工作，如资源的释放、写备份元数据信息等，innobackupex 等待 xtrabackup 子进程结束后退出。

## 对数据库的影响 ##

1.  xtrabackup的文件拷贝，都是备份进程直接通过操作系统读取数据文件的，只在执行 SQL 命令时与数据库有交互，基本不影响数据库的运行，在备份非 InnoDB 时会有一段时间只读（如果没有MyISAM表的话，只读时间在几秒左右），在备份 InnoDB 数据文件时，对数据库完全没有影响，是真正的热备。
2.  InnoDB 和非 InnoDB 文件的备份都是通过拷贝文件来做的，但是实现的方式不同，前者是以page为粒度做的(xtrabackup)，后者是 cp 或者 tar 命令(innobackupex)，xtrabackup 在读取每个page时会校验 checksum 值，保证数据块是一致的，而 innobackupex 在 cp MyISAM 文件时已经做了flush（FTWRL读锁），磁盘上的文件也是完整的，所以最终备份集里的数据文件都是写入完整的。

## 增量备份 ##

extrabackup支持增量备份的，但是只能对 InnoDB 做增量，InnoDB 每个 page 有个 LSN 号，LSN 是全局递增的，page 被更改时会记录当前的 LSN 号，page中的 LSN 越大，说明当前page越新（最近被更新）。每次备份会记录当前备份到的LSN（xtrabackup\_checkpoints 文件中），增量备份就是只拷贝LSN大于上次备份的page，比上次备份小的跳过，每个 ibd 文件最终备份出来的是增量 delta 文件。MyISAM 是没有增量的机制的，每次增量备份都是全部拷贝的。增量备份过程和全量备份一样，只是在 ibd 文件拷贝上有不同。

## 恢复过程 ##

恢复的目的是把备份集中的数据恢复到一个一致性位点，所谓一致就是指原数据库某一时间点各引擎数据的状态，比如 MyISAM 中的数据对应的是 15:00 时间点的，InnoDB 中的数据对应的是 15:20 的，这种状态的数据就是不一致的。PXB 备份集对应的一致点，就是备份时FTWRL的时间点，恢复出来的数据，就对应原数据库FTWRL时的状态。  
因为备份时 FTWRL 后，数据库是处于只读的，非 InnoDB 数据是在持有全局读锁情况下拷贝的，所以非 InnoDB 数据本身就对应 FTWRL 时间点；InnoDB 的 ibd 文件拷贝是在 FTWRL 前做的，拷贝出来的不同 ibd 文件最后更新时间点是不一样的，这种状态的 ibd 文件是不能直接用的，但是 redo log 是从备份开始一直持续拷贝的，最后的 redo 日志点是在持有 FTWRL 后取得的，所以最终通过 redo 应用后的 ibd 数据时间点也是和 FTWRL 一致的。恢复过程只涉及 InnoDB 文件的恢复，非 InnoDB 数据是不动的。备份恢复完成后，就可以把数据文件拷贝到对应的目录，然后通过mysqld来启动了。

## 参数 ##

–defaults-file 指定配置文件  
–databases 指定备份的数据库，若不指定则全库备份  
–apply-log 使用redo log把备份的数据恢复到一个一致性位点。一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处于不一致状态。prepare的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件使得数据文件处于一致性状态。  
–incremental 创建一个增量备份而不是全量备份  
–incremental-basedir 后加全量备份的路径  
–redo-only 在’准备基本完整备份’和’合并所有的增量备份(除了最后一个增备)'时使用此选项。它直接传递给xtrabackup的 xtrabackup --apply-log-only 选项，使xtrabackup跳过"undo"阶段，只做"redo"操作。如果后面还有增量备份应用到这个全备,这是必要的。有关详细信息,请参阅xtrabackup文档。  
–apply-log-only 在准备备份(prepare)时，只执行重做(redo)阶段，这对于增量备份非常重要。  
–no-timestamp 若没有此参数，生成带有时间的文件夹

## 操作命令 ##

*  全量备份

innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user=pxb --password=123456  --socket=/tmp/mysql.sock    /data/xtrabackup/all-20180723.bak

*  全量恢复(prepare阶段)

innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user=pxb --password=123456  --apply-log  --socket=/tmp/mysql.sock    /data/xtrabackup/all-20180723.bak

*  增量备份 增加了–incremental --incremental-basedir选项

1.  首先全局备份

innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --socket /tmp/mysql.sock  /data/xtrabackup/all-20180720.bak

1.  第一次增量备份

innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --socket /tmp/mysql.sock --incremental --incremental-basedir=/data/xtrabackup/all-20180720.bak /data/xtrabackup/inc-20180721.bak

1.  第二次增量备份

innobackupex --defaults-file=/etc/my.cnf --no-timestamp --user root --socket /tmp/mysql.sock  --incremental --incremental-basedir=/data/xtrabackup/inc-20180721.bak /data/xtrabackup/inc-20180722.bak

*  增量备份恢复(prepare阶段)

1.  先将三次备份仅适用redo log进行还原，最后在使用undo进行回滚，因为会有部分redo尚未落盘，无法用于恢复

innobackupex --defaults-file=/etc/my.cnf --user root --password root --socket /tmp/mysql.sock   --apply-log --redo-only /data/xtrabackup/all-20170417.bak
           innobackupex --defaults-file=/etc/my.cnf --user root --password root --socket /tmp/mysql.sock --apply-log --redo-only /data/xtrabackup/all-20170417.bak --incremental-dir=/data/xtrabackup/inc-20170418.bak
           innobackupex --defaults-file=/etc/my.cnf --user root --password root --socket /tmp/mysql.sock --apply-log --redo-only /data/xtrabackup/all-20170417.bak --incremental-dir=/data/xtrabackup/inc-20170419.bak
           innobackupex --defaults-file=/etc/my.cnf --user root --password root --socket /tmp/mysql.sock --apply-log /data/xtrabackup/all-20170417.bak

*  数据恢复—将prepare后的数据文件，放入mysql的数据文件中，直接启动mysql即可