mysqldump 数据库备份简记

为了在误操作时恢复数据，对数据库备份是非常必要的，MySQL 自带了 mysqldump 命令来帮助我们实现简单的数据库备份。

数据库备份可以分为物理备份和逻辑备份，按照备份的数据内容页可以分为全量备份和增量备份，mysqldump 是用来进行逻辑备份的命令。

一. mysqldump 全量备份

最简单的 mysqldump 命令使用方法如下:

mysqldump -uroot -p demo_db > demo_db.bkp.1117.sql

上面命令表示将 demo_db 数据库 导出到 demo_db.bkp.1117.sql 文件中，实现了一次全量的备份，-u 表示用户名，-p 表示输入密码。下面是 mysqldump 命令的一些常用的参数

1. mysqldump 常用参数

• -u: 指定用户
• -p: 指定密码
• –single-transaction: 确保事务性操作，只对 innodb 有效，保证备份期间没有 DDL 操作
• -l (–lock-table): 对于非 Innodb 引擎的备份进行锁表，只能进行读操作。与 single-transaction 互斥
• -x，–lock-all-table: 给实例下的所有数据库的表进行加锁，保证一致性，该参数会导致在备份过程中数据库只读，不可写
• -d: 只备份表结构，不备份数据
• –master-data: 有两个值: 1 和 2。1 时只记录change master 语句，为 2 时change master 会注释掉，建议设置为 2
• –all-database: 备份 MySQL 实例下的所有数据库
• –database: 指定对应的数据库进行备份
• -R, -routines: 备份所有的存储过程
• –tiggers: 备份触发器
• -E, –events: 备份数据库中的调度时间
• –hex-blob: 将对 blog/binary 等格式的数据转为 16 进制形式进行保存
• -tab=path: 在指定路径下分别生成结构文件和数据文件，会对每个表分别生成一个记录表结构的 sql 文件和记录数据的 txt 文件
• -w,–where= 过滤条件，对于单表进行过滤条件的备份

2. 数据库的全量备份

看了上面的参数，下面我们拓展下上面的语句，执行一次对 demo_db 的全量备份，命令如下:

mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db > demo_db.bkp.1117.sql

上面语句在备份了数据库结构和数据的同时，还会备份存储过程，触发器和调度事件，并且在备份的时候不允许写操作，–master-data=2 参数会注释 change master 语句, 语句内容如下，记录了记录备份操作的二进制日志文件和时间点，对于之后使用 mysqlbinlog 进行增量备份非常有用。

 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=834501307;

3. 备份数据库中的表

有时候并不需要对整个数据库进行备份，只需要备份其中的表即可，这时可以直接在数据库后面跟表名即可，下面是备份 demo_db 中的 user 表的命令:

mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db user > demo_db.bkp.user.1117.sql

4. 使用 where 条件过滤进行单表备份

where 语句可以用来指定过滤条件，从而限定要备份的数据，不过只适用于单表备份, 下面是使用 where 进行备份的实例，在 demo_db 中有一张 用户订单关系表，我们要备份 user_id 为 1 到 999 的关系数据

mysqldump -uroot -p --master-data=2 --single-transaction --where "user_id>0 and user_id < 1000" demo_db user_order_ship > demo_db.bkp.user_order_ship.sql  

5. 使用 –tab 指定备份的目录

通过 –tab 指定备份的目录，会在目录下会分别生成结构文件和数据文件, 下面语句，这里我们指定的目录为 /tmp/backup_db 目录，mysqldump 命令会将 demo_db 中的每张表都导出为一个记录表结构的 sql 文件和记录数据的 txt 文件。

mysqldump -uroot -p --master-data=2 --single-transaction -R -E --triggers --tab='/tmp/backup_db' demo_db

注意

• 因为需要数据库对外写数据，因此需要授予 file 权限，授予语句如下:

grant file, select, reload, lock tables, replication client, show view, event, process on *.* to 'root'@'localhost';

这里我们授予了 file, select 等权限，保证 mysqldump 可以顺利执行

• 在MySQL 5.7 中, secure_file_priv 变量确定了 MySQL 可以往外写文件的路径，默认是 NULL ，表示不允许对外写文件，设置为 “” 后表示可以对外的任意路径写文件，也可以指定路径进行授予写权限，一般在 my.cnf 文件中指定为 “” 即可。在使用 –tab 时 尤其要注意，指定的目录是被 MySQL 拥有写权限的

