1: Why high availability
MySql can be used on a standalone server, but if MySql is suddenly down one day, then the entire service is unavailable, which is not acceptable, there has to be a backstop solution
2: MySql is highly available
High availability of MySql is to ensure that services can run properly even if a server is down. Therefore, in the production environment, at least one master and one slave architecture should be guaranteed. Since the master and slave are involved, there will be data synchronization problems
3: primary/secondary replication principle of MySql
There are three threads involved in master/slave synchronization:
- I/O thread of the secondary library: obtains binlog logs of the primary library and writes the logs to the relay log file
- SQL thread from slave library: read relay log content and parse it into SQL execution, which is actually executing the SQL of master library
- The log dump thread of the master library: reads the contents of the newly added binlog file of the master library and sends it to the slave library
4: indicates a master/slave synchronization policy
4.1: Full synchronous replication
When the client executes SQL, it must wait until all the slave and master libraries have committed successfully before returning a response to the client
-
Advantages: Data is not lost
-
Disadvantages: low efficiency. As the number of machines increases, the execution becomes more and more inefficient, because there are many machines to synchronize, and only after all the machines are committed can the response be returned to the client
4.2: Asynchronous replication
After executing SQL, the client will only notify the thread to send the binlog to the slave library. The master library will submit the binlog directly without waiting for the response from the slave library
- Advantages: High efficiency
- Disadvantages: The data will be lost. If the master database does not receive the binlog sent by the master database during synchronization, the master database will not be found in the slave database
- Application scenario: Low data security and high data efficiency
4.3: Semi-synchronous replication
After the client executes the SQL, at least one of the slave libraries must be synchronized successfully to return the response to the client, which means that the slave library still has the data even if the master library has died
There is a compromise between synchronous and asynchronous
5: Traditional replication
In traditional master-slave replication, when the master performs a transaction, the position at the beginning and end of the transaction are recorded in the binlog file. This is why in traditional master-slave replication, the slave library configures the position of the master library file, that is, the position from which the synchronization starts. The binlong log is then synchronized to the slave node whenever the binglog changes
5: GTID replication
GTID replication is available after MySql5.6. GTID is the Global Trancation ID, which is composed of the unique ID of the MySql machine +TID. TID is the number of transactions committed by the current MySql instance, which is increasing gradually.
Before updating the data, Matser will generate a GTID and record it together in the binlog. After the binlog is synchronized to the Slave, the Slave will get the GTID first and determine whether the GTID has been processed. If it has been processed, the Slave will ignore it and execute it if it has not
5.1 advantages of GTID:
- You don’t need to go to position to synchronize
- It’s easier than traditional copying
8: multi-source replication
Multi-source replication can merge data from multiple MySql instances into one MySql instance. However, multi-source replication does not solve conflicts, such as database name duplication, etc., which need to be resolved in the application itself