MySQL-备份恢复

1、逻辑备份恢复

1.1、mysqldump 工具

常用选项参数

-A，--all-databases 备份所有数据库
-B, --databases 备份指定的数据库
-R, --routines 备份存储过程及函数
--triggers 备份触发器
-E, --events 备份事件
-F, --flush-logs 备份开始时，刷新binlog日志文件
--master-data 记录二进制日志文件和位置点信息。如果设置 1，打印 change master 命令信息；如果设置 2，会以注释方式打印change master 命令信息。在启用该选项时，会自动锁表 FTWRL，需要配合 --single-transaction 选项使用，只对非innodb表进行锁表备份，innodb表进行快照备份。
--single-transaction innodb开启快照备份。
--set-gtid-purged 打印 "SET @@GLOBAL.GTID_PURGED" 信息，可选值为 on、off、auto。

导出包括系统数据库在内的所有数据库

[mysql@localhost ~]$ mysqldump -S mysql.sock --all-databases > ./backup/all.sql

导出指定数据库的所有数据

[mysql@localhost ~]$ mysqldump -S mysql.sock --databases world > ./backup/world.sql

导出某个数据库中指定的表数据

[mysql@localhost ~]$ mysqldump -S mysql.sock --databases world --tables city country > ./backup/table.sql

逻辑备份导出的数据是以SQL语句形式存储的，在恢复时，导入备份文件即可。

[mysql@localhost ~]$ mysql -S mysql.sock < ./backup/world.sql

1.2 mydumper 工具

mydumper 是一款社区开源的逻辑备份工具。主要特性包括：

• 支持多线程导出数据，速度更快；

• 支持一致性备份；

• 支持将导出文件压缩，节约空间；

• 支持多线程恢复；

• 数据和建表语句分离等。

安装mydumper工具

官网地址：https://launchpad.net/mydumper

GitHub 地址：https://github.com/maxbube/mydumper

[root@localhost ~]# release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)
[root@localhost ~]# yum install https://github.com/mydumper/mydumper/releases/download/${release}/mydumper-${release:1}.el7.x86_64.rpm

mydumper 选项

