MySQL/mariadb数据库备份工具及备份恢复过程
备份类型
- 全量备份
- 备份所有数据
- 增量备份
- 仅备份自上一次完全备份或增量备份以来变量的那部数据
- 差异备份
- 仅备份自上一次完全备份以来变量的那部数据
- 物理备份
- 复制数据文件进行的备份，类似与拿硬盘拷贝数据
- 逻辑备份
- 从数据库导出数据另存在一个或多个文件中；基于协议
根据数据服务是否在线划分
- 热备
- 读写操作均可进行的状态下所做的备份；不用停止服务
- 温备
- 可读但不可写状态下进行的备份，像微博站点一类的网站相当于无法使用
- 冷备
- 读写操作均不可进行的状态下所做的备份，需要停止服务
备份策略及手段
根据数据更新频率的不同来决定备份频率
全量+差异 + binlogs
全量+增量 + binlogs
备份手段：物理、逻辑
备份工具
mysqldump
它是客户端工具，可以远程连接到服务器上备份数据库
以下是简单备份示例，以下方法存在的问题：数据库服务器可能正在被访问，在备份前要锁住数据库，要不然时间点会不一致，没法用于还原数据库
# 全量备份
[root@centos7 ~]# mysqldump -uroot -p123456 -h192.168.6.17 --all-databases > mydb.sqlInnoDB存储引擎：支持温备和热备
–single-transaction：创建一个事务，基于此执行备份；可能有事务没有提交，完了要执行一次崩溃后的恢复操作
- 其它选项：
- -R, –routines：备份指定库的存储过程和存储函数；
–triggers：备份指定库的触发器；
-E, –events：
–master-data[=#]
1：记录为CHANGE MASTER TO语句，备份那一刻二进制文件在哪个文件的哪个位置，此语句不被注释；
2：记录为CHANGE MASTER TO语句，备份那一刻二进制文件在哪个文件的哪个位置，此语句被注释，记录备份时间点，方便备份；
–flush-logs：锁定表完成后，即进行日志刷新操作；
改进示例：
# 连接主机备份，全量备份
[root@centos7 ~]# mysqldump -uroot -p123456 -h192.168.6.17 --single-transaction -R --triggers -E --master-data=2 --flush-logs --all-databases > mydb-$(date +%F-%H-%M-%S).sql
# 二进制日志导出，由于执行日志滚动，这里二进制文件为master-log.000003，可以通过查看数据库备份文件确定,用-j指定位置，因为这里加了日志滚动，所以位置从245开始，这个位置写不写都一样，245前面没有东西
[root@centos7 ~]# mysqlbinlog -j 245 /mydata/logs/master-log.000003 > mybin-$(date +%F-%H-%M-%S).sql启动数据库服务
# 恢复
[root@centos7 ~]# mysql -uroot -p123456 < mydb-2017-11-11-14-56-05.sql
[root@centos7 ~]# mysql -uroot -p123456 < mybin-2017-11-11-14-57-51.sql
mysqldump 不支持增量备份、差异备份，所有只能用 全量+二进制日志的方式备份，这种效率比较低
xtrabackup
安装：
（1） 官网下载安装包
（2） yum install 包名 （可能会用到epel源，所以要准备好）基础：
（基于协议连接检验是否有访问权限，通过复制文件做备份，属于物理备份）
物理备份，速率快、可靠；备份完成后自动校验备份结果集是否可用；还原速度快
Usage: [innobackupex [–defaults-file=#] –backup | innobackupex [–defaults-file=#] –prepare] [OPTIONS]
[aaa@qq.com ~]# innobackupex --user=root --password=123456 --host=localhost /mydata/xtraback注意： 现在备份的文件属主、属组是root用户，等到还原的时候数据库相关文件一定要改成mysql用户，否则无法还原。
- 备份策略：
全量+差异 + binlogs
全量+增量 + binlogs
- 简单全量备份的实现：
备份：
[aaa@qq.com ~]# innobackupex --user=root --password=123456 --host=localhost /mydata/xtraback恢复：恢复到另外一台主机上，mysql主机是关闭的而且数据库目录是原始的数据，xtrabackup必须得装
scp -r /mydata/xtrback/2017-11-11_16-25-07 192.168.6.6:/root# 到另一台主机
[root@centos7 ~]# cd 2017-11-11_16-25-07
# 为恢复做准备工作
[root@centos7 ~]# innobackupex --apply-log /.
# 把当前目录下的内容复制到该去的位置
[root@centos7 ~]# innobackupex --copy-back /.
[root@centos7 ~]# chown -R mysql.mysql /var/lib/mysql/*
# 一定要看见completed OK 说明成功完成，mysql配置文件中含有数据目录，软件会自动读取配置文件，把数据恢复到mysql指定数据目录里。- 简单增量+全量的实现（在本机合并增量和全量，到另一台主机恢复）
备份：
# 全量备份
[aaa@qq.com ~]# innobackupex --user=root --password=123456 --host=localhost /mydata/xtraback
# 增量备份1
[aaa@qq.com ~]# innobackupex --user=root --password=123456 --host=localhost --incremental /mydata/xtraback --incremental-basedir=/mydata/xtraback/2017-11-11_17-25-07
# 增量备份2,相对于增量备份1
[aaa@qq.com ~]# innobackupex --user=root --password=123456 --host=localhost --incremental /mydata/xtraback --incremental-basedir=/mydata/xtraback/2017-11-11_17-35-17
[aaa@qq.com ~]# mysqlbinlog /mydata/logs/master-log.00003 > binlog.sql选项说明：
–incremental：增量备份保存的目录
–incremental-basedir：相对于哪个文件做增量，这个文件指向关系到是增量备份还是差异备份，如果一直相对于全量备份来说就是差异备份
恢复：
# 在本机
[root@node1 ~]# cd /mydata/xtraback/2017-11-11_17-25-07
[root@node1 ~]# innobackupex --apply-log --redo-only ./
[root@node1 ~]# innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/xtraback/2017-11-11_17-35-17
[root@node1 ~]# innobackupex --apply-log --redo-only ./ --incremental-dir=/mydata/xtraback/2017-11-11_17-50-33
[root@node1 ~]# innobackupex --apply-log ./
看到complete OK!说明成功#复制到远端去恢复数据库
[root@node1 ~]# scp -r /mydata/xtraback/2017-11-11_17-25-07 aaa@qq.com:/root
[root@node1 ~]# scp binlog.sql aaa@qq.com:/root
[root@node2 ~]# cd 2017-11-11_17-25-07
[root@node2 ~]# innobackupex --copy-back ./
[root@node2 ~]# mysql -uroot -p123456 < binlog.sql
[root@node2 ~]# chown -R mysql.mysql /var/lib/mysql/*特别注意：这时已经完成恢复，但这时要进行一次全量备份，为下一次恢复做准备
导入二进制日志的时候可以在命令行用source导入，导入的时候二进制日志没有必要记录，先关掉
[aaa@qq.com ~]# cp binlog.sql /tmp
[aaa@qq.com ~]# mysql -uroot -p123456
MariaDB [(none)]> SET @@session.sql_log_bin=OFF;
MariaDB [(none)]> \. /tmp/binlog.sql
MariaDB [(none)]> SET @@session.sql_log_bin=ON;总结：
mysqldump：逻辑备份先启动数据库服务再恢复
xtrabackup：物理备份先恢复数据再启动服务