likes
comments
collection
share

MySQL 系列之数据库备份与恢复方案（二）

作者站长头像
站长
· 阅读数 5

如何制定备份策略

关于备份策略的制定，我们需要考虑以下几点因素：

  • 备份方案：物理备份还是逻辑备份，这个可以由数据量来决定，比如：小于 100G 使用逻辑备份，大于 100G 使用物理备份。

  • 备份文件保留天数：按需保留 7天、30天、1年等。

  • 备份执行时间：应避免业务高峰期，比如 TP 库一般可以将备份操作放在凌晨，而 AP 库或许需要再往后延迟。

  • 备份间隔时间：视业务重要程度而定。

  • 备份节点：如果是主从架构的话，推荐在从库上进行备份，减小对主库的访问压力。

如何选择备份工具

在 MySQL 数据库环境中，mysqldump 是最常用的逻辑备份工具，受到广泛的欢迎和应用。然而，对于大规模数据量的备份，mydumper 往往更能满足我们的需求，它提供了更强大的高并发备份和恢复能力。当然，我们也不能忽视物理备份工具 Xtrabackup，其备份速度快、影响小等优点让它在特定场景下更具优势。

差异项指标物理备份逻辑备份
备份内容主要包括数据目录、文件、日志以及配置文件等主要由数据库的结构（如创建库和表的语句）和数据（写入数据的 SQL 语句）构成
备份耗时相对较快（主要过程为文件复制）一般较慢（尤其当数据库大时，需要将数据转换为逻辑格式）
备份工具Xtrabackup 和 Clone Plugin 等mysqldump（单线程）和 mydumper（多线程）
可移植性物理备份可能对底层硬件和操作系统有一定依赖，可移植性较弱逻辑备份具有较好的可移植性，不受底层硬件和操作系统的限制，逻辑文件更易于迁移
备份时机无论 MySQL 是否在运行，均可执行备份操作仅在 MySQL 运行时进行备份，并且应确保备份过程中前端暂停写操作

总之，根据数据规模、业务要求以及具体场景的不同，我们会选择最适合的工具进行数据库备份，确保数据的安全和完整性。

备份方案测试调研

Mysqldump 工具

MySQL 系列之数据库备份与恢复方案（二）

工具特点

  1. 适用性广：mysqldump 能够备份 MySQL 的任何版本，包括最新版本的数据。
  2. 灵活性：可以选择备份整个数据库，或者只备份某个库、某个表，甚至可以选择只备份数据、只备份结构。
  3. 方便性：mysqldump 是一个命令行工具，很容易在 shell 脚本中定期自动运行，也便于数据库的迁移。
  4. 完整性：它将生成一个包含 SQL 语句的文本文件，这个文件包含了恢复整个数据库所需的所有信息，如创建表的语句、插入数据的语句等。
  5. 适合小型和中型数据库：因为 mysqldump 需要锁表，所以在大型数据库上备份可能会影响生产环境，然后它适合小型和中型的数据库备份。
  6. 兼容性：mysqldump 生成的输出是标准的 SQL 语句，因此可以用于在不同的 SQL 数据库间迁移数据。
  7. 支持事务：如果你在使用支持事务的存储引擎（例如 InnoDB），mysqldump 会利用这个特性以确保备份过程的一致性。
  8. 不支持并行备份：mysqldump 是一个单线程工具，不实现并行备份，因此备份大型数据库可能相对较慢。

环境准备

备份端

在 db01 的数据库中，创建 'u_bak' 的用户，并赋予以下权限：

-- 创建一个专门用于数据库备份的'u_bak'用户
create user 'u_bak'@'%' identified by 'bak@12345';

-- 为'u_bak'用户授予一系列权限
grant select, trigger, show view, lock tables, process, reload, replication client, replication_slave_admin on *.* to 'u_bak'@'%';
权限描述
select允许用户读取数据，即运行 select 查询。
trigger允许用户创建、更新和删除触发器。
show view允许用户运行 show create view 命令。
lock tables允许用户锁定表，这对于备份操作是需要的，以确保数据在备份过程中的一致性。
process允许用户查看系统进程的信息。
reload允许用户重新加载服务器权限或刷新日志等。
replication client允许用户查询主服务器或从服务器的位置。
replication_slave_admin这是在 MySQL 8.0 及以上版本新增的权限，允许用户控制复制从服务器的操作。

创建测试库及数据：

-- 创建名为'bak_db'的测试备份数据库
create database if not exists bak_db;
use bak_db;

-- 创建名为'tt'的测试表
create table if not exists tt (
  id int not null auto_increment primary key,
  name varchar(20) default null,
  age int default null
) engine=InnoDB charset=utf8mb4;

-- 插入多条数据
insert into tt (name, age)
values ('Tom', 23),
       ('Jerry', 21),
       ('Alice', 17),
       ('Bob', 32),
       ('Charlie', 27),
       ('Daniel', 29),
       ('Eva', 16),
       ('Frank', 37),
       ('Grace', 25),
       ('Harry', 30);
恢复端

在 db02 的数据库中，创建 'u_rec' 的用户，并赋予以下权限：

-- 创建一个专门用于数据库恢复的'u_rec'用户
create user 'u_rec'@'%' identified by 'rec@12345';

-- 为'u_rec'用户授予一系列权限
grant select, insert, delete, create, drop, references, alter, lock tables, create view, trigger, system_variables_admin, set_user_id, system_user on *.* to 'u_rec'@'%';
权限描述
select允许用户从表中读取数据，也就是执行查询操作。
insert允许用户将数据插入表中。
delete允许从数据库表中删除数据。
create允许用户创建新的数据库或者表。
drop允许用户删除数据库或者表。
references允许创建外键，确保两张表之间的数据完整性。
alter允许用户修改现有的数据库或表，比如添加或删除字段。
lock tables允许用户对数据表进行锁定，这是在执行某些需要独占访问权限的操作时很重要的。
create view允许创建新的视图，视图是从一张或多张表中导出的虚拟表。
trigger允许创建或删除触发器，触发器是在特定事件（如插入、更新或删除数据）发生时自动执行的 SQL 代码块。
system_variables_admin允许用户更改系统变量的值。这是非常高级的权限，通常只有系统管理员才会有。
set_user_id允许更改线程的已认证用户和账户信息。通常，你需要 super 权限来更改线程的所有者，除非你有 set_user_id 权限。
system_user这是给予能够执行特殊系统操作的用户的权限，比如管理任务、恢复数据库等。
-- 创建名为'rec_db'的测试恢复数据库
create database if not exists rec_db;

