Home / server / Questions / 17066
Accepted
davidmytton
Asked: 2009-06-02 10:54:58 +0800 CST

How can I create a slave replica of a MySQL server without stopping the master?

  • 772

To ensure the data on the slave and master are in the same place for replication, the new master should be stopped and data transferred to the new slave before starting the master again, at least this is one option.

How can I start replication of a MySQL server without stopping the master?

mysql replication

4 Answers

  1. Best Answer

    easy option - if you have small amount of data use

    mysqldump --master-data -uyouruser -p.... -a > dump.sql

    this will lock all tables and generate dump with info about current master position. dump will lock all tables - probably not something you want to do when you have busy website and 20gb data to dump.

    more advanced option. take your data on lvm partition and use lvm snapshot. that is in one process lock all tables FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS; in another proces run sync few times for a good measure and create lvm snapshot of your data / innodb logs. just after creating snapshot [ that takes 2-3 seconds for me ] unlock all tables.

    now you have consistent snapshot and your sql server still running. mount snapshot and 'ship it' asap to another server. there run mysql on this data, let innodb reover all data from it's logs and run mysqldump which output put to slave.

    start slave using information from SHOW MASTER STATUS that you've run just before taking LVM snapshot.

    mylvmbackup does exactly that. read about it here.

    • 6

  2. FLUSH TABLES WITH READ LOCK on the master, copy the data to the slave (possibly with periodic SELECT NULL operations on the client that's talking to the master to prevent the locks from being lost to connection timeouts), then start replication on the slave and UNLOCK TABLES on the master.

    All the master operations specified need to be within a single client session. Don't go trying to exit it while the copy is in progress.

    • 0

  3. As pQd mentioned above:

    mysqldump --master-data -uyouruser -p.... -a > dump.sql

    but why not ssh into the remove machine and pipe the data directly trough ssh?

    • 0

  4. I can't comment yet, but what about the difference between MyISAM and INNODB? I'm not sure you can simply do a lock tables with INNODB and expect a good snapshot.

    • 0