MySQL 逻辑备份恢复

分类：MySQL| 发布时间：2019-11-05 21:30:00

MySQL 逻辑备份一般可以通过 mysqldump 命令来进行。本文主要描述如何对 MySQL 进行逻辑备份与恢复。

逻辑备份

如果是在 MySQL 所在的主机运行 mysqldump 可以用以下命令进行备份：

$ mysqldump \
    --password \
    --user="root" \
    --single-transaction \
    --quick \
    --socket=/var/run/mysqld/mysqld.sock \
    --set-gtid-purged=off \
    --protocol=socket \
    --add-drop-database \
    --add-drop-table \
    --hex-blob \
    --routines "test" > test.sql

--password 表示需要密码，运行命令后，mysqldump 会提示你输入密码
--user 表示用于登录的用户名
--single-transaction 表示在单个事务中进行 dump，当隔离级别设置为可重复读并且存储引擎支持可重复读的情况下，可以得到一致的备份
--quick 表示不缓存 MySQL 服务端发来的数据
--socket=xxx 和 --protocol=socket 表示通过 socket 协议进行通信
--set-gtid-purged=off MySQL 5.6.9 开始加入此选项，表示不设置 GTID Purged，除非你需要使用此备份集进行全量的逻辑备份恢复，否则将其设为 off 即可
--add-drop-database 在创建数据库命令前添加 DROP DATABASE;
--add-drop-table 在创建表命令前添加 DROP TABLE
--hex-blob 以 16 进制的形式 dump 二进制数据 (BINARY, VARBINARY, BLOB)
--routines dump 出存储过程
"test" 要 dump 的数据库名称

如果是在运行 MySQL 实例外的主机运行 mysqldump 可以用以下命令进行备份：

$ mysqldump \
    --password\
    --user="root" \
    --single-transaction \
    --quick \
    --host=10.0.3.120 \
    --port=3306 \
    --set-gtid-purged=off \
    --protocol=tcp \
    --add-drop-database \
    --add-drop-table \
    --hex-blob \
    --routines "test" > test.sql

跟上面的区别为：

使用 --host 参数来指定要连接的 MySQL IP
--port 指定 MySQL 的端口
--protocol=tcp 表示使用 TCP 协议进行通信

在上面的例子中，我们都是使用了 --password 参数并且不指定密码的形式，让 mysqldump 运行时向我们索要密码。如何运行 mysqldump 使得 mysqldump 能以无交互的方式运行备份呢： 一种方式是直接在 --password 参数后指定密码。显然这种方式并不是一种好的方法，这种方法会泄漏 MySQL 的登录密码。 另一种方式是创建一个如下的配置文件:

[mysqldump]
password=123456

然后运行的时候，去掉 --password 参数，添加 --defaults-extra-file 参数，如下：

$ mysqldump \
    --defaults-extra-file="./conf.conf" \
    --user="root" \
    ...

如果需要进行全备：

$ mysqldump \
    --password \
    --user="root" \
    --single-transaction \
    --quick \
    --socket=/var/run/mysqld/mysqld.sock \
    --protocol=socket \
    --add-drop-database \
    --add-drop-table \
    --hex-blob \
    --routines \
    --all-databases > all.sql

逻辑恢复

MySQL 逻辑恢复相当简单，首先创建一个新的数据库：

CREATE DATABASE test2;

然后运行命令：

$ mysql \
    --password \
    --user="root" \
    --host=10.0.3.120 \
    --port=3306 \
    --protocol=tcp \
    --force \
    test2 < test.sql

--force 表示即使发生错误，也继续进行

其他参数和 mysqldump 一样，这里不再赘述。

如果不希望手动输入密码，可以跟 mysqldump 一样，使用 --defaults-extra-file 参数，不同的是配置文件需要修改为：

[client]
password=123456

凌云的博客

行胜于言