-B, --database                # 指定导出的数据库
-o, --outputdir               # 要将文件输出到的目录
-d, --no-data                 # 不导出表数据
-D, --daemon                  # 启用守护程序模式
-L, --logfile                 # 要使用的日志文件名，默认情况下使用标准输出
--disk-limits                 # 如果确定没有足够的磁盘空间，则设置暂停和恢复的限制。接受以下值：'<resume>:<pause>'，以MB为单位。例如：100:500 当只有100MB可用时将暂停，如果有500MB可用时将恢复
-t, --threads                 # 要使用的线程数，默认为4
-C, --compress-protocol       # 对MySQL连接使用压缩
-V, --version                 # 显示程序版本并退出
-v, --verbose                 # 输出的详细性，0=无提示，1=错误，2=警告，3=信息，默认值2
--defaults-file               # 使用特定的默认文件
--ssl                         # 使用SSL连接
--ssl-mode                    # 与服务器连接的所需安全状态：DISABLED, PREFERRED, REQUIRED, VERIFY_CA, VERIFY_IDENTITY
--key                         # 密钥文件的路径名
--cert                        # 证书文件的路径名
--ca                          # 证书颁发机构文件的路径名
--capath                      # 包含PEM格式的受信任SSL CA证书的目录的路径名
--cipher                      # 用于SSL加密的允许密码列表
--tls-version                 # 服务器允许加密连接使用哪些协议
--stream                      # 一旦文件被写入，它将通过标准输出流
--no-delete                   # 流完成后，它不会删除文件
-O, --omit-from-file          # 包含数据库列表的文件。要跳过的表条目，每行一个（在应用regex选项之前跳过）
-T, --tables-list             # 要备份的表，以逗号隔开（不排除regex选项）
-h, --host                    # 连接的主机地址
-u, --user                    # 用户名
-p, --password                # 密码
-a, --ask-password            # 提示输入用户密码
-P, --port                    # 端口
-S, --socket                  # 用于连接的UNIX域套接字文件
-x, --regex                   # 正则表达式
-G, --triggers                # 导出触发器。默认情况下，不会转储触发器
--split-partitions            # 将分区转储到单独的文件中。此选项覆盖分区表的--rows选项。
--max-rows                    # 在估计表之后，限制每个块的行数，默认为1000000
--no-check-generated-fields   # 与生成的字段相关的查询将不会被执行。如果生成了列，则会导致恢复问题
--order-by-primary            # 如果不存在主键，则按主键或唯一键对数据排序
-E, --events                  # 转储事件。缺省情况下，不转储事件
-R, --routines                # 转储存储过程和函数。默认情况下，它不转储存储过程或函数
-W, --no-views                # 不转储视图
-M, --checksum-all            # 转储所有元素的校验和
--data-checksums              # 用数据转储表校验和
--schema-checksums            # 转储模式表和视图创建校验和
--routine-checksums           # 转储触发器、函数和例程校验和
--tz-utc                      # 在转储的顶部设置TIME_ZONE='+00:00'，以允许在服务器具有不同时区的数据或数据在不同时区的服务器之间移动时转储时间戳数据，默认情况下使用——skip-tz-utc禁用。
--complete-insert             # 使用包含列名的完整INSERT语句
-z, --tidb-snapshot           # 用于TiDB的快照
-N, --insert-ignore           # 使用INSERT IGNORE转储行
--replace                     # 使用REPLACE转储行
--exit-if-broken-table-found  # 如果遇到损坏的表则退出
--success-on-1146             # 如果表不存在，则不增加错误计数和警告而不是关键
--use-savepoints              # 使用保存点减少元数据锁定问题，需要SUPER特权
-s, --statement-size          # 尝试INSERT语句的大小(以字节为单位)，默认为1000000
-F, --chunk-filesize          # 将表拆分为输出文件大小的块。该值的单位是MB
-e, --build-empty-files       # 即使表中没有可用的数据，也要构建转储文件
--where                       # 只转储选定的记录
-i, --ignore-engines          # 逗号分隔的要忽略的存储引擎列表
--csv                         # 自动启用--load-data并设置变量以CSV格式导出。
-r, --rows                    # 尝试将表分成这样多行的块。这个选项关闭--chunk-filesize
-c, --compress                # 压缩输出文件
--exec                        # 命令使用文件作为参数执行
--long-query-retries          # 重试检查长查询，默认为0(不重试)
--long-query-retry-interval   # 重新尝试长查询检查之前的等待时间(以秒为单位)，默认为60
-l, --long-query-guard        # 以秒为单位设置长查询定时器，默认为60秒
--tidb-snapshot               # 用于TiDB的快照
-U, --updated-since           # 使用Update_time只转储最近U天更新的表
-k, --no-locks                # 不执行临时共享读锁。警告:这将导致不一致的备份
-Y, --all-tablespaces         # 转储所有表空间。
--no-backup-locks             # 不使用Percona备份锁
--lock-all-tables             #  使用LOCK TABLE代替FTWRL
--less-locking                # 最小化InnoDB表的锁时间。
-m, --no-schemas              # 不备份表数据
-K, --kill-long-queries       # 终止长时间运行的查询(而不是中止)
--set-names                   # 设置名称，使用风险自负，默认二进制

备份所有数据库

[mysql@localhost ~]$ mydumper -S mysql.sock -o ./backup/

备份所有数据库，包括触发器、事件、存储过程及函数

[mysql@localhost ~]$ mydumper -S mysql.sock -G -R -E -o ./backup/

备份指定数据库

[mysql@localhost ~]$ mydumper -S mysql.sock -G -R -E -B world -o ./backup/

备份指定表

[mysql@localhost ~]$ mydumper -S mysql.sock -G -R -E -T world.city -o ./backup/

备份指定库的所有表结构

[mysql@localhost ~]$ mydumper -S mysql.sock -G -R -E -B world -d -o ./backup/

备份指定库的所有表数据

[mysql@localhost ~]$ mydumper -S mysql.sock -G -R -E -m -B world -o ./backup/

myloader 恢复

选项参数

