MySQL primary/secondary replication

  • In large scale projects, MySQL replication is used to create MySQL master/slave clusters.

  • Data synchronization can be done primarily by configuring one or more standby repositories for the database server.

  • Replication is not only good for building high-performance applications, it is also the foundation for high availability, scalability, disaster recovery, backup, and data warehousing.

  • The master-slave replication of MySQL is used to separate read and write, which improves the performance of the service system and optimizes the user experience compared with the single-point database.

  • In addition, through the master slave replication to achieve high availability of the database, when the master node MySQL hangs, you can use the slave library to overhead.

Replication modes supported by MySQL

MySQL supports three replication modes

  • Statement based replication (also known as logical replication) basically means that SQL statements executed on the primary database are executed again on the secondary database.

    • Advantages: MySQL uses this type of replication by default, which is efficient.
    • Disadvantages: If you use uuid(), rand(), etc. in SQL, the data copied to the slave library will be biased.

  • Row-based replication refers to copying the updated data to the slave database instead of executing a side statement. Only versions from MySQL5.1 are supported.

  • Mixed replication: statement replication is adopted by default. When it is found that the statement cannot accurately copy data – (for example, the statement contains functions such as uUID () and rand()), line-based replication is adopted.

Principle of master-slave replication

  • MySQL replication principle overview can be generally divided into three steps:
    1. Data changes are recorded in the Binary Log on the main repository.
    2. The slave library copies logs from the master library to its own Relay Log.
    3. The standby library reads the events in the relay log and replaces them on top of the standby library data.

Here’s a closer look at the three steps of replication:

  • The first step is to log binary logs on the main library,
    • First, the main library should enable binlog logging.
    • Authorize Slave Indicates the access permission of the Slave library.

The important thing to note here is that the order in the binlog is the order in which transactions are committed, not the order in which each statement is executed.

  • Step 2: Copy the binLog from the library to its local RelayLog.
    • The slave library starts a worker thread, called the I/O thread, which establishes a normal client connection with the master library.
    • A special binlog dump thread is then started on the main library, which reads the events in the binlog.
    • After catching up with the master, it sleeps until the master notifies it of a new update statement.

This transfers binlog data to the slave relaylog through the slave I/O thread and the master binlog dump thread.

  • Step 3: Start an SQL thread from the repository, read events from Relaylog and execute them in the standby repository, thus updating the standby database data.

    • This replication architecture decouples the fetch and replay events, allowing the running I/O thread to work independently of the SQL thread.
    • This architecture also limits the replication process. Most importantly, operations that run concurrently on the primary can only be serialized in the secondary, because there is only one SQL thread to replay events from the relay log.
    • Data may be delayed and inconsistent, so if you want to ensure data consistency, always do data manipulation in the master library!

MySQL primary/secondary replication mode

MySQL supports asynchronous replication, semi-synchronous replication, GTID replication and other replication modes.

Asynchronous mode

The default replication mode of MySQL is asynchronous mode, which means that the I/O thread on the primary server of MySQL writes data to binlong and directly returns the data to the client, regardless of whether the data is transferred to the secondary server or written to relaylog. Copying data in this mode is actually risky, as data is written to the master’s binlog before it is synchronized to the slave.

  • This mode is also the most efficient, because the ability to change data is done only in the master database, and copying data from the master database does not affect the write operation of the master database.

Although the asynchronous replication mode is efficient, the risk of data loss is high. Therefore, the semi-synchronous replication mode is introduced.

Semi-synchronous mode

