A master-slave architecture

It’s basically a master server and a slave server

The master server performs normal reads and writes, while the slave synchronizes data from the master

Synchronization principle

  1. There is an IO thread on the slave library that makes a TCP connection to the master library
  2. The IO thread requests binlogs from the master library. The IO dump thread on the master library transfers binlogs to the slave I/O thread over the TCP connection
  3. The IO thread from the library writes the binlog read to its local relaylog
  4. Another SQL thread in the slave library will read the content in the relay log and redo the log. It will add, delete, change and check all the data executed by the master library and execute it on the slave library to make the data consistent with that of the master library

Secondary libraries can be set to readonly mode

  • Sometimes the query statements of some operation classes will be put into the standby database to check, and set to read-only to prevent misoperations.
  • Prevent the switchover logic from bugs. For example, the switchover logic may be double-write, which may cause inconsistency between the active and standby nodes.
  • You can use the readonly state to determine the role of a node.

How can I keep up to date with the primary library when I set the standby library to read-only?

Because the readOnly setting is not valid for super users, the thread used to synchronize updates has super privileges.

You are advised to set log_slave_updates to ON, which indicates that the standby database generates a binlog after executing the relay log

Reading and writing separation

Semi-synchronous replication

  1. After_commit The master library writes data to the binlog, waits for the binlog to be transferred to the slave library, commits the transaction, waits for the slave library to return a successful response, and then returns a successful response to the client
  2. Mysql5.7 By default, the primary database writes data to the binlog, and transfers the data to the secondary database. The secondary database writes data to the binlog, and returns the response to the primary database, and then returns the response to the client
Check whether shWO is running properly for semi-synchronous replicationglobal status like '%semi%'; If you see the status of Rpl_semi_sync_master_status ison, means onCopy the code

Master-slave delay

Using the Pt-HearBeat tool in the Percona-Toolkit, he creates a HearBeat table in the main library, and then has a thread timing to refine the timestamp field in the table. There is a monitor thread from the library that checks the timestamp of the hearbeat table from the library

Compare the timestamp to the current one to see how far behind the master/slave synchronization is

Solutions:

Mysql5.7 already supports parallel replication, set slave_parallel_workers>0 in the slave library and slave_parallel_type to logical_clock

If you want the data you just wrote to be forced to be read immediately, you can use middleware such as Mycat or Sharding-spherre to force both reads and writes from the main repository, so that data you write to the main repository is forced to be read immediately from the main repository

Three reasons

  1. The pressure of standby storage is large

  2. The performance of the machine where the standby library resides is worse than that of the machine where the primary library resides

  3. Large transactions

    • It is not recommended to delete too much data at once with delete statements

  4. Parallel replication capability of the standby

High availability

MHA, Master High Availablility Magager and Tools for Mysql. This tool is used to monitor the status of the main database. You can switch the slave library to the master library

This MHA also needs to be deployed separately, as manager and Node. Manager nodes are typically deployed on a separate machine, while Node nodes are typically deployed on each myslq machine, because node nodes need to perform some analysis by parsing the logs of each myslQ machine

Single table

It is recommended that the amount of single table data in mysql should not exceed 10 million, preferably within 5 million. If it can be controlled within 1 million, that is the best choice. The basic single table data within 1 million will not have too big a problem in performance, provided that you do a good job of indexing

Typically 100 million rows of data, ranging in size from one gigabyte to several gigabytes