凌云的博客

行胜于言

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