在进行数据库数据逻辑备份操作过程中，主要会运用mysqldump逻辑备份工具，可以实现本地或远程的数据备份；

利用mysqldump进行逻辑备份数据时，主要的备份逻辑是将建库、建表、数据插入语句信息导出，实现数据的备份操作；

基于mysqldump备份数据的逻辑原理，对于数据量比较小的场景（单表数据行百万以内），mysqldump备份工具做备份会更适合些；

在跨平台或跨版本进行数据库数据信息迁移时，mysqldump备份工具做备份也会比较适合，可以避免物理备份的兼容性问题；

说明：在一般情况下，对数据库进行数据恢复的时间耗费，大约是数据库进行数据备份的时间耗费的3~5倍。

工具命令使用语法：

-- 在执行mysqldump命令时，也会用到数据库连接登录的基础参数：-u -p -S -h -P
[root@master ~]# mysqldump -u数据库用户 -p数据库密码 [备份参数] > /路径信息/数据库备份文件.sql

工具命令常用参数：

序号 参数信息 官方说明 解释说明
01 -A Dump all the databases 表示备份所有库中数据信息
02 -B Dump several databases. 表示备份指定库中数据信息
03 -F Flush logs file in server before starting dump 表示在备份启动前自动刷新日志文件

工具命令实践操作：

数据库备份恢复练习环境准备：

-- 备份机器创建备份sql数据目录
[root@master ~]# mkdir -p /database_backup

-- 如果有用户，则使用下面语句进行创建
[root@master ~]# mysql
mysql> alter user root@'localhost' identified by '123456';

-- 如果没有用户，则使用下面语句进行创建
mysql> create user root@'localhost' identified with mysql_native_password by '123456';

一、数据库全库备份操作练习实践命令

将数据库中所有数据库全部备份（-A）

本机进行备份

-- 利用mysqldump命令备份的数据文件是纯文本文件，是可以进行查看或过滤的；下面warning内容是因为gtid开启的原因导致，可以不用管
[root@db01-51 ~]# mysqldump -uroot -p123456 -A >/database_backup/all_database.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.

-- 如果想消除上面告警提示，则可使用--single-transaction和--set-gtid-purged=OFF这两个参数
[root@db01-51 ~]# mysqldump -uroot -p123456 --single-transaction --set-gtid-purged=OFF -A > /database_backup/all_database.sql

-- 验证查看
[root@db01-51 ~]# ll -h /database_backup/all_database.sql
-rw-r--r-- 1 root root 49M Mar 10 12:07 /database_backup/all_database.sql

相关参数说明：

  • --single-transaction：保证Innodb引擎一致性备份及实现无锁热备份
  • --set-gtid-purged=OFF：设置 GTID 不写入备份文件

说明：利用-A创建数据库备份数据时，在备份数据中会含有 create建库语句和use切换库语句，可以直接进行恢复操作即可；

本机进行备份还原数据

-- 方法一
[root@db01-51 ~]# source /database_backup/all_database.sql

-- 方法二
[root@db01-51 ~]# mysql -uroot -p123456 < /database_backup/all_database.sql

远程机器进行备份

-- 远程机器创建存储目录
[root@db02-52 ~]# mkdir -p /database_backup

-- 备份
[root@db01-52 ~]# mysqldump -uroot -p123456 -h 10.0.0.51  --single-transaction --set-gtid-purged=OFF -A > /database_backup/all_database.sql

-- 验证
[root@db02-52 ~]# ll /database_backup/all_database.sql
-rw-r--r-- 1 root root 51267996 Mar 10 12:21 /database_backup/all_database.sql

远程机器进行备份还原数据

-- 方法一
[root@db01-51 ~]# mysql -uroot -p123456  -h 10.0.0.51 -e "source /database_backup/all_database.sql"

-- 方法二
[root@db01-51 ~]# mysql -uroot -p123456  -h 10.0.0.51 < /database_backup/all_database.sql

二、数据库部分备份操作练习实践命令

2.1 将数据库中单个数据库进行备份（-B）

# 进行数据库单库备份操作
[root@db01-51 ~]# mysqldump -uroot -p123456 -B zq  --single-transaction --set-gtid-purged=OFF >/database_backup/zq.sql

# 过滤部分内容后查看备份数据库文件信息：
[root@db01-51 ~]# egrep -vi '^-|^/\*|^$|lock' /database_backup/zq.sql
...
...
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int DEFAULT NULL,
  `name` char(5) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO `test1` VALUES (1,'a'),(2,'b'),(3,'c');
SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

以上指定数据库备份完毕后，可以模拟删除相应数据，利用备份的数据库文件进行数据库恢复操作：

-- 模拟删除数据库中数据表信息，造成数据库中数据损坏
mysql> use zq
mysql> show tables;
+--------------+
| Tables_in_zq |
+--------------+
| t100w        |
| test1        |
+--------------+
2 rows in set (0.00 sec)
-- 删除表
mysql> drop table t100w;
-- 查看表
mysql> show tables;
+--------------+
| Tables_in_zq |
+--------------+
| test1        |
+--------------+
1 row in set (0.00 sec)
Empty set (0.00 sec)

# 进行数据库数据复原恢复操作：
# 方式一：在数据库系统中加载数据库备份文件
mysql> source /database_backup/zq.sql;

# 方式二：在操作系统命令行执行数据恢复命令
[root@master ~]# mysql -uroot -p123456 zq </database_backup/zq.sql

# 数据信息恢复完毕后检查数据库情况
mysql> show tables;
+------------------------+
| Tables_in_oldboy   |
+------------------------+
| stu2                          |
+------------------------+
1 row in set (0.00 sec)

mysql> select * from stu2;

2.2 将数据库中多个数据库进行备份（-B）

[root@master ~]# mysqldump -uroot -p123456 --single-transaction --set-gtid-purged=OFF -B zq world >/database_backup/zq_world.sql

# 过滤部分内容后查看备份数据库文件信息：
[root@master ~]# egrep -vi '^-|^/\*|^$|lock' /database_backup/zq_world.sql

说明：利用-B创建数据库备份数据时，在备份数据中会含有 create建库语句和use切换库语句，可以直接进行恢复操作即可；

多个数据库备份进行还原：

# 方式一：在数据库系统中加载数据库备份文件
mysql> source /database_backup/zq_world.sql;

# 方式二：在操作系统命令行执行数据恢复命令
[root@master ~]# mysql -uroot -p123456 zq </database_backup/zq_world.sql

三、数据表部分备份操作练习实践命令

3.1 将数据库中单个数据表进行备份

# 备份指定数据库中的单个数据表：
[root@master ~]# mysqldump -uroot -p123456 --single-transaction --set-gtid-purged=OFF zq t100w >/database_backup/zq_tables_t100w.sql

# 恢复指定数据库中的单个数据表：
[root@master ~]# mysql -uroot -p123456  zq < /database_backup/zq_tables_t100w.sql

3.2 将数据库中多个数据表进行备份

# 备份指定数据库中的多个数据表：
[root@master ~]# mysqldump -uroot -p123456 --single-transaction --set-gtid-purged=OFF zq  t100w test1 >/database_backup/zq_tables_t100w_test1.sql

# 恢复指定数据库中的多个数据表：
[root@master ~]# mysql -uroot -p123456 zq </database_backup/zq_tables_t100w_test1.sql

说明：数据库单表或多表进行数据备份时，在备份数据中不含有create建库语句和use切换库语句，需要建库并指定库再恢复数据；