Welcome to pay attention to github.com/hsfxuebao, I hope to help you, if you think it can trouble to click on the Star ha

1. Master/slave replication Overview

1.1 How to Improve the Database Concurrency Capability

In practical work, we often compareReis works with MySQL as a cacheWhen there is a request, it will first look up from the cache, if there is a direct fetch. If there is no more access to the database, thenImproved read efficiency, also reduces the pressure to access the back-end database. Redis cache architecture isHigh concurrency architectureIs a very important part of.

The general application of the database is “read more write less”, that is to say, the pressure on the database to read data is relatively large, there is an idea is to use the database cluster scheme, do the master and slave architecture, read and write separation, which can also improve the concurrent processing capacity of the database. However, not all applications need to set up master/slave schema for the database. After all, there is a cost to set up schema.

If our goal is to improve the efficiency of high concurrent access to the database, we should first consider how to optimize SQL and indexes, which is simple and effective. Secondly, the cache strategy is adopted. For example, Redis is used to store hotspot data in the in-memory database to improve the reading efficiency. Finally, the database adopts master-slave architecture to separate read and write.

In accordance with the above method and optimization, the cost of use and maintenance from low to high.

1.2 Functions of Master/Slave Replication

Master-slave synchronous design can not only improve the database throughput, but also the following three aspects.

  • The first function: read/write separation, we can through the master/slave replication to synchronize data, and then through read/write separation to improve the database concurrency processing

    One of them is the Master library, which is responsible for writing data. We call it the write library.

    Everything else is the Slave Slave library that reads the data, which we call the read library.

    When the master database is updated, data is automatically copied to the slave database, and when the client reads data, it is read from the slave database.

    Faced with the requirement of “read more and write less”, the method of read and write separation can achieve higher concurrent access. At the same time, we can also load balance slave servers, so that different read requests are evenly distributed to different slave servers according to the policy, so that the read is smoother. Another reason for the smooth read is to reduce the impact of the lock table. For example, we let the main library take care of the write lock. When the main library has a write lock, it does not affect the SELECT from the library.

  • The second function is data backup. We use master-slave replication to copy data from the master to the slave, which is a hot backup mechanism, that is, while the master is running, it does not affect the service.

  • The third function is high availability. The degree of high availability can be measured by a metric called uptime/year time. For example, to make the system available 99.999% of the time throughout the year, it means that the system is unavailable for no more than 365*24*60*(1-99.999%)=5.256 minutes (including the time of system crash and downtime caused by routine maintenance), and the rest of the time must be kept available.

In fact, higher high availability means higher cost. In reality, we need to choose between business needs and costs.

2. Principle of master/slave replication

The Slave reads the binlog from the Master for data synchronization.

2.1 Principle Analysis

Three threads:

In fact, the principle of master-slave synchronization is based on binlog data synchronization. During master-slave replication, three threads operate, one for the master library and two for the slave library.

  • A binary log dump thread is a primary library thread. When the slave thread connects, the master library can send binary logs to the slave library. When the master library reads events, it locks the binary log, and releases the lock after reading.

  • The slave I/O thread connects to the master and sends a request to update the Binlog to the master. In this case, the I/O thread from the library can read the update portion of the Binlog sent by the binary log dump thread of the master library and copy it to the local Relay log.

  • The slave SQL thread reads the relay log from the slave and executes the events in the log to keep the slave data in sync with the master.

Note: Not all versions of MySQL have binary logging enabled for the server by default. Before performing master/slave synchronization, we need to check whether binary logging is enabled on the server.

Unless specified otherwise, the slave server by default executes all events saved in the master server. You can also configure specific events to be executed from the server.

Three steps of replication:

  • MasterLog write operations to binary logsbinlog. These records are called binary log events
  • Slave 将Master 的binary log eventsCopy to its relay logrelay log
  • SlaveRedo events in the relay log to apply the changes to your own database. MySQL replication is asynchronous and serialized, and is restarted fromAccess pointBegin to copy

Replication problem: latency

