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
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):

#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.log

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!)

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<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:

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

Share this page:

58 Comment(s)

Add comment


From: Anonymous at: 2006-01-16 23:29:48

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

From: Anonymous at: 2006-02-03 22:39:48

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.

From: Mike at: 2011-02-18 22:48:45

How can i use it on my server with the same replication structure.

From: Mike at: 2011-04-14 19:50:12

I think this is going on the board to get things done.

From: email to fax at: 2013-05-16 19:36:34


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

Thank you


From: Jason at: 2009-05-19 18:05:04

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

From: at: 2006-12-18 12:57:07

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

From: lionel at: 2009-04-13 14:24:52

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

From: at: 2008-01-10 11:11:33

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).



From: gopi at: 2009-09-05 11:44:48


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

From: Doma at: 2010-02-21 15:39:12

Very good article. I am new to mysql, still could configure replication by following steps.
Thanks a lot.

From: Steve Riddett at: 2010-03-17 10:18:06

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?

From: Andrew at: 2010-03-19 17:31:09

Hi Steve, 

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

 Thanks - Andrew

From: Haber Pan at: 2010-08-21 07:03:02


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

From: Bob Johnson at: 2010-10-18 17:51:37

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.    

From: mysql newbie at: 2010-10-19 06:22:10

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



From: abhishek at: 2010-11-27 19:49:28

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. 

From: Craig at: 2010-12-21 18:21:57

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.

From: shubes at: 2011-05-20 20:40:09

It should be noted that the server-id and log parameters go in the [mysqld] section of the configuration file. (DOH!)

From: elpedro at: 2011-06-14 20:06:35

YES!! An article update noting this would be very appropriate!! 

From: Anonymous at: 2011-09-02 11:40:23

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?

From: Anonymous at: 2011-12-14 13:09:20

should grant permissions for replication user with server ip 

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


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

From: heperez at: 2012-11-09 14:13:53

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.

From: Anonymous at: 2013-09-13 09:28:37

 Here is another smimple article describing MySQL Replication process.

From: Jason Bourne at: 2013-10-11 18:33:28

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

From: Kalyani at: 2013-12-09 08:06:46

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

From: Anonymous at: 2006-01-16 16:26:51

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.

From: Anonymous at: 2013-03-12 22:05:20

Just to be sure...

This is one way replication right? Only data from master flows to slave, not vice versa...? 


From: digitalunity at: 2013-06-12 21:57:40

That's correct, one way (asynchronous) is just from master to slave. Whereas synchronous is both ways and is done using a cluster.


From: Anonymous at: 2006-01-16 18:02:24

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.

From: herald setiadi at: 2009-05-11 16:42:42

Thanks.My mysql replication running well.

Herald BSD

From: Anonymous at: 2006-06-06 10:00:07

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.

From: at: 2007-05-01 23:00:33

    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.




From: at: 2008-03-12 18:25:36

The syntax 'LOAD DATA FROM MASTER' will not work on the newer versions of MySQL-server, see this page;

From: Jason at: 2008-11-21 14:42:38

putting the settings in the my.cnf file and doing:

CHANGE MASTER TO MASTER_HOST='', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

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

From: Valery at: 2008-12-11 08:28:57

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 ;)


From: Christophe at: 2009-07-24 23:19:22

Great tutorial, many thanks!

I also face some small pbs, but nothing really bad.

The privileges to grant to the slave user are:


 And I regret only one thing:

What must be done when the master break down ?

The slave is supposed to run instead, so I think there should be some commands to set ip up back as a normal server.. right ?

And finally when the master is back again, how to set the replication back as before, because the former slave has been running on its own for some time ?

Some answers to theses would be very appreciated.


From: Anonymous at: 2009-07-24 16:56:38

Thanks for the great tutorial. This really streamlines things compared to the mysql manual.

 One thing to remember when trying to do replication between different versions is that the slave needs to be a higher version than the master. Otherwise it may not work. For more info see

From: Marijan at: 2009-07-27 09:46:34

This is great tutorial. 

 Thank you very much.

 I have one question. How I can turn off replication?


From: fredzouille at: 2009-11-27 13:44:25

Great job !

No probleme to activate mysql replication thanks to your howto

Thanks a lot

From: shahrokh at: 2009-12-30 06:29:49

thanks man. You done Great help To Me.

Best Wish For You

From: David at: 2010-03-18 14:30:24

I have been trying for ages to get replication working between MySQL databases, following the instructions in the MySQL documentation to the letter but each time I was confronted with a slave that would not replicate, and its all because they didn't bother to mention "START SLAVE;" at the end of the process!!! Thank you for pointing out the missing step!

From: Jacek at: 2010-05-18 19:08:35

Your method and you've describe it works very well. Well done, thanx.

Can you do something like that for configuration sql server with some data bases?

 I wonder if it works with something like that:



From: at: 2011-12-13 01:02:47

Yes it works. Follow this link for more instruction :,171776,205870

From: Vivek Nandavanam at: 2010-07-22 07:49:23

Thanks to you.

I was able to get the replication of the master's database on the slave.

