mysql复制小记

1. FLUSH LOGS；
强制rotate一个新的binlog,以便备份。

2. # 刷新所有的表并锁定数据库，防止在检查binlog位置之前数据库发生改变
FLUSH TABLES WITH READ LOCK；

# 获取当前的binlog文件和pos
SHOW MASTER STATUS\G
其实mysqldump命令提供了master_data选项，自动把MASTER_LOG_FILE和MASTER_LOG_POS信息dump到backup.sql中
mysqldump -xxx
unlock tables

以上只对非innodb这种引擎起作用，innodb的只需要--single-transactions
--single-transaction

           This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the
           consistent state of the database at the time when BEGIN was issued without blocking any applications.

           When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this
           option may still change state.

           While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the
           following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a
           table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

           The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly