MariaDB/MySQL备份和恢复(一)：mysqldump工具用法详述【转】

本人博客搬家(本站无精力更新和维护)：骏马金龙www.junmajinlong.com

MariaDB/MySQL备份恢复系列：
备份和恢复(一)：mysqldump工具用法详述
备份和恢复(二)：导入、导出表数据
备份和恢复(三)：xtrabackup用法和原理详述

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump默认会从配置文件中的mysqldump段读取选项，配置文件读取的顺序为：

/etc/my.cnf --> /etc/mysql/my.cnf --> /usr/local/mysql/etc/my.cnf --> ~/.my.cnf

select ... into outfile。

--fields-terminated-by=name            
--fields-enclosed-by=name              
--fields-optionally-enclosed-by=name   
--fields-escaped-by=name               
--lines-terminated-by=name  

3.2.5 使用筛选选项示例

--all-databases, -A  
--databases, -B  
--ignore-table=db_name.tbl_name  
-d, --no-data       
--events, -E  
--routines, -R   
--triggers  
--tables 
--where='where_condition', -w 'where_condition'  

筛选数据库此处不再赘述。

--ignore-table表示忽略数据库中的某张表不被dump。要忽略多张表的时候多次写该选项即可。例如忽略backuptest.student和backuptest1.student1。

shell> mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --ignore-table backuptest.student --ignore-table backuptest1.student1 --databases backuptest backuptest1 >/tmp/backuptest.bak
shell> grep -i 'student' /tmp/backuptest.bak |wc -l
0

--no-data表示只dump表的结构，不dump数据。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --no-data -d backuptest>/tmp/backuptest.bak

grep -i 'insert' /tmp/backuptest.bak | wc -l
0

--tables指定数据库来导出其中的某些表。只支持指定一个数据库。该选项会覆盖--databases选项，所以不会建库语句和use语句。该选项可以用在分开备份innodb表和myisam表。

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --tables backuptest student teacher>/tmp/backuptest.bak

--where指定某个表的筛选条件，格式为--where="condition"。如果指定了筛选列，则要导出的表中必须要含有该列。另外，显式指定表时，格式为"db_name tab_list"。以下格式都正确。

# 导出某数据库下的一个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student>/tmp/backuptest.bak
# 导出某数据库下多个表中的筛选数据
mysqldump -uroot -p123456  -S /mydata/data/mysql.sock --where="gender='male'" backuptest student teacher>/tmp/backuptest.bak
# 导出多个数据库中的筛选数据
mysqldump -uroot -p123456 -S /mydata/data/mysql.sock --where="gender='male'" --databases backuptest backuptest1>/tmp/backuptest.bak

3.2.6 使用事务选项示例

--add-locks选项是在insert语句前后加上lock tables和unlock tables语句。

--no-autocommit选项是在insert语句前后加上set autocommit=0，并在事务结束的地方加上commit语句。这样插入数据时只需一次提交，可以大幅提升大量插入时的性能。

--flush-logs, -F

在开始dump前先flush logs，如果同时使用了--all-databases，则依次在每个数据库dump前flush；如果同时使用了--lock-all-tables,--master-data或者--single-transaction，则仅flush一次，因为这几个选项是dump前开启一个长事务或者全局锁定。等价于使用flush tables with read lock锁定所有表，这样可以让dump和flush在完全精确的同一时刻执行。

--flush-privileges  

在dump完所有数据库后在数据文件的结尾加上flush privileges语句，在导出的数据涉及mysql数据库或者依赖于mysql数据库时都应该使用该选项。

--lock-all-tables, -x 

为所有表加上一个持续到dump结束的全局读锁。该选项在dump阶段仅加一次锁，一锁锁永久且锁所有。该选项自动禁用--lock-tables和--single-transaction选项。

--lock-tables, -l 

在dump每个数据库前依次对该数据库中所有表加上read local锁(多次加锁，lock tables ... read local)，这样就允许对myisam表进行并发插入。对于innodb存储引擎，使用--single-transaction比--lock-tables更好，因为它不完全需要锁定表。因为该选项是分别对数据库加锁的，所以只能保证每个数据库之间的一致性而不能保证在所有数据库之间的一致性。 该选项主要用于myisam表，如果既有myisam又有innodb，则只能使用--lock-tables了，或者分开dump更好。

