preface

As the application service data continues to increase, the application response speed decreases. During the detection process, it is not difficult to find that most of the requests are query operations. At this point, we can expand the database into a master-slave replication mode, separate the read and write operations, multiple databases share requests, so as to reduce the single library access pressure, and then the application is optimized.

The body of the

Master/slave replication mode

MySQL 5.6 there are two ways to start master/slave replication: log-based (binlog) and GTID (global transaction identifier).

This article deals only with log binlog-based master/slave configuration.

Master slave replication process

MySQL synchronization is implemented by three threads. The basic steps are as follows:

  1. The master server logs updates to the Binary log, which records Binary log events. This step is done by the master library thread.

  2. The slave library copies the binary log of the master library to the local Relay log. This step is done by the slave I/O thread.

  3. Reading events from the relay log from the library and replacing them into the data is done by the slave SQL thread.

Advantages of master-slave mode

1. Load balancing

Typically, the master server is used to update, delete, and create data, leaving the query work to slave libraries.

2. Remote Dr And backup

Data can be synchronized from the primary server to the remote secondary server, greatly improving data security.

3. High availability

The replication function of the database synchronizes data between the master server and the slave server. Once the master server fails, the slave server immediately plays the role of the master server to ensure the continuous and stable operation of the system.

4. High scalability

The master/slave replication mode supports two expansion modes:

  • scale-up

Upward or vertical expansion is mainly to provide a server with better performance than the current server, such as increasing CPU and memory and disk array, because there are multiple servers, so the scalability is greater than a single server.

  • scale-out

Outward expansion or horizontal expansion refers to the expansion of increasing the number of servers, which can mainly distribute the pressure of each server.

Disadvantages of master-slave mode

1. Increased costs

The cost of setting up a master and slave server is definitely increased, because the cost of one server is completely different from that of two servers, and there is an additional performance cost because the master and slave must enable binary logging.

2. Data delay

There must be some data delay in copying data from the master to the slave. Therefore, when the query is just inserted, the query may not come out. Of course, if it is the inserter’s own query, it can be directly from the main library, of course, this also needs to use code control.

3. Write more slowly

Master-slave replication is mainly aimed at systems where read is much larger than write or where real-time requirements for data backup are high. Because the primary server requires more operations on the write, and there is only one primary library that can write, the write pressure cannot be spread out.

Prerequisites for master – slave replication

  1. The operating system versions and bits of the primary and secondary servers are consistent.

  2. The versions of the primary and secondary databases must be the same.

  3. The data in the master and slave databases must be consistent.

  4. If binary logging is enabled for the primary database, the server_id of the primary and secondary databases must be unique on the LAN.

Specific configuration

1. Environment preparation

The name of the The version number
Docker 18.03.1 – ce
Docker Compose 1.21.1
MySQL 5.7.17

2. Configuration docker – compose. Yml

docker-compose.yml

version: '2'
services:
  mysql-master:
    build:
      context: . /
      dockerfile: master/Dockerfile
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "MYSQL_DATABASE=replicas_db"
    links:
      - mysql-slave
    ports:
      - "33065:3306"
    restart: always
    hostname: mysql-master
  mysql-slave:
    build:
      context: . /
      dockerfile: slave/Dockerfile
    environment:
      - "MYSQL_ROOT_PASSWORD=root"
      - "MYSQL_DATABASE=replicas_db"
    ports:
      - "33066:3306"
    restart: always
    hostname: mysql-slave
Copy the code

3. Configure the primary database

3.1. The configuration Dockerfile

Dockerfile

FROM mysql:5.7.17
MAINTAINER harrison
ADD ./master/my.cnf /etc/mysql/my.cnf
Copy the code

3.2. Configure the my.cnf file

my.cnf

[mysqld]
Set server_id to IP
server_id=100  
SQL > select * from database where mysql database is not synchronized
binlog-ignore-db=mysql  
## Enable binary log function, can be arbitrary, the best meaning (key is here)
log-bin=replicas-mysql-bin  
The memory allocated for each session, used to store the binary log cache during the transaction
binlog_cache_size=1M  
## Mixed,statement,row, default statement
binlog_format=mixed  
## Number of days for automatic deletion/expiration of binary logs. The default value is 0, indicating that the data will not be automatically deleted.
expire_logs_days=7  
Skip all errors or specified types of errors encountered in master/slave replication to avoid replication interruption on slave.
Error 1032 is caused by data inconsistency between primary and secondary databases
slave_skip_errors=1062
Copy the code

