MySQL的几种备份恢复

一、mysqldump

mysqldump 客户机实用程序执行逻辑备份，生成一组 SQL 语句，可以执行这些 SQL 语句来重建原始数据库对象定义和表数据。它可以转储一个或多个 MySQL 数据库，以便备份或传输到另一个 SQL 服务器， mysqldump 命令还可以生成 CSV 、其他分隔符文本或 XML 格式的输出。

mysqldump 至少要求对 TABLE 具有 SELECT 权限，对转储 VIEW 具有 SHOW VIEW ，对转储触发器具有 TRIGGER ，如果不使用 --single-transaction 选项，则需要锁定表。

1.1、使用 mysqldump 以 SQL 格式转储数据

mysqldump [arguments] > file_name

1.1.1、转储整个数据库

mysqldump -uroot -p --all-databases --single-transaction > /mnt/20210126_all_backup.sql

1.1.2、转储选定数据库

mysqldump -uroot -p --databases mysql --single-transaction > /mnt/20210127-0001_backup.sql

--databases会导致命令行上的所有名称都被视为数据库名称。如果没有此选项，mysqldump会将名字视为数据库名称，将下面的名称视为表名。

使用--all-databases和--databases在每个数据库的转储输出之前写入创建数据库和 USE 语句。这可确保在重新加载转储文件时，如果每个数据库不存在，它将创建每个数据库，并使它成为默认数据库，以便将数据库内容加载到它们来自的同一个数据库中。如果要使转储文件强制删除每个数据库，请同时使用--add-drop-databases选项。

1.1.3、转储数据库中特定表

mysqldump -uroot -p --single-transaction cent t1 t2 [-d/-t]> /mnt/user.sql

-d：只转储数据库表结构

-t：只转储数据库表数据

1.1.4、

转储单个数据库

mysqldump --databases test > dump.sql

在单数据库情况下，允许省略--databases

mysqldump test > dump.sql

两个命令的区别在于，如果没有--databases，转储输出不包含创建数据库和USE语句。

• 重新加载转储文件时，必须指定默认数据库名称，以便服务器知道要重新加载哪个数据库。
• 对于重新加载，可以指定不同于原始名称的数据库名称，从而允许您将数据重新加载到其他数据库中。
• 如果要重新加载的数据库不存在，则必须先创建它。
• 由于输出不包含CREATE DATABASES语句，因此--add-drop-databases选项不起作用。如果使用它，它将不生成DROP DATABASES语句。

1.2、重新加载 SQL 格式备份

如果转储文件是由mysqldump使用--all-databases或 --databases选项创建的，则它包含create databases和use语句，并且不需要指定加载数据的默认数据库。

mysql < dump.sql

或从mysql内部使用命令：source

mysql> source dump.sql

如果文件是一个不含创建数据库和 USE 语句的单数据库转储，请先创建数据库，然后再加载转储文件时指定数据库名称

mysqldump 优点

• 允许所有数据库、特定数据库或特定表
• 允许备份本地服务器或远程服务器
• 与存储引擎无关
• 可移植性强
• 适用于小规模导出，但不适用于完整备份解决方案

1.3、部分 mysqldump Options

所有Options详见底部附录或官网：https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

Option Name	Description
–add-drop-database	在每个 CREATE DATABASE 语句之前添加 DROP DATABASE 语句
–add-drop-table	在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句
–add-drop-trigger	在每个 CREATE TRIGGER 语句之前添加 DROP TRIGGER 语句
–add-locks	用 LOCK TABLES 和 UNLOCK TABLES 语句包围每个表转储
–all-databases	转储所有数据库中的所有表
–allow-keywords	允许创建作为关键字的列名

二、mysqlbinlog

二进制日志包含描述数据库更改（如表创建操作或表数据更改）的"事件"，它有两个重要用途：

• 对于复制，复制源服务器上的二进制日志提供要发送到副本的数据更改的记录。源将二进制日志中包含的事件发送到其副本，该副本执行这些事件以对源进行相同的数据更改。
• 某些数据恢复操作需要使用二进制日志。还原备份后，将重新执行备份后记录的二进制日志中的事件。这些事件使数据库从备份点更新。

