mysql数据备份和还原

一、备份类型

完全备份：全部备份。
部分备份：之备份部分库或表。

完全备份
增量备份：备份最近一次完全备份或增量备份以来变化的数据，备份较快，还原较复杂。
差异备份：仅备份最近一次完全备份依赖变化的数据，备份较慢，还原简单。

冷备份：停机备份
温：可读不可写备份
热备份：正常提供服务的同时进行备份

备份内容
1.数据
2.二进制日志、InnoDB事务日志
3.用户账号，权限设置
4.程序代码（存储过程、函数、触发器、事件调度器）
5.服务器配置文件

注意点
能容忍最多丢失多少数据
备份产生的负载
备份过程多长
温备锁持续多久
恢复数据需要在多长时间内完成
需要备份和恢复哪些数据

还原测试
还原测试，测试数据可用性。
还原演练，写成规范的技术文档。

二、备份工具

cp，tar等复制归档工具：物理备份，适用于所有存储引擎；只支持冷备份；完全和部署备份。

LVM快照：先加读锁，做快照后解锁。借助文件系统进行备份。

mysqldump：逻辑备份工具，使用于所有引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份。

xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全部分、增量备份。

MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现。

mysqlbackup：热备份，MySQL Enterprise Edition 组件

mysqlhotcopy：PERL语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。

三、备份案例

4.1 冷备份

systemcl stop mysqld
#停止服务

#备份/var/lib/mysql数据目录
#备份binlog文件
#备份配置文件

还原

#将文件恢复到指定目录
#启动mysql

4.2 热备份-mysqldump工具

备份表

#备份表
mysqldump -uroot -p database1 table1 > /opt/backup/database1.table1.sql

#还原表
source /opt/backup/database1.table1.sql

备份库内所有表

#备份库内所有表
mysqldump -uroot -p database1 > /opt/backup/database1.sql

#创建库
CREATE DATABASE database2
#先创建好数据库之后再还原,数据库名称可以与之前不同。
#创建的数据库可能与之前的数据库不同，例如字符集等。

#选择库
USE database2

#在选择的库中执行表恢复
source /opt/backup/database1.sql
\. /opt/backup/database1.sql
#两种写法等价

备份库

#备份库
mysqldump -uroot -p --databases database1 database2 > /opt/backup/database1+2.sql
mysqldump -uroot -p -B database2 > /opt/backup/database2.sql
#两种写法等价

#还原库
source /opt/backup/database2.sql
#如果没有库，会自动创建库。如果有库，则自动选择库并进行还原。

备份全部库

#备份全部库
mysqldump -uroot -p --all-databases > /opt/backup/database-all.sql
mysqldump -uroot -p -A > /opt/backup/database-all.sql

包括mysql库，但是不包括其它mysql自建库。

重要选项

-E,--events 备份相关的所有event shculer
-R,--routines 备份所有存储过程和自定义函数
在使用-A时 同时备份events 和 routines 。

--source-data,--master-data=1|2
1:默认，不注释CHANGE MASTER TO，用于主从复制备份。
2:注释CHANGE MASTER TO，用于单机备份。
记录当前备份时的二进制位置。此选项需要启用二进制日志功能。

mysqldump -uroot -p -A -F > /opt/backup/database-all.sql
-F,--flush-log:刷新二进制日志，生成一个新的二进制日志。

-d,--no-data:只备份表结构，不备份数据。
-t,--no-create-info：只备份数据，不备份表结构。
--flush-privileges
-f,--force:忽略sql错误，继续执行。
--hex-blob：使用十六进制符号转储二进制，当有包括BINARY,VARBINARY,BLOB,BIT的数据类型的列时使用，避免乱码。
-q,--quick:不查询缓存，直接输出，加快备份速度。

InnoDB参数

--single-transaction
此选项会在备份开始前，先执行START TRANSACTION指令开启事务。
通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表(InnoDB)；转储不保证与其它存储引擎保持一致。在进行单事务转储时，要确保有效的转储文件(正确的表内容和二进制日志位置)，没有其它链接应该使用以下语句：ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables选项是相互排斥，备份大型表时，建议将--single-transaction和--quick结合一起使用。

MyISAM参数

-x,--lock-all-tables
#加全局读锁

生产环境建议备份策略
InnoDB

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8mb4 --hex-blob > ${backup}/fullbak_${BACKUP_TIME}.sql

