Configuring MySQL master-slave replication can enhance the availability and performance of your database. Here are the steps to set up MySQL master-slave replication:
Prerequisites
Two servers (master and slave), each with MySQL installed.
Ensure that the MySQL versions on both servers are the same.
-
Ensure network connectivity between the two servers.
1. Install mysql8 in docker
docker run -d -p 3306:3306 --restart=always --privileged=true -v /var/lib/mysql:/var/lib/mysql -v /etc/mysql/my.cnf:/etc/mysql/my.cnf -e MYSQL_ROOT_PASSWORD=your_password --name mysql mysql:8.0.37
2. Master Server Configuration
CREATE USER 'replica_user'@'%' IDENTIFIED BY 'your_password';
GRANT REPLICATION SLAVE ON *.* TO 'replica_user'@'%';
ALTER USER 'replica_user'@'%' IDENTIFIED WITH mysql_native_password BY 'your_password';
FLUSH PRIVILEGES;
SELECT user, host, plugin FROM mysql.user;
SHOW MASTER STATUS;
3. Slave Server Configuration
CHANGE MASTER TO MASTER_HOST='host_name',MASTER_USER='replica_user',MASTER_PASSWORD='your_password',MASTER_LOG_FILE='mysql-bin.000003',MASTER_LOG_POS=1138;
start slave;
show slave status \G;
4. Test Replication
Create a new database or table on the master server and check if the same changes appear on the slave server.
By following these steps, you have successfully configured MySQL master-slave replication.
5.Reset slave service
RESET SLAVE;
Top comments (1)
Please update your language to use less offensive terms. "Master" and "slave" should be replaced by "source" and "replica".