How to set up Database replication in MySQL

How to set up Database replication in MySQL

This article 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 (say we can call it as 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 method or policy because an accidentally issued DELETE or ALTER command will also be carried out on the slave; but replication can help protect against hardware failures though.

 

Hire Bobcares Linux Server Administrators
Get super reliable servers and delighted customers

See how we do it!

 

 

2. Configure The Master:

We can configure the master server for replication using the mysql configuration file /etc/my.cnf. We have to enable networking for MySQL, and MySQL should listen on all IP addresses, therefore we will comment these lines (if already existing in my.cnf)

 #skip-networking
 #bind-address            = 127.0.0.1

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

log-bin
binlog-do-db=db_test
server-id=1

Then restart MySQL:

 # /etc/rc.d/init.d/mysql restart

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

mysql> GRANT REPLICATION SLAVE ON *.* TO 'u1@%' IDENTIFIED BY 'p1';
mysql> FLUSH PRIVILEGES;

Next (still on the MySQL shell) do this:

mysql> USE db_test;
mysql> FLUSH TABLES WITH READ LOCK;
mysql> SHOW MASTER STATUS;

The sample master status will be as follows:

mysql> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| rs2-bin.000063 |       79 | db_test      |                  |
+----------------+----------+--------------+------------------+
1 row in set (0.00 sec)

We need this information later on for the slave configuration Then leave the MySQL shell:

mysql> quit;

 

3.Configure The Slave:

We can configure the slave server for replication using the file /etc/my.cnf. We have to tell MySQL on the slave that it is the slave, that the master is H1, and that the master database to watch is db_test. Therefore we add the following lines to /etc/my.cnf

server-id=2
master-host=H1
master-user=u1
master-password=p1
master-connect-retry=100
replicate-do-db=db_test

The master-user and master-password are those to which we set when we granted REPLICATION SLAVE permission on the master. The server-id must be a unique number, different to the master or any other slaves in the system. The master-connect-retry is time in seconds for the slave to attempt to reconnect if the master goes down. 60 seconds is default.

Then restart MySQL:

# /etc/rc.d/init.d/mysql restart

 

4.Data Transfer:

There are two possibilities to get the existing tables and data from db_test 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.

(a)For Mysqldump method:

On master server, please take the dump of the database using

# mysqldump  db_test>db_test.sql
Then scp the file db_test.sql to slave server.
# Scp db_test.sql root@slave_serverip:/root
root@slaveip's password:

Restore the db_test.sql file at slave server

# mysqldump db_test < db_test.sql

(b)To LOAD DATA FROM MASTER:

Please perform the following at Master end:

# mysql -u root -p
Enter password:
mysql> UNLOCK TABLES;
mysql> quit;

Please perform the following at Slave end:

# mysql -u root -p
Enter password:
mysql> LOAD DATA FROM MASTER;
mysql> quit;

 

5. Replication in action:

Once the mysql master and slave configuration completed, we can check the replication action using the command ‘SHOW SLAVE STATUS’.

We can check if the slave is running correctly by looking at the Slave_IO_Running and Slave_SQL_Running. The most important field is the Last_Error field.

Mysql> SHOW SLAVE STATUSG

This will give the result as follows:
mysql> SHOW SLAVE STATUSG
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: H1
                Master_User: u1
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: rs2-bin.000063
        Read_Master_Log_Pos: 346
             Relay_Log_File: vrh-relay-bin.000001
              Relay_Log_Pos: 312
      Relay_Master_Log_File: rs2-bin.000063
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB: db_test
        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: 346
            Relay_Log_Space: 312
            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: 0
1 row in set (0.00 sec)

If you need to force the slave to begin at a certain point, usually when the master has been running with an active binary log, you can do so as follows ( refer the master status value). The following starts with the 63rd binary log, as position 79.

mysql> CHANGE MASTER TO MASTER_HOST='H1',
MASTER_USER='u1',MASTER_PASSWORD='p1', MASTER_LOG_FILE='rs2-bin.000063', MASTER_LOG_POS=79;

 

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

The SLAVE START and SLAVE STOP commands are used to manually stop and start the slave. The slave will also always stop if it comes across an error while replicating.

mysql> SLAVE STOP;
mysql> SLAVE START;

On active databases, the binary logs tend to grow quite quickly. We can use RESET MASTER to clear them. The RESET MASTER command tells the master to flush all its binary logs and start fresh. But while replication in action, we will have to execute the following.

mysql> PURGE MASTER LOGS TO 'rs2-bin.000063';

 

6. Options:

By default, the slave will replicate everything, but you can change this behavior with the following options in the slave configuration file (my.cnf).

replicate-do-db=db_name (replicate this database)
replicate-ignore-db=db_name (don't replicate this database)
replicate-do-table=db_name.table_name (replicate this table)
replicate-ignore-table=db_name.table_name (don't replicate this table)

 

7. Testing:

At master end if you are modifying the database db_test, it will reflect on slave server in real time using replication.

Example: On the master server if we are inserting the values as follows:-

mysql> use db_test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

 

mysql> show tables;
+-------------------+
| Tables_in_db_test |
+-------------------+
| staffactive       |
+-------------------+
1 row in set (0.00 sec)
mysql> desc staffactive;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| username | varchar(20) |      |     |         |       |
| date     | varchar(20) |      |     |         |       |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> INSERT INTO staffactive (username,date) VALUES ('savi','20070305');
Query OK, 1 row affected (0.02 sec)
mysql> INSERT INTO staffactive (username,date) VALUES ('testuser', 'testdate');
Query OK, 1 row affected (0.00 sec)
mysql> select * from staffactive;
+----------+----------+
| username | date     |
+----------+----------+
| savi     | 20070305 |
| testuser | testdate |
+----------+----------+
2 rows in set (0.00 sec)

mysql> q

Bye

This will reflect on the slave server in real time as follows:-

mysql> use db_test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from staffactive;
+----------+----------+
| username | date     |
+----------+----------+
| savi     | 20070305 |
| testuser | testdate |
+----------+----------+
2 rows in set (0.00 sec)

mysql> q

Bye

 


Articles by Savitha

About the author:
Savitha Nair works as Sr. Software Engineer Grade 11 in Bobcares.com. Savitha has worked in Bobcares for 3 years and is a passionate advocate of linux server administration.



13 Comments

  1. HI
    THANKS FOR GIVING ALL THE STEP FOR REPLICATION
    I AM ALREADY DONE ALLTHESE THING BUT REPLICATION
    IS NOT DONE. ONE VALUE INSERT INTO MASTER DOES NOT SHOW IN SLAVE OR VICE-VERSA

    PLEASE GIVE ME ANY SUGESSION

    THANKS
    SANDEEP BENIWAL

    Reply
  2. Hello Sandeep,

    Make sure that your master as well as the slave has got the same database content.

    The best idea is to take the dump on master and restore it on slave. Make sure that the master database is not updated during this process.

    You can stop the mysql service on master during the restoration process on slave.

    Once this step is fine, proceed with Step 5 onwards.

    Regards,
    Savitha

    Reply
  3. am new bibie to mysql, was having 2 servers and planning to test the mysql database replication , when I gone through the document was quiet confused about the step 4 and 5
    Data transfer who mentioned 2 methods, if I proceed with a. do I need to set up the later steps.

    Thanks in advance

    Reply
  4. Hi Savitha,

    Thanks alot for the valuable document,

    Please ignore the previous post, Its my ignorance,

    Was about try and hope everything will work fine with application

    Reply
  5. Hi Savitha,

    I am facing a problem in Master-Master replication. I have setup
    two databases db1 and db2 both configured to run as Master as well as Slave.

    Now the problem is that when the network breaks, db1 goes down and db2 takes its place, which is fine. When db1 gets back online it starts logging again, which is also fine. But when db1 gets back online it is not in sync with db2. I have to manually run STOP SLAVE and START SLAVE commands to make both the databases in sync with each other. Can you figure what could be the problem? Is a requirement to manually run STOP and START SLAVE commands.

    Looking forward to hearing from you.

    Reply
  6. Hi,

    Can I make slave and master on the single machine ? If Yes, How ? I would have just one My.ini file , so how can I configure it for both slave as well as master ?
    I am trying this to learn the replication mechanism in MySQL.

    Thanks,
    Aditya

    Reply
  7. hello savitha,

    i was able to successfully replicate a single database between two servers..
    for testing the replication i did following
    i)added new records to the mater, reflected in the slave
    ii)added new table in the master ,reflected in the slave

    with same apporach i tried the two databases with the following conditions ..
    but when i tried
    i)added new records to the master in the second database
    not reflected in the slave in the second database
    ii)added new table for the second database not reflected in the
    slave for the second databse
    note: with this two db architecture i was still be able to see whatever
    changes happeining in the first database..

    my aim is :

    i have two databases under mysql 5.0 in one server .. i need to replicate both databases to the slave ..

    can you helpme out by directing in the right direction

    my configurations in the master
    /etc/my.cnf [master]
    log-bin = /var/log/mysql/mysql-bin.log
    innodb_flush_log_at_trx_commit=1
    sync_binlog=1
    binlog-do-db=db1
    binlog-do-db=db2
    server-id=1

    /etc/my.cnf [slave]
    old_passwords=1
    server-id =2
    master-host=192.168.1.3
    master-user=slave
    master-password=slavepw
    master-connect-retry = 60
    replicate-do-db=db1
    replicate-do-db=db2

    Reply
  8. Hello Savitha
    Could you please help me.
    I wana use our local magento server database to update the additions and deletions of products to the live server and vice versa.
    Is that possible?
    Because We want to work on local server on the back end and that should be updated on the live server; that is for SEO purpose also.

    Thanks

    Reply
  9. Hi Savitha,

    It would be very nice of u if could help me with this issue: After running the Show slave status;
    Error ‘Duplicate entry ‘2491’ for key ‘PRIMARY” on query. Default database: *****. Query: ‘INSERT INTO `***’

    what could be the problem?

    Reply
  10. Hi Savitha,

    I hope you could provide me an oriantatio, the problem that i having with replication is related with Seconds_Behind_Master. I’ve tried so many possible solutions, since increase values to tune the mysql configuration, use memory drive for the mysql temp directory, and the list still so go on. Unfortunately i did not found the problem.

    Just for the record both server and master are enough sized with RAM, CPU and harddrive (RAID 1 for OS, and RAID 10 for DATA). Reading on internet and googleing i understand that could be so many reason for the replication is out of phase.

    Best regards,
    RR

    Reply
  11. Savitha,

    Thanks for the article , however we are seeing mysql slave running behind master not sure what would be the cause.

    It shows as 43876 seconds behind. Any pointers on troubleshooting this issue?

    Reply
  12. how to mysql apply in high availability?

    Reply
  13. how to add product master of point of sale system in android with coding

    Reply

Trackbacks/Pingbacks

  1. Twitter Trackbacks for Bobcares Blog [bobcares.com] on Topsy.com - [...] Bobcares Blog bobcares.com/blog/?p=108 – view page – cached Bobcares.com provides Quality Outsourced Technical Support to Hosting companies…

Submit a Comment

Your email address will not be published. Required fields are marked *

KEEP OUT MALWARE & HACKERS

Hackers

Security specialists will audit, update, patch, harden and monitor your server 24/7.

Starting at $69.99/month

SEE PLANS