mysql 的备份和恢复
一，为什么要备份
运维工作的核心简单概括就两件事
1）第一个是保护公司的数据.
2）第二个是让网站能7*24小时提供服务(用户体验)。
3）备份就是为了恢复。
4）尽量减少数据的丢失（公司的损失）
二，备份的类型
1.冷备份:先停止数据库的访问，然后备份
这些备份在用户不能访问数据时进行，因此无法读取或修改数据。这些脱机备份会阻止执行任何使用数据的活动。这些类型的备份不会干扰正常运行的系统的性能。但是，对于某些应用程序，会无法接受必须在一段较长的时间里锁定或完全阻止用户访问数据。
2.温备份:不停止数据库的访问，不阻止数据的读取，阻止数据修改的同时然后备份
这些备份在读取数据时进行，但在多数情况下，在进行备份时不能修改数据本身。这种中途备份类型的优点是不必完全锁定最终用户。但是，其不足之处在于无法在进行备份时修改数据集，这可能使这种类型的备份不适用于某些应用程序。在备份过程中无法修改数据可能产生性能问题。
3.热备份:不停止数据库的访问，不锁表，不阻止数据的读取和写入的同时进行备份
这些动态备份在读取或修改数据的过程中进行，很少中断或者不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。
三，备份的方式
1）逻辑备份:
基于SQL语句的备份
1）binlog
2）into outfile
[root@db01 ~]# vim /etc/my.cnf
[mysqld]
secure-file-priv=/tmp
mysql> select * from world.city into outfile '/tmp/world_city.data';
3）mysqldump
4）replication（数据库主从，属于逻辑备份，并没有是真正的做到备份)
2) 物理备份:
基于数据文件的备份
1.直接备份data目录
1）将数据目录data打包
2）将data打包文件推送到另一台数据库服务器（两台机器必须使用相同安装方式）
3）到新机器解压data目录到数据库的数据目录进行替换
4）登录数据库查看
1）Xtrabackup（percona公司）
四.备份策略
1）全量备份 full
2）增量备份 increamental
1.全备：全部数据都进行备份
1）比较浪费磁盘空间
2）恢复数据方便
2.增备：对新增加的数据进行备份
1）相对于全备比较节省磁盘空间
2）恢复数据需要合并多次数据
3.差异备份：针对上一次全备进行备份
1）也会出现数据的重复，比较浪费磁盘空间
2）恢复数据，只需要与全备合并进行恢复即可
五.备份工具
1）mysqldump（逻辑）
mysql原生自带很好用的逻辑备份工具
2）mysqlbinlog（逻辑）
实现binlog备份的原生态命令
3）xtrabackup（物理）
precona公司开发的性能很高的物理备份工具
1.备份工具的使用
1）mysqldump
mysqldump的常用参数：
-u # mysql用户名
-p # mysql用户密码
-h # 数据库IP地址
-P # 数据库端口
-S # mysql连接的socket文件位置
-A,--all-databases # 全库备份
" " # 不加参数：单库、单表多表备份
-B # 指定库备份
-F # flush logs在备份时自动刷新binlog（不常用）
--master-data=2 # 备份时加入change master语句0没有1不注释2注释
-d # 仅表结构
-t # 仅数据
-R, --routines # 备份存储过程和函数数据
--triggers # 备份触发器数据
gzip # 压缩备份
-x # 锁表备份（myisam温备份）
--single-transaction # 快照备份
# 一般常用备份使用方法
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /backup/full.sql.gz
# 示例
1）不使用任何参数的时候
1.导出指定库
[root@db01 ~]# mysqldump -uroot -p world > /tmp/world.sql
2.导出指定库下面的指定表
[root@db01 ~]# mysqldump -uroot -p world city > /tmp/city.sql
3.导出指定库下面的多个表（第一个参数必须是库，后面的其他参数都只能是表名）
[root@db01 ~]# mysqldump -uroot -p world city country > /tmp/city2.sql
2） 加参数
1. -A 等于 --all-databases，导出所有库
[root@db01 ~]# mysqldump -uroot -p -A > /tmp/full.sql
2. -B 指定库备份，只能指定库的名字
[root@db01 ~]# mysqldump -uroot -p -B world row events > /tmp/1.sql
3. -F 备份数据的同时刷新binlog
[root@db01 ~]# mysqldump -uroot -p -F world city > /tmp/city2.sql
4. --master-data=2 备份时进行打点，记录备份时的binlog位置点（必须开启binlog才能使用）
[root@db01 ~]# mysqldump -uroot -p123 -B world --master-data=2 > /tmp/world.sql
--master-data=0 不打点不记录位置
--master-data=2 打点并注释（单纯的进行备份时）
--master-data=1 打点不注释（主从数据库扩展从库时）
5. --single-transaction 快照备份
6. -d 仅表结构
7. -t 仅数据
8. -R --routines 备份存储过程和函数数据
9. --triggers 备份触发器数据
#备份的完整命令
[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql
3）扩展
1. gzip 压缩备份
[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.tar.gz
#压缩恢复数据时
[root@db01 ~]# zcat /tmp/full.tar.gz | mysql -uroot -p
2. 备份同时加上时间戳
[root@db01 ~]# mysqldump -uroot -p -A -R --triggers --master-data=2 --single-transaction > /tmp/full$(date +%F).sql
2）mysqldump的恢复
#先不记录二进制日志
mysql> set sql_log_bin=0;
#库内恢复操作
mysql> source /backup/full.sql
#库外恢复操作
[root@db01 ~]# mysql -uroot -p123 < /backup/full.sql
3) 注意事项
1）mysqldump在备份和恢复时都需要MySQL实例启动为前提
2）一般数据量级100G以内，大约15-30分钟可以恢复（PB、EB就需要考虑别的方式）
3）mysqldump是以覆盖的形式恢复数据的
六、Xtrabackup 物理备份
1.上传或者下载Xtrabackup工具
1.上传
[root@db01 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
#或者
2.下载
#下载epel源
wget -O /etc/yum.repos.d/epel.repo https://mirrors.aliyun.com/repo/epel-6.repo
#安装依赖
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
#下载Xtrabackup
wget httpss://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
2.安装
[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
[root@db01 ~]# xtrabackup #旧版本的命令
[root@db01 ~]# innobackupex #新版本的命令
3.xtrabackup特性
1.对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2.对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3.备份时读取配置文件/etc/my.cnf
4.xtrabackup全量备份
1.创建备份的目录
[root@db01 ~]# mkdir /backup
2.全备
[root@db01 ~]# innobackupex --user=root --password=123 /backup/full/
3.查看全备的文件
[root@db01 ~]# ll /backup/full/
总用量 0
drwxr-x--- 9 root root 271 11月 4 17:50 2020-11-04_17-50-18
drwxr-x--- 9 root root 271 11月 4 17:51 2020-11-04_17-51-34
4.去除时间戳进行备份
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full/
5.再次查看备份文件
[root@db01 ~]# ll /backup/full/
[root@db01 /backup/full]# vim xtrabackup_checkpoints #备份的信息
backup_type = full-backuped
from_lsn = 0
to_lsn = 2330991066
xtrabackup_binlog_info #binlog的信息
xtrabackup_info #备份工具的信息
xtrabackup_logfile #binlog
5.使用xtrabackup进行数据恢复
1.删库
mysql> drop database test;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+
2 rows in set (0.00 sec)
2.停止数据库
[root@db01 ~]# systemctl stop mysqld
3.将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程
[root@db01 ~]# innobackupex --user=root --password=123 --apply-log /backup/full
4.恢复数据
[root@db01 ~]# cd /usr/local/mysql
[root@db01 /usr/local/mysql]# mv data databack
#方式一：
[root@db01 /usr/local/mysql]# cp -r /backup/full ./data
[root@db01 /usr/local/mysql]# chown -R mysql.mysql data
#方式二：
[root@db01 /usr/local/mysql]# innobackupex --copy-back /backup/full/
[root@db01 /usr/local/mysql]# chown -R mysql.mysql data
6.xtrabackup增量备份
1.基于上一次备份进行增量
2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中
7.xtrabackup增量备份实践
1.首先进行全备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp /backup/full$(date +%F)
2.模拟新增数据
mysql> create database xtra;
mysql> use xtra
mysql> create table xtrabackup(id int);
mysql> insert xtrabackup values(1),(2),(3);
3.进行增量备份
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full2020-11-05 /backup/firstadd
参数说明:
--incremental：
--incremental-basedir：上一次备份的路径
4.确认增量备份是否准确
[root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2327894806
[root@db01 /backup]# cat firstadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327894806 #第一次增备的起始位置点是全备的结束位置点
to_lsn = 2327902069
5.再次新增数据
mysql> use xtra
mysql> insert xtrabackup values(4),(5),(6);
6.再次增备
[root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/firstadd /backup/twoadd
7.再次查看是否衔接
[root@db01 /backup]# cat full2020-11-05/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2327894806
[root@db01 /backup]# cat firstadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327894806 #全备的结束点
to_lsn = 2327902069
[root@db01 /backup]# cat twoadd/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2327902069 #第一次增备的结束点
to_lsn = 2327903988
8.xtrabackup增量备份恢复实践
1.准备恢复
1）full + firstadd + twoadd
2）需要将 firstadd 和 twoadd 按顺序合并到full中
3）分步骤进行 --apply-log
2.第一步：在全备中apply-log时，只应用redo，不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/full2020-11-05
3.第二步：将 firstadd 合并到 full 中，并且apply-log，只应用redo，不应用undo
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/firstadd/ /backup/full2020-11-05
4.确认合并
[root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 2327902069 #全备的值等于第一次增备的值
5.第三步：将 twoadd 合并到 full 中，redo和undo都应用
[root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/twoadd/ /backup/full2020-11-05
6.再次确认合并
[root@db01 ~]# cat /backup/full2020-11-05/xtrabackup_checkpoints
backup_type = full-prepared
from_lsn = 0
to_lsn = 2327903988
7.第四步：整体full执行apply-log，redo和undo都应用
[root@db01 mysql]# innobackupex --apply-log /backup/full2020-11-05
[root@db01 ~]# systemctl stop mysqld
[root@db01 ~]# mv /usr/local/mysql/data /usr/local/mysql/databack
[root@db01 ~]# innobackupex --copy-back /backup/full2020-11-05
[root@db01 ~]# chown -R mysql.mysql /usr/local/mysql/data
[root@db01 ~]# systemctl start mysqld
