Mysql数据库备份类型介绍以及mysqldump、cp/tar、mydumper、innobackupex等备份方式配置介绍、部分报错解决

目录

一.备份类型

1.按服务器状态分

（1）热备份：

（2）温备份：

（3）冷备份：

2.按对象分

（1）物理备份：

（2）逻辑备份：

3.按数据收集分

（1）完全备份：

（2）增量备份：

（3）差异备份：

二.备份的内容

1.库、表等数据文件

2.日志文件：二进制日志等

3.存储过程、存储函数、触发器

4.各个配置文件

5.各个实现数据库备份的脚本文件， 相关例行性任务等

三.备份/恢复工具和策略介绍

1.mysqldump

mysqldump全量备份和恢复

2.mysqlhotcopy

3.cp/tar命令

（1）查看设备1的数据，并打包发送到设备2

（2）设备2的/var/lib/mysql目录下如果有原有数据要先对这一部分先备份出来，如果没有可以选择关停mysqld，将目录下的文件都删掉

（3）设备2收到文件将包解到/var/lib/mysql下 ，启动mysql，查看数据备份情况

4.lvm

5.mydumper

（1）下载安装

（2）备份

（3）删除数据myloader进行恢复备份

（4）注意报错

6.xtrabackup（innobackupex）

（1）xtrabackup优点

（2）这里主要使用innoackupex进行全量备份和恢复，mysql5.7+innobackupex2.4

（3）使用innobackupex增量备份和恢复（基于上一步的全量备份进行）

（4）注意报错

---

 

一.备份类型

1.按服务器状态分

（1）热备份：

是在数据库运行时进行备份；

读写不受影响，备份时间短；可以恢复到某一个时间点上的状态；

恢复数据迅速，可以对数据库实体进行恢复；

但一旦出错的维护成本高，恢复难度大。

注意：为保证所备份数据的完整性，在停止mysql数据库之前，需要先执行flush tables语句将所有数据写入到数据文件的文本文件里

（2）温备份：

在数据运行时备份，仅可以执行读写操作，不允许写操作操作数据库有影响

（3）冷备份：

在数据库停止时备份；

只需要备份数据库物理文件（简单拷贝），读写中止；

维护简单；

不能按表或用户进行备份。

2.按对象分

（1）物理备份：

复制数据文件；

备份和还原速度快；

最小粒度只能到表；

针对于大型数据占用存储空间大且耗时；

对版本要求高

（2）逻辑备份：

将数据导出为sql语句在导到文件中，备份时间相对长；

备份结构一般是ASCII，可以使用文本工具进行处理；

兼容性非常强；

会对RDBMS（关系型数据库管理系统）产生额外压力；

备份出来的结果占用空间大，通常会进行压缩；

逻辑备份对浮点数的精度信息不敏感，容易丢失。

3.按数据收集分

（1）完全备份：

将全部数据都备份，可以是目录、分区甚至硬盘；

恢复数据方便；

数据量大，占用空间大，备份时间长。

（2）增量备份：

前提要进行一次完全备份，仅备份上次完全备份或增量备份后发生变化后的数据；

每次新变化的数据量相对小，占用空间小，耗时短。

（3）差异备份：

前提是进行过一次完全备份，仅备份上次完全备份以来变化的数据，参考的是原始完全备份所产生的差异，不参照上一次差异备份；

开始数据量不大，随着差异备份次数增加和数据增多，会变得占用空间大，备份慢，耗时长。

二.备份的内容

1.库、表等数据文件

2.日志文件：二进制日志等

3.存储过程、存储函数、触发器

4.各个配置文件

5.各个实现数据库备份的脚本文件， 相关例行性任务等

三.备份/恢复工具和策略介绍

1.mysqldump

mysqldump，是mysql数据库管理系统自带的逻辑备份工具，支持所有引擎，MyISAM引擎是温备，InnoDB引擎是热备，备份速度中速，还原速度非常非常慢。但是在实现还原的时候，具有很大的操作余地，具有很好的弹性

mysqldump全量备份和恢复

准备数据

mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.01 sec)

mysql8.0 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)
mysql8.0 [SLB]>flush tables;
Query OK, 0 rows affected (0.05 sec)

备份数据并删除原表

mysqldump -u -p 库 表 > 存放路径

注意：不指定表名时表示备份库内所有表，若有多个库需要使用空格隔开，--all-databases可以是定备份所有库

