MySQL database + Docker master-slave synchronization build configuration

MySQL master-slave synchronization

1. Preface

MySQL’s built-in replication capabilities are the foundation for building large, high-performance applications. Distribute MySQL data to multiple systems. This distribution mechanism is realized by copying the data of a certain mysql host to other hosts (slave) and re-executing it.

During replication, one server acts as the master and one or more other servers act as slaves. The master server writes updates to binary log files and maintains an index of the files to track log cycles that record updates sent to the slaves. When a slave connects to the master, it notifies the master where the slave read the last successful update in its log. The slave server receives any updates that have occurred since then, and then blocks and waits for updates notified by the master server.

Note that:

  • When doing MySQL replication, all updates to the tables being replicated must be made on the primary server. Otherwise care must be taken to avoid conflicts between user updates to tables on the master and updates to tables on the slave.

(1) Which replication does mysql support

  • a. Statement-based replication: The SQL statement executed on the master server executes the same statement on the slave server. MySQL uses statement-based replication by default, which is highly efficient. Once it is found that it cannot be copied exactly, it will automatically select row-based replication.
  • b. Row-based replication: copy the changed content instead of executing the command on the slave server. Supported since mysql 5.0
  • c. Mixed type of replication: statement-based replication is used by default, and row-based replication is used once it is found that statement-based replication cannot be accurately replicated.

(2) Problems solved by mysql replication

 a. Data distribution (data distribution)

 b. Load balancing

 c. Data backup (backup) to ensure data security

 d. High availability and fault tolerance (high availability and failover)

 e. Realize read-write separation and relieve database pressure

(3) mysql master-slave replication principle

The master server records data changes in the binary binlog log. When the data on the master changes, it writes the change into the binary log; the slave server will detect whether the master binary log has changed within a certain time interval. If If there is a change, start an I/OThread to request the master binary event, and at the same time, the master node starts a dump thread for each I/O thread to send the binary event to it, and save it to the local relay log of the slave node, from The node will start the SQL thread to read the binary log from the relay log and replay it locally so that its data is consistent with that of the master node. Finally, the I/O Thread and SQL Thread will go to sleep and wait for the next wake-up.

A few points to note:
 1--The master records the operation statement into the binlog log, and then grants the remote connection permission to the slave (the master must enable the binlog binary log function; usually for data security considerations, the slave also enables the binlog function).
 2--slave opens two threads: IO thread and SQL thread. Among them: the IO thread is responsible for reading the binlog content of the master into the relay log; the SQL thread is responsible for reading the binlog content from the relay log and updating it to the slave database, so that the slave data and the master data can be kept agreed.
 3--Mysql replication requires at least two Mysql services. Of course, Mysql services can be distributed on different servers, or multiple services can be started on one server.
 4--Mysql replication is best to ensure that the Mysql version on the master and slave servers is the same (if the version cannot be satisfied, then ensure that the version of the master master node is lower than the version of the slave slave node)
 5--The time between the master and slave nodes needs to be synchronized

2. Master-slave synchronization mechanism

1. Master-slave synchronization introduction and advantages

  • Among the multiple data servers, there are master servers and slave servers. One master server corresponds to multiple slave servers.
  • The master server is only responsible for writing data, and the slave server is only responsible for synchronizing the data of the master server and allowing external programs to read the data.
  • After the master server writes data, it immediately sends the command to write data to the slave server, so that the master-slave data is synchronized.
  • The application can randomly read the data of a slave server, so that the pressure of reading data can be distributed.
  • When the slave server fails to work, the entire system will not be affected; when the master server fails to work, one of the slave servers can be easily elected as the master server

Advantages of using master-slave synchronization:

  • Improve read and write performance
    • Because after master-slave synchronization, data writing and reading are performed on different servers, and the read performance of the database can be improved by adding slave servers.
  • Improve data security
    • Because the data has been replicated to the slave server, it can be backed up on the slave server without destroying the corresponding data on the master server.

2. Master-slave synchronization mechanism

The master-slave synchronization between MySQL servers is based on the binary log mechanism. The master server uses binary logs to record changes in the database, and the slave server maintains data consistency with the master server by reading and executing the log file.

