Saturday, April 18, 2009

How To Set Up Database Replication In MySQL

How To Set Up Database Replication In MySQL

Version 1.1
Author: Falko Timme
Last edited: 01/14/2006

This tutorial describes how to set up database replication in MySQL. MySQL replication allows you to have an exact copy of a database from a master server on another server (slave), and all updates to the database on the master server are immediately replicated to the database on the slave server so that both databases are in sync. This is not a backup policy because an accidentally issued DELETE command will also be carried out on the slave; but replication can help protect against hardware failures though.

In this tutorial I will show how to replicate the database exampledb from the master with the IP address to a slave. Both systems (master and slave) are running Debian Sarge; however, the configuration should apply to almost all distributions with little or no modification.

Both systems have MySQL installed, and the database exampledb with tables and data is already existing on the master, but not on the slave.

I want to say first that this is not the only way of setting up such a system. There are many ways of achieving this goal but this is the way I take. I do not issue any guarantee that this will work for you!

1 Configure The Master
First we have to edit /etc/mysql/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we comment out these lines (if existant):

#skip-networking#bind-address =

Furthermore we have to tell MySQL for which database it should write logs (these logs are used by the slave to see what has changed on the master), which log file it should use, and we have to specify that this MySQL server is the master. We want to replicate the database exampledb, so we put the following lines into /etc/mysql/my.cnf:

log-bin = /var/log/mysql/mysql-bin.logbinlog-do-db=exampledbserver-id=1

Then we restart MySQL:

/etc/init.d/mysql restart

Then we log into the MySQL database as root and create a user with replication privileges:

mysql -u root -p
Enter password:

Now we are on the MySQL shell.

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY ''; (Replace with a real password!)

Next (still on the MySQL shell) do this:

USE exampledb;

The last command will show something like this:

+---------------+----------+--------------+------------------+| File | Position | Binlog_do_db | Binlog_ignore_db |+---------------+----------+--------------+------------------+| mysql-bin.006 | 183 | exampledb | |+---------------+----------+--------------+------------------+1 row in set (0.00 sec)

Write down this information, we will need it later on the slave!

Then leave the MySQL shell:



There are two possibilities to get the existing tables and data from exampledb from the master to the slave. The first one is to make a database dump, the second one is to use the LOAD DATA FROM MASTER; command on the slave. The latter has the disadvantage the the database on the master will be locked during this operation, so if you have a large database on a high-traffic production system, this is not what you want, and I recommend to follow the first method in this case. However, the latter method is very fast, so I will describe both here.

If you want to follow the first method, then do this:

mysqldump -u root -p --opt exampledb > exampledb.sql (Replace with the real password for the MySQL user root! Important: There is no space between -p and !)

This will create an SQL dump of exampledb in the file exampledb.sql. Transfer this file to your slave server!

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.


Finally we have to unlock the tables in exampledb:

mysql -u root -p
Enter password:

Now the configuration on the master is finished. On to the slave...

2 Configure The Slave

On the slave we first have to create the database exampledb:

mysql -u root -p
Enter password:


If you have made an SQL dump of exampledb on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created exampledb on the slave:

mysql -u root -p exampledb < /path/to/exampledb.sql (Replace with the real password for the MySQL user root! Important: There is no space between -p and !)

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.


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


Then we restart MySQL:

/etc/init.d/mysql restart


If you have not imported the master exampledb with the help of an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:

mysql -u root -p
Enter password:

If you have phpMyAdmin installed on the slave you can now check if all tables/data from the master exampledb is also available on the slave exampledb.


Finally, we must do this:

mysql -u root -p
Enter password:

In the next command (still on the MySQL shell) you have to replace the values appropriately:


