mysql逻辑备份采用什么命令_mysql备份-使用mysqldump实施逻辑备份

Mysqldump是一个逻辑备份命令；意思就是将数据库中的数据备份成一个文本文件；也可以说是将表的结构和数据存储在文本文件中。

Mysqldump命令的工作原理很简单，它先查出需要备份的表的结构，再在文本文件中生成一个CREATE语句。然后，将表中的所有记录转换为一条INSTERT语句。这些CREATE语句和INSTERT语句都是还原时使用的。还原数据时就可以使用其中的CREATE语句来创建表。使用其中的INSERT语句来还原数据。它可以实现整个服务器备份，也可以实现单个或部分数据库、单个或部分表、表中的某些行、存储过程、存储函数、触发器的备份；并且能自动记录备份时刻的二进制日志文件及相应的位置。对于InnoDB存储引擎来讲支持基于单事务模式实现热备，对于MyISAM则最多支持温备。

1)命令常用参数解释：

--all-databases, -A

导出全部数据库。

--add-drop-database

每个数据库创建之前添加drop数据库语句。

--add-drop-table

每个数据表创建之前添加drop数据表语句。(默认为打开状态，使用--skip-add-drop-table取消选项)

--add-locks

在每个表导出之前增加LOCK TABLES并且之后UNLOCK

TABLE。(默认为打开状态，使用--skip-add-locks取消选项)

--comments

附加注释信息。默认为打开，可以用--skip-comments取消

--compact

导出更少的输出信息(用于调试)。去掉注释和头尾等结构。可以使用选项：--skip-add-drop-table --skip-add-locks --skip-comments--skip-disable-keys

--complete-insert,-c

使用完整的insert语句(包含列名称)。这么做能提高插入效率，但是可能会受到max_allowed_packet参数的影响而导致插入失败。

--compress, -C

在客户端和服务器之间启用压缩传递所有信息

--create-options,-a

在CREATE TABLE语句中包括所有MySQL特性选项。(默认为打开状态)

--databases, -B

导出几个数据库。参数后面所有名字参量都被看作数据库名。

--default-character-set

设置默认字符集，默认值为utf8

--events, -E

导出事件。

--flush-logs

开始导出之前刷新日志。

--force

在导出过程中忽略出现的SQL错误。

--ignore-table

不导出指定表。指定忽略多个表时，需要重复多次，每次一个表。每个表必须同时指定数据库和表名。例如：--ignore-table=database.table1 --ignore-table=database.table2……

--lock-all-tables,-x

提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭--single-transaction和--lock-tables选项。

--lock-tables,-l

开始导出前，锁定所有表。用READ

LOCAL锁定表以允许MyISAM表并行插入。对于支持事务的表例如InnoDB和BDB，--single-transaction是一个更好的选择，因为它根本不需要锁定表。

请注意当导出多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证导出文件中的表在数据库之间的逻辑一致性。不同数据库表的导出状态可以完全不同。

--master-data

该选项将binlog的位置和文件名追加到输出文件中。如果为1，将会输出CHANGE MASTER命令；如果为2，输出的CHANGE MASTER命令前添加注释信息。该选项将打开--lock-all-tables选项，除非--single-transaction也被指定(在这种情况下，全局读锁在开始导出时获得很短的时间；其他内容参考下面的--single-transaction选项)。该选项自动关闭--lock-tables选项。

--no-create-db,-n

只导出数据，而不添加CREATE

DATABASE语句。

--no-create-info,-t

只导出数据，而不添加CREATE

TABLE语句。

--no-data, -d

不导出任何数据，只导出数据库表结构。

--single-transaction

该选项在导出数据之前提交一个BEGIN

SQL语句，BEGIN不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于多版本存储引擎，仅InnoDB。本选项和--lock-tables选项是互斥的，因为LOCK TABLES会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用--quick选项。

--triggers

导出触发器。该选项默认启用，用--skip-triggers禁用它。

2)备份实例

1、备份单个数据库

[root@data-slave~]# mysqldump -uroot -p123456 oldboy >/opt/oldboy_$(date +%F).sql

2、指定默认的编码格式

--default-character-set=utf8指定默认的编码格式

[root@data-slave~]# mysqldump -uroot -p123456 --default-character-set=utf8 oldboy>/opt/oldboy_$(date +%F)_utf8.sql

3、-B参数使用

直观的看加-B参数的作用是增加了创建数据库和连接数据库的语句。该参数用于导出多个库，在备份结果中会加入USE db_name和CREATE db-name，使用加-B参数备份后恢复时不需要再新建库

mysqldump-uroot -p123456 -B oldboy>/opt/oldboy_$(date +%F).sql

4、使用gzip对备份进行压缩

[root@data-slaveopt]# mysqldump -uroot -p123456 -B oldboy |gzip >/opt/oldboy_$(date+%F)_B.sql.gz

5、备份多个库

[root@data-slaveopt]# mysqldump -uroot -p123456 -B oldboy wd_gbk >/opt/data_$(date+%F)_B.sql

6、备份单个表

[root@data-slavebak]# mysqldump -uroot -p123456oldboytest >/opt/bak/test.sql

7、备份多个表

[root@data-slavebak]# mysqldump -uroot -p123456oldboytest student >/opt/bak/test.sql

8、利用mysqldump

-d参数只备份表结构。

[root@data-slavebak]# mysqldump -uroot -p123456 -B -d oldboy >/opt/oldboy_struck.sql

9、利用mysqldump

-t参数只备份数据。

[root@data-slaveopt]# mysqldump -uroot -p123456 -B -t oldboy >/opt/oldboy_data.sql

10、刷新binlog参数

mysqldump用于定时对某一时刻的数据的全备，例如：00点进行备份bak.sql.gz

增量备份，当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的文件里，这个文件叫作binlog文件。

-F参数刷新binlog日志，生成新文件，将来增量恢复从这个文件开始。

[root@data-slavedata]# mysqldump -uroot -p123456 -B -F oldboy >/opt/bak/test_binlog.sql

11、myisam引擎企业生产备份命令()(适合所有引擎或混合引擎)：

[root@data-slavedata]# mysqldump -uroot -p123456 -A -B -F --master-data=2 -x --events>/opt/bak/bak.sql

12、innodb引擎企业生产备份命令：推荐使用的

[root@data-slave data]# mysqldump -uroot -p123456 -A -B -F --master-data=2 --single-transaction

--events >/opt/bak/bak.sql