-d, --directory                              # 要导入的转储文件的目录
-q, --queries-per-transaction                # 每个事务的查询数，默认为1000
-o, --overwrite-tables                       # 如果存在表则删除
--append-if-not-exist                        # 添加IF NOT EXISTS到建表语句中
-B, --database                               # 还原到的数据库（目标库）
-s, --source-db                              # 被还原的数据库（源数据库），-s db1 -B db2，表示源库中的db1数据库，导入到db2数据库中。
-e, --enable-binlog                          # 启用还原数据的二进制日志记录
--innodb-optimize-keys                       # 创建没有索引的表，并在最后添加索引
--set-names                                  # 设置名称，使用风险自负，默认二进制
-L, --logfile                                # 要使用的日志文件名称，默认使用标准输出
--purge-mode                                 # 指定截断模式，可以是：NONE, DROP, TRUNCATE and DELETE
--disable-redo-log                           # 禁用REDO_LOG，然后启用它，不检查初始状态
-r, --rows                                   # 将INSERT语句拆分为这么多行。
--max-threads-per-table                      # 每个表使用的最大线程数，默认为4
--skip-triggers                              # 跳过触发器。默认情况下，它导入触发器
--skip-post                                  # 跳过事件、存储过程和函数。默认情况下，它导入事件、存储过程或函数
--no-data	                                   # 不导入表数据
--serialized-table-creation                  # 重新创建表将以每次一个线程的方式执行
--resume                                     # 期望在备份目录中找到恢复文件，并只处理那些文件
--pmm-path                                   # 默认值将是/usr/local/percona/pmm2/collectors/textfile-collector/high-resolution
-t, --threads                                # 要使用的线程数，默认为4
-C, --compress-protocol                      # 在MySQL连接上使用压缩
-V, --version                                # 显示程序版本并退出
-v, --verbose                                # 输出的详细性，0=无提示，1=错误，2=警告，3=信息，默认值2
--no-delete                                  # 不会在流完成后删除文件
-O, --omit-from-file                         # 包含数据库列表的文件。要跳过的表项，每行一个(在应用regex选项之前跳过)
-T, --tables-list                            # 要备份的表，以逗号隔开（不排除regex选项）
-h, --host                                   # 连接的主机地址
-u, --user                                   # 用户名
-p, --password                               # 密码
-a, --ask-password                           # 提示输入用户密码
-P, --port                                   # 端口
-S, --socket                                 # 用于连接的UNIX域套接字文件
-x, --regex                                  # 正则表达式
--skip-definer                               # 从CREATE语句中移除DEFINER。缺省情况下，不修改语句

2、数据导入导出

设置 secure_file_priv 选项开启数据导入导出

指定导出导入目录
secure_file_priv=/tmp

不限目录
secure_file_priv=

禁止导入导出
secure_file_priv=NULL

查看该选项值

mysql> show variables like 'secure_file_priv';

导出表数据到文本文件

mysql> select * from test3 into outfile '/tmp/test2.txt';

导入文本数据到表中

mysql> load data infile '/tmp/test2.txt' into table test3;

将本地文本数据导入到远程数据库中，需将 local_infile 选项设置ON

指定列分隔符导出表数据到文本数据文件，默认是\t

mysql> select * from test3 into outfile '/tmp/test3.txt' fields terminated by ',';

指定列分隔符导入文本数据

mysql> load data infile '/tmp/test3.txt' into table test3 fields terminated by ',';

指定字段引用符

mysql> select * from test3 into outfile '/tmp/test3.txt' fields enclosed by '"';
# 使用 optionally 选项，引用符只用在char、varchar和text等字符型字段上，数值类型会忽略使用引用符
mysql> select * from test3 into outfile '/tmp/test3.txt' fields optionally enclosed by '"';

行前缀字符串，

mysql> select * from test3 into outfile '/tmp/test3.txt' lines starting by 'xxx';

ignore number lines 导入时，忽略前几行数据

mysql> load data infile "/tmp/test3.txt" into table test4 ignore 1 lines;

使用 mysqldump 工具导出表数据，-T 为备份的表生成文本文件。

[mysql@localhost tmp]$ mysqldump -uroot -proot -h127.0.0.1 --fields-terminated-by ',' --fields-enclosed-by '"' world -T /tmp

使用 mysqlimport 工具导入文本文件数据。

