MySQL备份与恢复

一、备份分类、备份策略

MySQL备份的分类和策略介绍如下：
备份分类：

1. 完全备份：这是备份整个数据库的方法，包括所有表、视图、触发器等。完全备份是其他备份类型的基础。
2. 差异备份：差异备份是备份自上次完全备份以来发生更改的数据。
3. 增量备份：增量备份只备份自上次备份（无论是完全备份还是差异备份）以来发生更改的数据。与差异备份相比，增量备份需要更少的存储空间和备份时间。但在恢复数据时，需要先恢复完全备份，然后按顺序恢复每个增量备份。
4. 物理备份：物理备份是直接复制数据库的物理文件，如数据文件、日志文件等。
5. 逻辑备份：逻辑备份是导出数据库的结构和数据到某种格式的文件，如SQL脚本文件。
6. 点时间备份：在某一特定时间点对数据库进行备份。
7. 每日备份：每天对数据库进行一次备份。
8. 事务日志备份：只备份事务日志文件，而不是整个数据库。

备份策略：

1. 冷备：在数据库停止的情况下进行备份，也称为离线备份。在这种备份中，MySQL实例是停止的，用户不能访问数据，因此无法读取或修改数据。冷备不会干扰正在运行的系统的性能，但对于某些应用程序，可能无法接受在长时间内锁定或完全阻止用户访问数据。
2. 热备：在数据库运行中直接备份，也称为在线备份。这种备份在读取或修改数据的过程中进行，很少中断或不中断传输或处理数据的功能。使用热备份时，系统仍可供读取和修改数据的操作访问。
3. 温备是备份在数据库运行时进行，但仅支持读请求，不允许写请求，例如，加一个读锁以保证备份数据的一致性。

在选择备份策略和分类时，需要根据具体的业务需求、系统环境、数据重要性等因素综合考虑，以确保数据的安全性和可恢复性。

二、常用的备份工具

备份工具	安装难度	备份类型	备份速度	备份即时点	压缩备份	远程备份
mysqldump	自带	逻辑	慢	不支持	不支持	支持
mydumper	简单	逻辑	快	支持	支持	支持
xtrabackup	较难	物理	较快	支持	不支持	不支持

2.1 mysqldump备份

2.1.1 备份整个数据库

若是主从模式，一般在从节点进行备份操作。

/data/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqldump --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf -uroot -py-yxoqupe33Z -h127.0.0.1 --single-transaction --skip-add-locks -R --all-databases > alldatabases.sql

这个命令的各个参数的含义如下：
-uroot: 这个参数用于指定连接数据库的用户名为root。	
-pyxoqupe33Z: 这个参数用于指定连接数据库的密码为 yxoqupe33Z 。注意，通常 -p 和密码之间没有空格。
-h127.0.0.1: 这个参数指定了数据库服务器的地址，127.0.0.1 表示本地主机。
--single-transaction: 这个选项使 mysqldump 在导出每个数据库时启动一个单一事务。这确保了在导出过程中数据库的一致性视图，并且适用于存储引擎如 InnoDB，它支持事务。
--skip-add-locks: 这个选项告诉 mysqldump 在输出的 SQL 文件中不添加 LOCK TABLES 语句。通常，mysqldump 会为导出的每个表添加 LOCK TABLES 语句来锁定表，以便在导出时获得一致的快照。使用 --skip-add-locks 可能会影响到导入时的数据一致性，因此需谨慎使用。
-R: 这个选项告诉 mysqldump 导出存储过程和函数。
--all-databases: 这个选项告诉 mysqldump 导出所有数据库。
alldatabases.sql: 这部分是将 mysqldump 的输出重定向到一个名为 alldatabases.sql 的文件中。> 是 Unix/Linux 系统中用于重定向输出的符号。

[root@mha-slave01 backup]# /data/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqldump --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf -uroot -py-yxoqupe33Z --single-transaction --skip-add-locks -R --all-databases > alldatabases.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

Warning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.

第二个警告是关于 xtrabackup 备份 MySQL 数据库时的一个重要注意事项。当 MySQL 服务器启用了全局事务标识符（GTIDs）时，执行备份操作会默认包含所有事务的 GTIDs，这包括那些更改了被抑制的数据库部分的事务。

如果在恢复数据时不想使用 GTIDs，在执行备份时添加 --set-gtid-purged=OFF 参数。这个参数会确保备份数据中不包含 GTIDs，这样在恢复时就不会考虑 GTIDs。

此外，警告还提到，为了制作一个完整的备份，应该使用 --all-databases、–triggers、–routines 和 --events 参数。这些参数的含义如下：