[root@localhost ~]# mkdir /backupmysql
[root@localhost ~]# mysqldump -uroot -p SLB worker > /backupmysql/SLB.worker.sql
Enter password: 
[root@localhost ~]# cd /backupmysql/
[root@localhost backupmysql]# ll
total 4
-rw-r--r-- 1 root root 2439 Jul 25 14:16 SLB.worker.sql

mysql8.0 [SLB]>drop table worker;
Query OK, 0 rows affected (0.01 sec)

mysql8.0 [SLB]>show tables;
Empty set (0.01 sec)

恢复备份并查看结果

• mysql -u -p 库 < 备份文件

[root@localhost backupmysql]# mysql -uroot -p SLB < /backupmysql/SLB.worker.sql 
Enter password: 
[root@localhost backupmysql]# 

mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.00 sec)

mysql8.0 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

• cat 备份文件 | mysql -u -p 库

mysql8.0 [SLB]>drop table worker;
Query OK, 0 rows affected (0.02 sec)

mysql8.0 [SLB]>show tables;
Empty set (0.00 sec)

[root@localhost backupmysql]# cat SLB.worker.sql | mysql -uroot -p SLB
Enter password: 

mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.00 sec)

mysql8.0 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

2.mysqlhotcopy

物理备份工具，只支持MyISAM引擎，基本上属于冷备的范畴，物理备份，速度比较快。多用于mysql5.5之前。mysqlhotcopy使用lock tables、flush tables和cp或scp来快速备份数据库或单个表，属于裸文件备份（物理备份），本质是使用锁表语句，然后cp或scp。

3.cp/tar命令

 冷备份，支持所有引擎，复制命令，只能实现冷备，物理备份。使用归档工具，cp命令对其进行备份的备份速度快，还原速度几乎最快，但是灵活度很低，可以跨系统，但是跨平台能力很差。

这种方式建议多台设备的操作系统和mysql版本一一致

（1）查看设备1的数据，并打包发送到设备2

mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.01 sec)

mysql8.0 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

---发送给设备2
[root@localhost mysql]# pwd
/var/lib/mysql
[root@localhost mysql]# mkdir /backupdata
[root@localhost mysql]# tar -czf /backupdata/myalldb.tar.gz *
[root@localhost mysql]# scp  /backupdata/myalldb.tar.gz 192.168.2.191:/backupdata1
root@192.168.2.191's password: 

（2）设备2的/var/lib/mysql目录下如果有原有数据要先对这一部分先备份出来，如果没有可以选择关停mysqld，将目录下的文件都删掉

