This guide provides a comprehensive overview of configuring MySQL replication. Replication is a feature in MySQL that allows you to copy data from one MySQL database server to another. This is particularly useful for creating read replicas, backup servers, and for disaster recovery.

Prerequisites

Before you start configuring replication, make sure you have:

  • A MySQL server installed on both the master and the slave.
  • The MySQL server on the master and the slave should have the same version.
  • Proper user accounts with the necessary privileges on the master server.

Step-by-Step Guide

  1. Create a replication user on the master server:
CREATE USER 'replication_user'@'%' IDENTIFIED BY 'password';
  1. Grant replication privileges to the user:
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
  1. Stop the MySQL server on the master server and edit the my.cnf file:
[mysqld]
server-id = 1
log-bin = /path/to/log-bin
binlog-format = ROW

  1. Start the MySQL server on the master server.

  2. Stop the MySQL server on the slave server and edit the my.cnf file:

[mysqld]
server-id = 2

  1. Start the MySQL server on the slave server.

  2. Configure the replication on the slave server:

CHANGE MASTER TO
  MASTER_HOST='master_server_ip',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='password',
  MASTER_LOG_FILE='master-bin.000001',
  MASTER_LOG_POS=4;
  1. Start the replication on the slave server:
START SLAVE;
  1. Verify the replication is working:
SHOW SLAVE STATUS \G

Troubleshooting

If you encounter any issues during the configuration, here are some common problems and their solutions:

  • Connection issues: Ensure that the MySQL server on the master and slave can communicate with each other.
  • Binary log format: Ensure that the binary log format is set to ROW on both the master and the slave.
  • Replication user privileges: Make sure that the replication user has the necessary privileges.

For more detailed troubleshooting, you can refer to the MySQL documentation.

MySQL Server