--all-databases: 备份所有数据库。
--triggers: 备份数据库的触发器。
--routines: 备份存储过程和函数。
--events: 备份事件调度器的事件。

创建一个完整的、不包含 GTIDs 的备份，并同时包括触发器、存储过程和事件，使用以下命令：

/data/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqldump --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf -uroot -py-yxoqupe33Z --single-transaction --set-gtid-purged=off -R --all-databases > alldatabases.sql

[root@mha-slave01 backup]# /data/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqldump --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf -uroot -py-yxoqupe33Z --single-transaction --set-gtid-purged=off -R --all-databases > alldatabases.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

2.1.2 还原整个数据库

mysql -u用户名 -p密码 -h主机地址 -P端口号 < 备份文件

2.1.3 备份单个数据库

mysqldump -u用户名 -p密码 -h主机地址 --single-transaction --skip-add-locks -R 数据库名> xx.sql

注：如果将备份文件导入到其他实例中去，且建库用户不一致时。需要修改备份文件中的存储过程的DEFINER用户，命令如下：

sed  -i "s/\(DEFINER=\)\(.*\)\`%\`//g"  备份文件

同时备份多个库通过参数： --databases dbname1 dbname2 dbname3

2.1.4 还原单个数据库

-- 先创建需要还原的库
CREATE DATABASE 需要还原的库名 DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;
mysql -u用户名-p密码 -h主机地址 -P端口号 需要还原的库名< 备份文件

还原的库名必须存在，可以是原来备份的库名，也可以是新建的另一个库名，需对导出来的sql进行修改。（注意字符集和排序集）

2.1.5 配置定时任务备份数据库

mkdir -p /data/backup/{log,fullbak,scripts}
cat /data/backup/scripts/backup_inno.sh 
/data/mysql8.0.35/install/mysql-8.0.35-linux-glibc2.12-x86_64/bin/mysqldump --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf  --socket=/data/mysql8.0.35/3307/tmp/mysql.sock -uroot -p'Admin@dba1' --backup --no-server-version-check --target-dir=/data/backup --stream=xbstream 2>/data/backup/log/backup_$(date +%F_%H-%M-%S.log) | gzip > /data/backup/fullbak/$(date +%F_%H-%M-%S).tar.gz

crontab -e
#每天的23:59分执行备份
59 23 * * * sh /cloud_backup/scripts/backup_inno.sh

避免长时间生成的备份文件与备份日志过多，需定时清理

cat /data/backup/scripts/clean_backup.sh
#查找并删除 /data/backup/fullbak 目录下超过3天修改时间的备份文件文件
find /data/backup/fullbak -type f  -mtime +3 -exec rm {} \;
find /data/backup/log/backup_20* -type f  -mtime +7 -exec rm {} \;

crontab -e
##每天的凌晨3点59分执行
59 3 * * * sh /data/backup/scripts/clean_backup.sh

2.2 mydumper备份

2.2.1 工具介绍

mydumper 是一款社区开源的逻辑备份工具。该工具主要由 C 语言编写，目前由 MySQL 、Facebook 等公司人员开发维护。

官网地址：https://launchpad.net/mydumper
GitHub 地址：https://github.com/maxbube/mydumper

参考官方介绍，mydumper 主要有以下几点特性：

1. 支持多线程导出数据，速度更快；
2. 支持一致性备份；
3. 支持将导出文件压缩，节约空间；
4. 支持多线程恢复；
5. 支持以守护进程模式工作，定时快照和连续二进制日志；
6. 支持按照指定大小将备份文件切割；
7. 数据与建表语句分离。

备份原理：

备份过程主要步骤概括：

1. 主线程 FLUSH TABLES WITH READ LOCK , 施加全局只读锁，以阻止 DML 语句写入，保证数据的一致性；
2. 读取当前时间点的二进制日志文件名和日志写入的位置并记录在 metadata 文件中，以供即时点恢复使用；
3. N 个（线程数可以指定，默认是 4个）dump 线程 START TRANSACTION WITH CONSISTENT SNAPSHOT ; 开启读一致的事物；
4. dump non-InnoDB tables , 首先导出非事物引擎的表；
5. 主线程 UNLOCK TABLES 非事物引擎备份完后，释放全局只读锁；
6. dump InnoDB tables , 基于事物导出 InnoDB 表；
7. 事物结束。

2.2.2 安装方式

下载对应的mydumper安装包，此处以centos或redhat的x86_64的安装包为例：

