homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

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

WebmasterWorld Administrator httpwebwitch us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4160886 posted 7:47 pm on Jun 28, 2010 (gmt 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.


Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved