I ran into something that stumped me for a few minutes today while trying to setup some replicated databases in MySQL. As with any replication setup, you have to lock the master tables and note the binary log position via:
1 2 | FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; |
And you get something like:
1 2 3 4 5 | +-------------------+----------+-------------------+------------------+ | File | POSITION | Binlog_Do_DB | Binlog_Ignore_DB | +-------------------+----------+-------------------+------------------+ | mysqld-bin.000001 | 624432 | mycooldatabase | | +-------------------+----------+-------------------+------------------+ |
Then you create a dump of the DB to be imported on the slave. Only after these steps should you unlock the master tables. This is so that when you import the dump into your slave, you can start it up and tell it where to begin looking in the master’s logs.
So far, so easy. However, it turns out that if you log out of the MySQL console in which the “flush tables with read lock;” command was run, the tables get unlocked! I can definitely see why this is needed, since otherwise if you logged out and the tables stayed locked, you wouldn’t be able to log back in since the mysql DB tables (with user information) would be locked as well. But I just didn’t think twice about locking, checking status, logging out, running the dump, logging back in and unlocking. Doing that, you will very likely get errors as soon as you start the slave with that dump and the position noted.
Instead, open another terminal! They are cheap! Run the dump there, run another check status to be sure it didn’t move, and then unlock the tables.
I haven’t seen it mentioned in any tutorials on replication, but it can happen easily enough. So beware!
No related posts.
Related posts brought to you by Yet Another Related Posts Plugin.
hi. i have seen note on this on some places. on production i have set up mylvmbackup. it does all needed to backup mysql (binary data) using table locking and lvm snapshots. this database is un-backupable (almost 40min mysqldump with locking) but with mylvmbackup it takes 2 sec max to get consistent snapshot and then the mysql could be unlocked and data backed up on the background from the lvm snapshot.
You have mysqldump take a copy of the master’s log position and lock the tables all in one go by adding the –master-data flag. Much less fiddly.
Thanks, Dan! I just came across that same option a few days ago, made things a LOT easier. I also made a script that packages the dumping, transferring, and importing all in one, will share that soon.
HI Samuel,
Can you please the script? It will help me to backup my databases.
Thanks,
Sachin D.
@sachin: Sorry about that, thanks for the reminder! The script can be viewed and downloaded here in my SVN repo: http://trac.samuelhuckins.com/code/browser/sysadmin/mysql/dump-for-replication.sh
The echo statements that print information as it runs also serve as comments. Basically you just need to set the variables for user credentials and the like, and you should be good to go.
While I have used this several times without issue, please read it carefully and try it before using near a production system!
Just had the same issue. In my case I had scripted a replication resync procedure, using one one mysql command at a time. This cause the lock to be dropped as soon as it was made.