How to set up MySQL master-slave replication?

1 year ago
William Carter
2 minutes

To set up MySQL master-slave replication, you need to follow these steps:

  1. Set up the primary database (Master):
  2. Open the configuration file of the main database (usually my.cnf or my.ini), and enable the binary log feature. Add the following lines to the file:
    log-bin = mysql-bin
    server-id = 1
  3. Reboot the main database to apply the settings.
  4. Configuration from the database (Slave):
  5. Open the configuration file from the database and enable the server functionality. Add the following line in the file: server-id = 2.
  6. Restart the database for the configuration to take effect.
  7. Create a user for replication on the primary database.
  8. Log in to the main database and execute the following SQL statements to create a replication user: CREATE USER ‘replication_user’@’%’ IDENTIFIED BY ‘password’;
    GRANT REPLICATION SLAVE ON *.* TO ‘replication_user’@’%’;
    FLUSH PRIVILEGES;
  9. Back up the primary database and restore the backup to the secondary database.
  10. Execute the following command on the main database to create a database backup file: mysqldump -u username -p dbname > backup.sql
  11. Transfer the backup file to the database, and restore the backup using the following command: mysql -u username -p dbname < backup.sql
  12. Setting up a master-slave relationship from the database:
  13. Login to the database and execute the following SQL statement: CHANGE MASTER TO MASTER_HOST=’master_ip’, MASTER_USER=’replication_user’, MASTER_PASSWORD=’password’, MASTER_LOG_FILE=’mysql-bin.000001′, MASTER_LOG_POS=123;

    Where ‘master_ip’ is the IP address of the master database, ‘mysql-bin.000001’ is the latest binary log file in the master database, and 123 is the replication position in the master database.

  14. Initiate the replication process from the database.
  15. Execute the following SQL statement to start the replication process: START SLAVE;
  16. Check the status of master-slave synchronization.
  17. Execute the following SQL statement to check the status of master-slave synchronization: SHOW SLAVE STATUS\G
    Check if the values of the “Slave_IO_Running” and “Slave_SQL_Running” fields in the output are “YES”, indicating that the master-slave synchronization has been successfully established.

After completing the above steps, your MySQL master-slave synchronization has been successfully set up. Updates on the master database will be automatically replicated to the slave database.

bannerAds