2.1、使用二进制日志进行时间点恢复

时间点恢复的信息来源是完整备份操作后生成的一组二进制日志文件。若要启用二进制日志，请使用--log-bin= base_name选项启动服务器。如果未*base_name，*则默认名称是--pid 文件选项的值（默认情况下是主机的名称）：vi /etc/my.cnf

查看所有的binlog文件名、大小和当前正在记录日志的binlog：

show binary logs;

show master status；

查看日志内容：

mysqlbinlog /var/lib/mysql/binlog.000001

导出日志：

mysqlbinlog -v /var/lib/mysql/binlog.000002 > /mnt/log.txt

可在导出时对日志进行限制：

–start-position 指定从哪开始导出二进制日志

–stop-position 指定到哪结束

–start-datetime 从哪个时间开始，格式如"2021-01-27 11:25:56"

–stop-datetime 到哪个时间结束

将二进制日志文件中的事件应用于服务器

mysqlbinlog --start-position=1006 --stop-position=1868 /var/lib/mysql/binlog.000002 
         | mysql -u root -p

mysqld将数字扩展名追加到二进制日志基名，以生成二进制日志文件名称。每次服务器创建新日志文件时，该数字都会增加，从而创建一系列有序的文件。每次发生以下任何事件时，服务器都会在系列中创建一个新文件：

• 服务器已启动或重新启动
• 服务器刷新日志。--flush-logs
• 当前日志文件的大小达到max_binlog_size

使用日志恢复数据

做恢复增量备份前，需要恢复完全备份，再导入增量备份

mysql -uroot -p < /mnt/log.txt

导入增量备份

mysqlbinlog /var/lib/mysql/binlog.000002 | mysql -uroot -p

三、mysqlbackup

mysqlbackup是ORACLE公司提供的针对企业的备份软件，全名叫做MySQL Enterprise Backup，是一个收费的软件。官网：https://www.mysql.com/products/enterprise/backup.html

3.1、备份操作

基本语法，详见https://dev.mysql.com/doc/mysql-enterprise-backup/4.1/en/backup-commands-backup.html

mysqlbackup [--backup-dir=PATH][--backup-image=IMAGE] 
    		backup-to-image

mysqlbackup [--backup-dir=PATH][--backup-image=IMAGE] 
    		backup | backup-and-apply-log

备份整个 MySQL 实例

./mysqlbackup --user=root --password --host=127.0.0.1 
        --backup-image=/home/admin/backups/my.mbi 
        --backup-dir=/home/admin/backup-tmp 
        backup-to-image

Windows上备份

mysqlbackup -uroot -p 
		--backup-dir=E:\backup_data\datadir 
		--backup-image=E:\backup_data\my.mbi 
		backup-to-image

backup-to-image

生成保存备份数据的单个文件备份。在大多数情况下，单文件备份优先于使用备份命令创建的目录备份。该命令需要--backup-image 选项来指定目标文件。可用于将备份流式传输到存储设备或其他系统，而无需将数据存储在数据库服务器上。您可以指定--backup-image=-表示标准输出，允许将输出管道处理到另一个命令。为了避免将正常信息性消息与备份输出混合，消息、错误、警报和正常信息消息始终打印到标准错误流中。

该命令还要求使用--backup-dir选项提供临时文件夹来保存备份元数据和一些临时输出。但是请注意，除非使用--backup-image 流式传输备份映像，如果--backup-image未提供完整路径名称， mysqlbackup实际上将 --backup-image的值作为相对于--backup-dir 指定的目录的路径，从而将单文件备份存储在--backup-dir目录下。

backup

将数据备份到目录。在大多数情况下，使用备份到映像命令创建的单文件备份比目录备份更可取。

该命令仅执行完整备份过程的初始阶段。第二阶段稍后通过使用apply-log 命令再次运行mysqlbackup 来执行，该命令使备份保持一致。

backup-and-apply-log

备份和应用日志的组合。它不能用于增量备份。

3.2、验证操作

