Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

sequence of events, when setting up master/slave mysql on a live db

7:47 pm on Jun 28, 2010 (gmt 0)

Senior Member from CA 

WebmasterWorld Senior Member httpwebwitch is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 29, 2003
votes: 0

I've got a single MySQL database that's getting massive amounts of active reads & writes. I'm making up a plan for restarting the thing as a master, so I can connect a slave to it.

Downtime has to be minimal, and sync has to be perfect. This is my challenge.

Added to the challenge is: this database is getting about 40 writes per second. Yeah. FML.

At this time, the master db is not creating binary logs.

The sequence I think I need to do is this:

1) ssh in to this thing at a slow period... like 3am. But even then, the db is usually getting a few writes per second. It's never quiet. FML again.

2) flush tables with read lock;
// this locks the master db. at this point, I have to move quick, because queued queries start to fill memory. I've seen this happen (accidentally) in production before, and it's not pretty.

3) mysqldump --opt -u root -p [database_name] > /backup/[database_name]_master_backup.sql
// because the db is pretty big, this might take a while

4) add these lines to my.cnf:


5) restart mysql:
shell> service mysqld restart

when I restart mysql, master will start writing binary logs. Also, the temporary lock I put on the tables is unlocked. At this moment, traffic starts flowing again.

6) In order to sync my slave, I'll need the log position, which I get by asking this at the master:

mysql> show master status;

and the output will look something like this:

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
| [database_name]_bin_log.000001 | 106 | [database_name] | |
1 row in set (0.00 sec)

With that sql dump and the log position, I can sync up a slave, no problem.

The thing that puzzles me is this: what about transactions that happen in those moments between when I restart mysql, and when I output the master status? Restarting mysql unlocks all the tables, so there will be writes happening in those seconds when I'm getting the master status log position.

Or would the log position for a database with locked tables be... zero? Could it really be that simple?

If someone initiates a write immediately after I restart mysql and before I get that master status, then the log position will not be correct.

What I think I need is a way to restart mysql with table locks ON. Then I don't need to worry about writes happening after the binlogs are started, I can get an accurate Log Position number, then unlock the tables and let the honey flow.

But AFAIK there is no way to do that. I've been scouring the docs looking for clues.

Surely I'm not the first person to do this kind of thing... so does anyone have advice for me?

Maybe there's a "right" way to do this that I'm not aware of.