From the Mysql copy function to get the three benefits analysis _mysql

In the MySQL database, single, asynchronous replication is supported. During the replication process, one server acts as the primary server , while the other server acts as a server. As shown in the following figure. The primary server writes the update information to a specific binary file. An index of the file is maintained to track the log loop. This log can be logged and sent to the update from the server. When a server connects to the primary server, the server notifies the primary to read the location of the last successful update from the server's log file. The server then receives any updates that occurred from that moment and then locks them up and waits until the primary server notifies the new updates.

This is the MySQL server database replication principle of the basic instructions. As a database administrator, there are only a few basic understanding of this principle.

498) this.width=498; "Border=0>

Benefits One: Implement server Load Balancing

The server replication feature enables load balancing between the primary server and the server. That is, you can get better customer time by splitting the load from the primary server and from the server to handle the customer query. Typically, there are two ways for a database administrator to do so.

The first is to implement only the update operation of the data on the primary server. Includes updates, deletions, new jobs, and so on for data records. The query job that does not care about the data. The database administrator forwards all query requests to the data from the server. This can be useful in some applications. such as certain applications, such as the net value of the fund forecast site. The update of its data is updated by an administrator, that is, the newer users are relatively few. The number of users queried will be very large. At this point, you can set up a primary server that is dedicated to updating the data. At the same time set up multiple from the server, used in charge of user information query. By placing data updates and queries on separate servers, you can improve the security of your data and shorten your application's response time and improve system performance.

The second is the job that splits the query from the server on the primary server. In this way, the main server not only to complete the data update, delete, insert and other operations, but also need to bear part of the query work. And from the server, only responsible for the data query. When the primary server is busy, some query requests are automatically sent to the server to reduce the workload on the primary server. Of course, statements like modifying data, inserting data, and deleting data are still sent to the primary server for synchronization between the primary server and the server data.

To achieve a balanced load between databases, the key point is the time of data synchronization. If the data from the primary server is updated longer than the server, the data queried from the primary server will differ from the data obtained from the server. And if the synchronization time is relatively short, such as the implementation of synchronous replication, network bandwidth, server equipment, etc. have higher requirements.

The time selection of this synchronization is directly related to the effect of the application. So how much of this synchronization time should be chosen? There is no fixed answer. Mainly to see the needs of users. If the user's timeliness of data is not very high, or the frequency of data updates is not very high, then the synchronization time can be a little bit longer. However, if the timeliness of the data requirements, such as stock prices, and so on, you need to be able to implement synchronous updates. Therefore, it depends on the actual application of the enterprise to decide what kind of synchronization time to use.

In taking this application, it is necessary to note that the MySQL database replication is one-way. That is, it is only possible to copy data from the primary server to the server, rather than from the server to the primary server. This means that the database administrator may not be able to update data from the server, otherwise it might conflict with data on the primary server. By default, the data on the server is updated automatically by the system from the primary server. That is, any changes made from the server will expire at the time. If the user's request, generally do not worry. The system automatically determines whether the user's request is a query request or a data update request. and is automatically forwarded to a different server based on the requested type. Primarily the database administrator, do not itch hand, manually to update data from the server. Otherwise, it can cause data conflicts between the server and the primary server.

Benefits Two: Remote backup of data by replication

As shown in the figure above, you can periodically copy data from the primary server to the server, which is undoubtedly the first offsite backup of the data. Under the traditional backup system, data is backed up locally. At this point, the backup job runs on the same device as the database server, and the backup job affects the normal operation of the server when it runs. Sometimes it can significantly degrade the performance of the server. At the same time, it is not safe to store backup data locally. If the hard drive is damaged due to voltage, or the server is stolen, the database administrator cannot use the backup file to recover the data because the backup file is still on the hard disk . This obviously will cause the enterprise to bring the relatively big loss.

If you use replication to make a backup of your data, you can back up your data from the server. Not only does this not interfere with the normal operation of the main service gas, but the primary server can continue to process the associated update job during the backup process. At the same time in the data replication, but also to achieve the data off-site backup. Unless the primary server and two hard drives from the server are damaged at the same time, the database administrator will be able to recover the data in the shortest possible period to reduce the loss caused by the enterprise.

When making offsite backups in this way, you need to focus on the following two details:

One is the time of the data update. If you want to do this in this way, the time to synchronize the data is less than the cycle of the data backup. This is the only way to ensure that the most recently updated data is backed up correctly. At this point, even if the primary server fails, the user loses only the data for the most recent backup cycle.

The second is the space of the hard disk from the server. According to the general user's mentality, the configuration from the server is often lower than the primary server. But now from the server to assume the task of data backup, so the space requirements for the hard disk is higher than the primary server. Not only should it be able to store data on the primary server, but it should also be backed up. For this general requirement, the storage space from the server is twice times the size of the primary server. Otherwise, it is easy to have insufficient disk space and cause the backup job to fail.

Benefits Three: Improve the availability of the database system

The database replication function realizes the synchronization of the data between the primary server and the server, and increases the availability of the database system. When there is a problem with the primary server, the database administrator can immediately make the server as the primary server for data update and query services. Then go back and double-check the problem with the primary server. At this point, the general database administrator will also use two methods.

First, after a primary server failure, although the location of the primary server was replaced from the server, there are still some restrictions on what can be done by the primary server. If still only can carry on the data the query, but cannot carry on the data the update, the deletion and so on operation. This is mainly from the data security considerations. As now some of the banking system upgrades, in the process of upgrading, can only check the balance and not be able to withdraw money. This is the same truth.

Second, from the server to really become the primary server. When you switch from the server to the primary server, the status is exactly the same as the original primary server. At this time can be implemented to the data query, update, delete and other operations. Therefore, we need to do a good job of data security. The data security policy is exactly the same as the original primary server. Otherwise, it may leave a certain security risks.

Original link: http://publish.itpub.net/a2011/0303/1162/000001162464.shtml