MySQL 逻辑备份 mysqldump
逻辑备份特点
- 备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。
- 效率相对较低
在日常工作中，我们会使用 mysqldump 命令创建SQL格式的转储文件来备份数据库。或者我们把数据导出后做数据迁移，主从复制等操作。mysqldump是一个逻辑备份工具,复制原始的数据库对象定义和表数据产生一组可执行的SQL语句。 默认情况下,生成insert语句，也能生成其它分隔符的输出或XML格式的文件。
特点
- 自动记录position位置。
show master status\G;
- 可用性，一致性
锁表机制
用法
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锁定所有数据库中的所有表。这是通过在整个转储期间采用全局读锁来实现。
有几个选项控制mysqldump如何 处理存储的程序（存储过程和函数，触发器和事件）：
-
--events：备份事件计划程序事件 -
--routines：备份存储过程和函数 -
--triggers：备份表的触发器
--triggers默认情况下启用 该选项，以便在转储表时，它们伴随着它们具有的任何触发器。
默认情况下要禁用这些选项。需要明确设置这些选项：
--skip-events， --skip-routines, --skip-triggers。
日常用法
备份所有库
// 先配置用户名和密码
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
备份指定的多个库
// 为了考虑篇幅，请自行添加指定用户名密码参数和指定服务器的参数
// --defaults-file=~/.mysql_user -hip
shell> mysqldump --databases db1 db2 db3 > `date +%FT%H_%M_%S`dump_all.sql
备份指定库的指定几个表
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的优势：
- 可以查看或者编辑十分方便，它也可以灵活性的恢复之前的数据。
- 不关心底层的存储引擎，既适用于支持事务的，也适用于不支持事务的表。
- 不过它不能作为一个快速备份大量的数据或可伸缩的解决方案。如果数据库过大,即使备份步骤需要的时间不算太久,但有可能恢复数据的速度也会非常慢,因为它涉及的SQL语句插入磁盘I/O,创建索引等等。 对于大规模的备份和恢复,更合适的做法是物理备份,复制其原始格式的数据文件,可以快速恢复。
恢复
shell> mysql -uroot -p123456 db1 < 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
利用 mysqldump 创建增量备份
shell> mysqldump --single-transaction --flush-logs --master-data=2 \
--all-databases > backup_sunday_1_PM.sql
该
--flush-logs选项会导致服务器刷新其日志。
在星期一下午1点，我们可以通过刷新日志来开始新的二进制日志文件来创建增量备份。例如，执行mysqladmin flush-logs命令会创建gbichot2-bin.000008。周日下午1点完整备份和周一下午1点之间的所有更改都将在gbichot2-bin.000007文件中。此增量备份很重要，因此最好将其复制到安全的地方。
在星期二下午1点，执行另一个mysqladmin flush-logs命令。周一下午1点到周二下午1点之间的所有更改都将在gbichot2-bin.000008文件中（也应该在安全的地方复制）。
使用备份进行恢复
现在，假设我们在星期三上午8点发生了灾难性的崩溃，需要从备份中恢复。要恢复，首先我们恢复上一次完整备份（从星期日下午1点开始）。完整备份文件只是一组SQL语句，因此恢复它非常简单：
shell> mysql < backup_sunday_1_PM.sql
此时，数据将恢复到截至周日下午1点的状态。要恢复自那时以来所做的更改，我们必须使用增量备份; 也就是 gbichot2-bin.000007和 gbichot2-bin.000008二进制日志文件。必要时从备份位置获取文件，然后按如下方式处理其内容：
shell> mysqlbinlog gbichot2-bin.000007 gbichot2-bin.000008 | mysql
我们现在已经将数据恢复到周二下午1点的状态，但仍然缺少从该日期到崩溃日期的更改。为了不丢失它们，我们需要让MySQL服务器将其MySQL二进制日志存储到与存储其数据文件的地方不同的安全位置（RAID磁盘，SAN，…），以便这些日志不会在被破坏的磁盘上。（也就是说，我们可以使用一个--log-bin选项来启动服务器，该 选项指定与数据目录所在的物理设备不同的物理设备上的位置。这样，即使包含该目录的设备丢失，日志也是安全的。）如果我们做到了这一点，我们会有gbichot2-bin.000009 手头的文件（以及任何后续文件），我们可以使用mysqlbinlog和 mysql来应用它们来恢复最新的数据更改，直到崩溃时都没有损失：
shell> mysqlbinlog gbichot2-bin.000009 ... | mysql
有关使用mysqlbinlog 处理二进制日志文件的更多信息
使用二进制日志进行时间点（增量）恢复
时间点恢复基于以下原则：
时间点恢复的信息源是由完全备份操作之后生成的二进制日志文件表示的增量备份集。
要从二进制日志还原数据，您必须知道当前二进制日志文件的名称和位置。
要查看所有二进制日志文件的列表，请使用以下语句：
mysql> SHOW BINARY LOGS;
要确定当前二进制日志文件的名称，请发出以下语句：
mysql> SHOW MASTER STATUS;
-
从二进制日志执行事件会导致重做它们所代表的数据修改。这样可以在给定的时间范围内恢复数据更改。要从二进制日志执行事件，请使用mysql客户端处理 mysqlbinlog输出 ：
shell> mysqlbinlog binlog_files | mysql -u root -p -
在需要确定事件时间或位置以在执行事件之前选择部分日志内容时，查看日志内容非常有用。要从日志中查看事件，请将mysqlbinlog输出发送 到分页程序：
shell> mysqlbinlog binlog_files | more或者，将输出保存在文件中并在文本编辑器中查看文件：
shell> mysqlbinlog binlog_files > tmpfile shell> ... edit tmpfile ... -
将输出保存在文件中非常有用，可以在删除某些事件（例如意外事件）时执行日志内容
DROP DATABASE。您可以在执行其内容之前从文件中删除任何不执行的语句。编辑文件后，执行如下内容：shell> mysql -u root -p < tmpfile
472
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