wget https://github.com/maxbube/mydumper/releases/download/v0.10.7-2/mydumper-0.10.7-2.el7.x86_64.rpm
rpm -ivh mydumper-0.10.7-2.el7.x86_64.rpm

2.2.3 mydumper参数说明

参数名	缩写	含义
–user	-u	备份所使用的用户
–password	-p	连接所用的用户密码
–host	-h	MySQL服务端地址
–port	-P	MySQL端口
–threads	-t	开启的备份线程数，默认是4
–database	-B	要备份的数据库，不指定则备份所有库
–tables-list	-T	需要备份的表，名字用逗号隔开
–outputdir	-o	备份文件输出的目录
–statement-size	-s	生成的insert语句的字节数，默认1000000，与–rows冲突
–rows	-r	将表按行分割，指定这个选项会关闭 --chunk-filesize
–chunk-filesize	-F	将表按大小分割时，指定分割大小，单位是MB
–regex	-x	使用正则表达式匹配’db.table’
–compress	-c	压缩输出文件
–ignore-engines	i-	忽略的存储引擎
–no-schemas	-m	不备份表结构
–no-data	-d	不备份表数据
–triggers	-G	备份触发器
–events	-E	备份事件
–routines	-R	备份存储过程和函数
–no-views	-W	不备份视图
–no-locks	-k	不使用临时共享只读锁，使用这个选项会造成数据不一致
–daemon	-D	启用守护进程模式，守护进程模式以某个间隔不间断对数据库进行备份
–where		只导出选择的数据

2.2.4 使用语法

1. 备份全部数据库

mydumper -u root -p 123456  -r 100000 -o /data/backup/fullbak

注意：全量备份会备份 mysql、sys 系统库及其他自建库

2. 备份全部数据库 包含触发器、事件、存储过程及函数

mydumper -u root -p 123456 -G -R -E -o -r 100000 /data/backup/fullbak

3. 备份指定库

mydumper -u root -p 123456 -G -R -E -r 100000 -B  db -o /data/backup/fullbak/db

4. 使用正则 排除系统库

mydumper -u root -p 123456 -G -R -E  -r 100000 --regex '^(?!(mysql|sys))' -o /data/backup/gex_db

5. 备份指定表

mydumper -u root -p 123456 -B db1 -T tb1,tb2  -r 100000 -o /data/backup/tbs/

6. 只备份表结构

mydumper -u root -p 123456 -d  -r 100000 -B  db1 -o /data/backup/nodata/

7. 只备份表数据

mydumper -u root -p 123456 -m  -r 100000 -B db1 -o /data/backup/noschema/

8. 压缩备份某个表

mydumper -u root -p 123456  -r 100000 -B db1 -T tb1 -c -o /data/backup/compress/

注意：备份时建议配置-r 参数，按行分割，避免恢复时一次insert数据太多导致触发系统OOMKill。

2.3 myloader还原

2.3.1 参数说明

参数名	缩写	含义
–user	-u	使用的用户名
–password	-p	连接所用的用户密码
–host	-h	MySQL服务端地址
–port	-P	MySQL端口
–threads	-t	开启的线程数，默认是4
–directory	-d	指定待恢复的备份目录
–queries-per-transaction	-q	每次事务执行的查询数量，默认是1000
–overwrite-tables	-o	如果要恢复的表存在，则先drop掉该表
–database	-B	需要还原到哪个数据库（目标数据库）
–source-db	-s	选择被还原的数据库（源数据库）
–enable-binlog	-e	在恢复时开启binlog

2.3.2 使用语法

1. 恢复备份文件中的全部 如果表已存在则先删除

myloader -u root -p 123456 -o -d /data/backup/fullbak

2. 从全备中恢复指定库

myloader -u root -p 123456 -s db1 -o -d /data/backup/fullbak

3. 将某个数据库备份还原到另一个数据库中（目标库不存在则会新建）

myloader -u root -p 123456 -B recover_db -s db -o -d /data/backup/fullbak
myloader -u root -p 123456 -B recover_db -o -d /data/backup/db

4. 恢复时开启binlog（有备库的时候需要开启）

myloader -u root -p 123456 -e -o -d /data/backup/db

5. 无法直接还原单表 只能找到单表的sql文件 进入命令行source 执行

source db.tb1-schema.sql --还原表结构
source db.tb1.sql --还原表数据

2.3 xtrabackup工具备份

2.3.1 备份原理

innobackupex在后台线程不断追踪InnoDB的日志文件，然后复制InnoDB的数据文件。数据文件复制完成之后，日志的复制线程也会结束。这样就得到了不在同一时间点的数据副本和开始备份以后的事务日志。完成上面的步骤之后，就可以使用InnoDB崩溃恢复代码执行事务日志（redo log），以达到数据的一致性。

