关于LINUX下MYSQL命令备份

horizonhyg

回复 1# system71


   

--flush-logs, -F

          Flush the MySQL server log files before starting the dump. This option requires the RELOAD privilege. Note that if you use this option in combination with the
          --all-databases (or -A) 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.

          一般在全数据库一起备份的时候使用这个，刷新日志就是会产生一个新日志，这样你在恢复的时候就很清楚的知道从哪开始备份，而不用去指定--start-time等参数。你不太清楚的是，binlog是全局的，它并不区分是哪个库，所以

如何知道哪个日志文件是哪个数据库的。

的想法是不对的。
   

·  --lock-all-tables, -x

          Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off
          --single-transaction and --lock-tables.

       ·  --lock-tables, -l

          Lock all tables before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables
          such as InnoDB and BDB, --single-transaction is a much better option, because it does not need to lock the tables at all.

          Please note that when dumping multiple databases, --lock-tables locks tables for each database separately. Therefore, this option does not guarantee that the
          tables in the dump file are logically consistent between databases. Tables in different databases may be dumped in completely different states.

       -l是不太保险的，如果你要一起备份很多库，他是对不同的库是分别锁表的，就是当前备份哪个他就锁哪个，不能保证备份文件中不同库中的表逻辑上一致，可能他们处于完全不同的状态，所以最好使用-x，如果表全都是innodb的话，使用 --single-transaction是最好的，单一事务，他会保证备份的数据都是一致的。