[root@localhost ~]# systemctl stop mysqld
[root@localhost ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: inactive (dead) since Tue 2023-07-25 19:06:33 CST; 6s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3069 ExecStart=/usr/sbin/mysqld $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3039 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3069 (code=exited, status=0/SUCCESS)
   Status: "Server shutdown complete"

Jul 25 18:58:02 localhost.localdomain systemd[1]: Starting MySQ...
Jul 25 18:58:02 localhost.localdomain systemd[1]: Started MySQL...
Jul 25 19:06:30 localhost.localdomain systemd[1]: Stopping MySQ...
Jul 25 19:06:33 localhost.localdomain systemd[1]: Stopped MySQL...
Hint: Some lines were ellipsized, use -l to show in full.

[root@localhost ~]# rm -rf /var/lib/mysql/*
[root@localhost ~]# ll /var/lib/mysql
total 0

（3）设备2收到文件将包解到/var/lib/mysql下 ，启动mysql，查看数据备份情况

[root@localhost ~]# ll /backupdata1/
total 2612
-rw-r--r-- 1 root root 2671906 Jul 25 19:05 myalldb.tar.gz

[root@localhost ~]# tar -xf /backupdata1/myalldb.tar.gz -C /var/lib/mysql/
[root@localhost ~]# ll /var/lib/mysql
total 90608
-rw-r----- 1 mysql mysql       56 Jul 23 20:20 auto.cnf
-rw-r----- 1 mysql mysql     1782 Jul 23 20:33 binlog.000001
-rw-r----- 1 mysql mysql     1170 Jul 24 12:57 binlog.000002
-rw-r----- 1 mysql mysql      379 Jul 24 13:02 binlog.000003
-rw-r----- 1 mysql mysql     4214 Jul 25 14:13 binlog.000004
-rw-r----- 1 mysql mysql       64 Jul 24 13:02 binlog.index
-rw------- 1 mysql mysql     1676 Jul 23 20:20 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 23 20:20 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 23 20:20 client-cert.pem
-rw------- 1 mysql mysql     1680 Jul 23 20:20 client-key.pem
-rw-r----- 1 mysql mysql   196608 Jul 25 18:44 #ib_16384_0.dblwr
-rw-r----- 1 mysql mysql  8585216 Jul 23 20:20 #ib_16384_1.dblwr
-rw-r----- 1 mysql mysql     3607 Jul 23 20:33 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 25 14:13 ibdata1
-rw-r----- 1 mysql mysql 12582912 Jul 23 20:33 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jul 23 20:33 #innodb_redo
drwxr-x--- 2 mysql mysql      187 Jul 23 20:33 #innodb_temp
-rw-r----- 1 mysql mysql    25270 Jul 25 19:00 localhost.log
drwxr-x--- 2 mysql mysql      143 Jul 23 20:20 mysql
-rw-r----- 1 mysql mysql 25165824 Jul 25 14:13 mysql.ibd
-rw------- 1 mysql mysql        5 Jul 23 20:33 mysql.sock.lock
drwxr-x--- 2 mysql mysql     8192 Jul 23 20:20 performance_schema
-rw------- 1 mysql mysql     1680 Jul 23 20:20 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Jul 23 20:20 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Jul 23 20:20 server-cert.pem
-rw------- 1 mysql mysql     1680 Jul 23 20:20 server-key.pem
drwxr-x--- 2 mysql mysql       24 Jul 25 14:12 SLB
drwxr-x--- 2 mysql mysql       20 Jul 24 13:08 SLB1
drwxr-x--- 2 mysql mysql       28 Jul 23 20:20 sys
-rw-r----- 1 mysql mysql 16777216 Jul 25 18:44 undo_001
-rw-r----- 1 mysql mysql 16777216 Jul 25 18:44 undo_002

[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# mysql -uroot -pSLBmysql@317418
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.34 MySQL Community Server - GPL

Copyright (c) 2000, 2023, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql8.0 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| SLB                |
| SLB1               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql8.0 [(none)]>use SLB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.00 sec)

mysql8.0 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

4.lvm

几乎是热备份，支持所有引擎，基于快照(LVM，ZFS)的物理备份，速度非常快，恢复速度也比较快，几乎是热备。只影响数据几秒钟而已。但是创建快照的过程本身就影响到了数据库在线的使用。而且LVM的限制：不能对多个逻辑卷同一时间进行备份，所以数据文件和事务日志等各种文件必须放在同一个LVM上。

5.mydumper

（1）下载安装

链接：https://pan.baidu.com/s/1uqbqZboEObYH2fUBR2DvHg 
提取码：1jh1

[root@localhost ~]# mkdir /mydumper
[root@localhost ~]# cd /mydumper/
[root@localhost mydumper]# wget https://github.com/mydumper/mydumper/releases/download/v0.14.5-3/mydumper-0.14.5-3.el7.x86_64.rpm
[root@localhost mydumper]# yum install -y mydumper-0.14.5-3.el7.x86_64.rpm 
[root@localhost ~]# mydumper --help
Usage:
  mydumper [OPTION…] multi-threaded MySQL dumping

连接选项
  -h, --host                       要连接到的主机
  -u, --user                       具有必要权限的用户名，用户
  -p, --password                   用户密码
  -a, --ask-password               提示输入用户密码
  -P, --port                       要连接到的 TCP/IP 端口
  -S, --socket                     用于连接的 UNIX 域套接字文件
  -C, --compress-protocol          在 MySQL 连接上使用压缩
  --ssl                            使用 SSL 进行连接
  --ssl-mode                       与服务器连接的所需安全状态：已禁用、首选、必需、CA验证、身份验证
  --key                            密钥文件的路径名
  --cert                           证书文件的路径名
  --ca                             CA证书颁发机构文件的路径名
  --capath                         包含 PEM 格式的受信任 SSL CA 证书的目录的路径名
  --cipher                         用于 SSL 加密的允许密码列表
  --tls-version                    服务器允许加密连接的协议

Filter Options
  -x, --regex                      匹配表的正则表达式
  -B, --database                   要转储的数据库
  -i, --ignore-engines             要忽略的以逗号分隔的存储引擎列表
  --where                          仅转储选定的记录。
  -U, --updated-since              使用 Update_time 仅转储最近 U 天内更新的表
  --partition-regex                正则表达式按分区名称进行筛选。
  -O, --omit-from-file             包含要跳过的 database.table 条目列表的文件，每行一个（在应用正则表达式选项之前跳过）
  -T, --tables-list                要转储的逗号分隔表列表（不排除正则表达式选项）。表名必须包含数据库名。例如：test.t1，test.t2

锁定选项
  -z, --tidb-snapshot              用于 TiDB 的快照
  -k, --no-locks                   不要执行临时共享读锁定。可能将导致备份不一致
  --use-savepoints                 使用保存点减少元数据锁定问题，需要超级权限
  --no-backup-locks                不要使用Percona备份锁
  --lock-all-tables                对所有人使用 LOCK TABLE，而不是 FTWRL
  --less-locking                   最大限度地减少InnoDB表上的锁定时间。
  --trx-consistency-only           仅事务一致性

PMM Options
  --pmm-path                       默认值为 /usr/local/percona/pmm2/collectors/textfile-collector/high-resolution
  --pmm-resolution                 哪个默认值会很高

执行选项
  --exec-threads                   要与 --exec 一起使用的线程数
  --exec                           使用文件作为参数执行的命令
  --exec-per-thread                设置将由 STDIN 接收的命令，并将 STDOUT 写入输出文件
  --exec-per-thread-extension      使用 --exec-per-thread 时设置 STDOUT 文件的扩展名

如果发现长查询运行
  --long-query-retries             重试检查长查询，默认值为 0（不重试）
  --long-query-retry-interval      重试长查询签入前的等待时间（以秒为单位），默认值为 60
  -l, --long-query-guard           设置长查询计时器（以秒为单位），默认为 60
  -K, --kill-long-queries          终止长时间运行的查询（而不是中止）

工作选项
  --max-rows                       估计表后限制每个块的行数，默认为 1000000。它已被弃用，请改用 --rows。在将来的版本中已删除
  --char-deep                      
  --char-chunk                     
  -r, --rows                       将表拆分为这么多行的块。它可以是 MIN：START_AT：MAX.MAX可以是 0，这意味着没有限制。如果查询时间少于 1 秒，它将使块大小翻倍，如果超过 2 秒，则块大小将增加一半
  --split-partitions               将分区转储到单独的文件中。此选项将覆盖分区表的 --rows 选项。

校验和选项
  -M, --checksum-all               转储所有元素的校验和
  --data-checksums                 转储包含数据的表校验和
  --schema-checksums               转储架构表和视图创建校验和
  --routine-checksums              转储触发器、函数和例程校验和

对象选项
  -m, --no-schemas                 不要转储包含数据和触发器的表架构
  -Y, --all-tablespaces            转储所有表空间。
  -d, --no-data                    不转储表数据 
  -G, --triggers                   转储触发器。默认情况下，它不会转储触发器 
  -E, --events                     转储事件。默认情况下，它不转储事件 
  -R, --routines                   转储存储过程和函数。默认情况下，它不转储存储过程或函数 
  --views-as-tables                将视图导出为表格
  -W, --no-views                   不要转储视图

语句选项
  --load-data                      
  --csv                            自动启用 --load data 并设置变量以 CSV 格式导出。
  --fields-terminated-by           
  --fields-enclosed-by             
  --fields-escaped-by              将用于转义 LOAD DATA 静态中的字符的单个字符，默认值：“\”
  --lines-starting-by              在每行的开头添加字符串。当使用 --load-data 时，它被添加到 LOAD DATA 语句中。它会影响 INSERT INTO 语句，当它被使用时。
  --lines-terminated-by            在每行末尾添加字符串。当使用 --load-data 时，它被添加到 LOAD DATA 语句中。它会影响 INSERT INTO 语句，当它被使用时。
  --statement-terminated-by        这可能永远不会被使用，除非你知道你在做什么
  -N, --insert-ignore              使用插入忽略转储行
  --replace                        使用替换转储行
  --complete-insert                使用包含列名的完整 INSERT 语句
  --hex-blob                       使用十六进制表示法转储二进制列
  --skip-definer                   从 CREATE 语句中删除定义器。默认情况下，不修改语句
  -s, --statement-size             尝试的 INSERT 语句大小（以字节为单位），默认值为 1000000
  --tz-utc                         在转储顶部设置 TIME_ZONE='+00：00' 以允许在服务器具有不同时区的数据或数据在不同时区的服务器之间移动时转储 TIMESTAMP 数据，默认为 on use --skip-tz-utc 禁用。
  --skip-tz-utc                    
  --set-names                      设置名称，使用风险自负，默认二进制

额外选项
  -F, --chunk-filesize             将表拆分为此输出文件大小的块。此值以 MB 为单位
  --exit-if-broken-table-found     如果发现损坏的表，则退出
  --success-on-1146                不增加错误计数和警告而不是严重，如果表不存在
  -e, --build-empty-files          生成转储文件，即使表中没有可用的数据
  --no-check-generated-fields      不会执行与生成的字段相关的查询。如果您生成了列，则会导致恢复问题
  --order-by-primary               如果不存在主键，则按主键或唯一键对数据进行排序
  -c, --compress                   压缩输出文件

守护程序选项
  -D, --daemon                     启用守护程序模式
  -I, --snapshot-interval          每个转储快照之间的间隔（以分钟为单位），需要 --守护程序，默认值为 60
  -X, --snapshot-count             快照数，默认为 2

应用选项
  -?, --help                       显示帮助选项
  -o, --outputdir                  要将文件输出到的目录
  --stream                         写入文件后，它将通过 STDOUT 进行流式传输。从 v0.12.7-1 开始，接受 NO_DELETE、NO_STREAM_AND_NO_DELETE 和 TRADITION，这是默认值，如果没有给出参数，则使用
  -L, --logfile                    要使用的日志文件名，默认情况下使用 stdout
  --disk-limits                    设置限制，以便在确定没有足够的磁盘空间时暂停和恢复。接受以下值：“：”（以 MB 为单位）。例如：当只有 100MB 可用时，100：500 将暂停，如果 500MB 可用，则恢复
  -t, --threads                    要使用的线程数，默认为 4
  -V, --version                    显示程序版本并退出
  --identifier-quote-character     这将设置标识符引号字符，该字符仅用于在 mydumper 上插入语句，并在 myloader 上用于拆分语句。使用 SQL_MODE 更改 CREATE TABLE 语句可能的值为：反引号和DOUBLE_QUOTE。默认值：反引号
  -v, --verbose                    输出的详细程度，0 = 静默，1 = 错误，2 = 警告，3 = 信息，默认值 2
  --defaults-file                  使用特定的默认文件。默认值：/etc/mydumper.cnf
  --defaults-extra-file            使用其他默认文件。这是在 --defaults-file 之后加载的，替换以前定义的值

（2）备份

[root@localhost ~]# mydumper -u root -p SLBmysql@317418 -B SLB -o /mydumperbackup/
---备份具体表就用“-T 库名.表名”，只备份结构使用“-d”参数，不写库名默认备份所有库
[root@localhost ~]# ll /mydumperbackup/
total 16
-rw-r--r-- 1 root root 243 Jul 25 20:00 metadata ---记录备份开始和结束时间，以及binlog日志文件位置。
-rw-r--r-- 1 root root 152 Jul 25 20:00 SLB-schema-create.sql ---建库语句
-rw-r--r-- 1 root root   0 Jul 25 20:00 SLB-schema-triggers.sql ---创建触发器语句
-rw-r--r-- 1 root root 514 Jul 25 20:00 SLB.worker.00000.sql
-rw-r--r-- 1 root root 463 Jul 25 20:00 SLB.worker-schema.sql ---建表语句

（3）删除数据myloader进行恢复备份

mysql8.0 [SLB]>drop database SLB;
Query OK, 1 row affected (0.03 sec)
mysql8.0 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| SLB1               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# myloader -u root -p SLBmysql@317418 -B SLB -d /mydumperbackup/
---d指定从哪个目录还原（存放备份文件的目录），-o参数可以覆盖同名表

mysql8.0 [(none)]>show databases;
+--------------------+
| Database           |
+--------------------+
| SLB                |
| SLB1               |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
6 rows in set (0.00 sec)

mysql8.0 [(none)]>use SLB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.00 sec)

