There are two types of replication.

• Master-Master - Both databases are masters, meaning both can be used for reading and writing.
• Master-Slave - One database is the master, the other is the slave. Only the master can read and write, the slave can only read.

This assumes you have installed mySQL or MariaDB on two different Docker containers.

AVOID TROUBLE

Before setting up replication, you will want to ensure that the databases on both masters are identical. You could use the mysqldump command to export a .sql file of the database on master "a" and then use the mysql command to import the .sql file into master "b".

On the first master, define the following directives in the main configuration file (e.g. /etc/mysql/mariadb.conf.d/50-server.cnf) below the mysqld header.

The auto_increment directives are only needed if you have an auto_increment PRIMARY KEY column. Refer to Understanding PRIMARY KEY and auto_increment.

[mysqld]
server-id = 1
log_bin = /var/lib/mysql/binary-log
auto_increment_increment=1

On the second master, define the following directives in the main configuration file (e.g. /etc/mysql/mariadb.conf.d/50-server.cnf).

[mysqld]
server-id = 2
log_bin = /var/lib/mysql/binary-log
auto_increment_increment=1

On both masters, create a user account that will be used for replication and grant the user the replication slave priviledge.

sudo docker exec mariadb mysql -e "create user 'replication_user'@'%' identified by 'itsasecret'"
sudo docker exec mariadb mysql -e "grant replication slave on *.* to 'replication_user'@'%'"

Or if the replication user account already exists, a select statement can be used to return the users password.

~]# mysql -e "select Password from mysql.user where User = 'replication_user' and Host = '%'\G"
Password: *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF

And if you know the replication users password, it can be validated.

~]# mysql -e "select password('itsasecret')"
+-------------------------------------------+
| password('itsasecret')                    |
+-------------------------------------------+
| *964A30F03EBE2D0D8EADC172640E4389BC9AF7FF |
+-------------------------------------------+

openssl s_client connect can be used to ensure a connection can be made between each Docker container.

[ec2-user@ip-172-31-80-56 ~]$ openssl s_client -connect docker1.example.com:3306
CONNECTED(00000003)

On the first master, use the flush tables with read lock command to disable write access to tables. The tables will continue to have read access.

sudo docker exec mariadb mysql -e "flush tables with read lock"

On the first master, use the show master status command to get the name and position of the binary log file. Optionally, the reset master command can be used so that prior binary log files are removed and a new binary log file ending in 000001 is used.

~]# sudo docker exec mariadb mysql -e "show master status \G"
            File: binary-log.000015
        Position: 725
    Binlog_Do_DB:
Binlog_Ignore_DB:

On the second master, stop the slave.

sudo docker exec mariadb mysql -e "stop slave"

On the second master, issue the following command, replacing <master binary file> with the value of File from the show master status command and <master position> with Position from the master status command.

sudo docker exec mariadb mysql -e "CHANGE MASTER TO
  MASTER_HOST='<first master hostname>',
  MASTER_USER='replication_user',
  MASTER_PASSWORD='itsasecret',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='<master binary file>',
  MASTER_LOG_POS=<master position>,
  MASTER_CONNECT_RETRY=10;"

On the second master, start the slave.

sudo docker exec mariadb mysql -e "start slave"

On the second master, issue the show slave status command.

sudo docker exec mariadb mysql -e "show slave status \G"

And something like this should be returned.

       Slave_IO_State: Waiting for master to send event
          Master_Host: master.example.com
          Master_User: replication_user
          Master_Port: 3306
        Connect_Retry: 10
      Master_Log_File: binary-log.000015
  Read_Master_Log_Pos: 725
       Relay_Log_File: mariadb-relay-bin.000002
        Relay_Log_Pos: 552
Relay_Master_Log_File: binary-log.000015
     Slave_IO_Running: Yes
    Slave_SQL_Running: Yes

On the first master, release the tables lock, restoring write access to the tables.

sudo docker exec mariadb mysql -e "unlock tables"

Repeat the steps above, flipping the first master and second master.

The container logs should have an event like this.

~]# sudo docker logs mariadb1
2021-12-20  8:33:48 6585 [Note] Slave I/O thread: connected to master 'replication_user@node2.example.com:3306',replication started in log 'binary-log.000005' at position 342
2021-12-20  8:33:48 6586 [Note] Slave SQL thread initialized, starting replication in log 'binary-log.000005' at position 342, relay log './mysqld-relay-bin.000001' position: 4

Let's say column001 in table001 in the db1 database contains "foo".

~]# sudo docker exec mariadb mysql -e "select column001 from db1.table001 where id = 123"
+---------------+
| column001     |
+---------------+
| foo           |
+---------------+

Let's update column001 on only one of the masters.

~] docker exec mariadb1 mysql -e "update db1.table001 set column001 = 'bar' where id = 123"

Reissuing the select command should show that the change was made and then immediately replicated to the other master.

~] sudo docker exec mariadb mysql -e "select column001 from db1.table001 where id = 123"
+---------------+
| column001     |
+---------------+
| bar           |
+---------------+

And the binary log in one of the containers should contain an event showing the update.

~]$ sudo docker exec mariadb tail /var/log/mysql/binary-log.000005
            UPDATE table001
            SET column001 = 'bar'
            WHERE id = 1123