Steps To Configure Master Slave MySQL Replication

MySQL Replication is a procedure that permits to duplicated or exchanged information starting with one MySQL server then onto the next fasterly. The master server will duplicate every one of its information to slave server without expending much time. That can supportive for some reasons like a reinforcement of the database, investigate the database without utilizing the primary database, or basically as intends to scale out.

This article will encourages you to setup MySQL replication. We will cover straightforward case of MySQL replication where one master server will send or duplicated data to the single slave server. For the procedure to work, you will require two IP addresses: one of the master server and one of the slave.

MySQL Replication Advantages

• Offload a few quiries from one server to other.
• Utilize master for all writes and Use slave for all reads.
• All progressions can be duplicating. This is the most secure type of replication.
• Slave server also can be used to take backup from it.

This article assume that you have a user with sudo benefits and have MySQL installed on the system. If MySQL is not installed on the system than type the below command to install.

$ sudo apt-get update
$ sudo apt-get install mysql-server mysql-client

Configure MySQL Master Server

After installing MySQL server on your system, edit the MySQL configuration file “/etc/mysql/my.cnf” In this file you have to make some changes to replicate the master data to slave server

# vim /etc/my.cnf

#Add below lines under [mysqld] section,
#Change standard IP address with your system private or local address.

bind-address = 192.168.0.5

#Next uncomment the below line and make sure the server-id your using does not match with other server-ids. This id must be unique.

server-id = 1

#Move on to the log_bin line. This is where the real details of the replication are kept. The slave is going to copy all of the changes that are registered in the log. For this step, we simply need to uncomment the line that refers to log_bin.

log_bin  = /var/log/mysql/mysql-bin.log

#Now use the database that will be replicated on the slave server. You can include more than one database by repeating below lines with the database name.

binlog_do_db = mydb

Now save and close the file.

Restart MySQL Server

Type the below command to restart the MySQL server.

$ sudo /etc/init.d/mysql restart

Create Slave User and Grant Privileges

Now login into mysql shell and type the below command:

$ mysql -u root -p

Type root password here.

Then run the below command to provide the grant privileges to the slave:

$ GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY 'password';

$ FLUSH PRIVILEGES;

Now select the database which you want to replicate to the slave server and check the master status.

mysq> USE mydb;
mysql> FLUSH TABLES WITH READ LOCK;

Now run the show master status command:

mysql> show master status;

+------------------+----------+--------------+------------------+

| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |      557 | mydb       |                  |

+------------------+----------+--------------+------------------+

1 row in set (0.00 sec)

Note down the above position “557” which the slave database will start replicating.

Take MySQL Dump Of Database

Now dump the MySQL database typing below command:

$ mysqldump -u root -p testdb > mydb.sql

After dump completed now unlock the table:

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.01 sec)
mysql> quit
Bye

Now your master database server has is ready.

Configure MySQL Slave Server

Create A Database

Now configure slave server for replication first you have to install and MySQL server.

$ sudo apt-get update
$ sudo apt-get install mysql-server mysql-client

Once installation is done login with slave server and create the new database that you will replicate from the master.

$ mysql -u root -p
Type your root password here"

mysql> CREATE DATABASE testdb;
mysql> EXIT

Restore MySQL Dump File On Slave Server

Now you have restore the dump file that we taken from master database server.

$ mysql -u root -p mydb < mydb.sql

Slave Configuration File

Now you need to configure the slave server the same way we did for master server.

# vim /etc/mysql/my.cnf

#Change the server-id and use different no. here we have used no. 2 as server-id.

server-id = 2

#Following that, make sure that you have the following three criteria appropriately filled out.

relay-log     = /var/log/mysql/mysql-relay-bin.log
log_bin       = /var/log/mysql/mysql-bin.log
binlog_do_db  = mydb

Note :- You have to create a relay-log file in /var/log/mysql/ location.

$ touch /var/log/mysql/mysql-relay-bin.log

Restart MySQL Server

$ sudo /etc/init.d/mysql restart

Setup Replication

Now you have to enable the replication using the MySQL shell.

$ mysql -u root -p

Now run the change master to master command like below:

$ CHANGE MASTER TO MASTER_HOST='192.168.0.5',MASTER_USER='slave_user', MASTER_PASSWORD='password', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=557;

Start the Slave server

mysql> start slave;

Now run the below command to show the slave status;

mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.5
                  Master_User: slave_user
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 199588548
               Relay_Log_File: mysql-relay-bin.000001
                Relay_Log_Pos: 557
        Relay_Master_Log_File: mysql-bin.002365
             Slave_IO_Running: Yes
            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: 199588548
              Relay_Log_Space: 570501271
              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
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 81
                  Master_UUID: 3278afa0-b348-11e4-9942-ee9830262d73
             Master_Info_File: /var/lib/mysql/data/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
1 row in set (0.03 sec)

ERROR:
No query specified

In slave status you have to check two things:

Slave_IO_Running: Yes
Slave_SQL_Running: Yes     or

Seconds_Behind_Master: 0

If Slave_IO_Running and Slave_SQL_Running is showing yes means slave is running ok or if you see Seconds_Behind_Master: 0 means no delay in replication.

That’s it.

Thanks:)