MySQL Database Replication

There are 2 database servers.  One master and one client.  The client is to be sync with the master.  Official documentation: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

  1. Master: Shutdown the MySQL server.
    # /etc/init.d/mysql stop
  2. Master: Add in the following options into /etc/mysq./my.cnf configuration file.
    • server-id=1
    • log_bin=/var/log/mysql/mysql-bin.log
    • binlog_do_db=TEST_DB
    • innodb_flush_log_at_trx_commit=1
    • sync_binlog=1
  3. Master: Startup MySQL server.
    # /etc/init.d/mysql start
  4. Master: Login as the root MySQL login to the database to be replicate.
    $ mysql -u root -p TEST_DB
  5. Master: Create a mysql login purely for replication exercise.
     mysql> grant replication slave on *.* to 'repl'@'%' identified by 'Repl';
  6. Master: Lock the master database.  Do not logout from the mysql program or else the lock will be released.
    mysql

    > flush tables with read lock;
  7. Master: Get the master database status.  Copy down the value of File and Position.  You’ll need them later.
    mysql

    > show master status;
    +------------------+----------+--------------+------------------+
    | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
    +------------------+----------+--------------+------------------+
    | mysql-bin.000003 |      178 | TEST_DB      |                  |
    +------------------+----------+--------------+------------------+
  8. Master: Export the master database.
    $ mysqldump -u root -p TEST_DB > TEST_DB.dmp
  9. Master: Release the lock or simply exit the mysql program.
    mysql> unlock tables;
    mysql> quit;
  10. Client: Shutdown MySQL server.
    # /etc/init.d/mysql stop
  11. Client: Add in the following options into /etc/mysq./my.cnf configuration file.
    • server-id=2
  12. Client: Startup MySQL server.
    # /etc/init.d/mysql start
  13. Client: Create the client database by importing the master database.  You’ll need to transfer the dump file from master machine to client machine first.
    $ mysql -u root -p TEST_DB < TEST_DB.dmp
  14. Client: Log into MySQL.
    $ mysql -u root -p TEST_DB
  15. Client: Provide master database settings.
    mysql> change master to
    ->  master_host='master_machine_ip',
    ->  master_user='repl',
    ->  master_password='Repl',
    ->  master_log_file='mysql-bin.000003',  -- from step 7.
    ->  master_log_pos=178;                  -- from step 7.
  16. Client: Start replication.
    mysql> slave start;
  17. Done.
    mysql> quit;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: