mysql备份还原

14、 备份还原
14.1、 备份的目的

做灾难恢复：对损坏的数据进行恢复和还原
需求改变：因需求改变而需要把数据还原到改变以前
测试：测试新功能是否可用

14.2、 备份需要考虑的问题

可以容忍丢失多长时间的数据；
恢复数据要在多长时间内完；
恢复的时候是否需要持续提供服务；
恢复的对象，是整个库，多个表，还是单个库，单个表。

14.3、 备份的类型

14.3.1、 1、根据是否需要数据库离线

冷备（cold backup）：需要关mysql服务，读写请求均不允许状态下进行；
温备（warm backup）： 服务在线，但仅支持读请求，不允许写请求；
热备（hot backup）：备份的同时，业务不受影响。

注：

1、这种类型的备份，取决于业务的需求，而不是备份工具
2、MyISAM不支持热备，InnoDB支持热备，但是需要专门的工具

14.3.2、 2、根据要备份的数据集合的范围
完全备份：full backup，备份全部字符集。
增量备份: incremental backup 上次完全备份或增量备份以来改变了的数据，不能单独使用，要借助完全备份，备份的频率取决于数据的更新频率。
差异备份：differential backup 上次完全备份以来改变了的数据。
建议的恢复策略：
完全+增量+二进制日志
完全+差异+二进制日志

14.3.3、 3、根据备份数据或文件

物理备份：直接备份数据文件

优点：

备份和恢复操作都比较简单，能够跨mysql的版本，
恢复速度快，属于文件系统级别的

建议：

不要假设备份一定可用，要测试
mysql>check tables；检测表是否可用
逻辑备份: 备份表中的数据和代码

优点：

恢复简单、
备份的结果为ASCII文件，可以编辑
与存储引擎无关
可以通过网络备份和恢复

缺点：

备份或恢复都需要mysql服务器进程参与
备份结果占据更多的空间，
浮点数可能会丢失精度
还原之后，缩影需要重建

14.4、 备份的对象

1、 数据；
2、配置文件；
3、代码：存储过程、存储函数、触发器
4、os相关的配置文件
5、复制相关的配置
6、二进制日志

14.5、 注意事项：
使用的时候MySQL当要导入或者导出数据量大的库的时候,用PHPMYADMIN甚至MySQL Administrator这些工具都会力不从心,这时只能使用MySQL所提供的命令行工具mysqldump进行备份恢复。数据量太大的时候不推荐使用，可支持MyISAM,InnoDB
14.6、 备份和恢复的实现
逻辑备份工具mysqldump（温备份）
特点：适用于小型企业，
适用于数据库中数据少的情况，
导出数据时锁表（不允许写入数据）
支持MyISAM,InnoDB

14.6.4、 MySQL数据的导出和导入工具:mysqldump
MySQLdump常用
备份：语法： mysqldump [TMPIONS] database [tables] >导出的文件名.sql
mysqldump -u root -p --databases 数据库1 数据库2 > xxx.sql

还原：系统命令行： MySQL -uroot -p123456 <f:\all.sql

常见选项：
–all-databases, -A： 备份所有数据库
–databases, -B： 用于备份多个数据库，如果没有该选项，mysqldump把第一个名字参数作为数据库名，后面的作为表名。使用该选项，mysqldum把每个名字都当作为数据库名。

–force, -f：即使发现sql错误，仍然继续备份
–host=host_name, -h host_name：备份主机名，默认为localhost
–no-data, -d：只导出表结构
–password[=password], -p[password]：密码
–port=port_num, -P port_num：制定TCP/IP连接时的端口号
–quick, -q：快速导出
–tables：覆盖 --databases or -B选项，后面所跟参数被视作表名
–user=user_name, -u user_name：用户名
–xml, -X：导出为xml文件
备份：

1.备份全部数据库的数据和结构

mysqldump -uroot -p123456 -A >F:\all.sql

2.备份全部数据库的结构（加 -d 参数）

mysqldump -uroot -p123456 -A-d>F:\all_struct.sql

3.备份全部数据库的数据(加 -t 参数)