为了确保备份数据的完整性，MySQL 企业备份提供了一个验证命令，用于在创建备份或传输到其他系统后，通过备份页的校验和值验证备份。

mysqlbackup [STD-OPTIONS]
             [--backup-dir=PATH][--backup-image=IMAGE] 
validate

下面是用于验证备份映像的示例命令：

mysqlbackup -uroot --backup-image=/logs/fullimage.mi  validate

下面是用于验证备份目录的示例命令：

mysqlbackup -uroot --backup-dir=/logs/backupext validate

为了避免在损坏过严重的文件上花费过多的时间和资源，mysqlbackup在找到二十多个损坏的页面后停止验证 .ibd 文件，然后继续访问下一个文件。在这种情况下，操作摘要不会给出损坏页面的完整计数，而只显示"至少 20 页已损坏"。对于任何备份目录，操作只能验证 InnoDB 数据文件（和文件）。不能检测到备份目录中的其他文件类型（例如，文件损坏）的问题。

3.3、还原操作

还原操作将数据文件从备份还原到数据库服务器上的原始位置，或还原到其他所需位置。通常，还原过程要求数据库服务器已关闭（或者，至少不在还原数据的目录上操作）。

必须在目标服务器的配置文件中、在--defaults file选项指定的文件中或作为命令行选项指定选项datadir、innodb_log_files_in_group和innodb_log_file_size。

mysqlbackup [--backup-image=IMAGE]
            [--backup-dir=PATH]
            [--uncompress]
            copy-back-and-apply-log

mysqlbackup [STD-OPTIONS]
            [SERVER-REPOSITORY-OPTIONS]
            [--backup-dir=PATH]
            [--uncompress]
            copy-back

恢复数据库

./mysqlbackup  --defaults-file=/etc/mysql/my.cnf 
		--datadir=/var/lib/mysql  
  		--backup-image=/home/admin/backups/my.mbi 
  		--backup-dir=/home/admin/restore-tmp 
  		copy-back-and-apply-log

Windows上恢复

mysqlbackup 
--datadir="C:\ProgramData\MySQL\MySQL Server 8.0\Data" 
--backup-dir="E:\backup_data\backdir" 
--backup-image="E:\backup_data\my.mbi" 
--force 
copy-back-and-apply-log

–datadir 提供用于还原数据的数据目录的位置。您必须为任何还原操作指定此选项。

–backup-image 提供单文件备份的路径。

–backup-dir 提供空文件夹的位置，以存储还原过程中创建的所有临时文件。

copy-back-and-apply-log

在单个步骤中，将由 --backup-image选项指定的单文件备份或从 --backup-dir 选项指定的目录备份还原到服务器的数据目录，并执行对还原的数据执行应用日志操作，以使它们保持最新。与恢复单文件备份的多步骤方法（通常包括执行提取、解压缩、应用日志和复制回的连续步骤以还原压缩映像，或提取、应用日志和复制回恢复未压缩映像）相比，该命令使还原过程更简单、更快，还可以节省所需的磁盘空间。

以下是使用copy-back-and-apply-log进行不同类型的备份还原的一些特殊要求：

• 若要还原压缩目录或映像，请在命令行中包括–uncompress 选项。
• 若要还原单文件备份，除了使用–backup-image 选项指定备份映像的位置外，还要使用 --backup-dir选项提供用于存储还原过程中生成的临时文件的文件夹的位置。
• 若要还原单个文件增量备份，假设已还原完整备份（增量备份基于此备份）：
  • 在命令行中包括 --incremental选项。
  • 使用 --backup-image选项指定增量备份映像的位置
  • 使用--backup-dir选项提供用于存储还原过程中生成的临时文件的文件夹的位置。
• 若要还原增量备份目录，假设已还原完整备份（增量备份基于此备份）：
  在命令行中包括 --incremental选项。
  使用--backup-dir或--incremental-backup-dir选项指定增量备份目录。

copy-back

将文件从目录备份还原到 MySQL 服务器中的原始位置。

3.4、其他单文件备份操作

除了用于创建和还原单文件备份的命令（即备份到映像和复制备份和应用程序日志）之外，mysqlbackup还提供许多其他命令供您使用单文件备份。