mysql8.0 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

（4）注意报错

Segmentation fault (core dumped)

• 试验了几遍得出第一个解决方法，暂时不知道到底是什么原因，加了参数就试出来了

将上面备份和恢复产生的文件删掉重来，添加以下参数

-G导出触发器，-R导出存储过程，-E导出事件

[root@localhost backup]# mydumper -u root -p SLBmysql@ -G -R -E -B SLB -o /backup
[root@localhost backup]# ll
total 1244
-rw-r--r-- 1 root root 1230558 Jul 20 18:40 alldatabase.sql
-rw-r--r-- 1 root root    3233 Jul 20 18:38 descschool.sql
-rw-r--r-- 1 root root     135 Jul 20 22:42 metadata
-rw-r--r-- 1 root root    4353 Jul 20 17:28 muldatabase.sql
-rw-r--r-- 1 root root    3233 Jul 20 17:18 schooldrop.sql
-rw-r--r-- 1 root root    1135 Jul 20 17:21 schoolgzip.sql.gz
-rw-r--r-- 1 root root    3233 Jul 20 17:11 school.sql
-rw-r--r-- 1 root root     198 Jul 20 22:42 SLB.dom-schema.sql
-rw-r--r-- 1 root root     133 Jul 20 22:42 SLB.dom.sql
-rw-r--r-- 1 root root     127 Jul 20 22:42 SLB-schema-create.sql

