MySQL Master-Slave Replication Setup

1 year ago
Liam
2 minutes

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

  1. Make sure that MySQL is installed on both the primary server and the secondary server.
  2. Modify the configuration file my.cnf on the master server to enable the binary logging feature. Once you open the configuration file, locate the [mysqld] section and add the following content below it.
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log

The server-id is used to identify the unique ID of the main server and can be set to any value, as long as it is not the same as the ID of the secondary server. log-bin specifies the path and filename for the binary log.

  1. Restart the main server to apply the configuration.
  2. Create a replication account on the master server and grant replication permissions. Open the MySQL client and execute the following SQL statement:
CREATE USER 'replication'@'从服务器IP地址' IDENTIFIED BY '密码';
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'从服务器IP地址';
FLUSH PRIVILEGES;

Replace the server IP address with the actual IP address of the server, and replace the password with the password of the copied account.

  1. Execute the command SHOW MASTER STATUS on the primary server, and make a note of the values for File and Position in the results.
  2. When modifying the configuration file my.cnf on the server, add the following content:
server-id = 2

The server-id is used to identify the unique ID of the slave server, it can be set to any value, but it cannot be the same as the ID of the master server.

  1. Restart the server for the configuration to take effect.
  2. Execute the following command on the server:
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='主服务器IP地址', MASTER_USER='replication', MASTER_PASSWORD='密码', MASTER_LOG_FILE='主服务器上的File值', MASTER_LOG_POS=主服务器上的Position值;
START SLAVE;

Replace the main server’s IP address with the actual IP address of the main server, replace the password with the password of the copied account, and replace the File and Position values on the main server with the values recorded in step 5.

  1. Display the status of the slave server.
  2. The Slave I/O process is functioning.
  3. The SQL process for the slave is currently running.
  4. Certainly.

After completing the above steps, the MySQL master-slave replication has been successfully set up. Data updates on the master server will be automatically synchronized to the slave server.

bannerAds