Setting up database replication on MySQL

Database replication is, as the americans would say, quite awesome. It provides, for one, an always fresh database backup. But most of the time you won't do it for backups. The greatest thing about database replication is that you can use it for load balancing.

Just replicate the database onto another server, and you have two atabase servers for SELECTs and you can leave the master for INSERTs, DELETEs and UPDATEs. The only bad thing about this is that you will need different connections for read and write (nothing that Dr. Nic's magic can't resolve), and you must have a fast connection between the two servers or you may have records that don't show up instantly when you store them (that's bad for a web application).

First of all, I'm supposing you already have a production database running in a server. If you start from scratch with several database servers, then it's much easier, as you don't have to dump/restore databases.

Go to your master server (let's say her name is scherie), and edit /etc/mysql/my.cnf:

Make sure you have set server-id to 1:

server-id = 1

You need to uncomment the bind-address line, as we need port 3306 open for our slave:

#bind-address =

By default, Debian for instance, already saves the transaction log. Uncomment or add it if you don't have it:

log_bin  = /var/log/mysql/mysql-bin.log

Add a line for the database that will be dumped to the binary log:


You can now restart the database:

scherie:~# /etc/init.d/mysql restart

Open a mysql client, and create a new user called replication_user (change this to whatever you want):

scherie:~# mysql -u root -p

mysql> grant replication slave on *.* to 'replication_user'@'%' identified by 'password';

Now we have to lock temporarily the database while we proceed to dump it, so we have the correct binary log offset (Position).

mysql> flush tables with read lock;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status\G;
*************************** 1. row ***************************
File: mysql-bin.00002
Position: 230
Binlog_Do_DB: database_to_be_replicated

1 row in set (0.00 sec)

Don't close the mysql client
. Leave the terminal open, otherwise, the database will be unlocked. Write down the File and Position values, we will need them later.

Dump the database (you could use load data from master, but it's deprecated and will be removed in the future).

scherie:~# mysqldump -u root -p database_to_be_replicated > database_to_be_replicated.sql
Enter password:

Once dumped return to the open terminal, and unlock tables:

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> quit;

It would be a good idea to compress the database dump before transferring it:

scherie:~# bzip2 database_to_be_replicated.sql
scherie:~# scp database_to_be_replicated.sql daniel@athena:

You should also allow access from the slave to the master server, but block it otherwise (basic iptables rules):

-A INPUT -s ip_of_slave_server -p tcp -m tcp --dport 3306 -j ACCEPT
-A INPUT -p tcp -m tcp --dport 3306 -j REJECT --reject-with icmp-port-unreachable

You're done with the master configuration. Let's open a terminal to our slave server (let's say she is called athena):

Check whether we have access to the master:

athena:~# mysql -u replication_user -p -h scherie
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 38
Server version: 5.0.45-Debian_1-log Debian etch distribution

We can restore now the database dump from the master:

athena:~# bzip2 -d insolitus.sql.bz2
athena:~# mysql -u root -p insolitus < insolitus.sql
Enter password:

Add the following data to the slave's my.cnf, under the [mysqld] section:

server-id                   = 2
master_host             = ip_of_master_server
master_user             = replication_user
master_password    = password
replicate_do_db       = database_to_be_replicated

Restart mysql:

athena:~# /etc/init.d/mysql restart
Stopping MySQL database server: mysqld.
Starting MySQL database server: mysqld.

Connect as root to our slave server:

athena:~# mysql -u root -p

Stop the slave:

mysql> slave stop;
Query OK, 0 rows affected (0.00 sec)

Now, we need those File and Position parameters we got from the master, change the values on master_log_file and master_log_pos for those you wrote down previously:

mysql> change master to master_host='ip_of_master_server', master_user='replication_user', master_password='password',master_log_file='mysql-bin.000002', master_log_pos=230;
Query OK, 0 rows affected (0.01 sec)

We start the slave:

mysql> slave start;
Query OK, 0 rows affected (0.00 sec)

Now, if we issue a show slave status, you should see the following variables to 'Yes':

mysql> show slave statusG;

          Slave_IO_Running: Yes
          Slave_SQL_Running: Yes

If you have Slave_IO_Running set to No, you haven't probably locked correctly the database prior to dumping it. You will have to repeat the dumping process locking correctly the database.

If everything is correct, your database will be automatically replicated. Doing a simple show processlist; in either the master or the slave will show whether the communication is working.

You can also take a look at the full guide to replication at the MySQL official site.



Trackback URL for this entry:

Here's what others have to say about 'Setting up database replication on MySQL':

Step-by-step: how to setup MySQL Database replication |
Tracked on Thursday, January 10 2008 @ 05:49 PM CET


Post a comment


About is a blog about technology, opinion and random thoughts written by Daniel Alvarez, a computer engineer currently living in Zurich, Switzerland.


User Functions



Lost your password?

Latest posts

Stories last 24 hours

No new stories

Comments last 2 days

No new comments

Trackbacks last 2 days

No new trackback comments

Links last 2 weeks

No recent new links