Setting up MySQL database replication

It’s always about data. What’s the use of any system without the data it relies on?

Setting up a database replication is one of many steps that should be taken in order to preserve data, preventing any loss and making disaster recovery easier.

Luckily, it’s easy with MySQL. So let’s suppose we have two servers running MySQL, one called host1 and the other host2.
Replication can be either master-master or master-slave. With a master-slave replication, the slave always replicates what the master database is executing. In master-master replication, both databases synchronize with each others.

For the purpose of this tutorial, a master-slave (here host1 and host2 respectively) scenario is examined.

First of all, open the mysql config file on host1 (usually found at /etc/my.cnf on linux, and c:\windows\my.ini on windows), and uncomment (remove the hash of) the following line:


Secondly, you need to specify the file where the master (host1) should log (write) the queries it’s executing. This will enable the slave (host2) to read these queries and execute them as well. As such, add a line such as:

log-bin = /path/to/mysql-bin.log

where the value above is the path to file where MySQL should be doing the logging. You could very well create a separate directory or use the default mysql installation directory (such as c:\program files\mysql\ on windows or /var/lib/mysql on linux)

Then, you need to specify the name of the database in question. So if you’re setting up replication for one of your MySQL databases called ‘work_data’, then, this is the line you need to add to your MySQL config:

binlog-do-db = work_data

Finally, you need to specify a server id, which says that this is the master server


Save the config file and exit.
Now you need to give host2 the permission to replicate the data. As such, a MySQL query needs to be issued on the master.

So on host1, login to the MySQL prompt (mysql -u root -pyour_root_password) (or PHPMyAdmin, etc… whatever you use), and issue the following statement:

grant replication slave on *.* to ‘username’@’%’ identified by ‘password’;

Make sure to replace username and password with a credential of your choice. Do keep the single quotes though.
The % sign means that the slave can connect from any host. If you want it to be more secure, replace that with host2 (the slave’s hostname).

After all the above is done, restart the MySQL service (service mysqld restart (linux) or, net stop mysql, net start mysql (on windows)).

If the database had data earlier, make sure you dump it and load it on the slave before doing any of the above. Dumping data is easy and can be done by cd’ing to the MySQL bin directory and issuing:

mysqldump -Q -u root -pyour_root_password databasename > database_dump.sql

(replace the password and database name with the correct login). The whole database will now be in the file called database_dump.sql

To import it on host2, cd to the mysql bin directory and issue:

mysql -u root -pyour_root_password databasename < /path/to/the/file/database_dump.sql

The MySQL config file on host2 should have the following lines:

master-host = host1
master-user = username
master-password = password
master-port = 3306

where host1 is the master’s hostname/IP, and username and password are the credentials you used when granting replication access a few steps above. 3306 is the port MySQL is running on (which is the default)

Then start the slave process on host2 by issuing at the MySQL prompt:

start slave;

To make sure replication is working, issue the following SQL query on host1:

show slave status \G

(Slave_SQL_Running and Slave_IO_Running should report ‘Yes’)

Good luck

6 Responses to “Setting up MySQL database replication”

  1. ????????????????????????????????5????????????????????????????????????????????????????????????????????????????????????86-592-5556663 says:


    [...]Setting up MySQL database replication « Tips and Tricks[...]…

  2. host2 says:

    [...] makes it possible to add or remove nodes from the cluster without needing to reconfigure clients.Tips and Tricks Setting up MySQL database replicationSo let's suppose we have two servers running MySQL, one called host1 and the other host2. … If you [...]

  3. admin says:

    You could setup a local DNS service, and make users use a hostname (in normal cases pointing to the master) to access your service.
    In case of failure, you simply change the DNS record of that hostname to point to the slave machine.
    Users wouldn’t notice the change if you change the IP that hostname resolves to.

  4. Robert says:

    Thats Genius ! , Although i’ll have to change the way the users connects to the database in order to make a seamless transition in the event of a failure.

  5. admin says:

    Dear Robert,
    What about running multiple MySQL services on the slave host? Each would be running on a different port and would be dependent on a different master.

  6. Robert says:

    Great, just what i needed. Now … Is it possible to set several Master devices to replicate in just one Slave Server ?. I’m asking this because im trying to set up an Active-Stanby topology in which, if one of the Masters Fails it could be replace temporarily by the Slave server. Thanks

Leave a Reply