In my prior article, \u201cRunning a relational database with Docker<\/a>\u201d, I showed how to spin up a Docker container with an instance of MySQL running in it. I also showed how you can persist data even when you blow away the container and recreate it by connecting to a Volume or Bind Mount.<\/p>\n\n\n\n In this article I am going to extend this to show you how to use Replication with MySQL instances in Docker Containers.<\/p>\n\n\n\n For a relational database like MySQL, For standalone servers or virtual machines you will typically set MySQL replication by manually configuring the my.cnf<\/em> file; here\u2019s an article on the SimpleTalk blog that describes this in detail<\/a>. But MySQL replication for Docker is done using environment variables, Docker networks and volumes to persist data.<\/p>\n\n\n\n Replication involves syncing data between a primary database which is also referred to as master and one or more secondary databases (replicas) also referred to as slaves. For the master and slave(s) to communicate, the master logs all changes to the binary log<\/a> and the slave(s) reads and applies those changes.<\/p>\n\n\n\n This means that read operations are distributed across replicas while only the master handles all write operations. Each MySQL instance will have a unique identifier, In our use case we will spin up the master MySQL container first and then configure it for replication. After that, we\u2019ll start a slave MySQL container that will sync data from the master. Keep in mind that, the master and its replicas should communicate over the same Docker network and then use a shared replication user for authentication and secure data transfer.<\/p>\n\n\n\n To begin, remove your already existing MySQL container:<\/p>\n\n\n\n We already have a Docker network From the command above we are running the master container with the following configuration settings:<\/p>\n\n\n And then, pass several MySQL specific configuration options for replication:<\/p>\n\n\n Next, confirm that the MySQL container is running;<\/p>\n\n\n\n Once it is running, we must create a replication user with REPLICATION SLAVE privileges<\/a> that the slave containers will use to connect and sync data from the master\u2019s binary logs.<\/p>\n\n\n\n Connect to the MySQL shell inside the master container and sign in as root:<\/p>\n\n\n\n Execute the following SQL commands one by one to create a replication user:<\/p>\n\n\n\n From the above command, the replication user is jane, <\/em>and \u2018%\u2019 allows connections from any host\/IP address, unlike a very restrictive user definition jane\u2019@\u2019%.example.com<\/em><\/a> <\/em>which restricts access to hosts within the example.com <\/em>domain. The follow up command For the other statement – Display the status of the MySQL binary log with the below command:<\/p>\n\n\n\n You should see the following output:<\/p>\n\n\n\n From the output above the position is The other parameters Now start a MySQL slave container:<\/p>\n\n\n\n This will do the following:<\/p>\n\n\n Confirm the slave container is running:<\/p>\n\n\n\n Open a shell in the slave container as the root user:<\/p>\n\n\n\n Configure the replica with the following command:<\/p>\n\n\n\n Here\u2019s what each part of the statement above does:<\/p>\n\n\n Next, initiate the replication process by allowing the slave (replica) container to start reading and applying changes from the master\u2019s binary logs:<\/p>\n\n\n\n Verify that the replication is done successfully and that it is working correctly:<\/p>\n\n\n\n If you have an output similar to the one below, the replication is set:<\/p>\n\n\n\n From the above output:<\/p>\n\n\n We have successfully set replication to apply changes from the master MySQL container. To test out our setup, we\u2019ll need to insert a test record into the Access the MySQL shell in the master container:<\/p>\n\n\n\n Switch to the Create a table Now confirm that the data has been replicated on the mysql-slave <\/em>container:<\/p>\n\n\n\n If the replication is working correctly you should have the new row outputted to you like so:<\/p>\n\n\n\nReplication Basics<\/h2>\n\n\n\n
you<\/code> can achieve high availability even when using it inside of Docker containers. While this is mostly common in production environments like standalone servers and virtual machines, you can do the same with Docker, however with a different setup.<\/p>\n\n\n\nserver-id=1<\/code> <\/em>and server-id=2<\/code>, <\/em>server-id=3<\/code> <\/em> and the master must have binary logging enabled to allow all records or changes to the database in a binary format so that slaves read from these logs and apply the changes.<\/p>\n\n\n\nConfiguring Replication in Containers<\/h2>\n\n\n\n
docker rm <container-id> -f<\/pre><\/div>\n\n\n\n
mysql-net<\/code> <\/em>created; use it to start the MySQL master container:<\/p>\n\n\n\ndocker run -d --name mysql-master \\\n --network=mysql-net \\\n -e MYSQL_ROOT_PASSWORD=superadminuser \\\n -e MYSQL_USER=admin \\\n -e MYSQL_PASSWORD=admin123 \\\n -e MYSQL_DATABASE=testdb \\\n -v mysql-master-data:\/var\/lib\/mysql \\\n mysql \\\n--server-id=1 \\\n--log-bin=mysql-bin \\\n--binlog-do-db=testdb \\\n--binlog-format=row<\/pre><\/div>\n\n\n\n
\n
\n
docker ps<\/pre><\/div>\n\n\n\n
docker exec -it mysql-master mysql -u root -p<\/pre><\/div>\n\n\n\n
CREATE USER 'jane'@'%' IDENTIFIED BY 'jane123' REQUIRE SSL;\n\nGRANT REPLICATION SLAVE ON *.* TO 'jane'@'%';<\/pre><\/div>\n\n\n\n
- IDENTIFIED BY 'jane123';<\/code> <\/em>means jane123<\/code> <\/em>is the assigned password for the user jane.<\/em><\/p>\n\n\n\nGRANT REPLICATION SLAVE ON *.* TO 'jane'@'%';<\/code> we are simply telling MySQL to grant the REPLICATION SLAVE<\/code> <\/em>privilege to the user jane<\/code>; which applies immediately.<\/p>\n\n\n\nSHOW BINARY LOG STATUS;<\/pre><\/div>\n\n\n\n
+------------------+----------+--------------+------------------+-------------------+\n| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\n+------------------+----------+--------------+------------------+-------------------+\n| mysql-bin.000003 | 3172 | testdb | | |\n+------------------+----------+--------------+------------------+-------------------+\n1 row in set (0.00 sec)<\/pre><\/div>\n\n\n\n
3172<\/code> and the Binary_Do_DB<\/code> is set to testdb<\/code> which is what we want. The position 3172<\/code> <\/em>is a binary position within the binary log where the next event should be read by the slave(s). This implies that the slave containers should start reading the binary log file mysql-bin.000003<\/code> <\/em>from position 3172<\/code>.<\/em><\/p>\n\n\n\nBinlog_Ignore_DB <\/code>and Executed_Gtid_Set<\/em><\/a> <\/em>specifies which database should not be logged in the binary log and tracks the GTID<\/code>s of executed transactions respectively. Since these parameters were not set, they are empty, no databases are explicitly ignored, and no GTID<\/code> transactions have been recorded.<\/p>\n\n\n\ndocker run -d --name mysql-slave \\\n --network=mysql-net \\\n -e MYSQL_ROOT_PASSWORD=superadminuser \\\n -e MYSQL_USER=john \\\n -e MYSQL_PASSWORD=john123 \\\n -e MYSQL_DATABASE=mydb \\\n -v mysql-slave-data:\/var\/lib\/mysql \\\n mysql \\\n --server-id=2 \\\n --relay-log=mysql-relay-bin<\/pre><\/div>\n\n\n\n
\n
mysql-slave<\/code>.<\/em> <\/li>\n\n\n\nmysql-net<\/code>.<\/em> <\/li>\n\n\n\nsuperadminpassword<\/code> <\/em>as the MYSQL_ROOT_PASSWORD<\/code>, this can be anything and doesn\u2019t need to be the same as the master. <\/li>\n\n\n\nMYSQL_USER<\/code>), password (MYSQL_PASSWORD<\/code>) and database (MYSQL_DATABASE<\/code>) with the following values – john, john123<\/code> and mydb<\/code> respectively. Again, the values do not have to be the same as the master. <\/li>\n\n\n\nmysql-slave-data<\/code> <\/em>and mount it on \/var\/lib\/mysql<\/code> <\/em>which is where database files are stored in MySQL. <\/li>\n\n\n\nmysql-relay-bin<\/code> <\/em> before applying them. <\/li>\n<\/ul>\n<\/div>\n\n\ndocker ps<\/pre><\/div>\n\n\n\n
docker exec -it mysql-slave mysql -u root -p<\/pre><\/div>\n\n\n\n
CHANGE REPLICATION SOURCE TO \n SOURCE_HOST='mysql-master',\n SOURCE_LOG_FILE='mysql-bin.000003',\n SOURCE_LOG_POS=3172,<\/pre><\/div>\n\n\n\n
\n
CHANGE REPLICATION SOURCE<\/code> TO configures the replica with information about where to start the replication from, which master to connect to, which log file to use, and at which position it should begin reading the binary log events. <\/li>\n\n\n\nSOURCE_HOST<\/code> specifies the MySQL master container using the container name mysql-master<\/code>.<\/em> <\/li>\n\n\n\nSOURCE_LOG_FILE<\/code> defines the binary log file on the master which the slave (replica) container should start reading from. <\/li>\n\n\n\nSOURCE_LOG_POS<\/code> specifies the exact position within the master’s binary file where replication should begin. The byte offset `3172<\/code>` is usually the starting point for replication. <\/li>\n<\/ul>\n<\/div>\n\n\nSTART REPLICA USER='jane' PASSWORD='jane123';<\/pre><\/div>\n\n\n\n
SHOW REPLICA STATUS\\G<\/pre><\/div>\n\n\n\n
*************************** 1. row ***************************\n Replica_IO_State: Connecting to source\n Source_Host: mysql-master\n Source_User: jane\n Source_Port: 3306\n Connect_Retry: 60\n Source_Log_File: mysql-bin.000003\n Read_Source_Log_Pos: 3172\n Relay_Log_File: mysql-relay-bin.000001\n Relay_Log_Pos: 4\n Relay_Source_Log_File: mysql-bin.000003\n Replica_IO_Running: Connecting\n Replica_SQL_Running: Yes\n Replicate_Do_DB:\n Replicate_Ignore_DB:\n Replicate_Do_Table:\n Replicate_Ignore_Table:\n Replicate_Wild_Do_Table:\n Replicate_Wild_Ignore_Table:\n Last_Errno: 0\n Last_Error:\n Skip_Counter: 0\n Exec_Source_Log_Pos: 3172\n Relay_Log_Space: 158\n Until_Condition: None\n Until_Log_File:\n Until_Log_Pos: 0\n Source_SSL_Allowed: No\n Source_SSL_CA_File:\n Source_SSL_CA_Path:\n Source_SSL_Cert:\n Source_SSL_Cipher:\n Source_SSL_Key:\n Seconds_Behind_Source: NULL\nSource_SSL_Verify_Server_Cert: No\n Last_IO_Errno: 2061\n Last_IO_Error: Error connecting to source 'jane@mysql-master:3306'. This was attempt 2\/10, with a delay of 60 seconds between attempts. Message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.\n Last_SQL_Errno: 0\n Last_SQL_Error:\n Replicate_Ignore_Server_Ids:\n Source_Server_Id: 0\n Source_UUID:\n Source_Info_File: mysql.slave_master_info\n SQL_Delay: 0\n SQL_Remaining_Delay: NULL\n Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates\n Source_Retry_Count: 10\n Source_Bind:\n Last_IO_Error_Timestamp: 250318 15:03:31\n Last_SQL_Error_Timestamp:\n Source_SSL_Crl:\n Source_SSL_Crlpath:\n Retrieved_Gtid_Set:\n Executed_Gtid_Set:\n Auto_Position: 0\n Replicate_Rewrite_DB:\n Channel_Name:\n Source_TLS_Version:\n Source_public_key_path:\n Get_Source_public_key: 0\n Network_Namespace:\n1 row in set (0.00 sec)<\/pre><\/div>\n\n\n\n
\n
Replica_IO_State<\/code> means the replica is caught up with the master and is waiting to receive new changes. <\/li>\n\n\n\nReplica_IO_Running<\/code> means the replica is successfully reading changes from the master. <\/li>\n\n\n\nSeconds_Behind_Source<\/code> means there is no lagging and the replica is in sync fully with the master. <\/li>\n\n\n\nLast_IO_Error<\/code> and Last_SQL_Error<\/code> means there are no errors in the replication process. <\/li>\n<\/ul>\n<\/div>\n\n\n<\/a>Testing the Replication Configuration<\/h2>\n\n\n\n
testdb<\/code> <\/em>database on the mysql-master container<\/code>. We are using the testdb<\/em> database because it is the one explicitly included in the master\u2019s binary logs via the binlog-do-db <\/em>setting.<\/p>\n\n\n\ndocker exec -it mysql-master mysql -u root -p<\/pre><\/div>\n\n\n\n
testdb<\/code> <\/em>database:<\/p>\n\n\n\nUSE testdb;<\/pre><\/div>\n\n\n\n
users<\/code> <\/em>and insert a record:<\/p>\n\n\n\nCREATE TABLE users (\n id INT PRIMARY KEY AUTO_INCREMENT,\n name VARCHAR(100) NOT NULL,\n email VARCHAR(100) NOT NULL UNIQUE\n);<\/pre><\/div>\n\n\n\n
INSERT INTO users (name, email) VALUES ('Alice', 'alice@example.com');<\/pre><\/div>\n\n\n\nUSE testdb;\n\nSELECT * FROM users;<\/pre><\/div>\n\n\n\n