image-to-backup-dir

将单个文件备份解压缩到完整备份目录结构。您可以指定解包的图像文件和目标目录的路径。

backup-dir-to-image

将现有备份目录打包到单个文件中。参数的值应为"-"（代表标准输出）或目录外的绝对路径。 指定--backup-image值（标准输出），将现有备份目录结构流式传输到磁带设备或将备份转移到其他服务器的命令。

list-image

显示单个文件备份的内容。列出图像中的所有文件和目录。

mysqlbackup --backup-image=/home/admin/backups/my.mbi [list-image][extract]

extract

从单个文件备份中解包单个文件或目录。它可用于故障排除或不需要完整备份数据的恢复。生成的文件或目录进入当前目录，或进入备份目录，如果使用--backup-dir 指定；在这两种情况下，目标目录都必须为空。

---

附录

全部 mysqldump Options

Option Name	Description
–add-drop-database	在每个 CREATE DATABASE 语句之前添加 DROP DATABASE 语句
–add-drop-table	在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句
–add-drop-trigger	在每个 CREATE TRIGGER 语句之前添加 DROP TRIGGER 语句
–add-locks	用 LOCK TABLES 和 UNLOCK TABLES 语句包围每个表转储
–all-databases	转储所有数据库中的所有表
–allow-keywords	允许创建作为关键字的列名
–apply-slave-statements	包括在 CHANGE MASTER 语句之前 STOP SLAVE ，在输出结束时 START SLAVE
–bind-address	使用指定的网络接口连接到 MySQL 服务器
–character-sets-dir	安装字符集的目录
–comments	向转储文件添加注释
–compact	产生更紧凑的输出
–compatible	生成与其他数据库系统或旧 MySQL 服务器更兼容的输出
–complete-insert	使用包含列名的完整 INSERT 语句
–compress	压缩客户端和服务器之间发送的所有信息
–create-options	在 CREATE table 语句中包含所有特定于 mysql 的表选项
–databases	将所有名称参数解释为数据库名称
–debug	编写调试日志
–debug-check	程序退出时打印调试信息
–debug-info	当程序退出时，打印调试信息、内存和 CPU 统计信息
–default-auth	要使用的验证插件
–default-character-set	指定默认字符集
–defaults-extra-file	除了通常的选项文件外，还要读取指定的选项文件
–defaults-file	只读指定的选项文件
–defaults-group-suffix	选项组后缀值
–delayed-insert	编写插入延迟语句而不是插入语句
–delete-master-logs	在主复制服务器上，执行转储操作后删除二进制日志
–disable-keys	对于每个表，用语句包围 INSERT 语句来禁用和启用键
–dump-date	如果给出了注释，则将转储日期包含为 “dump completed on” 注释
–dump-slave	包含 CHANGE MASTER 语句，该语句列出 master 的二进制日志坐标
–enable-cleartext-plugin	启用 cleartext 身份验证插件
–events	从转储数据库中转储事件
–extended-insert	使用多行 INSERT 语法
–fields-enclosed-by	此选项与 --tab 选项一起使用，其含义与 LOAD DATA 的对应子句相同
–fields-escaped-by	此选项与 --tab 选项一起使用，其含义与 LOAD DATA 的对应子句相同
–fields-optionally-enclosed-by	此选项与 --tab 选项一起使用，其含义与 LOAD DATA 的对应子句相同
–fields-terminated-by	此选项与 --tab 选项一起使用，其含义与 LOAD DATA 的对应子句相同
–flush-logs	在开始转储之前刷新 MySQL 服务器日志文件
–flush-privileges	在转储 mysql 数据库之后发出一个 FLUSH PRIVILEGES 语句
–force	即使在表转储期间发生 SQL 错误，也要继续
–help	显示帮助信息并退出
–hex-blob	使用十六进制表示法转储二进制列
–host	要连接到的主机 (IP 地址或主机名 )
–ignore-table	不转储指定的表
–include-master-host-port	在使用 --dump-slave 生成的 CHANGE MASTER 语句中包含 MASTER_HOST/MASTER_PORT 选项
–insert-ignore	编写 INSERT IGNORE 而不是 INSERT 语句
–lines-terminated-by	此选项与 --tab 选项一起使用，其含义与 LOAD DATA 的对应子句相同
–lock-all-tables	跨所有数据库锁定所有表
–lock-tables	转储前锁表
–log-error	在命名文件中添加警告和错误
–login-path	从 .mylogin.cnf 中读取登录路径选项
–master-data	将二进制日志文件名和位置写入输出
–max-allowed-packet	从服务器发送或接收的最大数据包长度
–net-buffer-length	TCP/IP 和套接字通信的缓冲区大小
–no-autocommit	将每个转储表的 INSERT 语句封装在 SET autocommit = 0 和 COMMIT 语句中
–no-create-db	不写 CREATE DATABASE 语句
–no-create-info	不写重新创建每个转储的表的 CREATE TABLE 语句
–no-data	不转储表内容
–no-defaults	不读取 default 选项文件
–no-set-names	–skip-set-charset 一样
–no-tablespaces	不写任何 CREATE LOGFILE GROUP or CREATE TABLESPACE 语句
–opt	–add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset 的简写
–order-by-primary	转储按其主键或第一个惟一索引排序的每个表的行
–password	连接到服务器时使用的密码
–pipe	在 Windows 上，使用命名管道连接到服务器
–plugin-dir	安装插件的目录
–port	用于连接的 TCP/IP 端口号
–print-defaults	打印默认选项
–protocol	使用的连接协议
–quick	每次从服务器中逐行检索表的行
–quote-names	反勾字符中的引号标识符
–replace	编写 REPLACE 语句而不是 INSERT 语句
–result-file	直接输出到给定的文件
–routines	从转储数据库中转储存储的例程 ( 过程和函数 )
–secure-auth	不发送旧 (4.1 前 ) 格式的密码到服务器 , 5.7.5 弃用
–set-charset	将集合名 default_character_set 添加到输出
–set-gtid-purged	是否添加 SET @@GLOBAL.GTID_PURGED 输出
–shared-memory-base-name	用于 shared-memory 连接的共享内存的名称
–single-transaction	在从服务器转储数据之前发出 BEGIN SQL 语句
–skip-add-drop-table	不要在每个 CREATE TABLE 语句之前添加 DROP TABLE 语句
–skip-add-locks	不要添加锁
–skip-comments	不向转储文件添加注释
–skip-compact	不生产更紧凑的输出
–skip-disable-keys	不要禁用键
–skip-extended-insert	关掉 extended-insert
–skip-opt	关闭由 --opt 设置的选项
–skip-quick	不从服务器一次一行检索表的行
–skip-quote-names	不要引用标识符
–skip-set-charset	不写 SET NAMES 语句
–skip-triggers	不要转储触发器
–skip-tz-utc	关掉 tz-utc
–socket	要连接到本地主机，请使用 Unix 套接字文件
–ssl	启用加密连接
–ssl-ca	包含受信任 SSL 证书颁发机构列表的文件
–ssl-capath	包含可信 SSL 证书颁发机构证书文件的目录
–ssl-cert	包含 X.509 证书的文件
–ssl-cipher	连接加密的允许密码列表
–ssl-crl	包含证书撤销列表的文件
–ssl-crlpath	包含证书撤销列表文件的目录
–ssl-key	包含 X.509 键的文件
–ssl-mode	连接到服务器的安全状态
–ssl-verify-server-cert	根据服务器证书公共名称标识验证主机名
–tab	生成制表符分隔的数据文件
–tables	覆盖 --databases 或 -B 选项
–triggers	每个转储表的转储触发器
–tz-utc	将 SET TIME_ZONE=’+00:00’ 添加到转储文件
–user	连接到服务器时使用的 MySQL 用户名
–verbose	详细模式
–version	显示版本信息并退出
–where	只转储由给定 WHERE 条件选择的行
–verbose	详细模式
–version	显示版本信息并退出
–where	只转储由给定 WHERE 条件选择的行
–xml	生成 XML 输出