In our usual work, the most used database is MySQL. With the increase of business, if only a server is overloaded, it is easy to cause downtime.
The data stored in the MySQL database will be lost, so how to solve the problem?
In fact, MySQL itself has a master/slave replication function to help us achieve load balancing and read/write separation.
For the Master server, writing is the primary task, while the Slave server is the primary task, which greatly reduces stress and improves efficiency.
Let’s follow Koba to see what the key points are:
Introduction As services grow, a data server is overloaded. At this time, it is necessary to reduce the pressure and achieve load balancing read and write separation, one master and one plexus or one master and many slave.
The master server only writes, while the slave server only reads, improving efficiency and reducing stress.
Primary/secondary replication can be classified as follows:
Primary/secondary synchronization: When a user writes data, the primary server must synchronize data with the secondary server to inform the user that the data is successfully written, which takes a long time.
Primary/secondary asynchronous: If a user accesses the primary server, the write data is returned to the user immediately.
Primary/secondary semi-synchronization: When a user accesses write data, the primary server writes data and synchronizes one of the secondary servers.
In the form of
A master from
From more than a master
One master, one slave and one master, many slave are the most common master and slave architectures we see today. They are simple and effective to use, not only to achieve HA, but also to achieve read and write separation, thus improving the concurrency of the cluster.
More from Lord
Multiple MySQL databases can be backed up to a server with good storage performance.
Double master replicates
Double master replication, that is, each master can master the slave replication, each master is the master of the other server salve. Changes made by either party are then replicated to the other party’s database.
Cascade copy
Cascading replication In cascading replication mode, data on some slaves is synchronized to slave nodes instead of the master node.
Because if the master node has too many slave nodes, some of the performance is lost for replication, we can have 3-5 slave nodes connected to the master node and the other slave nodes connected to the slave node as secondary or tertiary nodes, which not only relieves the stress on the master node, but also has no negative impact on data consistency.
Principle MySQL master-slave replication is based on the master server tracking all changes to the database in binary logs. Therefore, to replicate, binary logging must be enabled on the primary server.
Each slave receives data that has been logged from the master server. When a slave connects to the master, it informs the master to read the last successful update from the server log.
Any updates that have occurred since then are received from the server and the same updates are performed on the host. Then block updates waiting for notifications from the master server.
The backup performed by the secondary server does not interfere with the primary server, which can continue to process updates during the backup process.
The primary/secondary replication process of MySQL is as follows:
Generate two threads from the library, one I/O thread and one SQL thread;
The I/O thread requests the master binlog and writes the resulting binlog to the relay log file.
The primary library generates a log dump thread to pass binlogs to the secondary LIBRARY I/O thread.
The SQL thread will read the logs in the relay log file and parse them into specific operations to achieve the consistency of the primary and secondary operations and the final data consistency.
Working process Request flow The primary and secondary processes for setting up a request are as follows:
When the secondary server connects to the primary server, the primary server creates a log dump thread to send the contents of the binlog. When reading the contents of a binlog, the binlog on the master node of the object is locked, which is unlocked when the reading is complete and sent to the slave server.
After the start slave command is executed on the slave node, the slave node creates an IO thread to connect to the master node and request the update of the binlog in the master library. The I/O thread receives updates from the binlog dump process on the primary node and saves them to relay log.
The SQL thread of the slave node is responsible for reading the contents of realY-log and parsing them into specific operations to ensure the consistency of master and slave data.
type
Asynchronous replication
A master library, one or more slave libraries, data asynchronously synchronized to the slave library.
In asynchronous replication mode, the master node does not actively push data to the slave node. The master database immediately returns the data to the client after executing the transaction submitted by the client, regardless of whether the slave database has received and processed the data.
One problem is that if the master node crashes, the transactions that have been committed on the master node may not be transmitted to the slave node. If the slave node is forcibly promoted to the master node, the data on the new master node may be incomplete.
Synchronous replication A unique replication mode in MySQL Cluster.
A success message is returned to the client when the primary library completes a transaction and all secondary libraries copy the transaction successfully.
Because you have to wait for all slave libraries to complete the transaction before returning a success message, the performance of a fully synchronous replication must be severely affected.
Semi-synchronous replication
On the basis of asynchronous replication, ensure that at least one slave library has received and logged an item on any master library before committing it.
Semi-synchronous replication is between asynchronous replication and full synchronous replication. After executing the transaction submitted by the client, the master database does not immediately return the transaction to the client, but waits for at least one slave database to receive and write the transaction to the relay log before returning the success message to the client (the Binlog of the master database can only be guaranteed to be transmitted to at least one slave node). Otherwise, you need to wait until the timeout period and then switch to asynchronous mode before committing.
Compared with asynchronous replication, semi-synchronous replication improves data security and ensures that data can be successfully backed up to the slave database to a certain extent. Meanwhile, semi-synchronous replication also causes a certain degree of delay, but the delay is lower than that in full synchronous mode. The delay is at least one TCP/IP round trip time. Therefore, semi-synchronous replication is best used on low-latency networks.
The semi-synchronization mode is not built-in to MySQL. Since MySQL 5.5 is integrated, the master and slave plug-ins need to enable the semi-synchronization mode.
Delayed replication On the basis of asynchronous replication, the data synchronization delay of the primary and secondary databases is manually set to ensure that the data synchronization delay is at least this parameter.
Mode The MySQL primary/secondary replication supports two log formats, and the two log formats correspond to their respective replication modes. Of course, there are hybrid types of copying that combine the two.
Statement replication Statement based replication is equivalent to logical replication, that is, statements that record operations in binary logs are replicated from the database by these statements.
This method is simple, the binary file is small, the transmission bandwidth consumption is small. But statement-based updates depend on other factors, such as the use of timestamps when inserting data.
Therefore, in the development, we should try to put the business logic logic in the code layer, rather than in MySQL, which is not easy to expand.
Features:
High transmission efficiency, reduce delay.
Statement assignment does not fail when updating non-existent records from the library. Row replication, on the other hand, leads to failure, which leads to earlier discovery of master/slave inconsistencies.
Let’s say there are a million data pieces in the table, and a SINGLE SQL update for all the tables. A statement based replication would send only one SQL, whereas a row based replication would send a million update records
Row data replication Row-based replication is equivalent to physical replication, that is, each row of the actual updated data recorded in the binary log.
As a result, the replication pressure is high, and logs occupy large space and transmission bandwidth. But this approach is more accurate than statement-based replication.
Features:
No query plan needs to be executed.
I don’t know exactly what statement is being executed.
For example, a statement that updates a user’s total score needs to count all the user’s points before writing them to the user table. In the case of statement-based replication, the user’s credits need to be counted again from the slave library, while row-based replication updates the record directly without counting the user’s credits.
Mixed replication Generally, statement-based replication is adopted by default. If statement-based replication cannot be accurate, row-based replication is adopted.
Configuration Main points of the configuration are as follows:
Server_id =1 binlog_format=mixed # After n transaction commits, Mysql will perform a disk synchronization command of fsync. Flushes buffer data to disk. If # is 0, Mysql controls the frequency itself. If sync_binlog=n # is 0, the log buffer will be written to the log file and flushed to disk once per second. The # mysqld process crashes and loses all transactions for a second. If # is 1, the log buffer will write the log file and flush to disk each time. Only one transaction is lost in a crash. Innodb_flush_logs_at_trx_commit =0 innodb_flush_logs_at_trx_commit=0 Automatic replication after a crash may cause more problems. Skip_slave_start =1; skip_slave_start=1; skip_slave_start=1 Log_slave_update # Log deletion expiration time. If the delay is severe, log files will occupy disks expire_logs_days=7Copy the codeThe problem
Delay When the TPS concurrency of the master library is high, the slave database SQL may not be able to keep up with the master database because the master database is written by multiple threads while the slave DATABASE SQL thread is single-threaded.
Solutions:
Network: try to ensure the network stability between the master and slave libraries, the delay is small;
Hardware: configure better hardware from the library to improve the performance of random write;
Configuration: try to make MySQL operations in memory, reduce disk operations. Or upgrade MySQL5.7 to use parallel replication;
Construction: try to read and write to the master library in the transaction, other non-transaction read in the slave library. Eliminate database inconsistencies caused by partial latency. Increasing the cache reduces some of the load on the slave library.
Data loss When the primary database is down, data may be lost.
Solutions:
Semi-synchronous replication can solve the problem of data loss.
Note the following for MySQL:
MySQL master-slave replication is the foundation of MySQL’s high availability, high performance (load balancing);
Simple, flexible, and diversified deployment modes. You can deploy different replication structures in different service scenarios.
During the replication process, you should always monitor the replication status. Replication errors or delays may affect the system.
MySQL master/slave replication currently has some problems. You can deploy replication enhancements as required.
Master slave replication has many benefits. When our master server has problems, we can switch to the slave server. Read and write separation can be performed at the database level; Daily backups can be made on a slave database. We can also ensure that:
More secure data: data redundancy, not because of a single server downtime and data loss;
Greatly improved performance: one master, multiple slaves, different users read from different databases, improved performance;
Better scalability: When traffic increases, secondary servers can be added conveniently without affecting system use.
Load balancing: One master and multiple slave servers share host tasks and perform load balancing.
Usage Scenario The MySQL primary/secondary replication cluster function enables the MySQL database to support large-scale concurrent read/write operations and effectively protects data backup when physical servers are down.
Scaling out improves system performance by distributing the workload to Slave nodes.
In this scenario, all write and update operations are performed on the Master node; All read operations are performed on the Slave node. By adding more Slave nodes, the system reads faster.
Data security Data is replicated from the Master node to the Slave node, and the replication process can be suspended on the Slave node. Data corresponding to the Master node can be backed up on the Slave node without affecting the running of the Master node.
Data analysis Real-time data can be created on the Master node and analyzed on the Slave node without affecting the performance of the Master node.
Remote data distribution allows replication to create a copy of local data on a remote host without a persistent connection to the Master node.
Split access can split several different slave servers based on the business of the company. Splitting helps reduce the stress on the primary server and allows the database to be independent of external user browsing, internal user business processing, and DBA backup.