MySQL 逻辑备份 mysqldump

逻辑备份特点

• 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
• 效率相对较低

在日常工作中，我们会使用 mysqldump 命令创建SQL格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主从复制等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句，也能生成其它分隔符的输出或XML格式的文件。

特点

1. 自动记录position位置。

show master  status\G;

1. 可用性，一致性

锁表机制

用法

mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql

/*查看帮助*/
mysqldump --help

常见参数

• --single-transaction 备份前启用一个事务，保证数据一致性。
  仅对 InnoDB 存储引擎有效。还有需要保证没有其他的连接正在使用以下语句： ALTER TABLE， CREATE TABLE，DROP TABLE， RENAME TABLE， TRUNCATE TABLE
• -l, --lock tables 对于不支持事务的存储引擎的表备份使用此选项，比如 MySAM, 可以保证备份期间的数据一致性。会依次对正在备份的每个数据库中的所有表进行锁表操作，此时只可以读。和 --single-transaction 互斥。
• -x, --lock-all-tables 锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。

有几个选项控制如何 处理存储的程序（存储过程和函数，触发器和事件）：

• --events：备份事件计划程序事件
• --routines：备份存储过程和函数
• --triggers：备份表的触发器

--triggers默认情况下启用 该选项，以便在转储表时，它们伴随着它们具有的任何触发器。

以上的选项都是默认开启的，如果不想备份以上的内容，需要明确设置这些选项：
--skip-events， --skip-routines, --skip-triggers。

日常用法

备份所有库

shell> mysqldump -uroot -p'*****'  -h  172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys

备份指定的多个库

// 为了考虑篇幅，请自行添加指定用户名密码参数和指定服务器的参数

shell> mysqldump --databases time1 time2 time3 > `date +%FT%H_%M_%S`dump_all.sql

备份指定库的指定几个表

shell> mysqldump time t1 t3 t7 > dump.sql

其他参数

• --master-data=0|1|2

  服务器的二进制日志必须打开

  0 不记录二进制日志文件及位置：

  1 以CHANGE MASTER TO 的方式记录位置，可用于恢复后直接启动从服务器：

  2 以CHANGE MASTER TO 的方式记录位置，但默认被注释：

• --dump-slave 用于在slave上dump数据，建立新的slave。因为我们在使用mysqldump时会锁表，所以大多数情况下，我们的导出操作一般会在只读备库上做，为了获取主库的Relay_Master_Log_File(二进制日志)和Exec_Master_Log_Pos(主服务器二进制日志中数据所处的位置)，需要用到这个参数，不过这个参数只有在5.7以后的才会有

• --no-data, -d 不导出任何数据，只导出数据库表结构

• --lock-all-tables: 锁定所有表,适用对MyISAM引擎的表开始备份前，先锁定所有表。

优势

mysqldump的优势：

1. 可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。
2. 不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。
3. 不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复。

恢复

shell> mysql -uroot -p123456   time1  < dump.sql

使用 source命令：

mysql> source dump.sql

如果文件是不包含CREATE DATABASE和 USE语句的单数据库转储 ，请首先创建数据库（如有必要）：

shell> mysqladmin create time1

然后在加载转储文件时指定数据库名称：

shell> mysql time1 < dump.sql

或者，在mysql中创建数据库，将其选为默认数据库，然后加载转储文件：

mysql> CREATE DATABASE IF NOT EXISTS time1;
mysql> USE time1;
mysql>source dump.sql

Example

shell> mysql --defaults-file=~/.mysql_user < /backup/2020-11-11-11-mysql-all.sql