删掉表再进行恢复，不再报那个问题，表中的数据也恢复

mysql8.0 [SLB]>drop table dom;
Query OK, 0 rows affected (0.04 sec)

mysql8.0 [SLB]>show tables;
Empty set (0.01 sec)

[root@localhost backup]# myloader -u root -p SLBmysql@ -s SLB -d /backup
[root@localhost backup]# 
mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| dom           |
+---------------+
1 row in set (0.00 sec)

mysql8.0 [SLB]>select * from dom;
+----+
| id |
+----+
| 20 |
+----+
1 row in set (0.00 sec)

• 第二个解决方法，有可能是我原有目录里的其他文件（mysqldump产生的备份文件）之间有冲突，尝试另外新建目录用于存放备份文件

查看原有数据

mysql8.0 [SLB]>select * from dom;
+----+
| id |
+----+
| 20 |
+----+
1 row in set (0.00 sec)

到另外的目录存放备份文件，删除原有数据

[root@localhost backupSLB]# mydumper -u root -p SLBmysql@ -B SLB -o /backupSLB
[root@localhost backupSLB]# ll
total 16
-rw-r--r-- 1 root root 135 Jul 20 23:29 metadata
-rw-r--r-- 1 root root 198 Jul 20 23:29 SLB.dom-schema.sql
-rw-r--r-- 1 root root 133 Jul 20 23:29 SLB.dom.sql
-rw-r--r-- 1 root root 127 Jul 20 23:29 SLB-schema-create.sql