MySQL has supported semi-synchronous master-slave replication since version 5.5 in the form of plug-ins.

  • Asynchronous replication mode: Main library after the execution of the client committed transaction, as long as it will be written to perform logic binlog, immediately returned to the client, don’t care whether from library implementation success, so there will be a hidden danger, is performed in the main library binlog haven’t synchronization to from the library, the library hang up, this time from the library is mainly to enhance library, This is when data loss can occur.

  • Synchronous replication mode: After the master library completes the transaction submitted by the client, all slave libraries complete the transaction, and the client is returned with a successful execution. Because all slave libraries are executed, execution is blocked waiting for results to be returned, so performance can be severely impacted.

  • Semi-synchronous replication mode: In semi-synchronous replication mode, it is a replication mode between asynchronous and synchronous. After executing the transaction committed by the client, the master database waits for at least one secondary database to receive the binlog and write the data to the relay log before returning a success result to the client. The semi-synchronous replication mode improves data availability compared with the asynchronous mode, but also incurs certain performance delays, such as the round-trip time of at least one TCP/IP connection.

    • In semi-synchronous replication, it’s very clear that once a transaction commits, it’s going to live in at least two places: one is the master and one is the slave.
    • The master dump will send a binlog to the slave as well as receive an ACK from the slave. When an exception occurs, the Slave does not respond to ACK transactions. To ensure performance, the Slave is automatically downgraded to asynchronous replication and then to semi-synchronous replication after the exception is rectified.
The MySQL semi-synchronous replication process is as follows

Risks of semi-synchronous replication

Semi-synchronous replication also has some data risks. If the Master is down while waiting for the slave ACK after the Master commits, there are two kinds of problems.

  • Transaction not sent to Slave yet: If the transaction has not been sent to the Slave, the client will resubmit the transaction after receiving the failure result. Because the resubmitted transaction is executed on the new Master, it will be successfully executed. If the original Master recovers, it will be added to the cluster as a Slave.

    • The first time was when this machine was the Master,
    • The second time is synchronized from the master library as a Slave.

  • The transaction has been synchronized to the Slave machine: because the transaction has been synchronized to the Slave machine, when the client receives the failure result and commits the transaction again, you will execute the transaction twice on the current Slave machine.

  • To solve this problem, MySQL has added a new semi-synchronization mode starting from version 5.7. In the new semi-synchronization mode, Storage Commit is moved to the end of Write Slave dump.

  • This ensures that master library transactions are committed only after a transaction ACK from the Slave. The rpl_semi_sync_master_wait_point parameter has two values:

  • AFTER_SYNC: If AFTER_SYNC is set to AFTER_SYNC, the semi-synchronous replication mode is adopted.

  • AFTER_COMMIT: indicates that the old semi-synchronous replication mode is used.

AFTER_SYNC is the default semi-synchronous replication mode in MySQL 5.7.2. However, AFTER_SYNC is not a universal solution, because AFTER_SYNC is committed to the Master database after the transaction is synchronized to the Slave database. The Master transaction fails to commit, and the client receives the result of the transaction failure. However, the Slave has already written the binLog to the Relay Log. At this point, the Slave data will be more.

Parameters of the semi-synchronous replication mode:
mysql> show variables like '%Rpl%';
+-------------------------------------------+------------+
| Variable_name                             | Value      |
+-------------------------------------------+------------+
| rpl_semi_sync_master_enabled              | ON         |
| rpl_semi_sync_master_timeout              | 10000      |
| rpl_semi_sync_master_trace_level          | 32         |
| rpl_semi_sync_master_wait_for_slave_count | 1          |
| rpl_semi_sync_master_wait_no_slave        | ON         |
| rpl_semi_sync_master_wait_point           | AFTER_SYNC |
| rpl_stop_slave_timeout                    | 31536000   |
+-------------------------------------------+------------+
Copy the code
  • Switch of semi-synchronous replication mode: rpl_semi_SYNc_master_enabled
  • Rpl_semi_sync_master_timeout: semi-synchronous replication timeout period, expressed in milliseconds. When the timeout period is exceeded, the replication mode is automatically switched to asynchronous replication
Introduced in MySQL 5.7.3, this variable sets how many slave replies the master must wait for before returning to the client. The default value is 1.
  • Rpl_semi_sync_master_wait_for_slave_count: This value indicates whether the number of slaves in the current cluster still meets the semi-synchronous replication mode. The default value is ON. If the semi-synchronous replication mode is not met, all slaves switch to asynchronous replication and the value also changes to OFF
  • Rpl_semi_sync_master_wait_no_slave: indicates the semi-synchronous replication transaction submission mode. After 5.7.2, the default value is AFTER_SYNC
  • rpl_semi_sync_master_wait_point
GTID mode