As shown in FIG:
The first part of the Mysql replication process is the master records the binary log. Before each transaction updates data, the master records these changes in the second log. MySQL writes transactions serially to the binary log, even if the statements in the transaction are interleaved. After the event is written to the binary log, the master notifies the storage engine to commit the transaction.
The second part is that the slave copies the master’s binary log to its own relay log. First, the slave starts a worker thread – the I/O thread. The I/O thread opens a normal connection on the master and starts the binlog dump process. The Binlog dump process reads events from the master’s binary log. If it has kept up with the master, it will sleep and wait for the master to generate new events. The I/O thread writes these events to the relay log.
SQL slave thread (SQL slave thread) handles the last step of the process. The SQL thread reads events from the relay log, and replays the events in it to update the data of the slave to make it consistent with the data in the master. As long as the thread is consistent with the I/O thread, the relay log will usually be in the OS’s cache, so the overhead of the relay log is small.
In addition, there is also a worker thread in the master: like any other MySQL connection, a slave opening a connection in the master will also cause the master to start a thread. The replication process has a very important limitation – replication is serialized on the slave, which means that parallel update operations on the master cannot be performed in parallel on the slave.

3. Configuration

  • Primary server: MySQL in ubuntu operating system.
  • Slave server: MySQL in a Docker container.

3.1 Docker installation and running MySQL slave

3.1.1. Get MySQL image

Master-slave synchronization tries to ensure that multiple MySQL versions are the same or similar.

$ sudo docker image pull mysql:5.7.30
or
$ sudo docker load -i file path/mysql_docker_5730.tar

3.1.2. Specify MySQL slave configuration file

  • Before using Docker to install and run the MySQL slave machine, you need to prepare the configuration file of the slave machine.
  • In order to quickly prepare the configuration file of the slave, we directly copy the configuration file of the master to the slave.
$ cd ~
$ mkdir mysql_slave
$ cd mysql_slave
$ mkdir data
$ cp -r /etc/mysql/mysql.conf.d ./

3.1.3. Modify MySQL slave configuration file

  1. Edit the ~/mysql_slave/mysql.conf.d/mysqld.cnf file.
  2. Since the master and slave are in the same computer, we choose to use different port numbers to distinguish the master and slave, and the slave port number is 8306.
# slave port number
port = 8306
# close log
general_log = 0
# The unique number of the slave
server-id = 2

3.1.4.Docker installs and runs MySQL slave machine

MYSQL_ROOT_PASSWORD : The password to create the root user is mysql.

$ sudo docker run --name mysql-slave -e MYSQL_ROOT_PASSWORD=mysql -d --network=host -v /home/ubuntu/mysql_slave/data:/var/lib/mysql -v /home/ubuntu/mysql_slave /mysql.conf.d:/etc/mysql/mysql.conf.d mysql:5.7.30
  • Explanation of command parameters

3.15. Test whether the slave machine is created successfully

$ mysql -uroot -pmysql -h 127.0.0.1 --port=8306

3.2. Master-slave synchronization

3.2.1. Configure the host (MySQL in ubuntu)

  • If the configuration file is modified, the host needs to be restarted.
sudo service mysql restart
# open log
general_log_file = /var/log/mysql/mysql.log
general_log = 1
# host unique number
server-id = 1
# binary log file
log_bin = /var/log/mysql/mysql-bin.log

3.2.2. Backup the original data of the master from the slave

  • When doing master-slave synchronization, if the slave needs the original data on the master, it must first copy a copy to the slave.
# 1. Collect the original data of the host
$ mysqldump -uroot -pmysql --all-databases --lock-all-tables > ~/master_db.sql

# 2. Copy the original data of the master from the slave
$ mysql -uroot -pmysql -h127.0.0.1 --port=8306 < ~/master_db.sql

3.2.3. Master-slave synchronization

1. Create an account for synchronizing data from the server

# log in to the host
$ mysql –uroot –pmysql
# Create a slave account
$ GRANT REPLICATION SLAVE ON *.* TO 'slave'@'%' identified by 'slave';
# Refresh permissions
$ FLUSH PRIVILEGES;

2. Display the binary log information of the MySQL host in ubuntu

$ SHOW MASTER STATUS;

3. MySQL slave in Docker connects to MySQL host in ubuntu

# log in to the slave
$ mysql -uroot -pmysql -h 127.0.0.1 --port=8306
# connect the slave to the master
$ change master to master_host='127.0.0.1', master_user='slave', master_password='slave', master_log_file='mysql-bin.000250', master_log_pos=990250;
# Start slave service
$ start slave;
# Display slave service status
$ show slave status \G


Finally a simple test:

  • After creating a new database on the host, check whether it exists directly on the slave.

Summary:

Brief summary:

  1. Configure the slave mysql file
  2. Docker installs and runs mysql from the machine
  3. Master-slave synchronization to implement configuration operations
  4. Slave synchronizes master data
  5. Test the master-slave synchronization mechanism

The above is the process of building MySQL database + Docker master-slave synchronization configuration.

The following is a link to realize the master-slave synchronization configuration on different servers:

  1. https://blog.51cto.com/sdwml/6101794
  2. https://www.cnblogs.com/huanyue9987/p/15844103.html
  3. https://blog.csdn.net/m0_63674789/article/details/124804013