mysql8.0 [SLB]>drop table dom;
Query OK, 0 rows affected (0.03 sec)

mysql8.0 [SLB]>show tables;
Empty set (0.00 sec)

进行备份恢复

[root@localhost backupSLB]# myloader -u root -p SLBmysql@ -d /backupSLB -o -B SLB
[root@localhost backupSLB]# 
mysql8.0 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| dom           |
+---------------+
1 row in set (0.00 sec)

mysql8.0 [SLB]>select * from dom;
+----+
| id |
+----+
| 20 |
+----+
1 row in set (0.00 sec)

问题解决

• 尝试将mydumper升级到更高或最新版本

6.xtrabackup（innobackupex）

Xtrabackup是一个对InnoDB做数据备份的工具，支持在线热备份（备份时不影响数据读写），

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

xtrabackup 只能备份InnoDB和XtraDB两种数据表，而不能备份MyISAM数据表。

innobackupex 是参考了InnoDB Hotbackup的innoback脚本修改而来的.innobackupex是一个perl脚

本封装，封装了xtrabackup。主要是为了方便的同时备份InnoDB和MyISAM引擎的表，但在处理myisam时需要加一个读锁。并且加入了一些使用的选项。如slave-info可以记录备份恢复后作为slave需要的一些信 息，根据这些信息，可以很方便的利用备份来重做slave。

（1）xtrabackup优点

支持完全备份和增量备份

备份过程快速、可靠；

备份过程不会打断正在执行的事务；

能够基于压缩等功能节约磁盘空间和流量；

自动实现备份检验；

还原速度快；

（2）这里主要使用innoackupex进行全量备份和恢复，mysql5.7+innobackupex2.4

链接：https://pan.baidu.com/s/1x1WviWy1RSC3pDKJPd0AIg 
提取码：11as

准备数据查看是否完整

mysql5.7 [(none)]>use SLB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql5.7 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.00 sec)

mysql5.7 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

innobackupex备份数据，并删除原表原库