mysqldump -uroot -p123456 -A-t>F:\all_data.sql

4.备份单个数据库的数据和结构(,数据库名mydb)

mysqldump -uroot-p123456 mydb>F:\mydb.sql

5.备份单个数据库的结构

mysqldump -uroot -p123456 mydb-d>F:\mydb.sql

6.备份单个数据库的数据

mysqldump -uroot -p123456 mydb-t>F:\mydb.sql

7.备份多个表的数据和结构（数据，结构的单独备份方法与上同）

mysqldump -uroot -p123456 mydb t1 t2>f:\multables.sql

8.一次备份多个数据库

mysqldump -uroot -p123456 --databases db1 db2>f:\muldbs.sql
还原

还原部分分（1）mysql命令行source方法 和 （2）系统命令行方法

1.还原全部数据库:

(1) mysql命令行：mysql>source f:\all.sql

(2) 系统命令行： mysql -uroot -p123456 <f:\all.sql

2.还原单个数据库(需指定数据库)

(1) mysql>use mydb

mysql>source f:\mydb.sql

(2) mysql -uroot -p123456 mydb <f:\mydb.sql

3.还原单个数据库的多个表(需指定数据库)

(1) mysql>use mydb

mysql>source f:\multables.sql

(2) mysql -uroot -p123456 mydb<f:\multables.sql

4.还原多个数据库，（一个备份文件里有多个数据库的备份，此时不需要指定数据库）

(1) mysql命令行：mysql>source f:\muldbs.sql

