MySQL备份与恢复——基于mysqldump 逻辑备份技术文

一、说明

mysqldump是mysql软件内自带的逻辑备份工具，把数据从MySQL库中以逻辑的SQL语句的形式直接输出或生成备份的文件的过程。该工具只支持单线程工作。支持远程导出。

1.1 Mysqldump导出内部流程

Eg：mysqldump –h127.0.0.1 -uroot –pmysql –R –single-transaction –master-data=2 --q –e –all-databases

二、参数列表

参考文档：https: // blog.csdn.net/stephenxu111/article/details/9310715

三、导出示例

涉及参数 secure-file-priv

3.1 SQL Format

3.1.1 全库导出

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

3.1.2 单库/多库导出

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --databases test [employees ceshi] > test.sql

3.1.3 单表/多表导出

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R  test a [b c] > double.sql

注：不指定databases参数，不包含建库语句

3.1.4 按条件导出

mysqldump -uroot -pmysql --single-transaction --master-data=2 test test --where="id<6  and name='a'" > test.sql

3.1.5 只导出结构 

mysqldump -uroot -pmysql --no-data --routines --events --databases test [ceshi]> dump-defs.sql

注：可用于测试升级

3.1.6 只导出数据

mysqldump -uroot -pmysql --no-create-info --routines --events --databases test [ceshi]> dump-data.sql

3.1.7 远程导出并导入

mysqldump --host=h1 -uroot -proot  remote_db1 tablename | mysql --host=h2 -uroot -proot db2

注：db2数据库必须存在

3.2 Delimited-Text Format

注：必须指定secure-file-priv 安全目录参数！！！否则报错无法导出

3.2.1 单库导出

mysqldump --tab=/tmp/12 --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by='\n' DBNAME

3.2.2 单表/多表导出

mysqldump --tab=/tmp/12 --fields-terminated-by=',' --fields-optionally-enclosed-by='"' --lines-terminated-by='\n' DBNAME TABNAME1 [TABNAME2]

注：每张表格生成两个文件

*.sql   属于执行命令用户(root)   包含建表语句

*.txt   属于数据库用户(mysql)    包含数据

四、案例

4.1 案例一: 测试 --all-databases参数

mysqldump -uroot -pmysql --single-transaction --master-data=2  --all-databases > full_1.sql

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

注：--all-database 参数是不包含存储过程，事件等，需要格外指定参数导出。

4.2 案例二: mysqldump 期间是否可以DDL操作

测试环境：

测试语句：

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

测试目的：针对INNODB表，开启事务性备份是否会堵塞DDL操作

测试方法：根据general 通用日志，判断表是否已经完成备份。对已备份完毕，正在完成备份，未开始备份表分别做DDL操作，判断是否会堵塞DDL操作

情况一：对已完成备份表做DDL操作

1.开启全库备份

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

2.观察通用日志，发现已经employees.departments表已经完成备份

3.进行DDL操作，正常进行

4.备份正常完成

情况二：对正在备份表做DDL操作

1.开启全库备份

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

2.观察通用日志，发现正在对xh.test表进行备份

3.进行DDL操作，并堵塞

4.查看进程

5.备份正常完成

情况三：对未开始备份表做DDL操作

1.开启全库备份

mysqldump -uroot -pmysql --single-transaction --master-data=2  -E -R --all-databases > full.sql

2.观察通用日志，现在仍在备份employees数据库，还未备份到xh库

3.进行DDL操作，正常进行

4.导出报错

结论：使用 --single-transaction 参数导出数据，DDL操作只会影响正在备份的表，已经完成备份或还未备份的表不会被堵塞。但是对未完成备份的表做完DDL操作，当备份到这张被DDL之后的表，mysqldump进程被中断并报错。