MySQL has introduced the GTID replication mode since version 5.6. GTID is short for global Transaction Identifier (GTID). GTID consists of UUID and TransactionId. When the MySQL instance is started for the first time, a server_uUID is automatically generated and written to the auto-.cnf (MySQL /data/ auto-.cnf) file by default. TransactionId is the number of transactions executed on this MySQL and increases as the number of transactions increases. This ensures that GTID is globally unique in a set of replicates.

In this way, GTID can clearly see which instance the current transaction is committed from and the number of transactions committed.

Let’s look at the specific form of a GTID:

mysql> show master status; +-----------+----------+--------------+------------------+-------------------------------------------+ | File | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-----------+----------+--------------+------------------+-------------------------------------------+ | on.000003 | 187  | | | 76147e28-8086-4f8c-9f98-1cf33d92978d:1-322| +-----------+----------+--------------+------------------+-------------------------------------------+ 1 row in set (0.00 SEC)Copy the code

GTID: 76147e28-8086-4f8C-9f98-1cf33d92978d :1-322 UUID: 76147e28-8086-4f8C-9f98-1cf33d92978d TransactionId:1-322

How GTID works

The uniqueness of GTID in a set of master/slave replication clusters ensures that each GTID transaction is executed only once on a MySQL database. So how does this work? How does GTID work?

  • When the secondary server connects to the primary server, it passes to the primary server the GTID it has executed (Executed_Gtid_Set: the transaction code that has been executed) and the GTID it retrieves (Retrieved_Gtid_Set: the transaction number that the secondary library has received from the primary library).
  • The master server sends the missing GTID and the corresponding transactionID to the slave server for the slave server to complete the data. When the primary server is down, the conf server with the most successful data synchronization is identified and promoted to the primary server.
  • If a slave server is not required to be the primary server, run the change command to complete GTID on the most successful server, and then promote the forced slave server to the primary server.
The main data synchronization mechanisms can be divided into the following steps:
  • When the master updates data, it generates a GTID before the transaction and records it in the binlog.
  • The SLAVE I/O thread writes the changed binlog to the relay log.
  • The SQL thread obtains the GTID from the relay log and compares the binlog on the Slave side to see if there are records.
  • If there are records, the transactions of the GTID have been executed, and the SLAVE ignores the GTID.
  • If there is no record, the Slave performs the GTID transaction from the relay log and records it to the binlog.
  • During the parsing process, determine if there is a primary key. If there is no primary key, use the secondary index. If there is no secondary index, scan the whole table.
Advantages and disadvantages of GTID

Through the above analysis, we can conclude that the advantages of GTID are as follows:

  • Each transaction corresponds to an execution ID, and a GTID is executed only once on a server;
  • GTID is used to replace traditional replication. The biggest difference between GTID and ordinary replication is that the binary file name and location need not be specified.
  • Reduce manual intervention and reduce service failure time, when the host hung up after the software from a number of standby machine to promote a standby machine host;
Disadvantages of GTID:
  • First, non-transactional storage engines are not supported.
  • Create table is not supported… Select statement replication (master library error); DDL create table SQL, INSERT into SQL; DDL create table SQL; Since DDL causes auto-commit, this SQL requires at least two GtiDs, but in GTID mode, only one GTID can be generated for this SQL.
  • Do not allow a SQL server to update a transaction engine table and a non-transaction engine table at the same time.
  • In a MySQL replication group, it is required that all GTID be enabled or disabled.
  • Restart GTID (except mysql5.7)
  • After GTID is enabled, the traditional replication mode is no longer used (unlike semi-synchronous replication, which can be degraded to asynchronous replication if semi-synchronous replication fails).
  • Create TEMPORARY Table and DROP TEMPORARY Table statements are not supported.
  • Does not support sql_slave_skip_counter;
Prerequisites for enabling GTID:

MySQL 5.6 update my.cnf;

Gtid_mode =on (mandatory) # Enable the GTID function log_bin=log-bin=mysql-bin (mandatory) # Enable the binlog binary log function log-slave-updates=1 (mandatory) # You can also write 1 as on Enforce - gtid - consistency = 1 (choice) # 1 can be written as on MySQL 5.7 or higher, in my. Add CNF file: Gtid_mode = ON (mandatory) enforcement-gtid -consistency=1 (mandatory) log_bin=mysql-bin (optional) log-slave-updates=1 (optional) It is best to turn this feature onCopy the code