How To Set Up MySQL Database Replication

In this tutorial i will try to describes how to set up mysql database replication. It’s simple process; MySQL replication allows you to have an exact copy of a database from a master server on slave server. It means all updates happening on the master server are immediately replicated to the slave server database so that both databases are in sync. In this case the both the databases are identical. e.g If you delete any record on master server it will be removed from slave server to; Replication can help protect data against hardware failures though.
In following tutorial i will show how to replicate mysql database “testdb” from the master to slave server (IP address 192.168.0.200). Both systems (master and slave) are running ubuntu; However, the configuration should apply to almost all distributions with little or no modification. Presuming that both systems have MySQL installed, and the database “testdb” with tables and data is already existing on the master, but not on the slave.
There are many ways of achieving this goal but this is the way it worked for me. I do not issue any guarantee that this will work for you!

Step 1: Master server Configuration
Now edit the mysql server config file located at “/etc/mysql/my.cnf” so that its enabled for networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines if exists.
because it listens this ip.

#skip-networking
#bind-address = 127.0.0.1

Step 2: Enable log file for specific database.
Now we have to tell MySQL for which database it should write logs as its been used by the slave to see what are changed happening on the master server, which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate “testdb” database, so we put the following lines into mysql config file ( i. e. “/etc/mysql/my.cnf”).

log-bin = /var/log/mysql/mysql-bin.log
binlog-do-db=testdb
server-id=1

now restart mysql server and see it got restart without any error message if so it means changes are done correctly.

Step 3: Create new mysql user with replication privileges.
Then we log into the MySQL database as root and create a user with replication privileges.

  GRANT REPLICATION SLAVE ON *.* TO 'testdb_slave_user'@'%' IDENTIFIED BY ''; (Replace  with a real password!)
FLUSH PRIVILEGES;
USE testdb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
Output will be as follows:
+---------------+----------+--------------+------------------+
| File          | Position | Binlog_do_db | Binlog_ignore_db |
+---------------+----------+--------------+------------------+
| mysql-bin.006 | 183      | testdb       |                  |
+---------------+----------+--------------+------------------+
1 row in set (0.02 sec)

Now quit the master server.

Step 4: create master server database backup and copy it to slave server. command to create testdb backup is as follows. This is the best way to create back if you are using production server. It may slowdown your server if database size is more.

  mysqldump -u root -p --opt testdb > testdb.sql

Step 5: now configure mysql slave.
Login to mysql on slave server and create database “testdb”.
Import data using existing dump file in to testdb.

 mysql -u root -p testdb < /path/to/testdb.sql

Step 6: Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.200, and that the master database to watch is testdb. Therefore we add the following lines to "/etc/mysql/my.cnf"

server-id=2
master-host=192.168.0.200
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb
Now we restart MySQL using "/etc/init.d/mysql restart".
If there is no error it means everything is fine.
Now stop the slave server.
CHANGE MASTER TO MASTER_HOST='192.168.0.200', MASTER_USER='testdb_slave_user', MASTER_PASSWORD='', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;
    MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.200).
    MASTER_USER is the user we granted replication privileges on the master.
    MASTER_PASSWORD is the password of MASTER_USER on the master.
    MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
    MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.
Now all that is left to do is start the slave. Still on the MySQL shell we run
START SLAVE;
quit;
That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave.
That's all now you can Test it!
Share this nice post:

3 thoughts on “How To Set Up MySQL Database Replication

  1. Many thanks for an incredible post, can see your others topics. i appreciate your notions on this, I experienced a bit thump by this post. Merit again! You make a great moment. Has excellent data here. I feel if more people consideration for it like that, they’d have got a better time have the suspend ofing the matter.

  2. I used to be advised this kind of web site by means of our uncle.
    Therefore i’m no longer confident whether this publish is usually composed by using him while no one else comprehend this kind of special approximately our trouble. You happen to be remarkable! Many thanks!

  3. I just want to tell you that I am all new to blogging and site-building and honestly loved you’re website. Very likely I’m want to bookmark your blog . You really have wonderful well written articles. Thanks a bunch for sharing your web site.

Leave a Reply

Your email address will not be published. Required fields are marked *