--single-transaction

该选项在dump前将设置事务隔离级别为repeatable read并发送一个start transaction语句给服务端。该选项对于导出事务表如innodb表很有用，因为它在发出start transaction后能保证导出的数据库的一致性时而不阻塞任何的程序。当使用该选项只能保证innodb表的一致性，对于myisam表是无法保证的。但是在使用该选项的时候，一定要保证没有任何其他数据库的连接在使用ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE语句，因为一致性读无法隔离这些语句。--single-transaction选项和--lock-tables选项互斥，因为lock tables会隐式提交事务，要导出大的innodb表，该选项结合--quick选项更好。

3.3 mysqldump使用建议

1.从性能考虑：在需要导出大量数据的时候，使用--quick选项可以加速导出，但导入速度不变。如果是innodb表，则可以同时加上--no-autocommit选项，这样大量数据量导入时将极大提升性能。

2.一致性考虑：对于innodb表，几乎没有理由不用--single-transaction选项。对于myisam表，使用--lock-all-tables选项要好于--lock-tables。既有innodb又有myisam表时，可以分开导出，又能保证一致性，还能保证效率。

3.方便管理和维护性考虑：在导出时flush log很有必要。加上--flush-logs选项即可。而且一般要配合--lock-all-tables选项或者--single-transaction选项一起使用，因为同时使用时，只需刷新一次日志即可，并且也能保证一致性。同时，还可以配合--master-data=2，这样就可以方便地知道二进制日志中备份结束点的位置。

4.字符集考虑：如果有表涉及到了中文数据，在dump时，一定要将dump的字符集设置的和该表的字符集一样。

5.杂项考虑：备份过程中会产生二进制日志，但是这是没有必要的。所以在备份前可以关掉，备份完后开启。set sql_log_bin=0关闭，set sql_log_bin=1开启。

以下是备份不同存储引擎类型表的示例：

备份myisam表：需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --lock-all-tables --flush-logs --master-data=2 --tables backuptest teacher >/tmp/myisam.sql ;

set sql_log_bin=1

备份innodb表：需要时加上--default-character-set

set sql_log_bin=0

mysqldump -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

set sql_log_bin=1

3.4 mysqldump + 二进制日志备份

mysqldump可以实现全备份，在mysqldump之后再二进制日志备份就相当于增量备份，这样就可以实现全备份之后的定时点还原。

假设要备份的是一张innodb表。使用下面的语句：

mysqldump  -uroot -p123456 -S /mydata/data/mysql.sock -q --no-autocommit --flush-logs --single-transaction --master-data=2 --tables backuptest student >/tmp/innodb.sql;

因为dump前会flush二进制日志，所以之后对该表的操作会记录到新的滚动日志中。然后只需备份新的二进制日志即可。

然后在该表中插入一行记录。

insert into student select 10,'xiaolonglong','male','2015-01-02';

备份新的二进制日志。

mysqlbinlog mysql-bin.000002 >/tmp/new_binlog.sql

设计刚才备份的表误操作，如删除该表。

drop table student;

使用该表的完全备份和二进制日志恢复。因为备份时使用的是--tables选项，所以要恢复需要进入数据库指定数据库，然后使用source来加载sql文件。

use backuptest;
source /tmp/innodb.sql;
source /tmp/new_binlog.sql;

3.5 mysqldump工具的评价

mysqldump备份的文件是逻辑SQL语句，总体来说，简单，便捷，在有些时候迁移数据的时候比较有用。另外，它的功能也很多，例如导出数据，导出表结构等。

但是缺点是恢复速度太慢，因为恢复数据时是通过insert不断插入记录的，它的恢复速度远不及load data infile导入数据。

在备份方式上，mysqldump备份myisam表时因为要加--lock-all-tables，这时要备份的数据库全部被上锁，可读不可写，所以实现的是温备。mysqldump备份innodb表时因为要加--single-transaction，会自动将隔离级别设置为repeatable read并开启一个事务，这时mysqldump将获取dump执行前一刻的行版本，并处于一个长事务中直到dump结束。所以不影响目标数据库的使用，可读也可写，即实现的是热备。

转载请注明出处：