CDC

Place to Be & Show Yourself

mysqldump的奇技淫巧

为什么要单独写一篇关于mysqldump的文章呢?这个用起来不是很简单吗?其实mysqldump有100多种命令行选项,如果不能正确使用,不仅会影响数据库的Performance,还有可能导致导出的数据有一致性的问题

使用举例

通常情况下,我们可以简单使用下面的shell命令导出单个数据库:

但是我们还是需要看看这些命令到底做了什么,否则如果不能正确使用,可能会导致线上环境的问题

缺省选项

缺省情况下,mysqldump包括了–opt选项。该选项和下列选项等价:

  • –add-drop-table – 在CREATE TABLE 之前增加一个DROP TABLE语句,使得结果sql文件可以反复执行(幂等)。
  • –add-locks – 这意味着当导入dump出来的文件的时候(不是在运行mysqldump时),在每个table之前和之后增加 LOCK TABLES 和 UNLOCK TABLES 。这样的好处是当导入数据时,表会被锁住,导入速度会更快
  • –create-options – 在create table语句中包含Mysql特定的table选项。测试中发现,如果使用了-create-options=false,主键上就不会有AUTO_INCREMENT了。
  • –disable-keys – 该选项只对MyISAM表的不唯一索引有效。因为索引是在所有行都导入完成才创建,所以会导入的更快一些。
  • –extended-insert – INSERT语句用包含多个值的多行语法格式。通常情况下都要使用该选项,除非是对超级大的列(blobs),会导致超过max_allowed_packet配置。否则逐行导入会非常慢。
    多行语法的例子:

单行语法的例子:

差别就在于第二种格式会导致导入过程非常慢。

  • –lock-tables – 该选项用于mysqldump本身,运行mysqldump的时候锁住所有的table,所以要避免在线上环境上使用。原本这个选项是导出MyISAM table的时候保护数据完整性。但是目前InnoDB是缺省的存储引擎,所以基本上都要使用–skip-lock-tables代替该选项,并且使用–single-transaction 来在一个事务中执行mysqldump。
  • –quick – 读取大table,而不需要足够的内存容纳整个table的数据

mysqldump和表锁

使用mysqldump的时候,往往需要在暂停/影响数据库性能和确保数据一致性之间做出权衡。这个策略很大程度上由数据库table的存储引擎来决定。但是不同的表会有不同的存储引擎,所以事情有点复杂。缺省情况下,mysqldump会锁住所有的表,这样会确保数据的一致性。

数据一致性

“一致性状态”意思是所有数据的状态都是期望的状态。假设有两个表table1和table2通过外键进行关联,当mysqldump导出table1之后有新的数据插入到table1和table2中,这个时候数据状态就不一致了,导出文件中,只包含了table2的相关数据,并没有包含table1的相关数据。

MyISAM tables不支持事务,所以在使用mysqldump的时候需要使用–lock-tables。然而InnoDB (MySQL 5.5.5及以后版本的缺省存储引擎)支持事务,所以需要使用事务机制避免锁表操作.

mysqldump和事务

作为一个原则,除非有特殊理由,否则一般情况下都应该使用InnoDB作为存储引擎。这种情况下,mysqldump的正确用法为:

–single-transaction会为mysqldump开启一个事务,通过读取事务开始时的数据库状态,而不是通过锁住整个数据库来确保dump文件的一致性,single-transaction 选项使用数据库的缺省隔离级别:可重复读
值得注意的是,如果数据库中的表部分使用MyISAM,部分使用InnoDB,上面的用法会导致MyISAM表的数据不一致,因为在mysqldump过程并没有锁表。这种情况下,建议单独导出MyISAM表。不过比较tricky的情况是,如果MyISAM和InnoDB表之间通过主键/外键关联,–lock-tables就会成为唯一的一种确保数据一致性的方法了。如果线上数据库遇到这种情况要特别小心,建议在从库而不是主库上导出,或者研究一下Xtrabackup选项来copy整个mysql的数据目录,这样来避免downtime

复制

如果我们使用MGR等数据库集群技术,则已经在从库上有了备份。这种情况下一般在从库上执行mysqldump,这就意味着:从库的数据会稍微落后于主库的数据。对于周期性的备份,这当然没问题,但是有的时候就是需要在某一个点上备份,这就需要等到数据同步到从库。

当然也有一些针对集群的mysqldump选项可用:

主库数据Master Data

–master-data 选项在dump文件中做特殊标记,这样搭建主库的另一个从库时,从库知道从哪里开始复制。该选项会自动关闭–lock-tables, 因为dump文件包含binlog的位置,从而知道要从哪里开始复制,在数据不一致的情况下不会导致数据丢失。(当然这个只是MyISAM才有的问题)

Dump从库数据

–dump-slave 选项和–master-data类似,在从库执行mysqldump的时候,导出文件包含了和从库相同的主库信息, 而–master-data 将自身设置为主库。
官方文档”This option should not be used if the server where the dump is going to be applied uses gtid_mode=ON and MASTER_AUTOPOSITION=1.”
GTID is a newer way to do MySQL replication as of MySQL 5.6. It’s a nicer method, so –dump-slave in theory can be one to ignore.

更多使用案例

通常情况下都是dump单个数据库,这样恢复也比较容易。但是也可以dump多个数据库

当然也可以导出某个数据库的多张表

还可以导出完整的数据库,包括内部mysql数据库

上面这个命令中,同时使用了–all-databases和–flush-privileges两个选项,因为导出的mysql数据库,包括了用户和权限信息,–flush-privileges在dump文件的最后增加FLUSH PRIVILEGES

点赞

发表评论

电子邮件地址不会被公开。