How To Set Up Database Replication In MySQL - Page 2

2 Configure The Slave

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

mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;


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

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 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to /etc/mysql/my.cnf:

server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb

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:
LOAD DATA FROM MASTER;
quit;

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:
SLAVE STOP;

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

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

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

START SLAVE;
quit;

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


Links

Share this page:

58 Comment(s)

Add comment

Comments

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

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

 Hi,

 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

Cheers

Lionel

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

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

 Hi,

 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

Hello,

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

Also

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. http://bit.ly/16pklCL

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. http://files.printf.dk/software/clustersync.txt

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

http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html

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

read-only
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; http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html

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

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

CHANGE MASTER TO MASTER_HOST='192.168.0.100', 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:

 GRANT SUPER,REPLICATION CLIENT,RELOAD, REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<pass>';

 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.

Christophe

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 http://dev.mysql.com/doc/refman/5.0/en/replication-compatibility.html

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:

replicate-do-db=exampledb1
replicate-do-db=exampledb2
... 

 

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

Yes it works. Follow this link for more instruction : http://forums.mysql.com/read.php?26,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

Hi,

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

hi,

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_Host: 192.168.1.104
                  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
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 190
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 1130
                Last_IO_Error: error connecting to master 'salve@192.168.1.104:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
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: 
http://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_increment

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_Host: 127.0.0.1
                  Master_User: root
                  Master_Port: 9306
                Connect_Retry: 1
              Master_Log_File:
          Read_Master_Log_Pos: 4
               Relay_Log_File: slave-relay-bin.000001
                Relay_Log_Pos: 4
        Relay_Master_Log_File:
             Slave_IO_Running: No
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 0
              Relay_Log_Space: 106
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No

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

No qurey specified.

Advance Thanks for all member

Kalings

 

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: 

http://askubuntu.com/a/129793/82519

 

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

Hi,

LOAD DATA FROM MASTER doesn't work with InnoDB tables (see http://dev.mysql.com/doc/refman/4.1/en/innodb-and-mysql-replication.html). My master server is remote , how can I replicate it to a slave server that I start from scratch?

mcn

Who can help me ? thank uou very much