MySql Replication
Check changes in syntax from ‘master‘ to ‘replica‘ starting from mysql 8.4
Setup
1. Setup Docker Compose 🛠️
YAML
services:
# The master server
mysql-master:
image: mysql:8.4.3
container_name: mysql-master
command: --server-id=1 --log-bin=mysql-bin --binlog-format=row
environment:
MYSQL_ROOT_PASSWORD: rootPassword
MYSQL_DATABASE: masterdb
MYSQL_USER: master
MYSQL_PASSWORD: masterPass
ports:
- "3306:3306"
volumes:
- ./master-data:/var/lib/mysql
- ./conf-master:/etc/mysql/conf.d
# The replica server
mysql-slave:
image: mysql:8.4.3
container_name: mysql-slave
depends_on:
- mysql-master
command: --server-id=2 --log-bin=mysql-bin --binlog-format=row
environment:
MYSQL_ROOT_PASSWORD: RootPassword
MYSQL_DATABASE: slavedb
MYSQL_USER: slave
MYSQL_PASSWORD: slavePass
ports:
- "3307:3306"
volumes:
- ./slave-data:/var/lib/mysql
- ./conf-slave:/etc/mysql/conf.d
- custom.ini (./master-data/custom.ini ./slave-data/custom.ini)
INI
[client]
default-character-set = utf8mb4
[mysql]
default-character-set = utf8mb4
[mysqld]
mysql_native_password=ON
max_connections = 666
bind-address = 0.0.0.0
collation-server = utf8mb4_unicode_ci
character-set-server = utf8mb4
2. Launch Containers 🚀
3. Configure the Master Server 🔧
Access the master server and configure the MySQL settings:
Execute the following SQL commands:
MySQL
CREATE USER 'replication_user'@'%' IDENTIFIED WITH 'mysql_native_password' BY 'replication_password';
GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';
FLUSH PRIVILEGES;
SHOW BINARY LOG STATUS;
Note the log file and position from SHOW BINARY LOG STATUS
for later use.
4. Configure the Replica Server 🔧
Access the replica server:
Configure the replication settings using the master log file and position:
MySQL
CHANGE REPLICATION SOURCE TO
SOURCE_HOST='mysql-master',
SOURCE_USER='replication_user',
SOURCE_PASSWORD='replication_password',
SOURCE_LOG_FILE='mysql-bin.000006',
SOURCE_LOG_POS=2527;
5. Start Replication on Replica Server ▶️
Initiate the replication process:
6. Verify Replication Status 🕵️♂️
Check the replication status to ensure everything is working correctly:
Check that Replica_IO_Running
and Replica_SQL_Running
show as "Yes".
7. Operation Check ✔️
Perform a simple data replication test to confirm the setup:
On the master server:
MySQL
create schema some_db default character set utf8mb4;
use some_db;
create table user (id int);
insert into user values (1);
select * from user;
Ensure the data is consistent across both servers.