2.2 Basic Principles of Replication

  • eachSlaveThere is only oneMaster
  • eachSlaveThere can be only one unique server ID
  • eachMaster You can have more than oneSlave

3. One master and one slave architecture

One host handles all write requests and one slave handles all read requests. The architecture diagram is as follows:

3.1 Preparations

  1. Two CentOS VMS are available
  2. Install MySQL (MySQL8.0) on each virtual machine

Before we talked about how to clone a CentOS. You can install MySQL on a CentOS, and then clone a VM that contains MySQL.

Note: You need to modify the following information about the cloned host: ① MAC address ② hostname ③ IP address ④ UUID.

In addition, if the cloned VMS (including MySQL Server) are generated, the UUID of MySQL Server must be the same. Otherwise, errors may occur in some scenarios.

For example, show slave status\G displays the following error:

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have
equal MySQL server UUIDs; these UUIDs must be different for replication to work.
Copy the code

Change the UUID mode of MySQL Server: Ensure that the UUID of the slave Server is different from that of the host

vim /var/lib/mysql/auto.cnf

systemctl restart mysqld
Copy the code

3.2 Host Configuration File

It is recommended that the mysql version be the same and the background run as a service. All the primary and secondary configuration items are configured under the [mysqld] node and are lowercase letters. Set the parameters as follows:

  • Will choose
#[must] primary server unique ID server-id=1 #[must] enable binary log, named path For example, the local directory /log/mysqlbin log-bin=atguigu-binCopy the code
  • optional
#[Optional] 0 (default) read/write (host), 1 read-only (slave) Read/write (slave) Read -only=0 # Set the length of log file retention in seconds binlog_expire_logs_seconds=6000 # Control the size of a single binary log. Max_binlog_size =200M #[Optional] Set database not to be copied binlog-ignore-db=test #[Optional] Set database to be copied, default all records. Example: binlog-do-db=atguigu_master_slave binlog-do-db= name of the primary database to be copied #[Optional] Set the binlog format bilog_format=STATEMENTCopy the code

Restart the background mysql service for the configuration to take effect

Note: The primary/secondary replication is set up first. When the primary/secondary replication of MySQL is started, the secondary machine does not inherit the host data

3.2.1 Setting the Binlog format

3.2.1.1 Format 1:The STATEMENT model

(Statement-based Replication (SBR))

binlog_format=STATEMENT
Copy the code

Each SQL statement that modifies data is logged in the binlog. This is the default binlog format.

  • Advantages of SBR:
    • Long history, mature technology
    • You do not need to record the changes of each row, reducing the amount of binlog logs and smaller files
    • The binlog contains all database changes and can be used to audit database security, etc
    • Binlog can be used for real-time restoration, not just replication
    • The version of the secondary server can be higher than that of the primary server
  • Disadvantages of SBR:
    • Not all UPDATE statements can be copied, especially if they contain indeterminate operations
  • Statements using the following functions also cannot be copied: LOAD_FILE(), UUID(), USER. , FOUND_ROWS().sysdate () (unless the -sysdate-is-now option is enabled at startup)
    • INSERT… SELECT produces more row-level locks than RBR
    • Replicating an UPDATE that requires a full table scan (WHERE no index is used) requires more row-level locks than RBR requests
    • For InnoDB tables with AUTCUNCREMENT fields, INSERT statements block other INSERT statements
    • For some complex statements, the resource consumption on the slave server is more severe, and in RBR mode, only the record of the generated change is affected
    • Executing complex statements can consume more resources if errors occur
    • Data tables must be almost identical to the primary server, otherwise replication errors may occur

3.2.1.2 Format 2:The ROW pattern

(Row-based replication (RBR))

binlog_format=ROW
Copy the code