备份恢复

备份端
# 备份到文件
root@db-backup:~# mysqldump -u'u_bak' -p'bak@12345' bak_db > bak_db.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

# 从文件中恢复
root@db-backup:~# mysql -u'u_rec' -p'rec@12345' -h db02 -P 3306 rec_db < bak_db.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
恢复端
# 操作前
mysql> show tables;
Empty set (0.00 sec)

# 操作后
mysql> show tables;
+------------------+
| Tables_in_rec_db |
+------------------+
| tt               |
+------------------+
1 row in set (0.00 sec)

mysql> select * from tt;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | Tom     |   23 |
|  2 | Jerry   |   21 |
|  3 | Alice   |   17 |
|  4 | Bob     |   32 |
|  5 | Charlie |   27 |
|  6 | Daniel  |   29 |
|  7 | Eva     |   16 |
|  8 | Frank   |   37 |
|  9 | Grace   |   25 |
| 10 | Harry   |   30 |
+----+---------+------+
10 rows in set (0.01 sec)

工作原理

备份端
# 开启 MySQL 的全局日志
mysql> set global general_log = 'on';
Query OK, 0 rows affected (0.00 sec)

# 获取全局日志的状态和文件位置
mysql> show global variables like '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /var/lib/mysql/db-server.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

我们重新再来执行一遍 mysqldump 备份命令，看看究竟它都做了些什么操作？（使用 tmux 分屏查看 general_log 日志文件流，等待实时 sql 刷新）

MySQL 系列之数据库备份与恢复方案（二）

ASCII 图示：

+--------------+
|   获取元数据   |
+--------------+
        |
        v
+--------------+
|   查看所有表   |
+--------------+
        |
        v
+--------------+ # 读锁（Shared Locks）：读锁是一种允许并发读取操作但不允许写入（更新）操作的锁。
|   加上读锁    | # 也就是说，当一个线程获得了数据的读锁后，其他的线程可以并发地获取同一份数据的读锁，
+--------------+ # 进行读取操作；但如果有线程想要对这份数据进行写操作，则必须等待读锁被释放后才能进行。
        |
        v
+--------------+
|  查看表结构    |
+--------------+
        |
        v
+--------------+
|  查看全表数据  |
+--------------+
        |
        v
+--------------+
|   查看触发器   |
+--------------+
        |
        v
+--------------+
|    解除读锁   |
+--------------+
恢复端
# 开启 MySQL 的全局日志
mysql> set global general_log = 'on';
Query OK, 0 rows affected (0.00 sec)

# 获取全局日志的状态和文件位置
mysql> show global variables like '%general%';
+------------------+------------------------------+
| Variable_name    | Value                        |
+------------------+------------------------------+
| general_log      | ON                           |
| general_log_file | /var/lib/mysql/db-server.log |
+------------------+------------------------------+
2 rows in set (0.00 sec)

同样，我们来看看在逻辑备份恢复的过程中会执行哪些操作呢？

MySQL 系列之数据库备份与恢复方案（二）

ASCII 图示：

+--------------+
| 删除表（若存在）|
+--------------+
       |
       v
+--------------+
|     创建表    |
+--------------+
       |
       v
+--------------+ # 写锁（Exclusive Locks）：写锁是一种只允许一个线程对数据进行读取或写入操作的锁。
|    加上写锁   | # 当一个线程获得了数据的写锁后，其他的线程无法进行任何读取或写入操作，直到写锁被释放。
+--------------+
       |
       v
+--------------+ # 在导入大量数据时，关闭表索引可以大幅度提高导入速度。
|   关闭表索引   | # 原因是：当数据库在插入数据时，索引也会同时更新。如果在插入大量数据的过程中索引处于开启状态，那么每插入一条数据，数据库就需要更新一次索引。这不但会消耗大量的额外资源，也会严重影响数据导入的速度。
+--------------+ # 而我们预先关闭索引，插入完所有数据后再开启索引，数据库只需要进行一次索引重建操作，对比而言效率更高，时间更短。
       |
       v
+--------------+
|  写入备份数据  |
+--------------+
       |
       v
+--------------+
|   打开表索引   |
+--------------+
       |
       v
+--------------+
|    解除写锁    |
+--------------+

更多用法

以下列举一些常用选项，详细可使用 mysqldump --help 查看：

# 远程备份
mysqldump -u'<username>' -p'<password>' -h <host> -P <port> <dbname> > /path/to/output.sql

# 备份不增加GTID信息
mysqldump -u'<username>' -p'<password>' --set-gtid-purged=off <dbname> > /path/to/output.sql

# 备份指定数据库（不包含create database语句，恢复时需要指定一个库名）
mysqldump -u'<username>' -p'<password>' <dbname> > /path/to/output.sql

# 备份指定多个数据库（包含create database语句，恢复时不需要指定库）
mysqldump -u'<username>' -p'<password>' -B <db1> <db2>... > /path/to/output.sql

# 备份所有数据库（包含create database语句，恢复时不需要指定库，由于全备，将系统表也会重建，所以，授权的用户需要手动重新创建并赋权）
mysqldump -u'<username>' -p'<password>' -A > /path/to/output.sql

# 备份指定库中的单张表
mysqldump -u'<username>' -p'<password>' <dbname> <table_name> > /path/to/output.sql

# 备份指定库中的多张表
mysqldump -u'<username>' -p'<password>' <dbname> <t1> <t2> > /path/to/output.sql

# 备份增加删库语句（需要与 -A 或 -B 参数搭配使用）
--add-drop-database

# 备份不加建库语句（需要与 -A 或 -B 参数搭配使用）
-n, --no-create-db

# 备份跳过删表语句
--skip-add-drop-table

# 备份insert语句包含所有的字段名
-c, --complete-insert

# 只备份表结构
-d, --no-data

# 只备份数据
-t, --no-create-info

# 用replace语句代替insert语句
--replace

# 备份存储过程和自定义函数
-R, --routines

# 转储事件（默认不会转储事件）
-E, --events

# 备份所有表空间
-Y, --all-tablespaces

# 通过 --opt 选项优化备份
--opt    Same as --add-drop-table, --add-locks, --create-options,
         --quick, --extended-insert, --lock-tables, --set-charset,
         and --disable-keys. Enabled by default, disable with
         --skip-opt.