6. 恢复

备份完数据后就可以在之前数据出问题的时候进行一次恢复了， mysqldump 后的恢复是单线程的，其性能完全取决于服务器的 IO 性能和 MySQL 实例的性能，当数据量
很大时可能需要执行很长时间

【1】两种基本的恢复语句

下面两条命令均可以将备份好的数据导入到对应的数据库中

 在 mysql 下调用: mysql> source demo_db.bkp.1117.sql
 在命令行执行: mysql -uroot -p demo_db < demo_db.bkp.1117.sql

【2】对误操作的一些恢复方法

通过联结表查出备份数据库和误操作数据库数据的差别，将相差的数据读取出来在写入到误操作的数据库中

【3】 –tab 备份后的单表恢复

将表结构导入到数据库中

mysql -uroot -p demo_db < user.sql;

在数据库中执行加载数据文件命令，将数据导入到指定表中

 mysql> LOAD DATA INFILE "/tmp/backup_db/user.txt" into table user

二. binlog 增量备份

一般来说全量备份虽然可靠，但是当数据量过大时会非常耗时，因此更常见的做法时在指定的时间点做全量备份，在两次全量备份之间做增量备份，此时需要用到 MySQL 的二进制文件，下面是一次误删数据后利用二进制文件进行恢复的操作过程。

1. 首先是一次全备操作

mysqldump -uroot -p --master-data=2 --single-transaction --routines --triggers --events demo_db > demo_db.bkp.1117.sql

2. 全备后的数据删除

假设我们的数据库中有一张 message 记录消息的表，我们误删了其中的 100 条数据

DELETE FROM message limit 100;

3. 发现错误后，首先进行一次全量恢复

发现数据库操作错误后，我们首先将数据恢复到上次全量备份时的数据

mysql -uroot -p demo_db < demo_db.bkp.1117.sql

4. 查看备份文件中的 change master 语句，

获取记录的二进制日志和时间点，记录的二进制文件为 mysql-bin.000006，时间节点为 834501307

 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=834501307;

5. mysqlbinlog 分析二进制日志文件

我们通过 mysqlbinlog 命令查看日志，找到最近的一次删除的时间点。关于 mysqlbinlog 的使用请自行查阅 MySQL 的官方文档，这里不再赘述。

mysqlbinlog --base64-output=decode-rows -vv --start-position=667610711 --database=demo_db mysql-bin.000006 | grep -B3 DELETE | more

获取的内容如下:

这里假定上次删除的时间节点为 11112222， 那么我们接下来的任务就是分析日志，获取上次全量备份到本次误删除之间所有的数据库操作的 sql

6. 导出两次节点之间的操作 sql

备份时的节点为 667610711，误删除时的节点为 11112222，现在只需要将两个节点之前的所有写数据的操作导出即可，命令如下

mysqlbinlog --start-position=667610711 --stop-position=11112222， --database=demo_db mysql-bin.000006 > demo_dbbinlog_backup_1117.sql

7. 数据恢复

获取到 sql 文件后就可以进行恢复操作了，直接执行 sql 文件即可

mysql -uroot -p gravity_1115 < mysql_binlog_backup_1115.sql

上面就是整个的增量恢复的过程，要做到这一点就需要要对二进制文件进行实时的备份，其实就是对二进制文件的一个复制过程，命令如下:

# 授权语句
grant replication slave on *.* to 'root'@'localhost' identify by 111111;
# 二进制实时备份命令
mysqlbinlog --raw --read-from-remote-server --stop-never --host localhost --port 3306 -uroot -p mysql-bin.000006

参数说明:

• –raw 输出的是一个 raw 格式的二进制日志
• –read-from-remote-server 从 MySQL 服务器读取日志
• –stop-never 表示不会停止，一直在后台进行实时备份

在进行二进制日志恢复的时候可能会遇到一个问题，如果在该段时间点内有新建表的话，在全量恢复时并不会删除新建的表，在进行增量恢复时可能会报错，这里需要自行将新建的表进行删除，具体的做法因人而宜，这里不再赘述，以后工作学习中碰到的话再做研究。以上就是 mysqldump 命令备份与恢复的基本操作了。