mysqldump 同时导出myisam和innodb的问题

请问mysql高手：为了获得数据一致性，myisam使用lock-table，innodb使用single-transaction，而这两个参数是互斥的，如果一个库里面这两种表都有，怎么一致的导出呢？还有个问题，为了方便增量备份，要执行flush logs生成新日志文件。那么是在锁表后备份前做呢，还是在备份后解锁前做呢？抑或是两者一样效果？

ofurn

只要在楼主的带领下，社区就有希望了。

ifeelstupid

基于你描述的情况：‘一个mysql应用中有多个库，在这多个库里既有myisam表也有innodb表’
为了了到有效的备份，你可以这样做：
  1、flush tables;             --确保备份过程中不会被后台进程再修改   
  2、两种方案可选择：
     1）执行 mysqldump --lock-all-tables  --flush-logs --master-data=2 --all-databases > backup.sql          (get a full backup)
        2）执行  mysqldump --single-transaction   --flush-logs --master-data=2  --databases db1 db2 db3  > backup.sql

3. over!
                  

longxibendi

直接用xtrabackup 或者ibbackup得了~

感谢楼上的回复，还是阿里系的大牛们给力。结论就是mysqldump --lock-all-table可以一致性导出，无论什么存储引擎。其实从lock-all-table参数会自动turn off   --lock-table 和 --single-transaction就可以看出些端倪。至于flush-log，两种不同顺序的写法是没有太大区别。

沃趣科技

lock-all-table 表示把所有的库，表都锁住了，此时不允许写入，MyISAM和InnoDB肯定是一致的。

flush tables with read lock;flush logs;cp.....;unlock table;还是flush tables with read lock;cp......;flush logs;unlock table;
这两种方式都没有问题。重要的是，你恢复数据库以后，从备份库的哪个位置开始应用binlog。

具体一点来说。备份是做什么用的列？它是为了恢复用的，首先，将所有的数据逻辑导入。这里并没有完，你保证了MyISAM和InnoDB或者其他引擎的一致性，没问题。因为备份完成后，你就解锁了，数据又有变更。你如果先把后面的这些变更赶上，那么就必须从备份库的某一个位置开始应用binlog。这个位置必须在lock table 和unlock table之间记录，只有在这段时间内，binlog是不变的，也正是在这段时间内，你把所有的数据拷贝出去了。所以，flush logs在你说的这两种情况下，并没有太大的区别。它只是在当前binlog最末尾的位置上加上Rotate Event，提示下一个binlog文件是谁。而你恢复的时候是从文件的末尾开始恢复，还是从下一个文件的开头位置恢复都没有问题。因为这里的binlog并不设计到业务数据的变化。
这里应该没有必要看MySQL的源码，你直接试试在lock和unlock之间，flush logs，然后用mysqlbinlog看看它做了什么事情就好了

先谢谢楼上的回复，原理都对，但并没有回答我的问题。我就是想知道，在一个应用中既使用了myisam表又使用了innodb表，使用mysqldump得到一个同时保证myisam和innodb一致性的逻辑导出，这个怎么写。换句话说，lock-all-table能否确保innodb的一致性。其次指出一点与楼上不同的理解：“它的含义是在备份结束后会结束当前的binlog，生成一个新的binlog”，而mysql手册中的描述是：

?--flush-logs, -F
Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. If you use this option in combination with the --all-databases option, the logs are flushed for each database dumped. The exception is when using --lock-all-tables or --master-data: In this case, the logs are flushed only once, corresponding to the moment that all tables are locked. If you want your dump and the log flush to happen at exactly the same moment, you should use --flush-logs together with either --lock-all-tables or --master-data.

写的是在dump前flush log，这也正是我的问题：在自己写脚本的时候，是flush tables with read lock;flush logs;cp.....;unlock table;还是flush tables with read lock;cp......;flush logs;unlock table;还是两者一样没有区别呢？楼上同样没有回答。还是用实际可行的代码更有说服力。

先谢谢楼上的回答，我的问题就是在一个mysql应用中有多个库，在这多个库里既有myisam表也有innodb表，这时要用mysqldump得到一个一致性的逻辑导出应该怎么写？换句话说，就是lock-all-table能否确保innodb表的一致性，楼上的回答并没有说明。再有指出与楼上的一点不同理解：“它的含义是在备份结束后会结束当前的binlog，生成一个新的binlog”，而mysql手册上对于这个参数的描述是这样的：
•--flush-logs, -F
Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. If you use this option in combination with the --all-databases option, the logs are flushed for each database dumped. The exception is when using --lock-all-tables or --master-data: In this case, the logs are flushed only once, corresponding to the moment that all tables are locked. If you want your dump and the log flush to happen at exactly the same moment, you should use --flush-logs together with either --lock-all-tables or --master-data.

写的是在dump前flush log，这也就是我的问题，自己写脚本时，是flush table with read lock; flush logs;cp.........;unlock table;还是 flush table with read lock; cp.........;flush logs;unlock table;还是说两者效果一样？楼上的回答同样没有说明。最好给个实际可行的代码更有说服力。

ifeelstupid

首先你对lock-table和single-transaction这两个参数的理解不对，lock-table有两个参数--lock-tables和--lock-all-tables，--lock-tables是锁定某个库的所有表，如果你只备份一个库，如mysql库，使用，--lock-tables就是锁定mysql中的所有表，而不会锁定其他库的表。而如果你的备份会涉及多个库，为了获得备份数据的一致性，你得用刚才指出的--lock-all-tables来锁定所有库的表。

其次，要说明一点，mysqldump这个工具，其备份方式是逻辑备份，对存储引擎无选择性，也就是说，不管是什么引擎都通吃。


再者就是single-transaction，它的含义是以事务的方式来做你所指定的备份，和锁表的方式相比，它的优点在于不一次锁定所有所需的表，而且能够保证所得到的备份时一致的，因为这个备份是在一个事务内完成的。但其不便就是，当备份对象很大的时候，其事务也会变得很大，对涉及到事务的相应对象，比如回退段影响大。


最后说明一下flush logs，在mysqldump的时候指定--flush-logs选项，它的含义是在备份结束后会结束当前的binlog，生成一个新的binlog，而这个新的binlog，就是你所谓的增量。