MySQL数据库的备份和恢复之一

喜大普奔，干了一个月的MySQL DBA的工作，又学到了一部分知识。

记录一下，以备不时只需

mysqldump 的备份其实很麻烦，要考虑很多因素

备份时候不能锁表
恢复的时候要快
有二进制数据的话需要备份二进制数据
有触发器、存储过程的都备份
通常 mysqldump 是做每天的fullbackup，要为之后的 binlog 做好准备，万一要恢复要提前做好准备

我们一点一点来说：

一、备份时不能锁表

1--single-transaction
2通过将导出操作封装在一个事务内来使得导出的数据是一个一致性快照
3该选项在导出数据之前提交一个BEGIN SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时数据库的一致性状态。
4它只适用于InnoDB存储引擎。
5本选项和--lock-tables选项是互斥的，因为LOCK TABLES会使任何挂起的事务隐含提交。

二、恢复时要尽量快

1--opt
2等同于--add-drop-table,  --add-locks, --create-options, --quick, --extended-insert, --lock-tables,  --set-charset, --disable-keys 该选项默认开启,  可以用--skip-opt禁用.
3
4--extended-insert,  -e
5--extended-insert=false
6使用具有多个VALUES列的INSERT语法。这样使导出文件更小，并加速导入时的速度。
7默认为打开状态，使用--skip-extended-insert取消选项。

三、有二进制的按二进制备份

1--hex-blob
2使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用该选项。影响到的字段类型有BINARY、VARBINARY、BLOB。

四、有触发器、存储过程的都备份

1--triggers
2导出触发器。该选项默认启用，用--skip-triggers禁用它。
3--routines, -R
4导出存储过程以及自定义函数

五、为之后如果要做binlog恢复提前做好准备

 1--flush-logs
 2开始导出之前刷新binlog日志。
 3请注意：假如一次导出多个数据库(使用选项--databases或者--all-databases)，将会逐个数据库刷新日志。
 4除非使用--lock-all-tables或者--master-data外。在这种情况下，日志将会被刷新一次，相应的所以表同时被锁定。
 5因此，如果打算同时导出和刷新日志应该使用--lock-all-tables 或者--master-data 和--flush-logs
 6
 7--master-data
 8在导出的时候同时生成binlog文件名和位置在导出的文件开头。
 9这个非常重要。binlog 的文件和位置可以从这里拿到。
10例如：
11-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=157;
12
13该选项将binlog的位置和文件名追加到输出文件中。
14如果为1，将会输出CHANGE MASTER 命令；
15如果为2，输出的CHANGE  MASTER命令前添加注释信息。
16该选项将打开--lock-all-tables 选项，除非--single-transaction也被指定（在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项）。
17该选项自动关闭--lock-tables选项。
18mysqldump  -uroot -p --host=localhost --all-databases --master-data=1;
19mysqldump  -uroot -p --host=localhost --all-databases --master-data=2;

那么综上所述，总结一行非常使用的备份语句：

1mysqldump -uroot -p  -h 192.168.1.35 -P3306 --opt --triggers -R --hex-blob  --single-transaction --flush-logs --master-data=2 -B 库名  > 库名.sql

MySQL数据库的备份和恢复之一

相关文章