【MySQL】备份与恢复

纸上得来终觉浅，绝知此事要躬行。

MySQL备份文件类型

物理备份

指复制数据库的物理文件，即可以在数据库运行中复制，也可以在数据库停止运行时直接的数据文件复制。这种类型的适用于出现问题时需要快速恢复的大型重要数据库。

冷备份：在数据库关闭状态下进行备份操作；只需要备份MySQL数据库的frm文件，共享表空间文件.ibdata1，独立表空间文件.ibd，重做日志文件.ib_logfile*。建议定期备份数据库配置文件，有利于恢复。
热备份：在数据库处于运行状态时进行备份操作，该备份方法依赖数据库的日志文件；
温备份：针对与MyISAM的备份（MyISAM不支持热备份），需要数据库锁定表格（flush tables with read lock不可写入，但可读取）的状态下进行备份，保证备份数据一致性。

逻辑备份

逻辑备份实质备份的就是SQL语句，表示为逻辑数据结构create database、create table和内容（insert语句或分割文本文件信息）。好处是可以观察导出文件的内容，缺点是之后通过执行SQL语句进行恢复时间较长。

物理备份与逻辑备份的区别

-	逻辑备份	物理备份
备份方式	备份数据库逻辑内容，如：`SQL`语句	备份数据的物理文件
优点	备份文件相对较小，可以只备份表中数据和结构	恢复速度快
缺点	恢复速度慢，需要重建索引等	备份文件较大
对业务影响	`I/O`负载大	`I/O`负载大
工具	`mysqldump`	`ibbackup、xtrabackup、mysqlbackup`

备份策略

完全备份：对数据库进行一个完整的备份，备份与恢复的操作非常简单，但是数据存在大量的重复，会占用大量的磁盘空间，备份的时间也很长。
增量备份：在上一次完全备份的基础上，对于更改的数据进行备份，下一次是在上一次的增备的基础上继续备份。
差异备份：基于上次完全备份之后被修改过的所有文件，备份数据量会越来越大。恢复数据时，只需要恢复上次的完全备份与最近一次的差异备份。
日志备份：指对MySQL数据库的二进制日志的备份，通过对于一个完全备份进行二进制日志的重做来完成数据库point-in-time的恢复工作。MySQL数据库复制（replication）的原理就是异步实时的将二进制日志重做传送并应用到从数据库

mysqldump

命令格式

mysqldump [选项] 数据库名 [数据库名或表名] > 脚本名

连接参数

mysqldump -uroot -proot -S  /var/run/mysqld/mysqld.sock -A > /data/fulldata.sql

备份参数

参数名	缩写	含义
`--host`	`-h`	服务器`IP`地址
`--port`	`-p`	服务器端口号
`--user`	`-u`	用户名
`--password`	`-p`	密码
`--socket`	`-S`	连接套接字
`--all-database`	`-A`	备份服务器上所有的数据库
`--databases`	`-B`	备份指定一个或多个数据库
`--compact`		压缩模式
`--comments`		添加注释
`--lock-tables`	`-l`	在备份中，依次锁住每个数据库下面的表
`--lock-all-tables`	`-x`	备份过程中对所有的数据库的所有表上锁
`--routines`	`-R`	备份存储过程和函数
`--events`	`-E`	备份事件调度器
`--triggers`		备份触发器
`--tab=path()`	`-T`	为给定的每个表创建制表符分隔的文本文件路径。（创建`.sql`和`.txt`文件。）
`--max_allowed_packet`		最大传输数据包
`--flush-log`		备份之前刷新日志
`--master-data=[1\|2]`		备份开始时，自动记录`binglog`文件名和位置号信息。参数为1转存文件记录`change master`，参数为2`change master`被注释，默认情况下为空。会自动忽略`--lock-tables`参数。如果没有使用`--single-trancaction`，则会自动使用`--lock-all-tables`选项
`--default-character-set`		指定默认字符集
`--single-transaction`		在备份开始前，先执行`start transaction`命令，以此来获得备份一致性

