MySQL的逻辑备份和物理备份

一、逻辑备份

1.逻辑备份工具：mysqldump

2.mysqldump命令：

(1) mysqldump [选项] 数据库名 [表名] > 文件名.sql
(2) msyqldump [选项] --数据库名 [选项 表名] > 文件名.sql
(3) mysqldump [选项] --all-databases [选项] > 文件名.sql
(4) 选项(参数)如下：
        --host -h 服务器IP地址
        --port -P 服务器端口号
        --user -u MySQL用户名
        --password -p MySQL密码
        --databases -B 指定要备份的数据库
        --all-databases -A 备份MySQL服务器上所有的数据库
        --compact 压缩模式，产生更少的输出
        --comments 添加注释信息
        --complete-insert 输出完成的插入语句
        --lock-tables 备份前，锁定所有数据库表
        --no-create-db/--no-create-info 禁止生成创建数据库语句
        --force 当出现错误时任然继续备份操作
        --default-character-set 指定默认字符集
        --add-locks 备份数据表时锁定数据库表
        --single-transaction 保证数据的一致性和服务的可用性
        --flush-logs -F 备份之前刷新日志
        --events -E 备份事件调度器代码
        --triggers -T 备份触发器
        --routines -R 备份存储过程和存储函数

3.在备份数据的时候最好加上 --set-gtid-purged=OFF

4.在备份完成后向备份中追加不记录二进制日志的命令：sed -i '23a SET sql_log_bin=0;' 备份文件所在路径(如果是内部恢复的话，就不用向备份中添加了，直接在内部操作就可以了)

5.在恢复完成后记得重新开启二进制日志的记录：set sql_log_bin=1;

6.在恢复数据出错的时候，可以在数据库中运行：reset master（不能在有主从关系的数据库上执行）

7.全量备份：mysqldump -uroot -p --all-databases --single-transaction --source-data=default --flush-logs --events --triggers --routines > 备份文件名.sql

8.查看全备中binlog备份到哪个点了：sed -n '22p' 全备路径

9.通过mysqlbinlog进行增量恢复：mysqlbinlog --start-position='查找到的binlog的位置' 要截取的binlog的文件名(可以有多个，但是要用空格隔开) | mysql -uroot -p

二、物理备份

1.物理备份分为：热备，冷备，温备

(1) 热备：在不关闭mysql服务的情况下备份
(2) 冷备：在关闭MySQL服务的情况下备份
(3) 温备：在备份的时候只能读不能写

2.物理备份工具Xtrabackup下载地址:https://www.percona.com/downloads/

3.下载链接：https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.21/binary/redhat/6/x86_64/Percona-XtraBackup-2.4.21-r5988af5-el6-x86_64-bundle.tar

4.依赖包下载地址：http://rpmfind.net/linux/atrpms/el6-x86 64/atrpms/stable/libev-4.04-2.el6.x86 64.rpm

5.保证配置文件中有：datadir=数据库的路径

6.Xtrabackup的使用：

(1) 一般使用innobackupex脚本，因为innobackupex是perl脚本对xtrbackup的封装和功能能扩展
(2) 系统用户在数据库的目录下具有读、写、执行的权限
(3) 数据库用户在数据库中有以下权限：
        RELOAD和LOCK TABLES权限，执行FLUSH TABLES WITH READ LOCK;
        REPLICATION CLIENT权限，获取binary log（二进制日志文件）位置；
        CREATE TABLESPACE权限，导入表，用户表级别的恢复；
        SUPER权限，在slave环境下备份用来启动和关闭slave线程
(4) 命令格式：innobackupex [参数] [目的地址|源地址]
(5) 常用参数：
        --user                                 用户名
        --password                         用户密码
        --port                                  数据库端口号
        --stream                             打包（数据流）
        --defaults-file                      指定默认配置文件，默认读取/etc/my.cnf
        --no-timestamp                  不创建时间戳文件，而改用目的地址（可以自动创建）
        --copy-back                       备份还原的主要选项
        --incremental                     使用增量备份，默认使用完整备份
        --incremental-basedir        与--incremental选项联合使用，该参数指定上一级备份的地址来做增量备份

7.xtrbackup完整备份：

(1) innobackupex --user=数据库用户名 --password=数据库密码 保存备份文件的路径（本方法在备份的时候会产生大量的日志，如果想要保存日志，则使用下面的方法）
(2) innobackupex --user=数据库用户名 --password=数据库密码 保存备份文件的路径 2 >> 保存产生日志的文件的路径

8.备份完成后，如果要查看备份信息，则查看备份文件目录下的xtrabackup_checkpoints文件

9.xtrbackup数据还原：
(1) 注意：innobackupex --copy-back不会覆盖已存在的文件。而且还原时需要先关闭服务，如果服务是启动的，那么就不能还原到datadir
(2) innobackupex --copy-back 已经备份的文件 2 >> 保存产生的日志文件的路径

10.在恢复完成后记得给目录相应的权限

11.xtrbackup增量备份：

(1) 增量备份需要使用参数--incremental指定需要备份到哪个目录，使用incremental-dir指定全备目录；
(2) innobackupex --user=数据库用户名 --password=数据库密码 --incremental 增量备份保存路径 --incremental-basedir=完整备份全路径

12.xtrbackup增量还原：
(1) 进行数据恢复时，需要使用参数--apply-log --redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性；
(2) 再将增量备份数据使用参数--incremental-dir合并到全备数据当中；

13.准备全量备份：

(1) --apply-log        声明日志
(2) --redo-only        对日志序列号进行审核统计
(3) innobackupex --apply-log --redo-only 全量备份的路径

14.应用第一次增量备份到全量备份

innobackupex --apply-log --redo-only 全量备份的路径 --incremental-dir=增量备份路径

15.查看全量备份的xtrabackup——checkpoints

16.把备份整体进行一次apply操作：

innobackupex --apply-log 全量备份路径

17.用--copy-back还原数据

innobackupex --copy-back 全量备份路径

18.恢复完成后记得更改数据库目录所在的权限（将用户改为mysql）

19.xtrabackup数据流压缩：

(1) --stream选项
        注意：使用--stream选项时，会输出打包的数据流，并不会直接生成打包文件，此时需要使用重定向或其他命令对数据流进行处理
(2) 将标准输出重定向为tar文件，将标准错误重定向到日志文件：
        innobackupex --databases=数据库名 --user=用户名 --password=数据库密码 --stream=tar 保存文件的路径 > 保存文件的路径/date +%F.tar(文件名) 2>保存产生的日志的路径
(3) 使用gzip在进行压缩一下：
        innobackupex --databases=数据库名 --user=用户名 --password=数据库密码 --stream=tar 保存文件的路径 2>保存产生的日志的路径 | gzip > 保存文件的路径/date +%F.tar.gz(文件名)