mysqldump的--master-data参数

最新推荐文章于 2024-04-23 11:31:48 发布

原创最新推荐文章于 2024-04-23 11:31:48 发布 · 1.7w 阅读

3 ·

CC 4.0 BY-SA版权

MySQL 专栏收录该内容

25 篇文章

订阅专栏

本文介绍使用mysqldump工具进行数据库导出时，如何利用--master-data参数来控制复制配置信息的输出形式。具体展示了当该参数值设置为1或2时，CHANGEMASTERTO语句的差异及其对数据库复制的影响。

先来看看官方文档的解释
Use this option to dump a master replication server to produce a dump file that can be used to set up another server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating.

If the option value is 2, the CHANGE MASTER TO statement is written as an SQL comment, and thus is informative only; it has no effect when the dump file is reloaded. If the option value is 1, the statement takes effect when the dump file is reloaded. If the option value is not specified, the default value is 1.

mysqldump导出数据时，当这个参数的值为1的时候，mysqldump出来的文件就会包括CHANGE MASTER TO这个语句，CHANGE MASTER TO后面紧接着就是file和position的记录，在slave上导入数据时就会执行这个语句，salve就会根据指定这个文件位置从master端复制binlog。默认情况下这个值是1

当这个值是2的时候，chang master to也是会写到dump文件里面去的，但是这个语句是被注释的状态。

[root@localhost tmp]# mysqldump -uroot -p --hex-blob --lock-all-tables -R --triggers --databases mydb mydb2 --master-data=2 --default-character-set='utf8' --quick>/tmp/a.sql
[root@localhost tmp]# grep -i "CHANGE MASTER TO" /tmp/a.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=2341;

[root@localhost tmp]# mysqldump -uroot -p --hex-blob --lock-all-tables -R --triggers --databases mydb mydb2 --master-data=1 --default-character-set='utf8' --quick>/tmp/a.sql
[root@localhost tmp]# grep -i "CHANGE MASTER TO" /tmp/a.sql
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000017', MASTER_LOG_POS=2341;