备份分为两个过程：

1. backup，备份阶段，追踪事务日志和复制数据文件（物理备份）。
2. preparing，重放事务日志，使所有的数据处于同一个时间点，达到一致性状态。

XtraBackup的优点：

1. 可以快速可靠的完成数据备份（复制数据文件和追踪事务日志）
2. 数据备份过程中不会中断事务的处理（热备份）
3. 节约磁盘空间和网络带宽
4. 自动完成备份鉴定
5. 因更快的恢复时间而提高在线时间

2.3.2 常用参数说明

--defaults-file: 指明服务器的配置文件，此参数必须作为innobackupex的第一个参数，否则报错
--host: 指明主机
--user: 指明备份的用户名
--password: 指明备份用户名的密码
--backup: 指明为备份
--apply-log: 指明为重做日志
--copy-back: 指明为恢复
--slave-info: 备份从库的show slave status信息，仅用于在备份从库时使用
--no-lock: 不锁表，仅适用于存储引擎为innodb，并且不在乎备份位置点时使用

增量备份：

--incremental: 这个选项告诉 xtrabackup 创建一个增量备份，而不是完全备份。它传递到 xtrabackup 子进程。
--incremental-dir:恢复增量备份时，指明增量备份的目录
--incremental-basedir: 指明此次的增量备份是基于之前的哪一次备份，增量备份仅适用于innodb和xtradb引擎

部分备份：

--include: 使用正则表达式备份匹配到的内容；例：备份mysql库中的user表，--include='^mysql[.]user'
--table-files: 如果要备份多个表，可以把多个表以”数据库名.表名“的方式写到文件中，备份时指定此文件
--databases: 可以备份整个库，也可以备份库中的某个表，备份表时用"库名.表名"的方式
--export: 使用部分备份恢复时用此选项，这和完整备份和增量备份有所区别

2.3.3 备份全库（MySQL5.7版本）

官方网站下载xtrabackup
https://downloads.percona.com

wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.29/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.29-1.el7.x86_64.rpm

备份命令：

innobackupex --defaults-file=数据库配置文件路径 --user=用户名 --password=密码 --no-lock 备份路径

示例：

innobackupex --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf --user=root --password=123456 --no-lock /data/backup/fullbak/20240206

2.3.4 备份脚本（MySQL5.7版本）

cat /data/backup/scripts/backup_inno57.sh

/usr/bin/innobackupex --defaults-file=/data/mysql5.7.36/3307/conf/my.cnf --socket=/data/mysql5.7.36/3307/tmp/mysql.sock -uroot -p123456 --stream=tar /data/backup/fullbak 2>/data/backup/log/backup_$(date +%F_%H-%M-%S.log) | gzip > /data/backup/fullbak/$(date +%F_%H-%M-%S).tar.gz

定时备份

crontab -e
#每天23点59分执行备份
59 23 * * * sh /data/backup/scripts/backup_inno57.sh

避免生成的备份文件与备份日志过多，因此制定清理脚本

cat /data/backup/scripts/clean_backup57.sh

find /data/backup/fullbak -type f  -mtime +3 -exec rm {} \;
find /data/backup/log/backup_20* -type f  -mtime +7 -exec rm {} \;

定时清理

crontab -e 
#每天凌晨3点59分清理
59 3 * * * sh /cloud_backup/scripts/clean_backup57.sh

2.3.5 恢复全库（MySQL5.7版本）

恢复命令：

1. 恢复重做日志：

    innobackupex --defaults-file=数据库配置文件路径 --user=用户名 --password=密码 --apply-log 已备份文件的路径
   

2. 恢复数据：

    innobackupex --defaults-file=数据库配置文件路径 --user=用户名 --password=密码 --copy-back 已备份文件的路径
   

示例：

1. 恢复重做日志：

innobackupex --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf --user=root --password=123456 --apply-log /data/backup/fullbak/20240306/2024-03-06_10-03-30

2. 恢复数据：

innobackupex --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf --user=root --password=123456 --copy-back /data/backup/fullbak/20240306/2024-03-06_10-03-30

2.3.6 备份全库（MySQL8.0版本）

官方网站下载xtrabackup
https://downloads.percona.com

https://downloads.percona.com/downloads/percona-distribution-mysql-ps/percona-distribution-mysql-ps-8.0.35/binary/redhat/7/x86_64/percona-xtrabackup-80-8.0.35-30.1.el7.x86_64.rpm

备份命令：

xtrabackup --defaults-file=数据库配置文件路径 --user=用户名 --password=密码 --no-lock --target-dir=备份路径

