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:

Suggested articles

32 Comment(s)

Add comment

Comments

By: Anonymous

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

By: Anonymous

Just to be sure...

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

 

By: digitalunity

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

 

By: Anonymous

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.

By: herald setiadi

Thanks.My mysql replication running well.

Herald BSD

By: Anonymous

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.

By:

    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.

 

 

 

By:

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

By: Jason

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.

By: Valery

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

 

By: Christophe

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

By: Anonymous

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

By: Marijan

This is great tutorial. 

 Thank you very much.

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

 

By: fredzouille

Great job !

No probleme to activate mysql replication thanks to your howto

Thanks a lot

By: shahrokh

thanks man. You done Great help To Me.

Best Wish For You

By: David

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!

By: Jacek

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

 

By:

Yes it works. Follow this link for more instruction : http://forums.mysql.com/read.php?26,171776,205870

By: Vivek Nandavanam

Thanks to you.

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

By: kushal

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

By: ABC

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 '[email protected]:3306' - retry-time: 60  retries: 86400
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)

 

please help.....

By: Anonymous

check .err file

By: Anonymous

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?

By: Anonymous

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 '[email protected]:9306' -
retry-time: 1  retries: 10
               Last_SQL_Errno: 0
               Last_SQL_Error:

No qurey specified.

Advance Thanks for all member

Kalings

 

By: KOBOI_aceh

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

please anybody help me

 

By: Senthil

Thanks.Great tutorial.Its save my time.

By: KarlosB

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

By: bill

Worked like a charm. Thanks - great tutorial.

By: Xavi

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.

By: M-K

 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

 

By: Anonymous

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
 

By: mcn

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