Mydumper 工具

MySQL 系列之数据库备份与恢复方案（二）

工具特点

  1. 并行和高效：MyDumper 通过使用多线程技术实现并行处理，同时避免了昂贵的字符集转换过程，以提升性能。
  2. 便于管理的输出：MyDumper 将不同的信息输出到不同的文件，如表数据，转储元数据，使得数据的查看和解析更为方便。
  3. 强一致性：无论线程数量，MyDumper 保持整体快照的一致性，并可以准确提供主从日志的位置。
  4. 高度可管理：MyDumper 支持 Perl 兼容的正则表达式 （PCRE），可以方便的指定需要备份或者排除的数据库和表。

下载安装

# need to install the dependencies
apt-get install libatomic1 zstd

# download and install the package
release=$(curl -Ls -o /dev/null -w %{url_effective} https://github.com/mydumper/mydumper/releases/latest | cut -d'/' -f8)
wget https://github.com/mydumper/mydumper/releases/download/${release}/mydumper_${release:1}.$(lsb_release -cs)_amd64.deb
dpkg -i mydumper_${release:1}.$(lsb_release -cs)_amd64.deb

# check usage
mydumper --help

备份恢复

备份端
  1. 在 db01 的数据库中，创建 'u_dumper' 的用户，并赋予以下权限：
-- 创建一个专门用于数据库备份的'u_dumper'本地用户
create user 'u_dumper'@'localhost' identified by 'dumper@12345';

-- 为'u_dumper'用户授予一系列权限
grant select, create, reload, process, lock tables, replication client, show view, trigger, backup_admin, replication_slave_admin on *.* to 'u_dumper'@'localhost';
权限描述
select允许用户读取数据，即运行 select 查询。
create允许用户在数据库中创建新的表或数据库。
reload允许用户重新加载服务器权限或刷新日志等。
process允许用户查看系统进程的信息。
lock tables允许用户锁定表，这对于备份操作是需要的，以确保数据在备份过程中的一致性。
replication client允许用户查询主服务器或从服务器的位置。
show view允许用户运行 show create view 命令。
event允许用户对事件进行创建、更改、删除、查看等操作。
trigger允许用户创建、更新和删除触发器。
backup_admin该权限在 MySQL 8.0 及以上版本中可用。它允许用户执行 backup database 和 restore database 命令，从而进行在线备份和还原操作。
replication_slave_admin这是在 MySQL 8.0 及以上版本新增的权限，允许用户控制复制从服务器的操作。
  1. 创建测试库及数据：
-- 创建名为'bak_db'的测试备份数据库
create database if not exists bak_db;
use bak_db;

-- 创建名为'tt'的测试表
create table if not exists tt (
  id int not null auto_increment primary key,
  name varchar(20) default null,
  age int default null
) engine=InnoDB charset=utf8mb4;

-- 插入多条数据
insert into tt (name, age)
values ('Tom', 23),
       ('Jerry', 21),
       ('Alice', 17),
       ('Bob', 32),
       ('Charlie', 27),
       ('Daniel', 29),
       ('Eva', 16),
       ('Frank', 37),
       ('Grace', 25),
       ('Harry', 30);
  1. 使用 mydumper 进行备份：
# 创建备份根目录
root@db-backup:~# mkdir -p /data/backup

# 我们这个测试环境这是在同一台机器上的客户端和服务器之间进行通信的，
# 因此通可以使用套接字连接到 MySQL 服务器，这样能够减少 TCP/IP 连接所带来的额外开销，
# 并最大程度提高 mydumper 的通信速度。当然对于在网络上的远程机器之间的连接，
# 通常还是使用常规的 -h <host> 的 TCP/IP 协议。
root@db-backup:~# mydumper -u 'u_dumper' -p 'dumper@12345' -S /var/run/mysqld/mysqld.sock -B bak_db -o /data/backup/db

# 查看备份文件
root@db-backup:~# ll /data/backup/db
total 16
-rw-r----- 1 root root 155 Dec 14 15:36 bak_db-schema-create.sql    # 建库语句
-rw-r----- 1 root root   0 Dec 14 15:36 bak_db-schema-triggers.sql  # 触发器（无，大小为空）
-rw-r----- 1 root root 284 Dec 14 15:36 bak_db.tt.00000.sql         # 数据文件（insert语句）
-rw-r----- 1 root root 326 Dec 14 15:36 bak_db.tt-schema.sql        # 建表语句
-rw-r----- 1 root root 262 Dec 14 15:36 metadata                    # 元数据信息（关于备份的信息）
  1. 将备份文件传输到恢复端：
# 压缩为tar.gz格式文件
root@db-backup:~# tar -Pzcf /data/backup/backup-db.tar.gz /data/backup/db

# 将tar.gz发送到恢复端服务器
root@db-backup:~# scp /data/backup/backup-db.tar.gz root@db-recovery:/tmp
root@db-recovery's password:
backup-db.tar.gz                                                                          100%  873   944.9KB/s   00:00

# 查看恢复端服务器的文件
root@db-backup:~# ssh root@db-recovery "ls -lh /tmp/backup-db.tar.gz"
root@db-recovery's password:
-rw-r----- 1 root root 873 Dec 14 15:44 /tmp/backup-db.tar.gz
恢复端
  1. 将备份文件进行解压还原：
root@db-recovery:~# tar -Pxf /tmp/backup-db.tar.gz

root@db-recovery:~# ll /data/backup/db/
total 16
-rw-r----- 1 root root 155 Dec 14 15:36 bak_db-schema-create.sql
-rw-r----- 1 root root   0 Dec 14 15:36 bak_db-schema-triggers.sql
-rw-r----- 1 root root 284 Dec 14 15:36 bak_db.tt.00000.sql
-rw-r----- 1 root root 326 Dec 14 15:36 bak_db.tt-schema.sql
-rw-r----- 1 root root 262 Dec 14 15:36 metadata
  1. 在 db02 的数据库中，创建 'u_loader' 的用户，并赋予以下权限：
-- 创建一个专门用于数据库恢复的'u_loader'用户
create user 'u_loader'@'localhost' identified by 'loader@12345';

-- 为'u_loader'用户授予一系列权限
grant select, insert, delete, create, drop, references, alter, super, lock tables, replication client, create view, event, trigger, replication_slave_admin on *.* to 'u_loader'@'localhost';
权限描述
select允许用户读取数据，即运行 select 查询。
insert允许用户在表中插入新的数据行。
delete允许从数据库表中删除数据。
create允许用户在数据库中创建新的表或数据库。
drop允许用户删除数据库或者表。
references允许创建外键，确保两张表之间的数据完整性。
alter允许用户修改现有的数据库或表，比如添加或删除字段。
super这个权限允许用户执行高级管理任务，比如控制复制和设置服务器级变量。
lock tables允许用户锁定表，这对于备份操作是需要的，以确保数据在备份过程中的一致性。
replication client允许用户查询主服务器或从服务器的位置。
create view允许创建新的视图，视图是从一张或多张表中导出的虚拟表。
event允许用户对事件进行创建、更改、删除、查看等操作。
trigger允许用户创建、更新和删除触发器。
replication_slave_admin这是在 MySQL 8.0 及以上版本新增的权限，允许用户控制复制从服务器的操作。
  1. 使用 myloader 进行恢复：
# 使用 -B 选项，后接的数据库名称可以为一个全新的名称
# -B, --database: An alternative database to restore into
root@db-recovery:~# myloader -u 'u_loader' -p 'loader@12345' -S /var/run/mysqld/mysqld.sock -B bak_loader -d /data/backup/db
  1. 检查恢复后的数据：
# 操作后
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| bak_loader         |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use bak_loader;
Database changed

mysql> show tables;
+----------------------+
| Tables_in_bak_loader |
+----------------------+
| tt                   |
+----------------------+
1 row in set (0.00 sec)

mysql> select * from tt;
+----+---------+------+
| id | name    | age  |
+----+---------+------+
|  1 | Tom     |   23 |
|  2 | Jerry   |   21 |
|  3 | Alice   |   17 |
|  4 | Bob     |   32 |
|  5 | Charlie |   27 |
|  6 | Daniel  |   29 |
|  7 | Eva     |   16 |
|  8 | Frank   |   37 |
|  9 | Grace   |   25 |
| 10 | Harry   |   30 |
+----+---------+------+
10 rows in set (0.00 sec)

工作原理

备份端

备份执行原理 （mydumper）：

+----------------------+
| 刷新所有打开的表到磁盘   |
+----------------------+
           |
           v
+----------------------+
| 给所有表加全局读锁      |
+----------------------+
           |
           v
+----------------------+ # RR（Repeatable Read）：在 MySQL 中，RR 是默认的事务隔离级别。
| 创建线程，隔离级别设为RR | # 在 Repeatable Read 隔离级别下，事务一旦开始，就不能看到其他事务对数据的修改。
+----------------------+ # 这确保了一个事务内部的多次读取操作返回的结果是一致的，即使在这个事务执行的过程中，其他事务对数据做了修改也不会影响到这个事务。
           |
           v
+----------------------+
| 获取位点、GTID信息     |
+----------------------+   
           |
           v
+----------------------+
| 获取建库语句           |
+----------------------+
           |
           v
+----------------------+
| 获取表结构             |
+----------------------+
           |
           v
+----------------------+
| 通过select *获取数据   |
+----------------------+
           |
           v
+----------------------+
| 解锁                  |
+----------------------+
           |
           v
+----------------------+
| 退出所有线程           |
+----------------------+
恢复端

恢复执行原理 （myloader）：

+----------------------+
| 创建多个线程           |
+----------------------+
           |
           v
+----------------------+
| 进入到恢复库中创建表     |
+----------------------+
           |
           v
+----------------------+
| 线程开启事务           |
+----------------------+
            |
            v
+----------------------+
| 线程导入不同表数据      |
+----------------------+
           |
           v
+----------------------+
| 提交数据更改           |
+----------------------+
           |
           v
+----------------------+
| 退出所有线程           |
+----------------------+

更多用法

以下列举一些常用选项，详细可使用 mydumper --help 查看：

# 远程备份
mydumper -u <username> -p <password> -h <host> -P <port> -B <database_name> -o /path/to/output

# 备份指定数据库
mydumper -u <username> -p <password> -S <mysql_socket_path> -B <database_name> -o /path/to/output

# 备份单张表
mydumper -u <username> -p <password> -S <mysql_socket_path> -T <database_name.table_name> -o /path/to/output

# 备份多张表
mydumper -u <username> -p <password> -S <mysql_socket_path> -T '<database_name1.table_name1>,<database_name2.table_name2>' -o /path/to/output

# 不加任何参数（表示备份全部）
mydumper -u <username> -p <password> -S <mysql_socket_path> -o /path/to/output

# 正则表达式：排除备份某些库
mydumper -u <username> -p <password> -S <mysql_socket_path> --regex '^(?!(mysql|sys).)' -o /path/to/output

# 正则表达式：仅备份某些库
mydumper -u <username> -p <password> -S <mysql_socket_path> --regex '^(mysql|sys).' -o /path/to/output

# 正则表达式：不备份以xxx开头的库
mydumper -u <username> -p <password> -S <mysql_socket_path> --regex '^(?!test)' -o /path/to/output

# 进行数据压缩备份（默认会使用gzip，也可选zstd）
-c, --compress

# 指定开启线程数（默认会开启4个线程）
# 一个较为常见的做法是将 mydumper 的 -t 参数设置为你的 CPU 核心数，这样可以最大化地利用你的 CPU 资源。
# 然而，最佳的线程数取决于你的具体环境，包括你的硬件配置、工作负载以及数据库的大小等。可以根据实际性能情况调整线程数。
--threads <num_of_threads>

# 转储触发器（默认不会转储触发器）
-G, --triggers

# 转储事件（默认不会转储事件）
-E, --events

# 转储存储过程和函数（默认不会转储存储过程或函数）
-R, --routines

# 备份所有表空间
-Y, --all-tablespaces

# 在数据恢复时，将视图作为普通表恢复
--views-as-tables

# 不备份视图
-W, --no-views

# 只备份表结构
-d, --no-data

最佳实践

测试数据准备

MySQL 数据库层级结构

在 MySQL 中，schema 的概念与 database 实际上是一样的，也就是说，在 MySQL 里，一个 schema 就是一个 database，且在这个层级下才会创建如 table、view 等对象。MySQL 没有像分布式数据库Greenplum 等数据库那样，database 下还有一层 schema，然后才是其他数据库对象。

不同的数据库系统 schema 的概念有所差别，当你从 MySQL 迁移到其它数据库系统（如 PostgreSQL 或 Greenplum 等分布式数据库），或反之，需要留意这些定义上的差异。

需注意

尽管从技术角度上来说，存储过程和函数是服务器级别的对象，但从逻辑、设计的角度来看，它们通常都会“属于”特定的数据库，因为它们的操作和逻辑都是针对某个或某些数据库的。这就需要在备份还原的时候，要确保被依赖的表或者数据库在存储过程或函数被创建出来之前，已经被正确还原了出来。

在实际的运行和管理中，我们需要根据存储过程和函数的实际逻辑和依赖关系来进行操作，包括备份还原。这比我们只从技术层面理解它们 “不是数据库级别的对象” 更有意义。

创建 db_rbac 测试库：

-- 检查是否存在同名，若存在则删除
drop database if exists db_rbac;

-- 创建 RBAC 数据库
create database db_rbac;

-- 切换到目标库
use db_rbac;

-- 用户表
create table t_users (
  user_id int primary key comment '用户ID',
  nickname varchar(50) not null comment '昵称',
  username varchar(50) not null comment '用户名',
  password varchar(50) not null comment '密码',
  is_active bool not null default 1 comment '激活状态',
  remark text comment '备注',
  created_at datetime default current_timestamp comment '创建时间',
  updated_at datetime default current_timestamp on update current_timestamp comment '更新时间',
  key idx_username_password (username, password)
) comment '用户表';

-- 角色表
create table t_roles (
  role_id int primary key comment '角色ID',
  role_name enum('Administrator', 'Editor', 'Viewer') not null comment '角色名称'
) comment '角色表';

-- 权限表
create table t_permissions (
  permission_id int primary key comment '权限ID',
  permission_name enum('Read', 'Write', 'Delete') not null comment '权限名称'
) comment '权限表';

-- 用户角色关联表
create table t_user_roles (
  user_id int comment '用户ID',
  role_id int comment '角色ID',
  primary key (user_id, role_id),
  foreign key (user_id) references t_users(user_id) on delete cascade,
  foreign key (role_id) references t_roles(role_id) on delete cascade
) comment '用户角色关联表';

-- 角色权限关联表
create table t_role_permissions (
  role_id int comment '角色ID',
  permission_id int comment '权限ID',
  primary key (role_id, permission_id),
  foreign key (role_id) references t_roles(role_id) on delete cascade,
  foreign key (permission_id) references t_permissions(permission_id) on delete cascade
) comment '角色权限关联表';

-- 创建存储过程
-- 描述：这个存储过程用来检查用户登录。它接收两个输入参数（用户名和密码），返回一个布尔值（true或false）来表示登录是否成功。
delimiter $$

create procedure sp_check_login(in u_username varchar(50), in u_password varchar(50), out result bool)
begin
  declare matched int;

  select count(*)
    into matched
    from t_users
    where username = u_username and password = u_password;

  if matched > 0 then
    set result = true;
  else
    set result = false;
  end if;
end $$
delimiter ;

-- 创建加密函数
delimiter $$

create function encode_password(raw_password varchar(255))
returns varchar(300) deterministic
begin
  declare salt varchar(255) default 'your_fixed_salt';
  set @temp_password = concat(raw_password, salt);
  set @encrypted_password = to_base64(@temp_password); 
  return @encrypted_password;
end $$
delimiter ;

-- 创建解密函数
delimiter $$

create function decode_password(encrypted_password varchar(300))
returns varchar(255) deterministic 
begin
  declare salt varchar(255) default 'your_fixed_salt';
  set @temp_password = from_base64(encrypted_password);
  set @raw_password = left(@temp_password, char_length(@temp_password) - char_length(salt));
  return @raw_password;
end $$
delimiter ;

-- 创建触发器（创建用户时操作）
delimiter $$

create trigger trg_before_insert_users
before insert on t_users
for each row
begin
  set new.password = encode_password(new.password);
end $$
delimiter ;

-- 创建触发器（更新用户时操作）
delimiter $$

create trigger trg_before_update_users
before update on t_users 
for each row
begin
  if decode_password(old.password) <> new.password then
    set new.password = encode_password(new.password);
  end if;
end $$
delimiter ;

-- 创建视图
create view v_user_details as 
  select u.user_id, u.nickname, u.username, u.password, u.is_active, r.role_name, p.permission_name, u.created_at as ctime, u.updated_at as utime
  from t_users u
  join t_user_roles ur on u.user_id = ur.user_id
  join t_roles r on ur.role_id = r.role_id
  join t_role_permissions rp on r.role_id = rp.role_id
  join t_permissions p on rp.permission_id = p.permission_id
  order by u.nickname;

-- 插入Demo数据
-- insert 10 users into t_users
insert into t_users (user_id, nickname, username, password) values 
  (1, 'alice', 'alice', 'Alice@12345'),
  (2, 'bob', 'bob', 'Bob@12345'),
  (3, 'charlie', 'charlie', 'Charlie@12345'),
  (4, 'david', 'david', 'David@12345'),
  (5, 'eve', 'eve', 'Eve@12345'),
  (6, 'frank', 'frank', 'Frank@12345'),
  (7, 'grace', 'grace', 'Grace@12345'),
  (8, 'heidi', 'heidi', 'Heidi@12345'),
  (9, 'ivan', 'ivan', 'Ivan@12345'),
  (10, 'judy', 'judy', 'Judy@12345');

-- insert 3 roles into t_roles
insert into t_roles (role_id, role_name) values 
  (1, 'administrator'),
  (2, 'editor'),
  (3, 'viewer');

-- insert 3 permissions into t_permissions
insert into t_permissions (permission_id, permission_name) values 
  (1, 'read'),
  (2, 'write'),
  (3, 'delete');

-- create user-role associations in t_user_roles
insert into t_user_roles (user_id, role_id) values 
  (1, 1),   -- alice is an administrator
  (2, 2),   -- bob is an editor
  (3, 3),   -- charlie is a viewer
  (4, 1),   -- david is an administrator
  (5, 2),   -- eve is an editor
  (6, 3),   -- frank is a viewer
  (7, 1),   -- grace is an administrator
  (8, 2),   -- heidi is an editor
  (9, 3),   -- ivan is a viewer
  (10, 2);  -- judy is an editor

-- create role-permission associations in t_role_permissions
insert into t_role_permissions (role_id, permission_id) values 
  (1, 1),   -- administrators can read
  (1, 2),   -- administrators can write
  (1, 3),   -- administrators can delete
  (2, 1),   -- editors can read
  (2, 2),   -- editors can write
  (3, 1);   -- viewers can read

创建 db_cmdb 测试库：

-- 检查是否存在同名，若存在则删除
drop schema if exists db_cmdb;

-- 创建 CMDB 模式库
create schema db_cmdb;

-- 切换到目标模式库
use db_cmdb;

-- 创建 t_server 服务器资产表
create table t_server (
  id int not null auto_increment comment '主键',
  serial_number char(20) not null comment '序列号',
  uuid char(36) not null comment 'UUID',
  manufacturer ENUM('HP', 'DELL', 'Toshiba', 'Apple') not null comment '制造商',
  model_type varchar(20) not null comment '型号类型',
  warranty_date date not null comment '保修日期',
  owner varchar(50) not null comment '所有者',
  primary key (id),
  unique key sn_unique (serial_number),
  unique key uuid_unique (uuid),
  key owner_key (owner)
) engine=innodb default charset=utf8mb4 collate=utf8mb4_general_ci comment='服务器资产表';

-- 创建 v_warranty_server 所有在保的服务器清单视图
create view v_warranty_server as
  select serial_number as sn, manufacturer as mfg, model_type as model, warranty_date as warranty
  from t_server
  where warranty_date > curdate()
  order by warranty_date asc;

-- 创建触发器，在 t_server 表入新行之前执行
delimiter $$

create trigger trg_server
before insert on t_server 
for each row 
begin 
  declare last_id int;
  select max(id) into last_id from t_server;
  if last_id is null then
    set last_id = 10000;
  else
    set last_id = last_id + 1;
  end if;
  
  set new.id = last_id;  -- ID Increment
  set new.uuid = uuid(); -- UUID Generation 
end;
$$
delimiter ;

-- insert 多条数据（10条）
insert into t_server(serial_number, manufacturer, model_type, warranty_date, owner) values
  ('HP1234DE5678', 'HP', 'ProLiant DL380', '2022-11-30', 'John Doe'),
  ('DE7890HP1234', 'DELL', 'PowerEdge R740', '2025-02-25', 'Jane Smith'),
  ('TO1234PO5678', 'Toshiba', 'Tecra A50', '2023-11-20', 'Michael Brown'),
  ('AP7890LE1234', 'Apple', 'Macbook Pro 16', '2026-04-30', 'Linda Davis'),
  ('HP3456AR7890', 'HP', 'EliteBook 840', '2025-05-20', 'Robert Adams'),
  ('TO1234SH5678', 'Toshiba', 'Portege X30', '2023-11-29', 'Emily Clark'),
  ('DE3456LL7890', 'DELL', 'Precision 7530', '2027-03-31', 'Steven Jackson'),
  ('HP1234MS5678', 'HP', 'ZBook 15', '2023-11-30', 'Angela Williams'),
  ('AP4670DC3254', 'Apple', 'iMac 24', '2030-06-01', 'Patricia Johnson'),
  ('DE3456JO7890', 'DELL', 'Inspiron 7000', '2024-07-05', 'Joseph Thompson');

查询测试：

mysql> select
    ->   user_id as uid,
    ->   nickname,
    ->   username,
    ->   decode_password(password) as plaintext_password,
    ->   if(is_active=1, 'True', 'False') as is_active,
    ->   role_name as role,
    ->   permission_name as perm,
    ->   ctime,
    ->   utime
    -> from db_rbac.v_user_details;
+-----+----------+----------+--------------------+-----------+---------------+--------+---------------------+---------------------+
| uid | nickname | username | plaintext_password | is_active | role          | perm   | ctime               | utime               |
+-----+----------+----------+--------------------+-----------+---------------+--------+---------------------+---------------------+
|   1 | alice    | alice    | Alice@12345        | True      | Administrator | Write  | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   1 | alice    | alice    | Alice@12345        | True      | Administrator | Delete | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   1 | alice    | alice    | Alice@12345        | True      | Administrator | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   2 | bob      | bob      | Bob@12345          | True      | Editor        | Write  | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   2 | bob      | bob      | Bob@12345          | True      | Editor        | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   3 | charlie  | charlie  | Charlie@12345      | True      | Viewer        | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   4 | david    | david    | David@12345        | True      | Administrator | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   4 | david    | david    | David@12345        | True      | Administrator | Write  | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   4 | david    | david    | David@12345        | True      | Administrator | Delete | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   5 | eve      | eve      | Eve@12345          | True      | Editor        | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   5 | eve      | eve      | Eve@12345          | True      | Editor        | Write  | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   6 | frank    | frank    | Frank@12345        | True      | Viewer        | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   7 | grace    | grace    | Grace@12345        | True      | Administrator | Delete | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   7 | grace    | grace    | Grace@12345        | True      | Administrator | Write  | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   7 | grace    | grace    | Grace@12345        | True      | Administrator | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   8 | heidi    | heidi    | Heidi@12345        | True      | Editor        | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   8 | heidi    | heidi    | Heidi@12345        | True      | Editor        | Write  | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|   9 | ivan     | ivan     | Ivan@12345         | True      | Viewer        | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|  10 | judy     | judy     | Judy@12345         | True      | Editor        | Read   | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
|  10 | judy     | judy     | Judy@12345         | True      | Editor        | Write  | 2023-12-12 20:09:15 | 2023-12-12 20:09:15 |
+-----+----------+----------+--------------------+-----------+---------------+--------+---------------------+---------------------+
20 rows in set (0.00 sec)

mysql> select *, datediff(warranty, curdate()) as remaining_days from db_cmdb.v_warranty_server;
+--------------+-------+----------------+------------+----------------+
| sn           | mfg   | model          | warranty   | remaining_days |
+--------------+-------+----------------+------------+----------------+
| DE3456JO7890 | DELL  | Inspiron 7000  | 2024-07-05 |            206 |
| DE7890HP1234 | DELL  | PowerEdge R740 | 2025-02-25 |            441 |
| HP3456AR7890 | HP    | EliteBook 840  | 2025-05-20 |            525 |
| AP7890LE1234 | Apple | Macbook Pro 16 | 2026-04-30 |            870 |
| DE3456LL7890 | DELL  | Precision 7530 | 2027-03-31 |           1205 |
| AP4670DC3254 | Apple | iMac 24        | 2030-06-01 |           2363 |
+--------------+-------+----------------+------------+----------------+
6 rows in set (0.00 sec)

mysql> select mfg, count(*) as num_of_servers from db_cmdb.v_warranty_server group by mfg having mfg > 1;
+-------+----------------+
| mfg   | num_of_servers |
+-------+----------------+
| DELL  |              3 |
| Apple |              2 |
+-------+----------------+
2 rows in set (0.00 sec)

数据库备份与恢复

Mysqldump 与 Mydumper

MySQL 系列之数据库备份与恢复方案（二）

Mysqldump 工具

当使用 mysqldump 备份 MySQL 数据库时，以下是一些最佳实践：

# 使用mysqldump命令进行逻辑备份，并将备份导出至dump.sql：
mysqldump -h <host> -P <port> -u'<username>' -p'<password>' \
  --set-gtid-purged=OFF \               # 禁止导出全局事务ID
  --flush-logs \                        # 执行备份前刷新和重置日志文件
  --complete-insert \                   # 在INSERT语句中包含所有列名
  --skip-add-drop-table \               # 跳过生成DROP TABLE语句
  --routines \                          # 包含存储过程和自定义函数
  --add-drop-database \                 # 在数据库前添加DROP DATABASE语句
  --databases <db01> <db02>... \        # 指定要备份的数据库
> dump.sql                              # 输出到dump.sql转储文件

 --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.


# 通过mysql命令将dump.sql备份恢复至remote-server上的MySQL数据库：
mysql -h <host> -P <port> -u'<username>' -p'<password>' < dump.sql

Mydumper 工具

当使用 mydumper 备份 MySQL 数据库时，以下是一些最佳实践：

# 使用mydumper命令进行多线程逻辑备份：
mydumper -h <host> -P <port> -u '<username>' -p '<password>' \
  --regex '^(?!(mysql|sys).)' \        # 指定转储所有不匹配正则表达式的数据库，这里会排除 'mysql' 和 'sys' 数据库
  --threads $(nproc) \                  # 开启的线程数为当前系统的处理器核心数
  --triggers \                          # 转储所有的触发器
  --events \                            # 转储所有的事件
  --routines \                          # 转储所有的存储过程和函数
-o /data/backup/<dump_dir>              # 指定转储文件的输出目录

# 使用myloader命令进行数据恢复：
myloader -h <host> -P <port> -u '<username>' -p '<password>' -d /data/backup/<dump_dir> --threads $(nproc)

备份处理脚本

如何使用

该脚本是一个多用途的 MySQL 数据库备份脚本。其主要目的是按需备份整个 MySQL 数据库实例，并将备份上传到 HDFS 集群以供后续数据恢复使用。

备份方案有三个选项：mysqldump 方法，mydumper 方法，以及 XtraBackup 方法。mysqldump 和 mydumper 是逻辑备份工具，可以备份数据库的结构以及内容。XtraBackup 是一个物理备份工具，可以备份数据文件，日志文件和系统表空间。

此脚本接受三个参数：操作人员名称，MySQL 实例唯一编号，以及所选的备份方案 （1. mysqldump、2. mydumper、3. xtrabackup）。这些参数用于个性化备份文件以及确定如何执行备份。

HDFS 的备份路径层级：<base_dir>/<operator_name>/<unique_id>/<timestamp>/

MySQL 系列之数据库备份与恢复方案（二）

备份脚本

#!/bin/bash

# -------------------------------------------------------------
# Script Name: MySQLBackupMaster.sh
# Author: Pokeya
# Creation Date: 2023/12/12
# Last Updated: 2023/12/15
# Version: v1.0.0
# Script Description: Backsup entire MySQL database instances.
# -------------------------------------------------------------

# 在出现错误或未设置的变量时立刻退出
set -eu

# 输出JSON格式的响应函数
json_response() {
  local status="$1"
  local message="$2"
  local code="$3"

  # 输出JSON格式的响应
  printf '{"status": "%s", "message": "%s", "code": %d}\n' "$status" "$message" "$code"
  exit 0
}

# 成功响应
ok() {
  local message="$1"
  local status="success"  # 设定成功描述
  local code=0            # 设定成功状态码

  # 调用json_response函数显示成功消息
  json_response "$status" "$message" "$code"
}

# 失败响应
fail() {
  local message="$1"
  local status="failed"   # 设定失败描述
  local code=${2:-1}      # 如果没有给出错误码，默认为1

  # 调用json_response函数显示错误消息
  json_response "$status" "$message" "$code"
}

# 检查参数数量，如果参数不够，则退出
if [[ $# -ne 3 ]]; then
  fail "Usage: $0 <operator> <unique_id> <backup_selector>"
fi

# 操作用户
OPERATOR=${1}
# MySQL实例唯一编号
UNIQUE_ID=${2}
# 备份方案Enum: 1: 逻辑备份（Mysqldump） | 2: 逻辑备份（MyDumper）| 3: 物理备份（XtraBackup）
BACKUP_SELECTOR=${3}

# 检查OPERATOR, UNIQUE_ID和BACKUP_SELECTOR是否为空
if [[ -z "$OPERATOR" ]] || [[ -z "$UNIQUE_ID" ]] || [[ -z "$BACKUP_SELECTOR" ]]; then
  fail "Error: All of <operator>, <unique_id> and <backup_selector> must be specified."
fi

# 检查备份工具是否已经安装
case $BACKUP_SELECTOR in
  1) # 如果选择的是“Mysqldump”
    command -v mysqldump >/dev/null 2>&1 || {
      fail "Mysqldump is not installed. Aborting.."
    } ;;
  2) # 如果选择的是“MyDumper”
    command -v mydumper >/dev/null 2>&1 || {
      fail "MyDumper is not installed. Aborting.."
    } ;;
  3) # 如果选择的是“XtraBackup”
    command -v innobackupex >/dev/null 2>&1 || {
      fail "XtraBackup is not installed. Aborting.."
    } ;;
  *) # 如果没有选择有效的备份方案
    fail "Invalid backup selector. Must be one of 1: Mysqldump | 2: MyDumper | 3: XtraBackup"
