MySQL 逻辑备份 mysqldump

逻辑备份特点

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

特点

1. 自动记录position位置。

show master  status\G;

2. 可用性，一致性

锁表机制

用法

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

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

日常用法

备份所有库

// 先配置用户名和密码
shell> vi ~/.mysql_user
[mysqldump]
user=root
password=123

shell> mysqldump --defaults-file=~/.mysql_user -h172.16.153.10 --all-databases > `date +%FT%H_%M_%S`dump_all.sql
# 不包含 INFORMATION_SCHEMA,performance_schema,sys

备份指定的多个库

shell> mysqldump db1 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 -h"ip地址" -p"密码" 库名 表名 < dump.sql

或者，在MySQL中，使用source命令：

mysql> source dump.sql

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

shell> mysqladmin create db1

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

shell> mysql db1 < dump.sql

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

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

Example

shell> mysql --defaults-file=~/.mysql_user < /backup/2016-12-08-04-mysql-all.sql