运维实战：备份恢复之mysqldump

运维实战：备份恢复之mysqldump

      mysqldump是MySQL的一种备份工具，属于逻辑备份。其原理是将数据库中的DML操作语句备份导出为以 .sql 结尾的文件。

一、特点

1. 备份时会锁表，默认是备份一张表锁一张表，也可以通过参数指明锁全表进行备份；
2. 备份会造成数据不一致；
3. 1GB以上的数据不适用，会带来延迟；

二、标准备份语句（InnoDB引擎）

mysqldump -u'root' -p'PASSWD' --single-transaction --events -R --triggers\
--master-data=2 --flush-logs -B hehe|gzip > /home/backup/hehe.sql.gz

三、常用参数

--lock-all-tables
# 锁定所有库的所有表;

--lock-tables or -x
# 对于每个单独的数据库，在启动备份之前锁定其所有表;

--single-transaction
# 备份事务;

--events or -E
# 备份指定数据库相关的所有event scheduler;

-R
# 备份指定的数据库相关的所有存储过程和存储函数;

--triggers
# 备份表相关的触发器;

--master-data=[0|1]
1 # 记录为CHANGE MASTER TO 语句，此语句不被注释;
2 # 记录为注释的CHANGE MASTER TO语句

--flush-logs or -F
# 日志滚动，将新的DML语句写入滚动后的binlog日志文件;

--compact
# 压缩模式，更少的输出用于做测试用;

--all-database or -A
# 备份全库

--database or -B
# 备份单库，且使用-B的同时会加入use database语句;

--defaults-character-set [character]
# 字符集，指明导出的字符集;

--no-data or -d
# 只导出表结构;

--quick or -q
# 快速导出;

--complete-insert or -c
# 将多个insert整合成一条，提高插入数据的效率，在Myisam引擎中效果显著;

--ignore-table=[db.tb1]
# 

--no-create-db or -n
# 只导出数据，而不添加 CREATE DATABASE 语句;

--no-create-info or -t
# 只导出数据，而不添加CREATE TABLE 语句;

四、常用备份方案

1. 导出整个数据库(包括数据库中的数据）
mysqldump -u'username' -p'PWD' dbname > dbname.sql
mysqldump -u'username' -p'PWD' -B dbname > dbname.sql
mysqldump -u'username' -p'PWD' -B dbname --master-data=1 > dbname.sql
   
2）导出数据库中的表结构
mysqldump -u'username' -p'PWD' -d dbname > dbname.sql  
   
3）导出数据库中的某张表
mysqldump -u'username' -p'PWD' dbname tbname > tbname.sql  
 
4）备份某个库内的多张表
mysqldump -u'username' -p'PWD' dbname tb1 tb2 > alltb.sql  
  
5）导出数据库中的某张数据表的表结构（不含数据）
mysqldump -u'username' -p'PWD' -d dbname tablename > tb_struct.sql

  五、恢复操作

1）source导入
source dbdata.sql;

2）mysql导入
mysql -u'username' -p'PWD' --default-character-set=utf8 < dbdata.sql

3）导入压缩文件
zcat dbdata.sql.gz|mysql -u'username' -p'PWD' --default-character-set=utf8

六、如何防止MySQL出现乱码情况

      MySQL出现乱码的原因大多数是因为字符集不一致。对症下药，理应保证客户端、服务端、操作系统、程序、终端字符集一致即可。