Master to Master Replication between two MariaDB Servers

Master to Master Replication between two MariaDB Servers using Docker

s.o.:

• Official Image of MariaDB on Docker Hub
• Setting Up Replication in MariaDB Knowledge Base
• HA for MySQL and MariaDB - Comparing Master-Master Replication to Galera Cluster

1. Start first MariaDB instance

   Start the first Maria DB server container on one node with activated binary log via Docker Compose

   docker-compose -f node1.yml up -d

   with sample node1.yml:

   version: "3"
   services:
     mariadb:
       image: mariadb:10.7.1
       ports:
         - "3306:3306"
       environment:
         MYSQL_ROOT_PASSWORD: verys3cret
         MYSQL_DATABASE: keycloak
         MYSQL_USER: keycloak
         MYSQL_PASSWORD: keycloak
       command:
         - "--character-set-server=latin1"
         - "--collation-server=latin1_swedish_ci"
         - "--log-bin"
         - "--log-basename=node1"
         - "--server-id=1"
         - "--replicate-do-db=keycloak"
         - "--auto_increment_increment=2"
         - "--auto_increment_offset=1"
       volumes:
         - dbdata:/var/lib/mysql
   volumes:
     dbdata:

2. Create a Replication User

   Connect to the started Maria DB server using the mysql command line client in the running container:

   docker exec -i -t <container-id> mysql -u root -p

   and execute the following SQL:

   CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
   GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

3. Getting the Binary Log Co-ordinates

   Run:

   SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | node1-bin.000003 |      694 |              |                  |
   +------------------+----------+--------------+------------------+

4. Start second MariaDB instance on other node

   Start the second Maria DB server container on the other node with activated binary log via Docker Compose

   docker-compose -f node2.yml up -d

   with sample node2.yml:

   version: "3"
   services:
     mariadb:
       image: mariadb:10.7.1
       ports:
         - "3306:3306"
       environment:
         MYSQL_ROOT_PASSWORD: verys3cret
         MYSQL_DATABASE: keycloak
         MYSQL_USER: keycloak
         MYSQL_PASSWORD: keycloak
       command:
         - "--character-set-server=latin1"
         - "--collation-server=latin1_swedish_ci"
         - "--log-bin"
         - "--log-basename=node2"
         - "--server-id=2"
         - "--replicate-do-db=keycloak"
         - "--auto_increment_increment=2"
         - "--auto_increment_offset=2"
       volumes:
         - dbdata:/var/lib/mysql
   volumes:
     dbdata:

5. Create a Replication User on second MariaDB instance

   Connect to the started second Maria DB server using the mysql command line client in the running container:

   docker exec -i -t <container-id> mysql -u root -p

   and execute the following SQL:

   CREATE USER 'replication_user'@'%' IDENTIFIED BY 'bigs3cret';
   GRANT REPLICATION SLAVE ON *.* TO 'replication_user'@'%';

6. Connect second MariaDB instance as Slave to first MariaDB instance as Master:

   Specify Host/IP and Binary Log Co-ordinates of first MariaDB instance in command:

   CHANGE MASTER TO
     MASTER_HOST='<host/IP of node1>',
     MASTER_USER='replication_user',
     MASTER_PASSWORD='bigs3cret',
     MASTER_LOG_FILE='node1-bin.000003',
     MASTER_LOG_POS=694;

   Start the slave with the START SLAVE command:

   START SLAVE;

   Check that the replication is working by executing the SHOW SLAVE STATUS command:

   SHOW SLAVE STATUS \G

   If replication is working correctly, both the values of Slave_IO_Running and Slave_SQL_Running should be Yes:

   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
   

7. Getting the Binary Log Co-ordinates on second MariaDB instance

   Run:

   SHOW MASTER STATUS;
   +------------------+----------+--------------+------------------+
   | File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
   +------------------+----------+--------------+------------------+
   | node2-bin.000003 |      694 |              |                  |
   +------------------+----------+--------------+------------------+

8. Connect first MariaDB instance as Slave to second MariaDB instance as Master:

   Using the mysql command line client connected with the first started MariaDB instance specify IP and Binary Log Co-ordinates of the second MariaDB instance in command:

   CHANGE MASTER TO
     MASTER_HOST='<host/IP of node2>',
     MASTER_USER='replication_user',
     MASTER_PASSWORD='bigs3cret',
     MASTER_LOG_FILE='node2-bin.000003',
     MASTER_LOG_POS=694;

   Start the slave with the START SLAVE command:

   START SLAVE;

   Check that the replication is working by executing the SHOW SLAVE STATUS command:

   SHOW SLAVE STATUS \G

   If replication is working correctly, both the values of Slave_IO_Running and Slave_SQL_Running should be Yes:

   Slave_IO_Running: Yes
   Slave_SQL_Running: Yes