esac

# 定义本地备份目录
declare LOCAL_BACKUP_DIR="/data/backup"
# 定义备份任务时间戳
declare TIMESTAMP=$(date "+%Y%m%d-%H%M%S")
# 定义备份文件名
declare FILENAME="mysql-backup-${TIMESTAMP}"
# 定义HDFS目录层级
declare HDFS_LOCATION_DIR="${OPERATOR}/${UNIQUE_ID}/${TIMESTAMP}"

# 定义MySQL登录选项
MYSQL_DEFAULT_PORT=3306
MYSQL_ROOT_HOST='localhost'
MYSQL_ROOT_USER='root'
MYSQL_ROOT_PASSWORD='1qaz!QAZ'
MYSQL_ROOT_CONN="-h ${MYSQL_ROOT_HOST} -P ${MYSQL_DEFAULT_PORT} -u${MYSQL_ROOT_USER} -p${MYSQL_ROOT_PASSWORD}"
MYSQL_BAK_USER='u_bak'
MYSQL_BAK_LIMIT_HOST='%'
MYSQL_BAK_PASSWORD='bak@12345'
MYSQL_BAK_PRIVILEGES='SELECT, CREATE, TRIGGER, SHOW VIEW, LOCK TABLES, PROCESS, RELOAD, REPLICATION CLIENT, REPLICATION_SLAVE_ADMIN, BACKUP_ADMIN'

