Website Scripts And Cloud Tutorials

How to configure Master Slave Replication in MySQL 5.7 Step By Step

Byadmin

Sep 29, 2015
Mysql Database Backup Options

Last updated on March 22nd, 2022 at 06:09 pm

Click to rate this tutorial!
[Total: 0 Average: 0]

In a typical MySQL Master Slave environment replication data stored in the master database will get automatically replicated to slave. This tutorial will help you configure a MySQL Master Slave replication with ease. Let us take 2 MySQL servers as an example.

Database Server Details

Server A (Master)
IP Address= 172.31.69.173
MySQL Server version= 5.7.37 
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic
Replica User: repl_user_connect

Server B (SLAVE)
IP Address=172.31.79.222 
MySQL Server version= 5.7.37
DISTRIB_ID=Ubuntu
DISTRIB_RELEASE=18.04
DISTRIB_CODENAME=bionic

Step 1 (Performed in Master server 172.31.69.173)

On Master server, Run the below commands inside the MySQL prompt. You are basically creating the user named repl_user_connect with password slavepwd. After that step we are granting replication permission for the user from the slave server.

mysql> CREATE USER 'repl_user_connect'@'172.31.79.222' IDENTIFIED BY 'slavepwd';
Query OK, 0 rows affected (0.02 sec)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_user_connect'@'172.31.79.222';
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000008 |      845 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Make a note of the File and Position, in my case it is mysql-bin.000008 and 845





STEP 2 (Performed in Slave server 172.31.79.222)

——-
In the slave machine run these commands, First stop the slave threads. After that we are asking the slave server to connect to master using the username and password we created in Step 1. Also make sure to update the MASTER_LOG_FILE and MASTER_LOG_POS with the number you got from Step 1

Note: From MySQL 8.0.22, STOP SLAVE is deprecated and the alias STOP REPLICA should be used instead.

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

mysql> CHANGE MASTER TO MASTER_HOST='172.31.69.173', MASTER_USER='repl_user_connect' ,  MASTER_PASSWORD='slavepass', MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=845;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql> start slave USER
Query OK, 0 rows affected (0.00 sec)

mysql> show warnings \G;
*************************** 1. row ***************************
  Level: Note
   Code: 1759
Message: Sending passwords in plain text without SSL/TLS is extremely insecure.
1 row in set (0.00 sec)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                     |
+--------------------+
4 rows in set (0.00 sec)

As you might have noticed we saw a warning. This may be ignored as long as these servers are private and you are sure there is no other security issues.
Tip: If you would like to set up encrypted connection between master and slave then check the documentation.

Also you might have noticed I listed the database in Slave server and there was no database other than the defaults.

STEP 3 (Test database creation in Master )

The final step here is to create a database named test_repl_mistonline_in in master so that we can test whether the replication is working as expected in slave.

mysql> create database test_repl_mistonline_in;
Query OK, 1 row affected (0.00 sec)

Now in Slave, Run the below command and confirm that the database got created.

mysql> show databases;
+-------------------------+
| Database                |
+-------------------------+
| information_schema      |
| mysql                   |
| performance_schema      |
| sys                     |
| test_repl_mistonline_in |
+-------------------------+
5 rows in set (0.00 sec)

mysql>

Troubleshooting

If the replication is not working, Run the slave status command in 172.31.79.222, you should see something similar to the one below for a successful connection (truncated for better visibility)

mysql> show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 172.31.69.173
                  Master_User: repl_user_connect
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000008
          Read_Master_Log_Pos: 845
               Relay_Log_File: 172-31-79-222-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000008
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
           Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           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
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)

If there is an error you should normally see these attributes in the slave status command.

               Last_IO_Errno: 1045
                Last_IO_Error: error connecting to master '[email protected]:3306' - retry-time: 60  retries: 1
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:

If error still exist, try these

  • Check and disable any firewall running on both Master / Slave
  • Make sure MySQL is listening on port 3306 and allow communication through that port (if you need firewall enabled)
  • Make sure the mysql process is binding to the ip address of the respective servers and not the localhost/127.0.0.1 (Check config file of MySQL)

If all the above conditions are checked but still you see an error then restart MySQL process on both Master and Slave and recheck the status.

Final approach, If required redo the setup starting from Step 1.

Click to rate this tutorial!
[Total: 0 Average: 0]

Leave a Reply

Your email address will not be published.