MASTER_HOST is the IP address or hostname of the master (in this example it is
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


That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!



A very helpful article
Submitted by lionel (not registered) on Mon, 2009-04-13 15:24.
Found this article very helpful. Followed it step by step and found no problems at all. Used to think mysql replication to be a big deal but this article made it look so simple. Thanks Guys



Developer at Shopnics

reply | view as pdf
A few more details
Submitted by d60eba (registered user) on Thu, 2008-01-10 12:11.
This is a great tutorial - I used it to set everything up myself. However, I found a few details lacking about what was going on behind the scenes, and also how to recover after a server crash. Anyway, I've written everything up here: It's for MySQL 5.0 on Centos 4, but is good for other distros. (I've credited you with a link at the bottom).



reply | view as pdf
locking/unlocking tables warning
Submitted by taikonautzero (registered user) on Mon, 2006-12-18 13:57.
When locking the tables with FLUSH TABLES WITH READ LOCK; do not quit the mysql shell else you will lose the lock, use another shell to do the db dump instead.

FreeBSD version 4.0.26

MySQL version 4.10

reply | view as pdf
master details in slave my.cnf not explicitly needed
Submitted by Anonymous (not registered) on Tue, 2006-01-17 00:29.
Nice and concice howto (too bad I only found it now while I figured this stuff out a month ago)! I'd like to remark that when editing the slave my.cnf config file, the master details do not necessarilly have to be filled in there, since the slave mysql server doesn't read this config when restarting. When issuing the CHANGE MASTER TO command, the slave mysql server creates a file in the mysql data directory where it stores the master details, along with it's current synchronization position. The file contains thus everything that the slave needs when restarting. Reason for not having the master details (including slave_user password) in the my.cnf config file is by default world readable (at least in debian GNU/Linux it is) and the can only be read by mysql (and root, obviously). Furthermore I'd like to point out another (fast) way of getting the master data to the slave when setting up replication: with the read lock still on (don't close the mysql client in which you issued the FLUSH TABLES WITH READ LOCK command, otherwise the lock will be gone); create a tarball of the entire mysql data directory (or only the desired databases, the filenames to include are obvious), release the lock, copy the tarball to the slave machine using ftp, scp or whatever, change to the data directory overthere and extract the tarball. Take special care when copying the mysql database to the slave this way, because you will override any existing account on the slave. In debian, dpkg-reconfigure mysql-server might be needed to resolve problems with the debian-sys-maint-user that arise when replacing the entire mysql database. Good luck, Thomas
reply | view as pdf
debian-sys-maint user
Submitted by Anonymous (not registered) on Fri, 2006-02-03 23:39.
When copying everything, including the mysql database on Debian, you will screw up the password for the debian-sys-main account. Just grab the password on your master server from /etc/mysql/debian.cnf and put this password in the same file on your new slave.

The issue I've faced:ERROR
Submitted by Valery (not registered) on Thu, 2008-12-11 09:28.
The issue I've faced:

ERROR 1218 (08S01): Error connecting to master: Master is not configured

In my case it was wrong master configuration placement in my.conf. Don't put it in the end of file. Place it somewhere in the middle ;)

reply | view as pdf
Submitted by Jason (not registered) on Fri, 2008-11-21 15:42.
putting the settings in the my.cnf file and doing:


in the mysql command line does the same. so only setting the my.cnf and restarting mysql does the trick.

reply | view as pdf
Won't work on newer MYSQL versions
Submitted by xabin (registered user) on Wed, 2008-03-12 19:25.
The syntax 'LOAD DATA FROM MASTER' will not work on the newer versions of MySQL-server, see this page;

reply | view as pdf
another thing that will help here
Submitted by nephish (registered user) on Wed, 2007-05-02 00:00.
if you start mysql with /etc/init.d/mysql restart, you cannot just put the lines above for the my.cnf file anywhere in the file for the slave.

they must be in the [mysqld] block.

took me two hours to figgure that one out.

reply | view as pdf
keep in mind this note on r
Submitted by Anonymous (not registered) on Tue, 2006-06-06 11:00.
keep in mind this note on replication (found on mysql doc site), I lost 2 days trying to understand why my DBs where not replicating!

Note that if you client does not do a "USE
dbname", binlog-do-db=dbname will not binlog a
query like: "update in dbname.foobar set foo=1"

You explicitly have to do a USE before a query in
order to have your query binlogged, it looks
like. Replication on the slave side can do
wildcard matches .. but the master cannot (a la
binlog-wild-do-table=dbname.%). So make sure your
clients do a use, if you plan to replicate those
tables it updates.

reply | view as pdf
Missing a few options, more detailed commands for slave
Submitted by Anonymous (not registered) on Mon, 2006-01-16 19:02.

You need to read the database replication documents a little more in depth; depending on your version of MySQL and wether or not you use InnoDB, you want to also include something like the following on the master server:

innodb_flush_log_at_trx_commit = 1
sync-binlog = 1
log-bin = /path/to/mysql/data/master-bin
log-bin-index = /path/to/mysql/data/master-bin.index

On the slave, you want to enable (usually) read-only behaviour to revent accidental commits as well as the relay logs:

relay-log = /path/to/mysql/data/slave-relay-bin
relay-log-index = /path/to/mysql/data/slave-relay-bin.index

A better method to ensure exact replication would go like the below:

1) start master

2) grant replication to slave

3) run "show master status" and record the index file and it's offset position

4) start the slave

5) run "stop slave"

6) run "change master to master_host='[MASTER HOST IP]', master_user='[USER]', master_password='[PASSWORD]', master_log_file='[NAME]', master_log_pos=[POSITION]" (from #3)

7) run "start slave"

Now go ahead and set your root password, create your databases, etc. Everything done on the master will replicate faithfully over to the slave.

reply | view as pdf
Little tip
Submitted by Anonymous (not registered) on Mon, 2006-01-16 17:26.
I've done the exact same thing a while ago. I wrote a little perlscript to keep the databases in sync (when run it makes sure they're synced). Some people might find it helpful.

1 comment:

Anonymous said...

let me share my experience with regard to the service of sql 2005 database recovery software, it automatically eliminates data corruption issues in selected databases