MyISAM

mysqldump -uroot -p -A -F -E -R -x --master-data=1 --flush-privileges --triggers --default-character-set=utf8mb4 --hex-blob > ${backup}/fullbak_${BACKUP_TIME}.sql

单数据库备份脚本

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR="/backup"
DB="database2"
PASS="123456"

[ -d $DIR ] || mkdir $DIR
mysqldump -h 127.0.0.1 -P 3306 -uroot -p "${PASS}" -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8mb4 --hex-blob > -B ${DB} | gzip > ${DIR}/{#DB}_${TIME}.sql.gz

分库备份脚本

#!/bin/bash
mysql -uroot -e 'SHOW DATABASES'|sed -rn '/^(Database|information_schema|performance_schema|sys)$/!s#(.*)#mysqldump -B \1' |gzip > ${DIR}/{#DB}_${TIME}.sql.gz | bash

案例：恢复误删除的表

凌晨2：30 进行全量备份，上午10：00误删除表，上午11：00发现问题，需要恢复数据。
只恢复删除表的操作，后续的正常操作数据保留。

在最近一次全量备份中找到二进制日志位置。
获取位置后，全量备份时间点之后的二进制日志导出为sql
在sql中删除误操作的命令
在全量备份的基础之上，执行sql脚本，进行剩余数据的恢复。

#从完全备份中找到二进制日志位置
grep '\-\- CHANGE MASTER TO' /backup/database2.sql

#将二进制日志导出为sql
mysqlbinlog --start-position=389 /var/lib/mysql/mysql-bin.000003 > /backup/inc.sql
mysqlbinlog /var/lib/mysql/mysql-bin.000004 >> /backup/inc.sql
#如果存在多个binlog，则追加到后面。

#打开sql文件并删除误操作的语句
vim inc.sql

利用全量备份和修改后的二进制日志进行还原

mysql -u root -p
set sql_log_bin=0;
source /backup/database2.sql
source /backup/inc.sql
set sql_log_bin=1;

4.3 xtrabackup工具

官方页面：
https://www.percona.com/software/mysql-database/percona-xtrabackup

下载页面：
https://www.percona.com/downloads/

对应版本
XtraBackup8.x => mysql 8.0+
XtraBackup2.x => mysql 5.7-

4.3.1 案例

完全备份-2.x与8.x
备份

xtrabackup -uroot -p123456 --backup --traget-dir=/backup/base
#备份数据库

还原

xtrabackup --prepare --target-dir=/backup/base
#预备，确保数据一致，提交完成的事务，回滚未完成的事务。

xtrabackup --copy-back --target-dir=/backup/base
#将备份文件拷贝回数据目录，数据目录必须为空。

chown -R mysql:mysql /var/lib/mysql
#修改目录属主

systemctl start mysql
#启动mysql

增量备份

每周一次完整备份，每天一次增量备份。

备份

#第一次完全备份
xtrabackup -uroot -p123456 --backup --traget-dir=/backup/base

#第一次增量备份
xtrabackup -uroot -p123456 --backup --traget-dir=/backup/inc1 --incremental-basedir=/backup/base

#第二次增量备份
xtrabackup -uroot -p123456 --backup --traget-dir=/data/inc2 --incremental-basedir=/backup/rc1

#第三次增量备份
xtrabackup -uroot -p123456 --backup --traget-dir=/data/inc3 --incremental-basedir=/backup/rc2

还原

xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#还原第一次全量更新，--apply-log-only不回滚事务。

xtrabackup --prepare --apply-log-only --target-dir=/backup/inc1 --incremental-basedir=/backup/base
#还原第一次增量备份，--apply-log-only不回滚事务。

xtrabackup --prepare --apply-log-only --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
#还原第二次增量备份，--apply-log-only不回滚事务。

xtrabackup --prepare --target-dir=/backup/inc3 --incremental-basedir=/backup/inc2
#还原后一次增量备份。回滚事务。

#全部增量都prepare完成后，全量备份base就包含了全部的数据，base目录的大小也会增大。将base进行还原。
xtrabackup --copy-back --target-dir=/backup/base
#将备份文件拷贝回数据目录，数据目录必须为空。

chown -R mysql:mysql /var/lib/mysql
#修改目录属主

systemctl start mysql
#启动mysql