How do I set up replication

Setting up MySQL replication is a powerful feature that enables you to create copies of your database instances for tasks such as load balancing and backup. Below, I will provide a detailed explanation of how to set up replication between a master and a slave instance of MySQL.

Steps to Set Up MySQL Replication

1. Configure the Master Server:
   • Ensure the following parameters are set in your MySQL configuration file (my.cnf or my.ini):
   [mysqld] server-id = 1 log_bin = mysql-bin binlog_do_db = your_database_name
2. Create a Replication User:
   • Log in to the MySQL shell and execute:
   CREATE USER 'replication_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password'; GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
3. Obtain the Binary Log Coordinates:
   • While still in the MySQL shell, run:
   FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS;
   • This will show you the current binary log file and position.
4. Configure the Slave Server:
   • Edit the MySQL configuration file on the slave:
   [mysqld] server-id = 2
   • Start MySQL service on the slave server.
5. Set Up the Slave to Connect to the Master:
   • Log in to the MySQL shell on the slave and run:
   CHANGE MASTER TO MASTER_HOST='master_ip', MASTER_USER='replication_user', MASTER_PASSWORD='your_password', MASTER_LOG_FILE='mysql-bin.000001', -- Replace with the file from SHOW MASTER STATUS MASTER_LOG_POS=123; -- Replace with the position from SHOW MASTER STATUS
   • Finally, start the slave:
   START SLAVE;

Once completed, you can check the slave status using the command:

This will show you if the slave is connected and actively replicating from the master.