# --replace 多表导入 --use-threads 指定多个线程
[mysql@localhost tmp]$ mysqlimport -uroot -proot -h127.0.0.1 --replace --use-threads=8  world /tmp/*.txt 

3、物理备份

3.1 percona xtrabackup

• 对于 8.0.20 版本，需要使用 PXB 8.0.12+ 以上版本。

• PXB 8.0 只能备份 MySQL 8.0，不能备份低版本

• 低版本 MySQL 使用 PXB2.4 版本

安装PXB工具

[root@localhost ~]# yum install percona-xtrabackup-80-8.0.13-1.el7.x86_64.rpm

全量备份

[mysql@localhost ~]$ xtrabackup --defaults-file=/opt/mysql/.my.cnf --host=127.0.0.1 --user=root --password=root --port=3306 --backup --target-dir=./backup/full/

全量备份恢复

# prepare 阶段，将备份中的数据未提交的事务进行回滚，保持数据一致性
[mysql@localhost ~]$ xtrabackup --prepare --target-dir=./backup/full/
# 数据恢复，要求 datadir 路径为空 
[mysql@localhost ~]$ xtrabackup --copy-back --target-dir=./backup/full/

增量备份

# 全量备份
[mysql@localhost ~]$ xtrabackup -uroot -proot -S/opt/mysql/mysql.sock --backup --target-dir=./bak/full
# 第一次增量备份，在全量备份的基础上
[mysql@localhost ~]$ xtrabackup -uroot -proot -S/opt/mysql/mysql.sock --backup --target-dir=./bak/inc1 --incremental-basedir=./bak/full/
# 第二次增量备份，在上次增量备份的基础上
[mysql@localhost ~]$ xtrabackup -uroot -proot -S/opt/mysql/mysql.sock --backup --target-dir=./bak/inc2 --incremental-basedir=./bak/inc1/

增量备份恢复，--apply-log-only 未提交的事务不发生回滚

[mysql@localhost ~]$ xtrabackup --prepare --apply-log-only --target-dir=./bak/full/
# 将第一次增量备份加载到全备中
[mysql@localhost ~]$ xtrabackup --prepare --apply-log-only --target-dir=./bak/full/ --incremental-dir=./bak/inc1/
# 将第二次增量备份加载到全备中
[mysql@localhost ~]$ xtrabackup --prepare --target-dir=./bak/full/ --incremental-dir=./bak/inc2
# 数据恢复
[mysql@localhost ~]$ xtrabackup --copy-back --target-dir=./bak/full/ --datadir=/opt/mysql/data

3.2 clone plugin

Clone Plugin是MySQL 8.0.17引入的一个重大特性

加载clone plugin

mysql> install plugin clone soname 'mysql_clone.so';

mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like 'clone';
+-------------+---------------+
| plugin_name | plugin_status |
+-------------+---------------+
| clone       | ACTIVE        |
+-------------+---------------+

my.cnf 配置文件写法

plugin-load-add=mysql_clone.so
clone=force_plus_permanent

创建用户

create user clone@'%' identified with mysql_native_password by 'clone';
grant backup_admin on *.* to clone@'%';

本地克隆

mysql> clone local data directory='/opt/mysq/test/clonedir';

查看最近一次clone情况

mysql> select stage,state,end_time from performance_schema.clone_progress;

日志文件观察clone 情况，设置以下变量，即可记录到日志文件中

mysql> set global log_error_verbosity=3;

远程克隆

要求被克隆端和克隆端都加载clone plugin，被克隆端提供一个具有backup_admin 权限的用户，克隆端使用一个具有clone_admin 权限的用户登录数据库，执行远程克隆数据。

克隆操作

# 指定被克隆端的IP地址和端口信息
mysql> SET GLOBAL clone_valid_donor_list='192.168.3.105:3306';
# 开始克隆，默认克隆的数据保存到 datadir 路径中
mysql> clone instance from clone@'192.168.3.105':3306 identified by 'clone';
# 指定数据保存路径
mysql> clone instance from clone@'192.168.3.105':3306 identified by 'clone' data directory '/opt/mysql/clonedir'

查看克隆的状态

mysql> select * from performance_schema.clone_status;
mysql> select * from performance_schema.clone_progress;