Oracle Solutions We believe in delivering tangible results for our customers in a cost-effective manner
Configuration of MYSQL Master – Slave Replication
Environment:-
Two Server’s with MYSQL database and different hosts/nodes, Master (172.21.50.162) and Slave (10.218.86.8). Replication will be configured between these two servers as MYSQL Master – Slave replication.
On Master (172.21.50.162):-
- Login to the Master (172.21.50.162) environment.
- Start the MYSQL workbench
- Connect to the server using the root user and password.
- Use this command to check the status of the slave server at the moment and verify the following screenshots having the same value.
Query:-
mysql> select host,user from mysql.user where user=’rep_user’;
Output:-
Empty set (0.00 sec)
- When the query returns null, now create the user naming ‘rep_user’ and make sure to use ‘mysql_native_password’ switch in the command
Query:-
mysql> CREATE USER ‘rep_user’@’172.21.50.162’ IDENTIFIED WITH mysql_native_password BY ‘Docker#2023’;
Output:-
Query OK, 0 rows affected (0.01 sec)
- Crosscheck the user creation on the specified host
Query:-
mysql> select host,user from mysql.user where user=’rep_user’;
Output:-
+—————+———+
| host | user |
+—————+———+
| 172.21.50.162 | rep_user |
+—————+———+
1 row in set (0.00 sec)
- Now grant the Replication on the created ‘rep_user’ on all the databases.
Query:-
mysql> GRANT REPLICATION SLAVE ON *.* TO ‘rep_user’@’172.21.50.162’;
Output:-
Query OK, 0 rows affected (0.01 sec)
- Use the following command to perform the checkpoint .
Query:-
mysql> FLUSH PRIVILEGES;
Output:-
Query OK, 0 rows affected (0.01 sec)
- Use the following command to enable a read lock on the tables in order to take a consistent backup
Query:-
mysql> FLUSH TABLES WITH READ LOCK;
Output:-
Query OK, 0 rows affected (0.01 sec)
- Note down the Master log Position
Query:-
mysql> SHOW MASTER STATUS;
Output:-
- Login to CMD of the server and take the backup on Master of all the databases you want to configure Replication of.
Query:-
Mysql>MYSQL –uroot –p
mysqldump -uroot -p -h<Source> –all-databases –master-data > <location><filename>.sql
- Now unlock the tables on Master.
Query:-
mysql> UNLOCK TABLES;
Output:-
Query OK, 0 rows affected (0.00 sec)
- Now Transfer Database Backup Dump File to Slave Server.
On Slave (10.218.86.8):-
Following are the steps to be performed.
- Check you current Databases.
Query:-
mysql> show databases;
Output:-
+——————–+
| Database |
+——————–+
| information_schema |
| mysql |
| performance_schema |
| sys |
- Restore the database dump into the slave server.
Query:-
mysql> mysql < /u01/mysql/backup/alldbdump.sql
- Now setup slave to communicate to Master, make sure to enter the same log file and log_pos noted on point ‘7’ while configuring master.
Query:-
mysql> CHANGE MASTER TO MASTER_HOST=’10.142.10.181′,MASTER_USER=’rep_user’,MASTER_PASSWORD=’Docker#2021′, MASTER_LOG_FILE=’TEST-SQL1-bin.000003′,MASTER_LOG_POS=428;
- Now start the Slave for replication.
Query:-
mysql> start slave
- Now check the slave status and also very the same as the screenshot below.
Query:-
mysql> show slave status
