Skip to content

MySql Replication

Stolen from

Check changes in syntax from ‘master‘ to ‘replica‘ starting from mysql 8.4

mysql tags

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 🚀

Bash
docker compose pull
docker compose up -d

3. Configure the Master Server 🔧

Access the master server and configure the MySQL settings:

Bash
docker exec -it mysql-master bash
mysql -u root -prootPassword

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:

Bash
docker exec -it mysql-slave bash
mysql -u root -pRootPassword

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:

MySQL
START REPLICA;

6. Verify Replication Status 🕵️‍♂️

Check the replication status to ensure everything is working correctly:

MySQL
SHOW REPLICA STATUS\G

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.

🎉