From: kushal at: 2010-11-20 07:02:41


It is Very Nice post and helpful for me.................................................................
I have done all the steps that u have mentioned but i am getting deadlock error while doing the replication with "statement based logging" format on the master server.
     The error is:-
101112 11:04:00 [ERROR] /usr/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
101112 11:04:00 [ERROR] /usr/libexec/mysqld: Sort aborted
101112 11:08:42 [ERROR] /usr/libexec/mysqld: Deadlock found when trying to get lock; try restarting transaction
101112 11:08:42 [ERROR] /usr/libexec/mysqld: Sort aborted

can you please help me

From: ABC at: 2010-09-11 17:32:29


I followed the same procedure for mysql replication but unable to set up.

 mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_User: salve
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 190
               Relay_Log_File: mysqld-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: No   --------------------------------> says no
            Slave_SQL_Running: Yes
              Replicate_Do_DB: replitest
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 190
              Relay_Log_Space: 106
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1130
                Last_IO_Error: error connecting to master 'salve@' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
1 row in set (0.00 sec)


please help.....

From: Anonymous at: 2011-08-05 04:35:57

check .err file

From: Anonymous at: 2010-11-18 17:27:11

Nice tutorial, and kudos to "Anonymous" who posted Missing a few options, more detailed commands for slave.

No one has mentioned auto_increment, which I believe is recommended and used by most applications.

We're about to begin our first replication effort, and I notice my logs are full of warnings like:

[Warning] Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it uses a system function that may return a different value on the slave. Statement: <query>

The manual discusses auto_increment_increment and auto_increment_offset:

I'm hoping implementing these reduces the number of warnings in the logs.  

Can any of you gurus confirm or deny this is the right/best way to support auto_increment?

From: Anonymous at: 2011-02-11 11:30:43

Hi Every one and Block admin Pls advice me for the mysql replication configuration in two masters

I configured similierlyfor your noted but i have a probles in my configuration

 Please anybody reply very very Urgent.

This is my error,

 mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Connecting to master
                  Master_User: root
                  Master_Port: 9306
                Connect_Retry: 1
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
                   Last_Errno: 0
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 106
              Until_Condition: None
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003
                Last_IO_Error: error connecting to master 'root@' -
retry-time: 1  retries: 10
               Last_SQL_Errno: 0

No qurey specified.

Advance Thanks for all member



From: KOBOI_aceh at: 2013-04-23 08:45:00

Hallo guys...
I have a same problem with U...

please anybody help me


From: Senthil at: 2012-02-22 10:17:42

Thanks.Great tutorial.Its save my time.

From: KarlosB at: 2013-03-22 16:08:58

Awesome!!! Helped me out a lot... There was no problems either

From: bill at: 2011-12-08 23:16:45

Worked like a charm. Thanks - great tutorial.

From: Xavi at: 2011-12-12 20:06:46

Hi man, thanks for this tutorial it's perfect and easy;)

 About errors on show slave status said user cannot connect a possible resolution is check privileges users: if you try connect in node01 to node02  mysql -u slave -p -hnode02 if access is successfully the problem is in the firewall you must be disabled it , if not you in the 2 nodes have done permit access to db remotly.

 sorry for my english and thanks again.

From: M-K at: 2012-10-01 12:30:07

 If you configure the slave like given here on a mysql 5.5 and above it won't start anymore. See e.g. here:


From: Anonymous at: 2013-09-02 15:25:18

Hi, the tutorial is really good.

I have two databases, stored in the same mysql server. For one of them, it works perfectly but for the second one the bin-log file is not being registered. This data base has 1824 columns. Could that be the reason? how could I solve it??

Thanks for your help

Variables (--variable-name=value)
and boolean options {FALSE|TRUE}  Value (after reading options)
--------------------------------- -----------------------------
auto-rehash                       FALSE
character-sets-dir                (No default value)
default-character-set             latin1
comments                          FALSE
compress                          FALSE
database                          (No default value)
delimiter                         ;
vertical                          FALSE
force                             FALSE
named-commands                    FALSE
local-infile                      FALSE
no-beep                           FALSE
host                              (No default value)
html                              FALSE
xml                               FALSE
line-numbers                      TRUE
unbuffered                        FALSE
column-names                      TRUE
sigint-ignore                     FALSE
port                              3306
prompt                            mysql>
quick                             FALSE
raw                               FALSE
reconnect                         TRUE
socket                            /var/lib/mysql/mysql.sock
ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-verify-server-cert            FALSE
table                             FALSE
debug-info                        FALSE
user                              (No default value)
safe-updates                      FALSE
i-am-a-dummy                      FALSE
connect_timeout                   0
max_allowed_packet                16777216
net_buffer_length                 16384
select_limit                      1000
max_join_size                     1000000
secure-auth                       FALSE
show-warnings                     FALSE

From: mcn at: 2015-03-23 16:59:52


LOAD DATA FROM MASTER doesn't work with InnoDB tables (see My master server is remote , how can I replicate it to a slave server that I start from scratch?


Who can help me ? thank uou very much