示例：

xtrabackup --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf --user=root --password=123456 --no-lock --target-dir=/data/backup/fullbak/20240306

2.3.7 备份脚本（MySQL8.0版本）

cat backup_inno80.sh 

/usr/bin/xtrabackup --defaults-file=/data/mysql/conf/my.cnf  --socket=/data/mysql/tmp/mysql.sock -uroot -p'123456' --backup --no-server-version-check --target-dir=/data/backup/fullbak --stream=xbstream 2>/cloud_backup/log/backup_$(date +%F_%H-%M-%S.log) | gzip > /cloud_backup/fullbak/$(date +%F_%H-%M-%S).tar.gz

定时备份

crontab -e
#每天23点59分执行备份
59 23 * * * sh /data/backup/scripts/backup_inno80.sh

避免生成的备份文件与备份日志过多，因此制定清理脚本

cat /data/backup/scripts/clean_backup80.sh

find /data/backup/fullbak -type f  -mtime +3 -exec rm {} \;
find /data/backup/log/backup_20* -type f  -mtime +7 -exec rm {} \;

定时清理

crontab -e 
#每天凌晨3点59分清理
59 3 * * * sh /cloud_backup/scripts/clean_backup80.sh

2.3.8 恢复全库（MySQL8.0版本）

恢复命令：

1. 恢复重做日志：

    xtrabackup --defaults-file=数据库配置文件路径 --user=用户名 --password=密码 --prepare --use-memory=4G --target-dir=已备份文件的路径
    
    --prepare：这个选项用于准备备份。当你使用 xtrabackup 创建了一个备份之后，你需要运行 xtrabackup 		--prepare 来完成备份文件的准备，以便它们可以被恢复。
   
    --use-memory=4G：这个选项允许你指定 xtrabackup 在准备阶段使用的内存量。在这里，它被设置为 4GB。增加内存使用可能会加速准备过程，特别是对于大型数据库。
   

2. 恢复数据：

   xtrabackup --defaults-file=数据库配置文件路径 --user=用户名 --password=密码 --copy-back --target-dir=已备份文件的路径
   

示例：

1. 恢复重做日志：

xtrabackup --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf --user=root--password=123456 --prepare --target-dir=/data/backup/fullbak/20240306/2024-03-06_10-03-30

2. 恢复数据：

xtrabackup --defaults-file=/data/mysql8.0.35/3307/conf/my.cnf --user=next --password=Kingdee@2020 --copy-back --target-dir=/data/backup/fullbak/20240306/2024-03-06_10-03-30

三、binlog备份

MySQL的binlog（二进制日志）是记录数据库数据操作行为的事务安全型的日志。它主要用于复制和数据恢复。
备份binlog需要确认MySQL是否开启。
验证binlog是否已启用：登录到MySQL服务器，并运行以下命令来查看二进制日志是否已启用：

SHOW VARIABLES LIKE 'log_bin';

如果返回的结果中Value是ON，那么binlog就已经启用了。

查看binlog列表和状态：使用以下命令来查看binlog文件列表和状态：
显示所有可用的binlog文件和当前活动的binlog文件及其位置。

SHOW MASTER LOGS;  
SHOW MASTER STATUS;

备份binlog文件：由于binlog文件会随着时间的推移而增长，并且可能会占用大量磁盘空间，因此定期备份和清理这些文件。

注意：binlog备份通常用于增量备份或复制，而不是全量备份。增量备份只备份自上次备份以来发生更改的数据。因此，在恢复数据时，需要从最近的全量备份开始，然后依次应用所有相关的binlog文件，以确保数据的完整性。

3.1 binlog备份脚本

mkdir -p /data/backup/binlog
cat /data/backup/scripts/rsync_binlog.sh

rsync -avz /data/mysql8.0.35/3307/binlog/mysql-bin* /data/backup/binlog/

3.2 定时备份binlog

crontab -e

#每小时执行备份
0 */1 * * * sh /cloud_backup/scripts/rsync_binlog.sh

3.3 定时清理binlog备份日志

cat /data/backup/scripts/clean_backup_binlog.sh

find /data/backup/binlog -type f  -mtime +4 -exec rm {} \;

crontab -e 

#每天3点59分执行
59 3 * * * sh /data/backup/scripts/clean_backup_binlog.sh

四、备份注意事项

1. 在备份和恢复的时候一定一定要预留足够大的磁盘空间。

2. 如果磁盘空间不足，可能会导致数据库文件损坏。

3. 至少在数据库本机外的其它机器存有最新的备份。