MySQL 5.1.5 does not record the context information of each SQL statement. It only records which data is modified and how it is modified.

  • Advantages of RBR:
    • Any situation can be replicated, which is the safest way to replicate. (e.g., stored procedures, function and trigger calls and triggers will not be copied correctly in certain cases)
    • In most cases, replication is much faster for tables on the slave server that have primary keys
    • There are fewer row locks when copying the following statements: INSERT… SELECT, INSERT with the AUTO “NCREMENT field, UPDATE or DELETE statements with no conditions attached or that don’t modify many records
    • Fewer locks for INSERT, UPDATE, and DELETE statements
    • It is possible to perform replication from the server using multi-line eyes
  • Disadvantages of RBR:
    • The binlog is much bigger
    • Complex rollbacks can contain a large amount of data in the binlog
    • When an UPDATE statement is executed on the primary server, all records that change are written to the binlog, whereas the SBR writes only once, resulting in frequent concurrent binlog writes
    • Can’t see what statements are copied from the binlog

3.2.1.3 Format 3:MIXED mode

(Mixed-mode Replication (MBR))

binlog_format=MIXED
Copy the code

Starting with version 5.1.8, MySQL offers a Mixed format, which is essentially a combination of Statement and Row.

In Mixed mode, the general statement modification uses the statment format to store the binlog. For some functions, such as a statement that cannot perform a master/slave copy, a row format is used to save a binlog.

MySQL selects a log format for each SQL Statement executed, that is, between Statement and Row.

3.3 Slave configuration File

All primary and secondary configuration items must be in the [mysqld] field of my.cnf and lowercase letters are required.

  • Will choose
#[must] unique ID of secondary server server-id=2Copy the code
  • optional
#[optional] Enable relay log relay-log=mysql-relayCopy the code

Restart the background mysql service for the configuration to take effect.

Note: Firewalls are disabled on both the master and slave computers

service iptables stop #CentOS 6

systemctl stop firewalld.service #CentOS 7

3.4 Host: Create and authorize an account

GRANT REPLICATION SLAVE ON *.* TO 'slave1'@' SLAVE database IP' IDENTIFIED BY 'abc123'; # 5.5 and 5.7Copy the code

Note: If you are using MySQL8, you need to create an account as follows and authorize slave:

CREATE USER 'slave1'@'%' IDENTIFIED BY '123456'; GRANT REPLICATION SLAVE ON *.* TO 'slave1'@'%'; # This statement must be executed. Otherwise, see below. ALTER USER 'slave1'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; flush privileges;Copy the code

Error: show slave status\G

Last_IO_Error: error connecting to master ‘[email protected]:3306’ – retry-time: 60 retries: 1 message: Authentication plugin ‘caching_sha2_password’ reported error: Authentication requires secure connection.

Query the Master status and record the values of File and Position.

show master status;
Copy the code

Record the values of File and Position

After this step, do not operate the MySQL server on the primary server to prevent the status of the primary server from changing.

3.5 Slave Host: Configure the host to be replicated

3.5.1 Step 1: Run the command to copy the host from the host

CHANGE MASTER TO MASTER_HOST=' HOST IP address ', MASTER_USER=' host username ', MASTER_PASSWORD=' host username password ', MASTER_LOG_FILE='mysql-bin '. ', MASTER_LOG_POS= specific value;Copy the code

For example:

CHANGE MASTER TO MASTER_HOST = '192.168.1.150, MASTER_USER =' slave1 ', MASTER_PASSWORD = '123456', MASTER_LOG_FILE = '000007' atguigu - bin., MASTER_LO G_POS=154;Copy the code

3.5.2 Step 2:

START slave;Copy the code

If an error is reported:

You can perform the following operations to delete previous relay_log information. Then re-execute CHANGE MASTER TO… Statement.

mysql> reset slave; Delete the relaylog file from the SLAVE database and re-enable the new relaylog fileCopy the code

Next, check the synchronization status:

SHOW SLAVE STATUS\G;
Copy the code

If the preceding two parameters are Yes, the primary/secondary configuration is successful.

If the following is explicitly stated, it is not true. The possible reasons for this error are:

Network failure 2. Account password error 3. Firewall 4. Mysql > connect to server 6Copy the code

3.6 test

Host new library table, new table, INSERT record, slave machine replication

3.7 Stopping primary/Secondary Synchronization

Command to stop master/slave synchronization:

stop slave;
Copy the code

How do I reconfigure the primary and secondary

If the replication function of the secondary server is disabled, you need to reconfigure the primary and secondary servers. Otherwise, the following error will be reported:

