Principle of master-slave replication

As the volume of services increases, the single-machine architecture cannot meet the I/O access requirements. To improve database performance, we separate read and write operations from database operations. Write operations are performed in the Master database and read operations are performed in the Slave database.

As shown in the figure above, the general process of master-slave replication is as follows:

  1. Master updates are written to binlog;
  2. Execute from library (Slave)start slaveCommand to start the I/O Thread and SQL Thread processes.
  3. The Slave I/O threads establish long connections with the Master. The Master has a Thread dedicated to maintaining the connection and sending incremental binlogs to the Slave.
  4. The Slave reads the binlog sent by the Master and writes it to the Relay Log.
  5. SQL threads from Slave libraries read Relay logs and write updates to the database.

Primary and secondary databases are set up

Docker – composer. Yml as follows:

version: '3'
services:
  mysql-master:
    image: Mysql: 5.7
    container_name: mysql-master
    command: --server_id=1 --log-bin=mysql-bin --gtid_mode=ON --enforce_gtid_consistency=ON
    environment:
      - MYSQL_ROOT_PASSWORD=root
    volumes:
      - ~/data/mysql-master:/var/lib/mysql
    ports:
      - "33061:3306"

  mysql-slave:
    image: Mysql: 5.7
    container_name: mysql-slave
    command: --server_id=2 --log-bin=mysql-bin --gtid_mode=ON --enforce_gtid_consistency=ON
    environment:
      - MYSQL_ROOT_PASSWORD=root
    volumes:
      - ~/data/mysql-slave:/var/lib/mysql
    ports:
      - "33062:3306"

networks:
  default:
    driver: bridge
Copy the code

Here a master from simple configuration, master-slave database request id, here the main library id is set to 1, from the library id is set to 2, run the docker – compose up – d command, start two database service.

Note: Binlog must be enabled. The implementation of master/slave replication depends on binlog logs.

Configure primary/secondary synchronization

The master/slave relationship is configured based on the GTID protocol, which is recommended for MySQL 5.6.

  • Enter the mysql-master database and run the following command to create a user for primary/secondary replication.

    CREATE USER 'slave'@The '%' IDENTIFIED BY '123456';
GRANT REPLICATION SLAVE.REPLICATION CLIENT ON*. *TO 'slave'@The '%';
Copy the code

  • Access the mysql-slave database and run the change master command to configure the master database information to be synchronized.

    CHANGE MASTER TO MASTER_HOST='mysql-master', MASTER_USER='slave', MASTER_PASSWORD='123456', MASTER_AUTO_POSITION=1;
Copy the code

  • runshow slave status\G;View slave library status.

  • Operation start slave; Command to start the I/O thread and SQL thread.

  • Log in to mysql-slave as user ROOT and run set global read_only=ON. Set the slave library to read-only mode.

The active/standby relationship is established

The establishment of the master/slave relationship is the same as that of the master/slave relationship. At the same time, the change master command is also run on the master library. We call this kind of architecture of the master/slave relationship between the master and the standby database as double M architecture. Note that the log_slave_updates configuration item must be set to ON ON the active and standby databases. In this way, the corresponding binlog can be generated after data is synchronized to the standby database to prepare for switching to the active database and ensure high availability of the cluster.

Verify primary/secondary data synchronization

Create a test database and a test table in the primary database and insert some data:

Enter the slave database to check whether the data has been synchronized:

Data has been synchronized to the slave database and the master/slave replication architecture has been set up.

Master-slave delay optimization

Synchronization of data from the master library requires several steps: the master library writes updates to binlog, the master library writes updates to log, and the SQL thread reads updates to log. All these steps have delays. Delays are inevitable and can only be optimized.

  1. The master and slave library servers are configured differently. The worse the configuration, the longer the delay time.
  2. There are many service read operations, resulting in high pressure from the slave database.
  3. It takes a long time for a large transaction to perform operations, and it also takes a long time to synchronize from the library.
  4. The network latency between the master and slave is large, or the data synchronization between the slave library based on the binlog takes a long time.

The corresponding optimization scheme is as follows:

  1. Upgrade the server and control the delay to a reasonable range;
  2. Increase the number of slave libraries;
  3. Major transaction operations should be arranged in the peak period of business.
  4. For scenarios requiring high real-time performance, read and write operations can be forced to the main library. For scenarios where real-time requirements are not high, Ajax or JavaScript can be used to countdown several seconds on the front-end UI interaction before the jump, or users can actively click the button to jump, thereby delaying the reading of the time point from the library.