Use the MySQL built-in replication function to optimize availability _ MySQL

With the built-in replication function of MySQL to optimize the availability of Soundbreak, we continuously play live audio and video 24 hours a day. Therefore, we cannot make convincing tests on the new replication features of MySQL. Through tests, we found that this feature can be used to maintain data synchronization with the backup database server, so that when the master server fails to process for some reason, it can use the backup machine to process all the queries.

It is not difficult to configure two servers. I will discuss in detail the entire process and how to use PHP to redirect queries when the master server fails.

The internal replication function of MySQL is implemented between two or more servers by setting the master-slave relationship between them. One of them serves as the master server and the other as the slave server. I will discuss in detail how to configure two servers, one as the master server and the other as the slave server. And describes the process of switching between them. I configured MySQL in MySQL 3.23.23 and tested it in MySQL. It is recommended that MySQL developers use the latest version and the master-slave servers use the same version. At the same time, MySQL 3.23 is still a beta version, and this version may not be backward compatible. For this reason, I have not used this version on my website. One advantage of fault tolerance is that the server is upgraded without interrupting any queries.

Step 1: configure the master server

In the rest of this article, I will designate two servers. A (IP address 10.1.1.1) serves as the master server (host address for short ). B (IP address 10.1.1.2) is used as the backup server (for short, the backup server ).

The replication function of MySQL is implemented by connecting the standby machine (B) to host (A), and then reading the binary update log of the host, then merge the changes into your own database. The standby machine requires a user account to connect to the host. therefore, create an account on the host and grant only the FILE permission to the account. perform the following operations:

Grant file on *. * TO replicate@10.1.1.2 identified by 'password ';

To connect the slave to the host, run 'flush privileges' on the host, but don't worry, because we will stop the server in the following steps.