[root@localhost innobackupexSLB]# mkdir /innobackupexSLB
[root@localhost innobackupexSLB]# innobackupex --defaults-file=/etc/my.cnf --user=root --password=SLBmysql@317418 /innobackupexSLB/
[root@localhost innobackupexSLB]# ll
drwxr-x--- 6 root root 288 Jul 27 09:58 2023-07-27_09-56-40
 
mysql5.7 [SLB]>drop table worker;
Query OK, 0 rows affected (0.03 sec)

mysql5.7 [SLB]>drop database SLB;
Query OK, 0 rows affected (0.01 sec)

关闭mysqld后进行恢复，需要删除你原有的/var/lib/mysql下的文件，并且将恢复过来的文件属主和属组都改为mysql后之后才有权限去查看

[root@localhost innobackupexSLB]# innobackupex --apply-log /innobackupexSLB/2023-07-27_09-56-40/
---完全备份恢复数据

[root@localhost innobackupexSLB]# systemctl stop mysqld.service

[root@localhost mysql]# rm -rf /var/lib/mysql/*

[root@localhost innobackupexSLB]# innobackupex --copy-back /innobackupexSLB/2023-07-27_09-56-40/
[root@localhost mysql]# ll
total 122920
-rw-r----- 1 root root      436 Jul 27 09:59 ib_buffer_pool
-rw-r----- 1 root root 12582912 Jul 27 09:59 ibdata1
-rw-r----- 1 root root 50331648 Jul 27 09:59 ib_logfile0
-rw-r----- 1 root root 50331648 Jul 27 09:59 ib_logfile1
-rw-r----- 1 root root 12582912 Jul 27 09:59 ibtmp1
drwxr-x--- 2 root root     4096 Jul 27 09:59 mysql
drwxr-x--- 2 root root     8192 Jul 27 09:59 performance_schema
drwxr-x--- 2 root root       56 Jul 27 09:59 SLB
drwxr-x--- 2 root root     8192 Jul 27 09:59 sys
-rw-r----- 1 root root      456 Jul 27 09:59 xtrabackup_info
-rw-r----- 1 root root        1 Jul 27 09:59 xtrabackup_master_key_id
[root@localhost mysql]# chown -R mysql:mysql /var/lib/mysql/*
[root@localhost mysql]# ll
total 122920
-rw-r----- 1 mysql mysql      436 Jul 27 09:59 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Jul 27 09:59 ibdata1
-rw-r----- 1 mysql mysql 50331648 Jul 27 09:59 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Jul 27 09:59 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 Jul 27 09:59 ibtmp1
drwxr-x--- 2 mysql mysql     4096 Jul 27 09:59 mysql
drwxr-x--- 2 mysql mysql     8192 Jul 27 09:59 performance_schema
drwxr-x--- 2 mysql mysql       56 Jul 27 09:59 SLB
drwxr-x--- 2 mysql mysql     8192 Jul 27 09:59 sys
-rw-r----- 1 mysql mysql      456 Jul 27 09:59 xtrabackup_info
-rw-r----- 1 mysql mysql        1 Jul 27 09:59 xtrabackup_master_key_id


[root@localhost innobackupexSLB]# systemctl start mysqld

mysql5.7 [(none)]>use SLB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql5.7 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.00 sec)

mysql5.7 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
6 rows in set (0.00 sec)

（3）使用innobackupex增量备份和恢复（基于上一步的全量备份进行）

插入一条数据，在原有全量上增量备份

mysql5.7 [SLB]>insert worker values(103,1007,'2018-06-07',8000,'群众','王五','1999-03-01');
Query OK, 1 row affected (0.01 sec)

mysql5.7 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
|       103 |      1007 | 2018-06-07   | 8000.00 | 群众         | 王五   | 1999-03-01   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
7 rows in set (0.00 sec)

[root@localhost innobackupexSLB]# innobackupex --incremental --user=root --password=SLBmysql@317418 /innobackupexSLB/ --incremental-basedir=/innobackupexSLB/2023-07-27_09-56-40/

进行恢复，需要先恢复全量再恢复增量

[root@localhost innobackupexSLB]# innobackupex --apply-log --redo-only /innobackupexSLB/2023-07-27_09-56-40/
[root@localhost innobackupexSLB]# innobackupex --apply-log --redo-only /innobackupexSLB/2023-07-27_09-56-40/ --incremental-dir=/innobackupexSLB/2023-07-27_10-33-52
[root@localhost innobackupexSLB]# systemctl stop mysqld
[root@localhost innobackupexSLB]# rm -rf /var/lib/mysql/*
[root@localhost innobackupexSLB]# innobackupex --copy-back /innobackupexSLB/2023-07-27_09-56-40/
---最后恢复的目录是全量备份的那个目录
[root@localhost mysql]# chown -R mysql:mysql /var/lib/mysql/*