# 检查本地备份目录是否存在
test ! -d ${LOCAL_BACKUP_DIR} && mkdir -p ${LOCAL_BACKUP_DIR}

# 检查备份用户是否存在，不存在则创建并授权
ubak_exist=$(mysql ${MYSQL_ROOT_CONN} -sse "SELECT EXISTS(SELECT 1 FROM mysql.user WHERE user = '${MYSQL_BAK_USER}' AND host = '${MYSQL_BAK_LIMIT_HOST}')" 2> /dev/null)
if [[ "$ubak_exist" == 0 ]]; then
  mysql ${MYSQL_ROOT_CONN} -sse "CREATE USER '${MYSQL_BAK_USER}'@'${MYSQL_BAK_LIMIT_HOST}' IDENTIFIED WITH caching_sha2_password BY '${MYSQL_BAK_PASSWORD}';" 2> /dev/null
  mysql ${MYSQL_ROOT_CONN} -sse "GRANT ${MYSQL_BAK_PRIVILEGES} ON *.* TO '${MYSQL_BAK_USER}'@'${MYSQL_BAK_LIMIT_HOST}';" 2> /dev/null
fi

# 避免在命令行中直接输入密码，亦可消除警告信息
if [ ! -f ~/.my.cnf ]; then
  cat > ~/.my.cnf <<EOF