Now we need a snapshot of the host database and configure the host to generate binary update logs. First, edit the 'My. cnf 'file to allow Binary updates. Therefore, add a line 'log-bin' somewhere below [mysqld '. When the next Server is started, the host generates a binary Update Log (named: <主机名> -Bin. <增量序号#> ). To make the binary update log valid, close the MySQL service program, then store all the database directories on the host to another directory, and restart mysqld.

Make sure all databases are obtained. Otherwise, if a table exists on the host but does not exist on the slave machine during replication, the table will exit due to an error. Now you have obtained a data snapshot and a binary log since the snapshot was created, which records any changes to the database. Note that MySQL data files (*. MYD, *. MYI and *. frm) depend on the file system. Therefore, you cannot transfer files only, for example, from Solaris to Linux. If you are in a heterogeneous server environment, you will have to use the mysqldump utility or other custom scripts to get data snapshots.

Step 2: configure the slave

Let's continue. Stop the MySQL service program on the slave machine and move the Database Directory copied from the master machine to the data directory on the slave machine. Make sure to change the directory owner and group to the MySQL User Value and Change the file mode to 660 (only readable and writable to the owner and group ), the bibliography is 770 (readable, writable, and executable only for the owner and group ).

Continue. Start the MySQL service program on the slave machine and confirm that MySQL works properly. Run several select queries (do not update or insert queries) to check whether the data snapshot obtained in step 1 is successful. Then, after the test is successful, turn off the MySQL service program.

Configure the host to be accessed on the slave machine to receive host changes. Therefore, you need to edit the 'My. cnf 'file on the server and add the following lines in the [mysqld] section:

Master-host = 10.1.1.1

Master-user = replicate

Master-password = password

After the backup server service program is started, the backup server service program is viewed in 'My. the host specified in the cnf file, check whether there are changes, and merge these changes into your own database. The slave server maintains the host update records, which are received from the host's 'Master. info' file. The STATUS of the standby thread can be seen through the SQL command 'show SLAVE-status. If

Errors will cause the standby thread to exit and generate a message in the *. err log file. Then the error can be corrected, and the SQL statement 'slave start' can be used to restart the backup thread. The thread will continue processing from where the binary log processing of the host is interrupted.

So far, the data changes on the host should have been copied to the slave machine. to test it, you can insert or update a record on the host, and select this record on the slave machine.

Now we have this master-slave relationship from server A to server B. This allows us to redirect all queries to server B when server A may become A machine, however, when server A recovers, we cannot recover the changes to server. To solve this problem, we create A master-slave relationship from Machine B to machine.

Step 3: Create a master-slave relationship

First, in the my. cnf file on Machine B, add 'log-bin' to the [mysqld] Section, restart mysqld, and create

The user account that executes the Copy function above it, using:

Grant file on *. * TO replicate@10.1.1.1 identified by 'password ';

Run the 'flush privileges' command on Machine B to load the new authorization table after the copied user is added, and then return to machine

The following lines are added to its 'My. cnf:

Master-host = 10.1.1.2

Master-user = replicate master-password = password

After restarting the Service Program of host A, we now have A master-slave relationship between host A and host B. No matter which server updates a record or inserts a record, it will be copied to another server. Note: I am not sure how fast a slave machine can merge binary logs. Therefore, using this method for load balancing of insert or update statements may not be a good solution.

Step 4: Modify Your Database Connection program

Since you have already established A relationship between Machine A and Machine B, you need to modify the Database Connection program to benefit from this method. The following function first tries to connect to machine A. If A connection cannot be established, it will connect to Machine B.

/Function db_connect ()

Returns a link identifier on success, or false on error

/Function db_connect () {$ username = "replUser"; $ password = "password"; $ primary = "10.1.1.1"; $ backup = "10.1.1.2 ";

# Attempt connection to primary if (! $ Link_id = @ mysql_connect ($ primary, $ username, $ password) # attempt connection to secondary $ link_id = @ mysql_connect ($ secondary, $ username, $ password) return $ link_id ;}

?>

In two cases, I tested the process of establishing a database connection using the above Technology. One is that the main MySQL service program is closed, but the server is still running, in another case, the master server is disabled. If mysqld is disabled, the connection is immediately switched to the standby server. However, if the entire server is closed, there was an infinite wait (two minutes later I gave up tracking-a short span of attention), because PHP was looking for a server that does not exist. Unfortunately, unlike the fsockopen function, the mysql_connect function does not have a timeout parameter. However, we can use fsockopen to simulate a timeout process.

Step 5: an improved Database Connection program

/Function db_connect_plus ()

Returns a link identifier on success, or false on error

/Function db_connect_plus () {$ username = "username"; $ password = "password"; $ primary = "10.1.1.1"; $ backup = "10.1.1.2"; $ timeout = 15; // timeout in seconds

If ($ fp = fsockopen ($ primary, 3306, & $ errno, & $ errstr, $ timeout) {fclose ($ fp); return $ link = mysql_connect ($ primary, $ username, $ password);} if ($ fp = fsockopen ($ secondary, 3306, & $ errno, & $ errstr, $ timeout) {fclose ($ fp ); return $ link = mysql_connect ($ secondary, $ username, $ password );}

Return 0 ;}

?>

This new improved function provides us with an adjustable timeout feature, which is exactly what mysql_connect function lacks. If the connection fails immediately, this is the case if the machine is "active", but mysqld "drops, the function is immediately moved to the second server. The above function is quite robust. test it before trying to connect and check whether the service program listens on the specified port so that your script times out after an acceptable period of time, allow you to handle errors as appropriate. If you have modified the default port 3306, make sure to modify the port number.

Conclusions and opinions

First, make sure you have obtained a complete data snapshot. If you forget to copy a table or database, the backup program will stop. The time when a snapshot is generated is critical. Make sure that the binary log function is invalid before copying data files. If the binary log function is allowed before the snapshot is obtained, the standby thread may stop because when the thread attempts to import important records, it may stop due to repeated primary keys. It is best to follow the solution discussed in the second part: disable-Copy-enable the binary log function to restart.

You may want to configure the replication process in the first way, and pay attention to the slave at the right time to ensure that the slave and the host are in the same step.

I have never tested the load balancing processing performance of a system that uses the replication feature, but I will use it flexibly to balance insertion and update. For example, if two records on both servers have the same auto_increment value, in which case will the backup thread stop? A problem like this will allow the server load balancer to be read-only. one server processes all the inserts and updates, and a group of slave servers (yes, you can have multiple slave servers separated from the host) to process all the options.

I am very happy that MySQL already has some features of the replication system, and the configuration is very simple. With this feature, you can begin to provide additional security measures for uncontrolled events. I only involved the replication feature. I have tested and used this feature, but I will provide more detailed instructions in section 11th of the MySQL online document.

As I used MySQL 3.22, I downloaded the latest version 3.23.24 program to test it. And because there is only one machine, I just added the binary log settings. However, as mentioned in this article, there is indeed a file generation. If you are interested in this, please test it on your own. In addition, in the latest MySQL User Manual, I found that this copy function was available only after version 3.23.15. check your MySQL version. In addition, the setting of binary logs is set in my. cnf. In my version 3.23.24, there are three files in the manual for parameter settings: my. ini file, c:/my. cnf and c:/mysql/data/my. cnf can be set. When setting 'log-bin' (you do not need to set the log parameter first), you can use the WinMySQLadmin software that comes with mysql to set it. the settings in ini are different from those in this article. please test them by yourself.