mysql备份与恢复

备份工具介绍

逻辑备份工具
主流： mysqldump （MDP）
基于SQL（create database ,create table , insert into）语句的备份。
使用场景： 100G以内，比较常用的就是逻辑备份。
优点： 自带工具，不需要单独安装；文本形式存储，便于查看处理；压缩比较高，节省空间。
缺点： 备份时间较长。恢复时间更长（4-6倍）。

物理备份工具
Percona - Xtrabackup(xbk)
备份数据文件。
优点：备份、恢复速度更快。
缺点：二进制方式，可读性差；压缩比较低，浪费空间。
使用场景： 100G-TB级别数据量，一般采用xbk。

mysqldump

基于SQL（create database ,create table , insert into）语句的备份。
针对InnoDB表可以实现非锁定备份。原理上是通过MVCC中的快照技术进行备份。
针对非InnoDB表，是启用了锁表备份。

备份参数

mysqldump -uroot -p123 -A >/opt/full.sql #全备

mysqldump -uroot -p123 -B world test >/opt/db.sql #单库或多库备份

mysqldump -uroot -p123 world city country >/opt/tab.sql #单表或多表备份，提前创建库，use后恢复

mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/opt/full.sql #--master-data=2在文件开头显示当时备份的pos号和gtid号，--single-transaction减少锁表，R脚本，E执行计划，--triggers触发器脚本

# 1153 - Got a packet bigger than ‘max_allowed_packet’ bytes报错提示

mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=128M >/opt/full.sql
#指定数据包大小，dump时客户端从服务端索要数据，sql语句是客服端向服务端发送数据

压缩

mysqldump -uroot -p123 -A  -R  --triggers -E  --master-data=2  --single-transaction --max-allowed-packet=128M |bzip2 -9   > /opt/full_$(date +%F).sql.bz2
#bzip2 -d解压

Percona-XtraBackup

配置

[client]
socket=/tmp/mysql.sock

恢复原理

备份：
InnoDB表：
1、xbk备份执行的瞬间,立即触发ckpt,已提交的数据脏页,从内存刷写到磁盘,并记录此时的LSN号
2、备份InnoDB表时，拷贝 ibd 、ibdata、undo、ibtmp1，将备份期间产生的redo截取拷贝，记录LSN
非InnoDB表：
1.触发 FTWRL 全局锁，关闭binlog记录
2.拷贝非InnoDB表数据。

数据完成后
记录binlog位置，停止redo拷贝，记录last lsn，记录所有备份日志到指定日志文件中，解锁

恢复：
1.prepare 备份：利用了InnoDB CSR的功能，使用redo进行前滚，undo进行回滚
2.恢复备份： cp 文件至源路径。

应用

全备

innobackupex --user=root --password=123 --no-timestamp  /opt/full/xbkfull_`date +%F`
#--no-timestamp指定目录名
--kill-long-queries-timeout=90 --kill-long-query-type=select --lock-wait-timeout=180 --lock-wait-threshold=120 --lock-wait-query-type=all --parallel=6 --slave-info --safe-slave-backup --safe-slave-backup-timeout=300 >> /bak3/log 2>&1

恢复

1. innobackupex --apply-log /opt/full/xbkfull_2020-03-27/
2. innobackupex --copy-back /opt/full/xbkfull_2020-03-27/ 自动拷贝到数据目录下
3. chown -R mysql.mysql /data

增备

原理
xrabackup自动检查LSN号码的变化，将每天LSN发生变化的数据页备份走。
备份的文件
xtrabackup_binlog_info ：binlog位置点
xtrabackup_checkpoints ：LSN号码记录
xtrabackup_info ：备份总览
xtrabackup_logfile ：部分redo
备份

innobackupex --user=root --password=123 --no-timestamp  --incremental-basedir=/opt/full  --incremental  /opt/inc1
#--incremental开启增备，--incremental-basedir=全备的文件路径

第二次增备

innobackupex --user=root --password=123 --no-timestamp  --incremental-basedir=/opt/inc1  --incremental  /opt/inc2

恢复

innobackupex --apply-log --redo-only /opt/full/
innobackupex --apply-log --redo-only  --incremental-dir=/opt/inc1 /opt/full 
innobackupex --apply-log  --incremental-dir=/opt/inc2  /opt/full #直到最后一次增备进行undo回滚，避免lsn号不一致
innobackupex --apply-log  /opt/full/
#思路：修改配置文件，可以直接将备份的数据目录作为datadir

从全备中恢复单个表数据

mysqldump

获得表结构
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q'  full.sql>createtable.sql
获得INSERT INTO 语句，用于数据的恢复
# grep -i 'INSERT INTO `city`'  full.sqll >data.sql &
获取单库的备份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql

xtrabackup

1. 单独起实例，备份数据拉起来
2. 截取日志恢复此实例
3. 表空间迁移恢复到生产

Mydumper

多线程备份，自动读取master和slave的位置点，位置点为刚开始备份时，会加全局锁

#mydumper -u mydumper -p -x '^(?!(mysql..*|sys..*|information_schema..*|performance_schema..*|U[0-9_]+.TOPIC_.*))' -S /usr/local/mysql/tmp/mysql.sock -G -E -R -l 90 -K -e --less-locking -t 4 -r 1000 --use-savepoints -o /bak3/mysqlBackup/192.168.17.205 -v 3 -L /bak3/mysqlBackup/192.168.17.205/log