To reconfigure the master and slave nodes, perform the following operations on the slave node:

stop slave; reset master; Mysql > delete from Master; mysql > delete from Master;Copy the code

3.8 the following

Set up a master/slave replication: Double master/slave replication

One host, M1, handles all write requests, and its slave, S1, m2, and its slave, S2, handle all read requests. When m1 is down, M2 is responsible for write requests. M1 and M2 are standby. The architecture diagram is as follows:

4. Data consistency problem

Requirements for master/slave synchronization:

  • Data consistency between read and write libraries (final consistency)
  • Write data must be written to the write library
  • Read data must go to read library (not necessarily)

4.1 Understand the master-slave delay problem

Binary logs are a file for master/slave synchronization. In the process of network transmission, there must be master/slave delay (for example, 500ms). As a result, the data read from the library may not be the latest data, that is, data inconsistency during master/slave synchronization.

For example, there are three points in time that cause master-slave delay:

  • Master A performs A transaction, writes to binlog, and we’ll call that T1;
  • And then we pass it to library B, and we call the moment when we receive this binloge from library B T2;
  • To complete the transaction from library line B, we label this moment T3.

4.2 Causes of primary/Secondary Delay

Under normal network conditions, the time required for the log to pass from the master to the slave is very short, that is, the t2-T1 value is very small. That is, under normal network conditions, the main source of the primary/secondary delay is the time difference between the secondary database receiving the binlog and executing the transaction.

The most direct manifestation of master/slave latency is that the slave consumes the relay logs more slowly than the master produces the binlogs. Reasons for making:

  • The slave library has worse machine performance than the master library
  • Pressure from the storage is large
  • Execution of large transactions

Example 1: Delete too much data with delete statement at one time Conclusion: Control the amount of data to be deleted in each transaction and divide it into multiple deletes.

Example 2: One-off insert… Select inserts too much data

Example 3: Large table DDL For example, if it takes 10 minutes to add a field to a 500W table in the master library, it will also take 10 minutes to add a field from the node.

4.3 How can I Reduce the Master/Slave Delay

To reduce the master-slave latency, do the following:

  • Reduce the concurrent probability of multi-threaded large transactions and optimize business logic
  • Optimize SQL, avoid slow SQL,Reduce batch operationIt is recommended to write the script in the form of update-sleep.
  • Improve the configuration of slave machinesTo reduce the efficiency difference between the master library writing binlog and the slave library reading binlog.
  • Try to useShort of the link, that is, the distance between the master library and the slave library server should be as short as possible to improve the port bandwidth and reduce the network delay of binlog transmission.
  • In real time, the service read is forcibly removed from the primary database, and the secondary database only performs DISASTER recovery and backup.

4.4 How to Solve the Consistency Problem

If the data for an operation is stored in the same database, write locks can be added to the records when the data is updated so that data inconsistencies do not occur when the data is read. But at this time the role of the slave library is backup, did not play read and write separation, share the role of the master library read pressure

In the case of read/write separation, data inconsistency between the primary and secondary synchronization is resolved by data replication modes. The following three replication modes are available based on the data consistency from weak to strong.

4.4.1 Method 1: Asynchronous Replication

Asynchronous mode is the client to submit the COMMIT after don’t need to wait from the library to return any results, but the results back to the client directly, the good place is to won’t affect the efficiency of the main library to write, but there might be the main library downtime, and Binlog haven’t synchronization to from the situation of the library, which is the main library and inconsistent data from library. At this point, a new master is selected from the library, and the new master may be missing committed transactions from the original master server. Therefore, data consistency in this replication mode is weakest.

4.4.2 Method 2: Semi-Synchronous Replication

Semi-synchronous replication has been supported since MySQL5.5. After COMMT is submitted by the client, the result is not directly returned to the client. Instead, it is returned to the client after at least one Binlog is received from the library and written to the relay log.

This has the advantage of improving data consistency and, of course, adding at least one more network connection delay than asynchronous replication, reducing the efficiency of primary library writes.