[client]
user=${MYSQL_BAK_USER}
password='${MYSQL_BAK_PASSWORD}'
EOF
  chmod 600 ~/.my.cnf
fi

# 使用mysqldump备份数据库的函数
function mysqldump_backup() {
  local backup_dir=$1
  local filename=$2

  user_databases=$(mysql --skip-column-names --silent -e "SELECT SCHEMA_NAME FROM information_schema.schemata WHERE SCHEMA_NAME NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys');")
  mysqldump --set-gtid-purged=OFF --flush-logs --complete-insert --skip-add-drop-table --routines --events --add-drop-database --databases ${user_databases} > ${backup_dir}/${filename}.sql
  sed -i "3s/^.*$/-- Backup User: '${MYSQL_BAK_USER}'@'${MYSQL_BAK_LIMIT_HOST}'/" ${backup_dir}/${filename}.sql
}

# 使用mydumper备份数据库的函数
function mydumper_backup() {
  local backup_dir=$1
  local filename=$2

  mydumper --defaults-file ~/.my.cnf --regex '^(?!(mysql|sys).)' --threads $(nproc) --triggers --events --routines -o ${backup_dir}/${filename}
  tar -Pzcf ${backup_dir}/${filename}.tar.gz ${backup_dir}/${filename}
  rm -rf ${backup_dir}/${filename}
}

