Use MySQL's built-in replication capabilities to optimize availability

MySQL internal replication function is established between two or more servers and is achieved by setting the master-slave relationship between them. One of them acts as the master server, and the other acts as the slave server. I will discuss in detail how to configure two servers, making one the master and the other a slave. And describe the process of switching between them. I performed the configuration setting process on MySQL version 3.23.23, and also conducted the test on this version. MySQL developers recommend that it is best to use the latest version, and that both master and slave servers use the same version. At the same time, MySQL version 3.23 is still a beta version, and this version may not be backward compatible. So for this reason, in actual websites, I don't use this version yet. One benefit of having fault tolerance is that the server can be upgraded without interrupting any queries.

Step 1: Configure the main server
For the rest of this article, I will specify two servers. A (IP is 10.1.1.1) serves as the main server (referred to as host). B (IP is 10.1.1.2) serves as the backup server (referred to as the backup server).

　The implementation process of MySQL's replication function is as follows: the standby machine (B) connects to the host machine (A), then reads the binary update log of the host machine, and then merges the changes into its own database. The standby machine needs a user account to connect to the host, so create an account on the host and give it only FILE permissions, as follows:

GRANT FILE ON *.* TO replicate@10.1.1.2 IDENTIFIED BY password;

　The standby machine can connect to the host machine, and FLUSH PRIVILEGES needs to be run on the host machine, but don't worry because we will stop the server in the next steps.

Now we need a snapshot of the host database and configure the host to allow binary update logs to be generated. First edit the my.cnf file to allow binary update logging, so somewhere below the [mysqld] section add a line: log-bin. The next time the server starts, the host will generate a binary update log (named: -bin.). In order to make the binary update log valid, shut down the MySQL service program, then move all database directories on the host to another directory, and then restart mysqld.
Please make sure you have obtained all databases, otherwise when copying, if a table exists on the main machine but does not exist on the standby machine, it will exit due to an error. Now you have a snapshot of your data, and a binary log of any changes to the database since the snapshot was taken. Please note that MySQL data files (*.MYD, *.MYI and *.frm) are file system dependent, so you cannot just do a file transfer, such as 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 a snapshot of the data.

Step 2: Configure the backup machine
　 Let’s continue. Stop the MySQL service program on the standby machine, and move the database directory copied from the host machine to the data directory on the standby machine. Please make sure to change the owner and group of the directory to the corresponding values ​​​​of the MySQL user, and modify the file mode to 660 (only readable and writable for the owner and group), and the directory itself to 770 (only for the owner and group) readable, writable and executable).

Continue. Start the MySQL service program on the backup machine and confirm that MySQL is working properly. Run a few select queries (do not update or insert queries) to see if the data snapshot obtained in the first step is successful. Then, shut down the MySQL service program after the test is successful.

Configure the host that needs to be accessed on the backup machine to receive changes from the host. Therefore, you need to edit the my.cnf file on the server machine and add the following lines in the [mysqld] section:

master-host=10.1.1.1
master-user=replicate
master-password=password

　Start the backup machine service After the program is started, the standby server service program will check the host specified in the my.cnf file to see if there are any changes, and merge these changes into its own database. The standby machine maintains the host's 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 an error occurs while processing the binary log on the standby machine, it will cause the standby machine thread to exit and generate a message in the *.err log file. The error can then be corrected and the standby thread can be restarted using the SQL statement SLAVE START. The thread will continue processing from where host binary log processing left off.

At this point, the data changes that occurred on the main machine should have been copied to the standby machine. To test it, you can insert or update a record on the main machine and select this record on the standby machine.

Now we have this master-slave relationship from machine A to machine B, which allows us to redirect all queries to machine B when machine A may crash, but when machine A recovers, We have no way to restore the changes to machine A. To solve this problem, we create a master-slave relationship from machine B to machine A.

Step 3: Create a mutual master-slave relationship
First, in the my.cnf file on machine B, add log-bin to the [mysqld] section, then restart mysqld, and then create a user account that can perform the copy function on it, use:

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 adding the replication user, then return to machine A and add the following to its my.cnf A few lines:

master-host=10.1.1.2
master-user=replicate
master-password=password

After restarting the service program of machine A, now we have the mutual communication between machine A and machine B. Master-slave relationship. No matter which server a record is updated or a record is inserted, it will be copied to the other server. Note: I'm not sure how quickly a standby machine can merge binary log changes, so using this method to load balance insert or update statements may not be a good idea.

Step 4: Modify your database connection program
Now that you have established a mutual relationship between machine A and machine B, you need to modify the database connection program to benefit from this approach. The following function first attempts to connect to machine A, and if the connection cannot be established, connects 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;
}

?>

I tested the database connection establishment process using the above technology in two situations. One is that the main MySQL service program is closed, but the server is still there. Running, another situation is that the main server is shut down. If only mysqld is shut down, the connection will be immediately transferred to the backup machine; but if the entire server is shut down, there will be an infinite wait (I gave up tracking after two minutes - a very short attention span) because PHP is looking for a server that does not exist. server. Unfortunately, unlike the fsockopen function, the mysql_connect function does not have a timeout parameter, however we can use fsockopen to simulate a timeout.

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 We provide an adjustable timeout feature, which is what the mysql_connect function lacks. If the connection fails immediately, which is the case if the machine is "alive" but mysqld is "down", the function is immediately moved to the second server. The above function is quite robust. Test it before attempting to connect to see if the service program is listening on the specified port. Let your script time out after an acceptable period of time, allowing you to handle error conditions appropriately. If you modify the default port 3306, please make sure to modify the port number.

Conclusions and comments
First, make sure you get a complete data snapshot. Forgetting to copy a table or database will cause the standby line program to stop. The time when the snapshot is generated is critical. You should ensure that the binary logging functionality is disabled before copying the data files. If the binary log function is enabled before taking the snapshot, the standby thread may stop because when the thread tries to import important records, it may stop due to duplicate primary keys. The best thing to do is to follow the solution discussed in part 2: close-copy-allow the binary log function to restart.

You may want to configure the replication process in the original way, and pay attention to the standby machine at the appropriate time to ensure that the standby machine is in sync with the master machine.

I have not tested the load balancing performance of a system that uses the replication feature, but I would flexibly use such a system to balance inserts and updates. For example, if two records on two servers give the same auto_increment value, which record will the standby thread stop on? A problem like this would have load balancing handled as read-only, with one server handling all inserts and updates, while a set of standbys (yes, you can have multiple standbys separate from the master) handle all selections .

　I am very happy that MySQL already has some functions of the replication system, and the configuration is very simple. Using this, you can begin to provide additional security against out-of-control events. I've only touched on the replication feature, which I've tested and used, but it's explained in more detail in Part 11 of the MySQL online documentation. .

The above is about using MySQL’s built-in replication function to optimize availability. If you want to get more content, please pay attention to the PHP Chinese website (www.php.cn)!