Get three benefits from the MySQL replication function

MySQL databases support single-item and asynchronous replication. During the replication process, one server acts as the master server, and the other server acts as the slave server. As shown in. The master server writes the update information to a specific binary file. An index of the file is maintained to track log loops. This log can be recorded and sent to the slave server for updates. When an slave server is connected to the master server, the slave server will notify the master server to read the last successful update location from the server's log file. Then, the slave server receives any updates from that time point, locks them, and waits until the master server notifies you of new updates.

This is the basic description of MySQL Server Database Replication principles. As a database administrator, you only need to have a few basic knowledge about this principle.

Benefits 1: Implementing serversServer Load balancer

Server replication allows you to achieve load balancing between the master server and slave server. That is, the customer query load can be split between the master server and slave server to get a better customer response time. Generally, the database administrator has two ideas.

First, only data update is performed on the master server. Including updating, deleting, and creating data records. The query job does not care about data. The Database Administrator forwards all data query requests to the slave server. This is useful in some applications. Such as some applications, such as websites that predict the net worth of funds. The updates of data are updated by the Administrator, that is, the number of updated users is relatively small. The number of users to query is very large. In this case, you can set up a master server for data updates. Multiple slave servers are configured to query user information. Data Update and query are performed on different servers, which improves data security, shortens application response time, and improves system performance.

The second is to split the query job on the master server and the slave server. In this way, the master server not only needs to update, delete, insert, and other data jobs, but also needs to undertake some query jobs. The slave server is only responsible for data query. When the master server is busy, some query requests are automatically sent to the slave server to reduce the workload of the master server. Of course, statements such as modifying data, inserting data, and deleting data are still sent to the master server for data synchronization between the master server and the slave server.

To achieve load balancing between databases, the key point is the data synchronization time. If the data between the master server and the slave server is updated for a long time, the data queried from the master server is different from the data obtained from the slave server. If the synchronization time is relatively short, such as synchronous replication, there are high requirements for network bandwidth, server devices, and so on.

It can be seen that the synchronization time is directly related to the effect of the application. So how much time should I choose for this synchronization? There is no fixed answer. It mainly depends on the user's needs. If you do not have high requirements on data timeliness or the frequency of data updates is not high, the synchronization time can be a little longer. However, if the data has high requirements on timeliness, such as the stock price, the data must be updated synchronously. Therefore, it depends on the actual application of the enterprise to determine the synchronization time.

When using this application, you must note that the replication of the MySQL database is unidirectional. That is, data can only be copied from the master server to the slave server, rather than from the slave server to the master server. This means that the database administrator cannot update data on the slave server. Otherwise, the data may conflict with the data on the master server. By default, the system automatically updates the data on the slave server using the data on the master server. That is, any changes made on the slave server will expire at that time. For user requests, do not worry. The system automatically determines whether a user's request is a query request or a data update request. And automatically forward the requests to different servers according to the request type. It is mainly for database administrators. Do not itch your hands and manually update the data on the slave server. Otherwise, data conflicts will occur between the slave server and the master server.

Benefits 2: Remote Data Backup through replication

As shown in, data can be regularly copied from the master server to the slave server, which is undoubtedly a remote backup of data. In the traditional backup system, data is backed up locally. The backup job runs on the same device as the database server. When the backup job runs, the normal operation of the server is affected. Sometimes it will significantly reduce the server performance. At the same time, it is not safe to store the backup data locally. If the hard disk is damaged due to voltage or other reasons, or the server is stolen, the database administrator cannot use the backup file to recover data because the backup file is still stored on the hard disk. This will obviously cause great losses to enterprises.

If you use replication to back up data, you can back up data on the slave server. In this case, the master server will not interfere with the normal operation of the primary service, but will continue to process related update jobs during the backup process. At the same time, data replication also achieves remote backup of data. Unless the two hard disks of the master server and slave server are damaged at the same time, the database administrator can restore data in the shortest time to reduce the losses caused by the enterprise.

When using this method for remote backup, pay attention to the following two details:

The first is the data update time. To back up data in this way, the Data Synchronization time must be less than or equal to the data backup cycle. Only in this way can the latest data be properly backed up. In this case, even if the primary server fails, the user only loses the data in the last backup cycle.

The second is the disk space on the server. According to the user's mentality, the configuration of the slave server is usually lower than that of the master server. But now the slave server has to undertake the data backup task, so the hard disk space requirement is higher than the master server. It not only needs to be able to store data on the master server, but also backs up the data. Therefore, it is generally required that the storage space on the slave server is twice that on the master server. Otherwise, the backup job may fail due to insufficient disk space.

Cost 3: Improving database system availability

The database replication function synchronizes data between the master server and slave server, increasing the availability of the database system. When a problem occurs on the master server, the database administrator can immediately use the slave server as the master server to update and query data. Then let's go back and carefully check the master server's problems. In this case, the database administrator usually uses two methods.

First, although the slave server replaces the master server after the master server fails, some restrictions are imposed on the operations that the master server can take. For example, you can still query data, but cannot update or delete data. This is mainly from the data security considerations. For example, during the upgrade of some banking systems, only the balance can be queried, but no money can be obtained. This is the same principle.

Second, it actually changes from a server to a master server. After the slave server is switched to the master server, its status is exactly the same as that of the original master server. You can query, update, and delete data. Therefore, we need to do a good job of data security. That is, the data security policy must be identical with the original master server. Otherwise, there may be security risks.