查看数据情况

mysql5.7 [(none)]>show databases;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: *** NONE ***

+--------------------+
| Database           |
+--------------------+
| information_schema |
| SLB                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

mysql5.7 [(none)]>use SLB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql5.7 [SLB]>show tables;
+---------------+
| Tables_in_SLB |
+---------------+
| worker        |
+---------------+
1 row in set (0.00 sec)

mysql5.7 [SLB]>select * from worker;
+-----------+-----------+--------------+---------+--------------+--------+--------------+
| 部门号    | 职工号    | 工作时间     | 工资    | 政治面貌     | 姓名   | 出生日期     |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
|       101 |      1001 | 2015-05-04   | 3500.00 | 群众         | 张三   | 1990-07-01   |
|       101 |      1002 | 2017-02-06   | 3200.00 | 团员         | 李四   | 1997-02-08   |
|       102 |      1003 | 2011-01-04   | 8500.00 | 党员         | 王亮   | 1983-06-08   |
|       102 |      1004 | 2016-10-10   | 5500.00 | 群众         | 赵六   | 1994-09-05   |
|       102 |      1005 | 2014-04-01   | 4800.00 | 党员         | 钱七   | 1992-12-30   |
|       102 |      1006 | 2017-05-05   | 4500.00 | 党员         | 孙八   | 1996-09-02   |
|       103 |      1007 | 2018-06-07   | 8000.00 | 群众         | 王五   | 1999-03-01   |
+-----------+-----------+--------------+---------+--------------+--------+--------------+
7 rows in set (0.01 sec)

（4）注意报错

使用innobackupex备份，关闭mysqld后进行恢复备份再启动mysqld时报错，没有告知具体错误

Can‘t read dir of ‘.‘ (errno: 13 - Permission denie)

[root@localhost innobackupexSLB]# innobackupex --copy-back /innobackupexSLB/2023-07-27_22-37-00/

[root@localhost innobackupexSLB]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.

[root@localhost innobackupexSLB]# systemctl status mysqld.service 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: failed (Result: start-limit) since Thu 2023-07-27 22:47:33 CST; 1s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 7187 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=1/FAILURE)
  Process: 7164 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 6538 (code=exited, status=0/SUCCESS)

Jul 27 22:47:32 localhost.localdomain systemd[1]: Failed to start MySQL Server.
Jul 27 22:47:32 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Jul 27 22:47:32 localhost.localdomain systemd[1]: mysqld.service failed.
Jul 27 22:47:33 localhost.localdomain systemd[1]: mysqld.service holdoff time over, scheduling restart.
Jul 27 22:47:33 localhost.localdomain systemd[1]: Stopped MySQL Server.
Jul 27 22:47:33 localhost.localdomain systemd[1]: start request repeated too quickly for mysqld.service
Jul 27 22:47:33 localhost.localdomain systemd[1]: Failed to start MySQL Server.
Jul 27 22:47:33 localhost.localdomain systemd[1]: Unit mysqld.service entered failed state.
Jul 27 22:47:33 localhost.localdomain systemd[1]: mysqld.service failed.
[root@localhost innobackupexSLB]# 

解决：看你数据库的datedir目录属主和属组，如果是root将其递归改为mysql在重启

[root@localhost innobackupexSLB]# cat /etc/my.cnf | grep datadir
datadir=/var/lib/mysql

[root@localhost innobackupexSLB]# chown -R mysql:mysql /var/lib/mysql
[root@localhost innobackupexSLB]# systemctl start mysqld
[root@localhost innobackupexSLB]# 

查看已经能够正常使用数据库

mysql5.7 [SLB]>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| SLB                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

此外，在数据库服务正在使用时，datedir目录属主和属组发生变化会导致以下报错，同样是要将datedir目录属主和属组改回mysql

mysql5.7 [SLB]>show databases; ERROR 1018 (HY000): Can't read dir of '.' (errno: 13 - Permission denied) mysql5.7 [SLB]>show tables; ERROR 1018 (HY000): Can't read dir of './SLB/' (errno: 13 - Permission denied)