说明:上述的步骤是简单的热备做dr的步骤,有常见需要注意的问题
1)mysqldump生成全备sql时需要注意–single-transaction –master-data等参数,详细可以参见mysqldump –help
2)全备入库时,需要设置–default-character-set,使用一致的字符集
先前在使用mysqldump的时候,没有加任何选项,查看dump出来的文件,发现每
个table的insert语句被lock tables write和unlock tables包住。mysqldump在
备份的时候,居然锁表!如果这张表非常大,在dump的过程中,其他线程岂不是
不能写数据?看了下mysql的manual,发现–lock-tables默认是True,可以使
用–skip-opt选项来屏蔽–lock-tables。另外,我们很多时候需要基于时间点
的备份,如早上9点,这时,可以使用–single-transaction选项,这个选项可
以在dump之前发出一个BEGIN语句,获取一个短暂的全局写锁,可以所有事务性
数据库的一致性(内部应该是使用snapshot来实现,待求证),另外通过指
定–master-data=2,可以在dump文件中用注释的方式指定当前dump快照使用的
binlog文件和位置,联合–single-transaction和–master-data两个选项,可
以实现基于时间点的备份和恢复,特别是做热备。
[注意]–skip-opt禁用了很多默认的选项,如–create-option等,其
中–create-option是比较重要的属性,如果该选项被disable掉,则在dump出来
的table会少了auto-increment等字段属性。
–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off –lock-tables.
–master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
–lock-all-tables on, unless –single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don't
forget to read about –single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns –lock-tables
off.