Search This Blog

Wednesday, November 24, 2010

Getting the data Sync'ed between master & slave servers in mysql replication

In order to get the database.tables sync'ed between master and slave server, follow these guidelines

  1. Shutdown mysql daemon on master server
  2. Shutdown mysql daemon on slave server
  3. Take dump of database.tables on master server
  4. Take backup of database.tables on slave server
  5. Load dump (of master) on slave
  6. Start mysql daemon on master server
  7. Take a note of new binlog filename on master server
  8. Update the master binlog filename on slave server
  9. Start mysql daemon on slave server


  1. Another alternative is to use the following command on the master server

    mysqldump -u xxxx -p xxxx test sample | ssh <> "mysql -u xxxx -p xxxx test"

    The advantage here is, you don't have to manage binlog filename management

    CAution: execute this command when replication on both the machine is in sync. There should not be any replication delay's.

  2. Performance Test

    when we tested the above command on a table with 16 Million Records, it took approx. 12 minutes.

    Giving us a throughput of 1.33 Million record per Minute.

    We experimented further with adding compression to this command, so the new command was as follows

    mysqldump -uxxxx -pxxxx test sample | gzip | ssh s4 "gunzip | mysql -uxxxx -pxxxx test"

    with this new command, the transfer was completed in 502 seconds

    Thus giving us a throughput of 1.91 Million records per Minute

    A performance increase of 43%

  3. Got following problem

    ERROR 1103 (42000) at line 147: Incorrect table name './db1/'
    mysqldump: Got errno 32 on write

    the reason is DATA/INDEX directory specified in CREATE TABLE statement


    in order to handle we need to tweak the command further as follows

    mysqldump -uxxxx -pxxxx test sample | sed "s/DATA DIRECTORY='.\/db1/'//" | sed "s/INDEX DIRECTORY='.\/db1/'//" | gzip | ssh s4 "gunzip | mysql -uxxxx -pxxxx test"