23 September 2009

Mysql replication confusion

Mysql's replication-setup instructions boil down to this:

- Stop all activity on the master with FLUSH TABLES WITH READ LOCK;
- Make note of the binary-log position on the master with SHOW MASTER STATUS;
- Dump out the future master with mysqldump, using the --lock-all-databases or --master-data options
- Release the locks, load the data on the server, start the slave.

The idea behind the locks is so that the dump is a consistent snapshot
of the database. No activity should happen between taking the lock and
finishing the dump. Presumably, that means that the master's position
in the binary log as given by SHOW MASTER STATUS; should not change after the user issues FLUSH TABLES WITH READ LOCK;

But that presumption would be wrong. Here's what I did:

Open mysql, issue:

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000124 | 21494 | | |
1 row in set (0.00 sec)

Leave that session open. At the command line, issue:

$ mysqldump -u root -p'the-password' --databases names of the databases --master-data |gzip > masterdump.sql.gz

Go back to the read lock session. Issue flush-tables again. And, lo and behold:

mysql> show master status;
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| mysql-bin.000124 | 21565 | | |
1 row in set (0.00 sec)

The position changed! Evidently, --master-data (which is supposed to
stick the CHANGE MASTER TO statement into the dump so the user doesn't
have to) must either release the original read lock or perform an
action that bypasses it.

The CHANGE MASTER TO statement included by --master-data refers to the
latter timepoint:

-- Position to start replication or point-in-time recovery from


So, what happened between 21494 and 21565? Dumping the log reveals:

# mysqlbinlog /var/log/mysql/mysql-bin.000124

[most of log snipped]

# at 21494
#090916 13:38:32 server id 8887 end_log_pos 21565 Query thread_id=86 exec_time=0 error_code=0
SET TIMESTAMP=1253122712/*!*/;
# End of log file
ROLLBACK /* added by mysqlbinlog */;

Between 21494 and 21565, the log contains a 'set timestamp', 'flush
tables', and 'delimiter' statement. These must have been added by

I'm assuming that those statements have no effect on the integrity of
the dump. Still, I find it a bit surprising that the manual doesn't
mention this. (Interestingly, if you issue the --lock-all-tables
option instead of --master-data, mysql does the same thing, except
that CHANGE MASTER TO is not included in the dump. You have to add it
yourself, deciding whether to trust the pre-dump or post-dump

For reference, I'm using Debian's mysql-server-5.0 package, version

No comments:

Post a Comment

About Me

blog at barillari dot org Older posts at http://barillari.org/blog