(2) 系统命令行： mysql -uroot -p123456<f:\muldbs.sql
写个shell备份脚本
[root@cong11 ~]# vim mysql-autoback.sh
#!/bin/bash
export LANG=en_US.UTF-8
savedir=/database_back/
cd “$savedir"time="$(date +”%Y-%m-%d")"
mysqldump -u root -p123456 book > book-"$time".sql
再添加计划任务就ok
14.6.5、 xtrabackup备份工具使用
xtrabackup（开源）简介（大型数据库）
我们知道，针对InnoDB存储引擎，MySQL本身没有提供合适的热备工具，ibbackup虽是一款高效的首选热备方式，但它是是收费的。好在Percona公司给大家提供了一个开源、免费的Xtrabackup热备工具，它可实现ibbackup（收费）的所有功能，并且还扩展支持真正的增量备份功能，是商业备份工具InnoDB Hotbackup的一个很好的替代品。
Xtrabackup包括两个主要工具：Xtrabackup和innobackupex：
Xtrabackup只能备份InnoDB和XtraDB两种引擎表，而不能备份MyISAM数据表。
innobackupex则封装了xtrabackup，同时可以备份MyISAM数据表。Xtrabackup做备份的时候不能备份表结构、触发器等等，智能区分.idb数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备

xtrbackup 安装
（mysql5.7.20需安装最新版XtraBackup2.4.9）
上传软件包
[root@cong11 ~]# ls
anaconda-ks.cfg boost_1_59_0.tar.bz2 mysql-community-5.7.26-1.el7.src.rpm
book_utf8.sql mysql-5.7.26 Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
网上下载地址
https://www.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.14/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
解压
[root@cong11 ~]# tar -xf Percona-XtraBackup-2.4.14-ref675d4-el7-x86_64-bundle.tar
安装解决并解决依赖
配置好本地yum源
[root@cong11 ~]# yum -y install percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm

报错：需要libev.so.4()依赖
解决：
上传libev-4.03-3.el6.x86_64.rpm包
或者
网络下载地址：http://rpmfind.net/linux/epel/6/x86_64/Packages/l/libev-4.03-3.el6.x86_64.rpm
[root@cong11 ~]# rpm -ivh libev-4.03-3.el6.x86_64.rpm
[root@cong11 ~]# yum install -y percona-xtrabackup-24-2.4.14-1.el7.x86_64.rpm
修改数据目录
注意：安装完成以后记得更改你的/etc/my.cnf配置文件制定数据目录，因为Xtrabackup是根据你的/etc/my.cnf配置文件来获取你备份的文件，比如在/etc/my.cnf的[mysqld] 下添加datadir=/usr/local/mysql/data，然后重启mysql
[root@cong11 ~]# vim /etc/my.cnf
[mysqld]
datadir=/data/mysql/data
[root@cong11 ~]# systemctl restart mysqld #重启msyql
xtrbackup使用
我们一般使用innobackupex脚本
innobackupex是perl脚本对xtrabackup的封装，和功能扩展。
备份准备工作：
权限和链接
xtrabackup需要连接到数据库（需要数据库用户）和datadir操作权限（需要系统用户）。
xtrabackup或者innobackupex在使用过程中涉及到2类用户权限：
1、系统用户，用来调用innobackupex或者xtrabackup
2.数据库用户，数据库内使用的用户
连接到服务：innobackupex或者xtrabackup通过-user和-password连接到数据库服务
$ innobackupex --user=DBUSER --password=SECRET /path/to/backup/dir/
$ innobackupex --user=LUKE --password=US3TH3F0RC3 --stream=tar ./ | bzip2 - 压缩
解释：stream:指定归档压缩
$ xtrabackup --user=DVADER --password=14MY0URF4TH3R --backup --target-dir=/data/bkps/
其他连接选项：

端口，socket，主机ip
[root@mysql-srv1 ~]# innobackup --help | grep “socket” //查看有关socket的帮助信息
可以单独创建用来备份数据库的用户，安全，并赋予对应的权限
全备和全备还原
使用innobackupex创建全备
创建全备：
#mkdir /db_backup

innobackupex --user=root --password=123456 -S /usr/local/mysql/mysql.socket /db_backup/

解释：-S：指定套接字文件

或：
[root@cong11 ~]# innobackupex --user=root --password=123456 /db_backup/ 2>>/db_backup/db_backup.log
#把输出信息从定向到/tmp/db_backup/db_backup.log文件

[root@cong11 ~]# ls /tmp/db_backup/
2019-05-21_12-19-25 2019-05-21_12-21-44 db_backup.log
内部机制：在备份的时候innobackupex会调用xtrabackup来备份innodb表，并复制所有的表定义，其他引擎的表(MyISAM,MERGE,CSV,ARCHIVE)。
其他选项:
–no-timestamp，指定了这个选项备份会直接备份在BACKUP-DIR，不再创建时间戳文件夹。
–default-file，指定配置文件，用来配置innobackupex的选择
innobackupex --user=root --password=123456 --no-timestamp /tmp/db_backup/full
(使用–no-timestamp时，后面的这个full目录必须跟上且不能提前自己建立，它由innobackupex自动建立，否则会报错innobackupex: Error: Failed to create backup directory)
使用innobackupex还原备份
1、停止数据库
[root@cong11 ~]# /etc/init.d/mysqld stop
2、移走数据库数据(危险不要删除数据库)
[root@cong11 ~]# mv /data/mysql/data /mnt
3、使用工具还原数据
[root@cong11 ~]# innobackupex --copy-back /db_backup/2019-05-21_12-21-44/
4、查看权限
[root@cong11 ~]# ll -d /data/mysql/data
drwxr-x— 7 root root 192 5月 21 14:27 /data/mysql/data/
5、修改权限
[root@cong11 ~]# chowm -R mysql:mysql /data/mysql/data
6、启动mysql
[root@cong11 ~]# systemctl restart mysqld
注：datadir必须是为空的，innobackupex --copy-back不会覆盖已存在的文件，还要注意，还原时需要先关闭服务，如果服务是启动的，那么就不能还原到datadir。
7、查看数据是否完整
[root@cong11 ~]# mysql -uroot -p123456 -e “select * from book.books;”

创建增量备份和还原
增量备份作用：减少备份数据重复，节省磁盘空间，缩短备份时间
增量备份的实现，依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。
创建增量备份
1：首先创建全备
在创建增量备份之前需要一个全备，不然增量备份是没有意义的。
[root@cong11 ~]# innobackupex --user=root --password=123456 -S /usr/local/mysql/mysql.sock /db_backup/
#ll /db_backup/ //查看结果
检查备份文件夹下的xtrabackup-checkpoints，查看信息
[root@cong11 ~]# cat /tmp/db_backup/2019-05-21_12-21-44/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2622668 #从2622668开始备份
last_lsn = 2622677 #到2622677结束，开始在这里创建第一个增量备份
compact = 0
recover_binlog_info = 0
flushed_lsn = 2622677
2、插入一些数据到表里面
mysql> use HA;
mysql> select * from students;
mysql> desc students;

mysql> insert into students values(1,‘zheng’,20,‘M’);
3、使用-incremental创建增量备份
语法：
innobackupex --user=root --password=123456 --incremental /增量1路径 --incremental-basedir=全备路径 后面指定为哪个全备上进行增量备份
解释：增量备份时需要查看xtrabackup_checkpoints 里的last_lsn号，根据这个号进行增量备份
[root@cong11 ~]# innobackupex --user=root --password=123456 -S /usr/local/mysql/mysql.sock --incremental /db_backup/ --incremental-basedir=/db_backup/2019-05-21_12-21-44/
//如果有问题，需要指定套接字文件
4、再查看LSN<日志序列号>
[root@cong11 ~]# cat /tmp/db_backup/2019-05-21_15-26-55/xtrabackup_checkpoints
backup_type = incremental
from_lsn = 2622668 #全备的最后编号，从这个编号开始备份
to_lsn = 2623258
last_lsn = 2623267 #编号更新
compact = 0
recover_binlog_info = 0
flushed_lsn = 2623267
5、增量备份创建的替代方法
可以使用指定-incremental-lsn来代替-incremental-basedir的方法创建增量备份。
[root@cong11 ~]# innobackupex --user=root --password=123456 --incremental /tmp/db_backup/ --incremental-lsn=2622668
注意：xtrabackup只会影响xtradb或者innodb的表，其他引擎的表在增量备份的时候只会复制整个文件，不会差异。
增量备份恢复
1、停止mysql,删除数据
[root@cong11 ~]# systemctl stop mysqld
[root@cong11 ~]# rm -rf /data/mysql/data
2、在所有备份目录下重做已提交的日志
执行命令，把所有备份日志 集中在全备目录中，然后使用修改后的全备去恢复数据库。
语法：
第一步：
步骤（1）innobackupex --apply-log --redo-only BASE-DIR
步骤（2）innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
步骤（3）innobackupex --apply-log BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
其中BASE-DIR是指全备目录，INCREMENTAL-DIR-1是指第一次的增量备份，INCREMENTAL-DIR-2是指第二次的增量备份，以此类推。
这里要注意的是：最后一步的增量备份并没有–redo-only选项！还有，可以使用–use_memory提高性能。
如果只有一个增量备份，只需要执行步骤（1）和步骤（3）即可
以上语句执行成功之后，最终数据在BASE-DIR（即全备目录）下。
[root@cong11 ~]# innobackupex --apply-log --redo-only /db_backup/2019-05-21_12-21-44
[root@cong11 ~]# innobackupex --apply-log /db_backup/2019-05-21_12-21-44 --incremental-dir=/db_backup/2019-05-21_15-26-55/
第一步完成之后，我们开始第二步：回滚未完成的日志：
解释：在执行备份的时候，如果有写入数据会写入到重做日志里，回滚未完成的日志时，会将写入到重做日志里的数据写入数据库里。
语法：innobackupex --apply-log BASE-DIR
innobackupex --apply-log /db_backup/2019-05-21_12-21-44
上面执行完之后，BASE-DIR里的备份文件已完全准备就绪，最后一步是拷贝：
语法：innobackupex --copy-back BASE-DIR
innobackupex --copy-back /db_backup/2019-05-21_12-21-44
4、恢复mysql权限
[root@cong11 ~]# chown -R mysql:mysql /data/mysql/data
5、启动mysql
[root@cong11 ~]# systemctl restart mysqld
6、测试数据
[root@cong11 ~]# mysql -uroot -p123456 -e"select * from HA.students;"