MYSQL Database replication configuration without server downtime

Steps to setup MySQL replication with zero master downtime: 

1. Make the below changes in the my.ini file of a master MySQL server instance, to enable binary logs:

server-id=1 (can have any unique digit) 

log-bin=mysql-bin 

2. Restart the master instance.
3. Create replication user in a master instance by firing below queries in master:

CREATE USER repluser@'<master IP>’ IDENTIFIED BY ‘replpass’; 

GRANT REPLICATION SLAVE ON *.* TO repluser@'<slave IP>’; 

4. Take a full dump of Master instance. While firing command for mysqldump, include “–master-data=2” in options. It won’t lock tables. This will include one commented SQL query which will contain a master’s current log position. This type of dump is known as Point-in-Time dump.

mysqldump –skip-lock-tables –single-transaction –flush-logs –hex-blob –master-data=2 -A > ~/mysqldump.sql 

5. Once dump is over, import it on the slave server.
6. Find and copy the bin log position and file name in dump SQL file. It must be there on 22nd or 80th line number in dump file.
7. Make below changes in my.ini file of slave instance:

server-id=2 (can have any unique digit) 

relay-log-index=slave-relay-bin.index 

relay-log=slave-relay-bin 

8. If you want to make slave server read-only (for non-replication users), then add below lines in my.ini of slave instance:

read_only=1 

super_read_only=1 

9. Restart the slave instance.
10. Fire below query to make Master configurations in slave instance:

CHANGE MASTER TO MASTER_HOST='<master IP>’, MASTER_PORT=<master Port>, MASTER_USER='<user’s username created in #3>’, MASTER_PASSWORD='<user’s password created in #3>’, MASTER_LOG_FILE='<log file name copied from #6>’, MASTER_LOG_POS=<log file position copied from #6>; 

11. Start the slave by firing below query in slave:

START SLAVE; 

12. To view the slave status and verify it, fire below query in slave:

SHOW SLAVE STATUS; 

13. Find “Slave_IO_running” and “Slave_SQL_Running” column and if everything went right, they should contain “Yes” as a value.

Janmejay Gohil 4 Mar 2022