The rpl_semi_SYNc_master_WAIT_for_slave_count parameter was also added in MySQL5.7. The number of slave libraries that respond can be set to 1 by default, meaning that as long as one slave library responds, it can be returned to the client. Increasing this parameter increases the strength of data consistency, but also increases the time the master waits for the slave to respond.

The master library receives a certain number of ACKS. If the synchronization is successful, the master library returns a successful ACK

4.4.3 Method 3: Group Replication

Neither asynchronous replication nor semi-synchronous replication can guarantee data consistency. Semi-synchronous replication determines whether the data is returned to the client by judging the number of responses from the library. Although data consistency is improved compared with asynchronous replication, it still cannot meet the requirements of high data consistency, such as the financial field. The MGR nicely compensates for these two replication modes. Group Replication technology, or MySQL Group Replication (MGR), is a new data Replication technology introduced by MySQL in 5.7.17. This Replication technology is state machine Replication based on Paxos protocol.

How does MGR work

First of all, we will form a replication group with multiple nodes. When performing RW transactions, the consent of Consensus layer is required. In other words, if a RW transaction is to be committed, it must be agreed by the “majority” of the group (corresponding to Node nodes). Most mean that the number of agreed nodes needs to be greater than (N/2+1) in order for the submission to proceed, rather than being dictated by the original originator. For read-only (R0) transactions, COMMIT does not require intra-group consent.

In a replication group, there are several nodes, each of which maintains its own copy of data, and implements atomic elimination and global ordered messages in the consistency protocol layer to ensure the consistency of data within the group.

One of the important reasons that MGR is based on the Paxos protocol is that it is an epoch-making innovation that brings MySQL into the era of strong data consistency. The Paxos algorithm was developed by 2013 Turing Prize winner Leslie Lamport in 1990. Search for the decision mechanism for this algorithm. In fact, Paxos algorithm has been widely used as a distributed consistency algorithm since it was put forward. For example, Apache’s ZooKeeper is also implemented based on Paxos.

5. Knowledge extension

In the configuration of master/slave architecture, if we want to adopt the policy of read/write separation, we can write our own programs, or we can implement it through the third-party middleware.

  • The advantage of writing our own programs is that we can be more independent, we can determine which queries are executed in the slave library, and we can consider which queries can be executed in the master library in order to achieve high real-time requirements. At the same time, the program directly connected to the temple (according to the library, reduce the middle layer, equivalent to reduce performance loss.

  • The middleware approach has obvious advantages and powerful functions. Simple to use. However, there is a performance penalty due to the addition of a middleware layer between the client and the database, and commercial middleware also has a usage cost. We can also consider adopting some excellent open source tools.

  • CobarAli B2B business group, started in 2008, served in Ali for more than 3 years, took over 3000+ MySQL database schema, cluster processing online SQL requests more than 50] times a day. Cobar discontinued maintenance due to the departure of the Cobar promoter.
  • MycatIt was the open source community that redeveloped Ali Cobar, solved the problems of Cobar, and added many new features to it. The youth is better than the blue.
  • OneProxyBased on the official proxy idea of My SQL, OneProxy used My words slowly in the process, which was a commercial charging middleware. I dropped some features and focused onPerformance and stabilityOn.
  • kingshardBy small teams using g. Language development, still need to develop, need to constantly improve.
  • VitessIt’s produced by Youtube, and the architecture is very complex. MySQL does not support the native protocol, the use of large chang to transform this.
  • AtlasIt is rewritten by 360 team based on mysql Proxy, and the function needs to be improved. It is unstable under high concurrency.
  • MaxScaleMariadb is a middleware developed by Mariadb (a version of MySQ maintained by the author)
  • MySQLRouteMySQL middleware is the official MySQL middleware released by Oracle

Active/standby switchover:

  • Take the initiative to switch
  • Passive switching
  • How can I tell if there is a problem with the main library? How to solve the data inconsistency problem in the process?

Refer to the article

MySQL from the beginning to the master of MySQL Technology Insider: InnoDB Storage Engine (Version 2) how MySQL is running From the root to understand MySQL database Index Design and Optimization