4. Configure from the database

4.1. The configuration Dockerfile

Dockerfile

FROM mysql:5.7.17
MAINTAINER harrison
ADD ./slave/my.cnf /etc/mysql/my.cnf
Copy the code

4.2. Configure the my.cnf file

[mysqld]
Set server_id to IP
server_id=101  
SQL > select * from database where mysql database is not synchronized
binlog-ignore-db=mysql  
The binary log function is enabled for the Slave to function as the Master of other slaves
log-bin=replicas-mysql-slave1-bin  
The memory allocated for each session, used to store the binary log cache during the transaction
binlog_cache_size=1M  
## Mixed,statement,row, default statement
binlog_format=mixed  
## Number of days for automatic deletion/expiration of binary logs. The default value is 0, indicating that the data will not be automatically deleted.
expire_logs_days=7  
Skip all errors or specified types of errors encountered in master/slave replication to avoid replication interruption on slave.
Error 1032 is caused by data inconsistency between primary and secondary databases
slave_skip_errors=1062  
## relay_log Configures relay logs
relay_log=replicas-mysql-relay-bin  
## log_slave_updates indicates that slave writes replication events to its binary log
log_slave_updates=1  
Prevent data from changing (except for special threads)
read_only=1  
Copy the code

5. Create a container

Go to the docker directory and run the docker-compose startup command.

$ docker-compose up -d
Copy the code

As shown in the figure, containers for the primary and secondary MySQL databases have been created successfully.

Configure the connection information for the primary and secondary databases as follows:

  • The master database

  • From the database

6. Configure the secondary database

Check the starting state of the slave library

$ show master status;
Copy the code

As shown in the figure, the slave database is in an unsynchronized replication state.

Check the status of the master library

$ show master status;
Copy the code

Record the file name and data synchronization start position of the primary database, binary-log.

  • File: replicas-mysql-bin.000003
  • Position: 154

The secondary library configures the master library information

Run the related configuration SQL of the master database on the slave database for master/slave association

CHANGE MASTER TO
    MASTER_HOST='mysql-master',
    MASTER_USER='root',
    MASTER_PASSWORD='root',
    MASTER_LOG_FILE='replicas-mysql-bin.000003',
    MASTER_LOG_POS=154;
Copy the code

Restart the slave service

$ stop slave
$ start slave
Copy the code

Further check the status information of the slave database, the two have been synchronized data association.

7. Create the target table

Create a test data table, course, in the primary database

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for course
-- ----------------------------
DROP TABLE IF EXISTS `course`;
CREATE TABLE `course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL.`lesson_period` double(5.0) DEFAULT NULL.`score` double(10.0) DEFAULT NULL,
  PRIMARY KEY (`id`))ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
Copy the code

Data on the primary and secondary databases is synchronized. The primary and secondary replication clusters are set up.

Replication type of MySQL

Statement based replication

Statements executed on the primary server are executed again on the secondary server, supported after mysqL-3.23.

Problems: Deviations may occur due to incomplete synchronization, and statements may be executed by a different user.

Row-based replication

In mysql-5.0, copy the changed content directly from the main server, regardless of which statement caused the change.

Problem: For example, if there are 10,000 users in a payroll table, if we add the salary of each user to 1000, then row-based replication will have to copy 10,000 rows of content, resulting in high overhead, whereas statement based replication only needs one statement.

Mixed type replication

MySQL uses statement-based replication by default. When statement-based replication causes problems, it uses row-based replication, which MySQL automatically selects.


Welcome to the public number: Zero one Technology Stack

This account will continue to share learning materials and articles on back-end technologies, including virtual machine basics, multithreaded programming, high-performance frameworks, asynchronous, caching and messaging middleware, distributed and microservices, architecture learning and progression.