How To Set Up Database Replication In MySQL

Want to support HowtoForge? Become a subscriber!
 
Submitted by falko (Contact Author) (Forums) on Sat, 2006-01-14 23:53. :: High-Availability | MySQL

This is a "copy & paste" HowTo! The easiest way to follow this tutorial is to use a command line client/SSH client (like PuTTY for Windows) and simply copy and paste the commands (except where you have to provide own information like IP addresses, hostnames, passwords,...). This helps to avoid typos.

How To Set Up Database Replication In MySQL

Version 1.1
Author: Falko Timme <ft [at] falkotimme [dot] com>
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 192.168.0.100 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 = 127.0.0.1

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.log
binlog-do-db=exampledb
server-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 '<some_password>'; (Replace <some_password> with a real password!)
FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

USE exampledb;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

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:

quit;


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<password> --opt exampledb > exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

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:
UNLOCK TABLES;
quit;

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


Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Submitted by Jason Bourne (not registered) on Fri, 2013-10-11 19:33.

don't use -p<password> but only -p

when using -p<password> your password will remain in your history. with -p only you will propted for your password

Submitted by Kalyani (not registered) on Mon, 2013-12-09 09:06.

Hi Fellow Mysql Admins,

 

 I have a problem, hope I will be able to get it right for you to understand.

 

 I have a test master Slave setup. We have bin logs stored on / Partition & due to the big size of the db the / volume is fulled up & the server became unrespoisve.

 

 Now we had added more space to / & had deleted both the databases on master & slave & removed all bin logs.

 

 Now if we create database with same name, will that have any negative impact on future replications.?

Thanks in Advance

Submitted by Anonymous (not registered) on Fri, 2013-09-13 10:28.
 Here is another smimple article describing MySQL Replication process. http://bit.ly/16pklCL
Submitted by heperez (not registered) on Fri, 2012-11-09 15:13.

To those who have the problem of locking tables, there is my best solution:

mysqldump -u root -p<password> --master-data --single-transaction exampledb > /algun/path/exampledb.master.sql

Then, in the archive exampledb.master.sql in some place there is the information of MASTER_LOG_FILE and MASTER_LOG_POS needed for the slave configuration.

Loof for a line of the similar to:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=1017207641;
With this procedure mysqldump will lock the tables while is making the dump. Also, it registers the MASTER_LOG_FILE and the MASTER_LOG_POS needed to the slave.
 
Submitted by Anonymous (not registered) on Wed, 2011-12-14 14:09.

should grant permissions for replication user with server ip 

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';

Also

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'Server_IP'%' IDENTIFIED BY '<some_password>';

Submitted by Anonymous (not registered) on Fri, 2011-09-02 12:40.
Will this sync method work on different MySql versions? Im looking a way to have mySql 4.0 data to be syncted with version 5.X. Is that possible?
Submitted by shubes (not registered) on Fri, 2011-05-20 21:40.
It should be noted that the server-id and log parameters go in the [mysqld] section of the configuration file. (DOH!)
Submitted by elpedro (not registered) on Tue, 2011-06-14 21:06.
YES!! An article update noting this would be very appropriate!! 
Submitted by Craig (not registered) on Tue, 2010-12-21 19:21.

If you want o replicate more than one database, just duplicate the conf directive, e.g.

binlog-do-db=database_1 binlog-do-db=database_2

The same goes for the slave side as well although they don't have to match, e.g. some Master DBs replicated to one slave and different databases to another slave.

Also, you can declare specific tables within a given database as well.

Submitted by mysql newbie (not registered) on Tue, 2010-10-19 07:22.

Hello, I am very new to MySQL and I have set up two server and configured them for replication.  The servers are replicating but I had a question.  I was under the impression that if the master has some records added or updated while the slave is stopped, the slave would copy these records.  There are records on an exiting table on both servers

 Not sure if I am missing something to make that happen.  I thought that's what 'seconds behind master' meant

 

 

Submitted by abhishek (not registered) on Sat, 2010-11-27 20:49.
yes, the replication is working properly, the slave can't copy any updated data from the master, in-fact it should not know what's going on the master's side, it should just update the master table when its table is updated. 
Submitted by Haber Pan (not registered) on Sat, 2010-08-21 08:03.

Hello,

I am using this replication on my server. Thank a lot.

Submitted by Bob Johnson (not registered) on Mon, 2010-10-18 18:51.
I'm curious how well replication is actually working and the types of environments that people are using it in / with.  We tried a few projects and had difficulty in getting it working property and consistently.    
Submitted by Steve Riddett (not registered) on Wed, 2010-03-17 11:18.

I found the same thing as the poster above. Exiting the shell removes the READ LOCK.

However, keeping the shell open and starting the dump from another shell prevents the dump from working. It just hangs until I run UNLOCK TABLES in the first shell.

 There must be a better way to do this. Run the dump from inside the shell that's doing the locking perhaps?

Submitted by Andrew (not registered) on Fri, 2010-03-19 18:31.

Hi Steve, 

 I'm encountering the same problem as you. What did you do in the end to solve this ?

 Thanks - Andrew

Submitted by Doma (not registered) on Sun, 2010-02-21 16:39.
Very good article. I am new to mysql, still could configure replication by following steps.
Thanks a lot.
Submitted by gopi (not registered) on Sat, 2009-09-05 12:44.

 Hi,

 Very good artice find much useful and easy understandable. I have bookmarked it. Step by step configuration made it easy to configure.

Submitted by Jason (not registered) on Tue, 2009-05-19 19:05.
Nice article.  We have Mysql running on a windows server.  Is the setup similar?  I want to set up replication or syncronization.  Not for sure which one is harder since I am not too familar with Mysql.  Thanks
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

Cheers

Lionel

 Developer at Shopnics

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: http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-database-replication/ 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).

 Cheers,

 Leon

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

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 master.info file in the mysql data directory where it stores the master details, along with it's current synchronization position. The master.info 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 master.info 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
Submitted by email to fax (not registered) on Thu, 2013-05-16 20:36.
 Hi,

 Very nice article, how to make MySQL Multi-Master Replication?

Thank you

 

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.
Submitted by Mike (not registered) on Fri, 2011-02-18 23:48.
How can i use it on my server with the same replication structure.
Submitted by Mike (not registered) on Thu, 2011-04-14 20:50.
I think this is going on the board to get things done.