# 使用XtraBackup备份数据库的函数
function xtrabackup_backup() {
  # XtraBackup方式暂未实现
  fail "XtraBackup mode is not supported yet."
}

# 将文件上传到HDFS的函数
function upload_to_hdfs() {
  local file_path=$1
  local hdfs_dir=$2

  # 检查hadoop命令是否存在，如果不存在则退出
  if ! command -v hadoop > /dev/null 2>&1; then
    fail "hadoop command not found. Please check the Hadoop installation."
  fi
  
  # 检查能否连接Hadoop集群
  if ! hadoop version > /dev/null 2>&1; then
    fail "Cannot connect to Hadoop cluster. Please check the Hadoop configuration."
  fi

  # 检查HDFS中的目录是否存在，如果不存在就创建
  if ! hadoop fs -test -d ${hdfs_dir}; then
    hadoop fs -mkdir -p ${hdfs_dir}
  fi

  # 将文件上传到HDFS
  hadoop fs -put ${file_path} ${hdfs_dir}

  # 删除本地的文件
  rm -f ${file_path}
}

# 根据备份类型选择函数执行（1. 生成本地备份文件，2. 上传至 HDFS 集群中，以便后续恢复）
case $BACKUP_SELECTOR in
  1)
    mysqldump_backup ${LOCAL_BACKUP_DIR} ${FILENAME}
    upload_to_hdfs ${LOCAL_BACKUP_DIR}/${FILENAME}.sql ${HDFS_LOCATION_DIR}
  ;;
  2)
    mydumper_backup ${LOCAL_BACKUP_DIR} ${FILENAME}
    upload_to_hdfs ${LOCAL_BACKUP_DIR}/${FILENAME}.tar.gz ${HDFS_LOCATION_DIR}
  ;;
  3)
    xtrabackup_backup
  ;;
esac

# 在脚本最后执行成功后，调用以下内容输出成功信息
ok "Backup to HDFS cluster was successful. Backup time: ${TIMESTAMP}, Backup filename: ${FILENAME}"

#EOF

测试执行

这将以 operator_name 作为操作用户，unique_id 为 MySQL 实例唯一编号进行备份，并且选择 mysqldump 方式

./MySQLBackupMaster.sh <operator_name> <unique_id> 1

成功输出：

{"status": "success", "message": "Backup to HDFS cluster was successful. Backup time: 20231215-114527, Backup filename: mysql-backup-20231215-114527", "code": 0}

失败输出：

{"status": "failed", "message": "MyDumper is not installed. Aborting..", "code": 1}

参考

MYSQL 官方网站 MySQL 8.0 参考手册 - 备份与恢复

AWS 云服务技术方案 - 大型 MySQL 数据库的迁移方案

Facebook 工程团队博客 - MySQL 备份与恢复

转载自:https://juejin.cn/post/7312431790714896419
评论
请登录