Master to Master Replication between two MariaDB Servers - dcm4che/dcm4chee-arc-light GitHub Wiki

Master to Master Replication between two MariaDB Servers using Docker

s.o.:

  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
    
⚠️ **GitHub.com Fallback** ⚠️