How To Repair Unraid Mysql Docker

MySQL Master-Slave Replication using Docker

This article is based on the repository docker-mysql-chief-slave on the GitHub I accept created a few years ago. It contains configurations and instructions with the MySQL replication example based on Docker. This is proficient to outset understanding replication and exam replication-unsafe statements and non intended for production utilise. 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 improve understand what MySQL replication really looks like. The problem here is the auto incremented columns sync.

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 docker-mysql-master-slave 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 proficient to start understanding replication andtest replication-unsafe statements and not intended for product use. Afterward getting dozens of starts I've considered to add some more explanations nearly how information technology works.

In this article I desire to give you more than detailed explanations about overall procedure. It might be useful for DevOps and Software Engineers who want to create working environment more closely to production environment and for those who desire meliorate understand what MySQL replication really looks like.

Prerequisites.

Yous have Docker v.18 or higher installed.
Installeddocker-etch v1.23 or higher.
Very basic MySQL knowledge.

Some theory.

MySQL replication is a special setup which involves ii or more than 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 washed through coping and performing SQL statements from source's binary log. MySQL configuration allows to select the whole source database or only item tables to be copied to the replica.

By default synchronisation type for MySQL replication is asynchronous (i-mode), which means "replica" does not notify information technology's "source" about results of coping and processing events. Additional types of synchronisation (semi-synchronous, synchronous) may exist bachelor via plugins or in special setups (like NDB Cluster).

With MySQL replication you can make some specific configuration types: chained replication,circular (also known as master-primary or band) and combinations of these. The limitation is that replica can have but one source server.

Chained replication means there is a chain of database servers.

Case: Source 1 — > Replica 1 — > Replica 2.

Replica i is source for Replica ii and replica for Source 1. It's useful for a instance when Replica ane contains a "merged" database, which consists of the "source" tables and its own "added" tables.

Round replication supposes to take master databases in the circle, that serves also as replicas at the same time. Case: Master← → Master. The trouble here is the auto incremented columns sync. Solution could be configuring 'auto_increment_increment' and ' auto_increment_offset' server variables to make increments with different steps or in different range according to each master server setup. If y'all are using InnoDB consider that fact that with ring replication, row will not be always added to the cease of the replica alphabetize, in such case it may lead to boosted insert latency on replica because of clustered alphabetize ordering.

Practise.

It'southward time to brand some exercise! I believe that learn-past-doing is the best learning arroyo!

Create an empty directory and clone repository.

                mkdir mysql-master-slave                  cd                  mysql-master-slave git                  clone                  https://github.com/vbabak/docker-mysql-primary-slave ./

Build process requires ports 4406 and 5506 are not in use on your arrangement. Otherwise you can update it to any non-used port in 'docker-compose.yml' file and re-run the build script.

If all goes smoothly you will get such messages:

Waiting for mysql_master database connectedness…

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 Main:

                docker                  exec                  -it mysql_master fustigate mysql -u root -p'111'                  mydb mysql> create table                  if                  not exists lawmaking(code int);                  # Query OK, 0 rows affected, 1 warning (0.01 sec)                  mysql> insert into code values (100), (200);                  # Query OK, 2 rows affected (0.01 sec)

And check Replica:

                docker                  exec                  -it mysql_slave fustigate mysql -u root -p'111'                  mydb mysql> select * from code;

Nether the hood.Master configuration.

Primary server configuration placed into "main/conf/mysql.conf.cnf".Here is some explaining. Outset two options are used to increase server performance and not related to the replication settings itself.

                              skip-host-cache

Disable use of the internal host enshroud for faster name-to-IP resolution.

                              skip-name-resolve

Disable DNS host name lookups

                              server-id = i

For servers that are used in a replication, you lot 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 (similarlog_bin_basename).

binlog_format = ROW

Possible values are ROW (replica replay only actual changes on the row), Argument (replica replay all the queries that changes the information), MIXED (argument-based replication is used unless server decides only row-based replication tin give proper outcome, like replicating issue 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 "master/mysql_master.env" file. They are described on the docker hub website for the mysql:five.7 image.

If you're a Windows user, the build.sh script probably volition not work, then you lot will need to setup primary database with creating `mydb_slave_user` user — run ii sql commands on Master and so setup slave database — run ii sql commands on Replica , come across details below.

Finally, add together a replication user on master server. Create a new user for replication with REPLICATION SLAVE permission:

                                  # SETUP Main SQL COMMANDS                  GRANT                  REPLICATION                  SLAVE                  ON                  *.*                  TO                  "mydb_slave_user"@"%"                  IDENTIFIED                  BY                  "mydb_slave_pwd";                  Affluent                  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.

Offset, y'all need to find a main host ip address. You can cheque "hosts" file on chief host

                docker                  exec                  -information technology mysql_master true cat                  '/etc/hosts'

The concluding line will contain ip address, instance: 172.xix.0.2. This is a MASTER_HOST.

Second, find a log file and position from this command:

                docker                  exec                  mysql_master sh -c                  'export MYSQL_PWD=111; mysql -u root -e "Bear witness Master STATUS \Chiliad"'

"File:" is MASTER_LOG_FILE and "Position:" is MASTER_LOG_POS.

Now, when nosotros have all the variables, we can modify and run the following command to start a replication:

                                  # SETUP REPLICA SQL COMMANDS                  CHANGE                  MASTER                  TO                  MASTER_HOST='${IP}', MASTER_USER='mydb_slave_user', MASTER_PASSWORD='mydb_slave_pwd', MASTER_LOG_FILE='${LOG}', MASTER_LOG_POS=$POS;                  START                  SLAVE;

Replication-unsafe statements and non-deterministic queries.

The "safeness" of a argument in MySQL replication refers to whether the statement and its furnishings can be replicated correctly using statement-based format. Argument is prophylactic if it deterministic. Deterministic means statement ever produce the same result. When using statement-based logging, statements flagged every bit being unsafe generate a alert. Check a list of dangerous statements here. Example: FOUND_ROWS(), RAND(), UUID().

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 example when WHERE clause matches a lot of rows.

Terminal thoughts.

Replication is powerful role of whatever database and fortunately it is supported by MySQL.

The use range is wide. It can be used for heigh availability and data distributions, information 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