备份案例：备份单表（db01下的user表）

# 备份
mysqldump -uroot -proot -S  /var/run/mysqld/mysqld.sock db01 user > /data/backup/user.sql

备份指定一个或多个数据库

mysqldump -h 127.0.0.1 -uroot -proot -B db02 --single-transaction > /data/backup/db02.sql

恢复

mysql -uroot -proot < /data/backup/db02.sql
#或者登录mysql执行 source /data/backup/db02.sql

全量备份与恢复

１．修改配置文件，开启二进制日志记录

[mysqld]
server_id=1
log-bin=/var/lib/mysql/mysqlbin # mysqlbinlog 日志的存储路径和文件名

２．创建存放二进制日志的文件目录并赋予权限

mkdir -p /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql/

３．创建全量备份文件存放目录并赋予权限

mkdir /data/backup/mysql/ -p
chown -R mysql:mysql /data/backup/mysql/

４．重启服务器

service mysql restart

５．创建一个数据库

６．进行全量备份

mysqldump --uroot -proot -hlocalhost -P3306 --all-database --triggers --routines --events --signle-transaction --master-data=1 --flush-logs --set-gtid-purged=OFF > /data/backup/mysql/$(date +%F-%H)-mysql-all.sql

７．设置恢复记录不追加到二进制命令

set sql_log_bin=0;
source /data/backup/mysql/2020-07-11-mysql-all.sql
set sql_log_bin=1;

增量备份与恢复

１．先进行全量备份

２．查看备份文件中的binlog文件名，以及位置

mysqlbinlog --start-position=107856 mysqlbin.0000001 | mysql -uroot -proot

二进制日志备份与恢复

查看二进制文件：

mysqlbinlog --no-defaults mysqlbin.000001

恢复多个二进制日志：

mysqlbinlog mysqlbin.000001.[0-10]* | mysql -uroot -proot

通过mysqlbinlog导出一个文件，然后通过source导入，可以方便我们对导出的文件进行修改:

mysqlbinlog mysqlbin.000001> /data/backup/db02.sql
mysqlbinlog mysqlbin.000001>> /data/backup/db02.sql
mysql -uroot -proot -e "source /data/backup/db02.sql"

指定位置恢复：

mysqlbinlog --start-position=107856 mysqlbin.0000001 | mysql -uroot -proot

命令中--start-position指定的是开始恢复数据的位置，这时所恢复的数据是从指定位置开始直到二进制日志文件的最后。也可以使用--stop-position选项指定停止的位置。还可以通过--start-datetime和--stop-datetime选项可以用来指定二进制日志某个时间点来进行恢复。

`XtraBackup`

XtraBackup备份工具是有Percona公司开发的开源热备工具。支持MySQL 5.0以上版本，安装方式参考官网

配置文件设置datadir需要指向数据目录

[mysqld]
datadir=/var/lib/mysql

`XtraBackup`使用

常用命令格式

innobackupex [参数][目的地址][原地址]

常用参数

--user                # 以什么身份进行操作
--password            # 数据库用户密码
--port                # 数据库端口号，默认3306
--stream              # 打包
--default-file        # 指定默认配置文件
--no-timestamp        # 不会创建时间戳文件
--copy-back           # 备份还原
--incremental         # 使用增量备份，默认使用完整备份
--incremental-basedir # 与--incremental选项联合使用，该参数指定上一级备份的地址来做增量备份

`XtraBackup`完整备份与还原

１．完整备份

# 把备份日志输出到一个文件
innobackupex --user=root --password=root /data/backup/mysql/ 2>> /data/backup/mysql/backup.log

２．完整备份还原

关闭数据库服务

service mysql stop

搞破坏

