In this article I want to share my experience of how to setup MySQL replication locally with using Docker. It is based on this repository on the GitHub I have created a few years ago. It contains configurations and instructions with the MySQL replication example based on Docker. This is good to start understanding replication and and not intended for production use. After getting dozens of starts I’ve considered to add some more explanations about how it works. docker-mysql-master-slave test replication-unsafe statements In this article I want to give you more detailed explanations about overall process. It might be useful for DevOps and Software Engineers who want to create working environment more closely to production environment and for those who want better understand what MySQL replication really looks like. Prerequisites. You have Docker v.18 or higher installed. Installed v1.23 or higher. docker-compose Very basic MySQL knowledge. Some theory. MySQL replication is a special setup which involves two or more MySQL servers where one database server (known as master or source) is copied to another (known as slave or replica). Process of replica synchronisation is done through coping and performing SQL statements from source’s binary log. MySQL configuration allows to select the whole source database or only particular tables to be copied to the replica. By default synchronisation type for MySQL replication is (one-way), which means “replica” does not notify it’s “source” about results of coping and processing events. Additional types of synchronisation (semi-synchronous, synchronous) may be available via plugins or in special setups (like NDB Cluster). asynchronous With MySQL replication you can make some specific configuration types: replication, (also known as master-master or ring) and combinations of these. The limitation is that replica can have only one source server. chained circular means there is a chain of database servers. Chained replication Example: Source 1 — > Replica 1 — > Replica 2. Replica 1 is source for Replica 2 and replica for Source 1. It’s useful for a case when Replica 1 contains a “merged” database, which consists of the “source” tables and its own “added” tables. supposes to have master databases in the circle, that serves also as replicas at the same time. Example: Master← → Master. The problem here is the auto incremented columns sync. Solution could be configuring ' and server variables to make increments with different steps or in different range according to each master server setup. If you are using InnoDB consider that fact that with ring replication, row will not be always added to the end of the replica index, in such case it may lead to additional insert latency on replica because of clustered index ordering. Circular replication auto_increment_increment' ' auto_increment_offset' Practice. It’s time to make some practise! I believe that learn-by-doing is the best learning approach! Create an empty directory and clone repository. mkdir mysql-master-slave mysql-master-slave git https://github.com/vbabak/docker-mysql-master-slave ./ cd clone ./build.sh Build process requires ports 4406 and 5506 are not in use on your system. Otherwise you can update it to any non-used port in ' file and re-run the build script. docker-compose.yml' If all goes smoothly you will get such messages: Waiting for mysql_master database connection… and finally a replica (slave) status report. The last line says it is waiting for new updates from master. To test replication is working, run this query on Master: docker -it mysql_master bash mysql -u root -p mydb mysql> create table not exists code(code int); mysql> insert into code values (100), (200); exec '111' if # Query OK, 0 rows affected, 1 warning (0.01 sec) # Query OK, 2 rows affected (0.01 sec) And check Replica: docker -it mysql_slave bash mysql -u root -p mydb mysql> select * from code; exec '111' Master configuration. Under the hood. Master server configuration placed into “ Here is some explaining. First 2 options are used to increase server performance and not related to the replication settings itself. master/conf/mysql.conf.cnf”. skip-host-cache Disable use of the internal host cache for faster name-to-IP resolution. skip-name-resolve Disable DNS host name lookups server-id = 1 For servers that are used in a replication, you must specify a unique server ID. It must be different from every other ID in use by any other source or replica. log_bin = /var/log/mysql/mysql-bin.log Enables bin log and sets the base name and path for the binary log files (like ). log_bin_basename binlog_format = ROW Possible values are ROW (replica replay only actual changes on the row), STATEMENT (replica replay all the queries that changes the data), MIXED (statement-based replication is used unless server decides only row-based replication can give proper result, like replicating result of GUUID() ). binlog_do_db = mydb Specify a database, which statements will be written to binary log file. Environment parameters related to launch MySQL in a docker container are placed into “ ” file. They are described on the docker hub website for the image. master/mysql_master.env mysql:5.7 If you’re a Windows user, the build.sh script probably will not work, so you will need to setup master database with creating `mydb_slave_user` user — run 2 sql commands on Master and then setup slave database — run 2 sql commands on Replica , see details below. Finally, add a replication user on master server. Create a new user for replication with REPLICATION SLAVE permission: *.* @ ; ; # SETUP MASTER SQL COMMANDS GRANT REPLICATION SLAVE ON TO "mydb_slave_user" "%" IDENTIFIED BY "mydb_slave_pwd" FLUSH PRIVILEGES Replica configuration. Some of the configuration parameters repeat the Master database. relay-log = /var/log/mysql/mysql-relay-bin.log Contains database events, read from the source binary log. Start a Replica. First, you need to find a master host ip address. You can check “hosts” file on master host docker -it mysql_master cat exec '/etc/hosts' The last line will contain ip address, example: 172.19.0.2. This is a MASTER_HOST. Second, find a log file and position from this command: docker mysql_master sh -c exec 'export MYSQL_PWD=111; mysql -u root -e "SHOW MASTER STATUS \G"' “File:” is MASTER_LOG_FILE and “Position:” is MASTER_LOG_POS. Now, when we have all the variables, we can modify and run the following command to start a replication: MASTER_HOST= , MASTER_USER= , MASTER_PASSWORD= , MASTER_LOG_FILE= , MASTER_LOG_POS=$POS; ; # SETUP REPLICA SQL COMMANDS CHANGE MASTER TO '${IP}' 'mydb_slave_user' 'mydb_slave_pwd' '${LOG}' START SLAVE Replication-unsafe statements and non-deterministic queries. The “safeness” of a statement in MySQL replication refers to whether the statement and its effects can be replicated correctly using statement-based format. Statement is safe if it deterministic. Deterministic means statement always produce the same result. When using statement-based logging, statements flagged as being unsafe generate a warning. Check a list of unsafe statements . Example: FOUND_ROWS(), RAND(), UUID(). here For row-level replication no distinction is made for deterministic and non-deterministic statements. The drawback of row-based replication is that they are expensive on range updates in case when WHERE clause matches a lot of rows. Final thoughts. Replication is powerful part of any database and fortunately it is supported by MySQL. The use range is wide. It can be used for heigh availability and data distributions, data backups, load balancing. And even for optimisation latency based on geolocation. Thanks for reading! Previously published at https://medium.com/@vbabak/docker-mysql-master-slave-replication-setup-2ff553fceef2