rm -rf /var/lib/mysql/*

恢复

innobackupex --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf --copy-back /data/backup/mysql/2020-07-11_17-43-08/

出现下面的异常：ERROR：datadir must be specified.需要指定配置文件

给/var/lib/mysql下的恢复的文件设置权限

chown -R mysql.mysql /var/lib/mysql

启动数据库查看数据

service mysql start

`XtraBackup`增量备份与还原

增量备份的实现，依赖于页上的LSN，其工作原理如下：

首选完成一个全备，并记录下此时检查点的LSN
在进行增量备份时，比较表空间中每个页的LSN是否大于上次备份的LSN，如果是，则备份，同时记录当前检查点的LSN

查看完整备份的LSN

cat /data/backup/mysql/2020-07-11_17-43-08/xtrabackup_checkpoints 

backup_type = full-backuped
from_lsn = 0
to_lsn = 325295530
last_lsn = 325295539
compact = 0
recover_binlog_info = 0
flushed_lsn = 325295539

基于全备实现增备

# 全备目录为：/data/backup/mysql/2020-07-11_17-43-08/
innobackupex --user=root --password=root --incremental /data/backup/mysql/ --incremental-basedir=/data/backup/mysql/2020-07-11_17-43-08/　2>> /data/backup/mysql/backup-incremental.log

对全量备份redo-only

# eg:innobackupex --apply-log --redo-only 全备目录　2>>输出文件
innobackupex --apply-log --redo-only /data/backup/mysql/2020-07-11_17-43-08/ 2>>/data/backup/mysql/copyback.log

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

# eg:innobackupex --apply-log --redo-only 全备目录　--incremental-dir=增备目录　2>>输出文件
innobackupex --apply-log --redo-only /data/backup/mysql/2020-07-11_17-43-08/ --incremental-dir=/data/backup/mysql/2020-07-11_18-50-08/　2>> /data/backup/mysql/copyback.log

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

# eg:innobackupex --apply-log 全备目录　2>>输出文件
innobackupex --apply-log /data/backup/mysql/2020-07-11_17-43-08/ 2>> /data/backup/mysql/copyback.log

搞破坏，模拟数据库数据丢失
使用--copy-back参数恢复拷贝到data目录

# eg:innobackupex --apply-log 全备目录　2>>输出文件
innobackupex --copy-back /data/backup/mysql/2020-07-11_17-43-08/ 2>> /data/backup/mysql/copyback.log

授权，重启，查看数据

`XtraBackup`数据流压缩

重定向生成压缩文件：--stream

innobackupex --user=root --password=root --stream=tar /data/backup/mysql/ >/data/backup/mysql/$(date +%F-%H).tar 2>> /data/backup/mysql/backup.log

使用ssh和cat命令组合，直接备份到其他服务器

innobackupex --databases=db01 --user=root --password=root --stream=tar /data/backup/mysql/ 2>> /data/backup/mysql/backup.log | ssh root@127.0.0.1 "cat - > /data/backup/mysql/$(date +%F-%H).tar"

使用gzip再压缩

innobackupex --databases=db01 --user=root --password=root --stream=tar /data/backup/mysql/ 2>> /data/backup/mysql/backup.log | gzip > /data/backup/mysql/$(date +%F+%H).tar.gz

其他备份

`select ...into outfile`

导出一张表中的数据，格式：

select [colum1],[colum2]... into outfile 'filename' from table where ...

file_name表示导出的文件，但是文件所在的路径的权限必须是mysql:mysql的。

chown -R mysql.mysql /xxx/xxx/

`load data infile`

若通过mysqldump　-tab，或者通过select...into outfile导出数据需要恢复时，这时就需要通过命令load data infile（原因是以上两种方法备份会存在两个文件.sql和.txt）

语法如下：

ignore 1 lines：表示可以忽略导入的前几行

load data  ignore 1 lines infile `/data/backup/xxx.txt`　into table　xxx；

`mysqlimport`

本质来讲，是load data infile的命令接口，而且大多数参数与load data infile相似

语法如下：

--user-thread：并发的导入不同的文件

mysqlimport --user-thread=2 db01 /data/backup/table1.txt /data/backup/table2.txt