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

At Soundbreak we play live audio and video 24 hours a day, so we cannot make a very convincing test of the new replication features of MySQL. Through testing, we found that we can use this feature to maintain data synchronization with the backup database server, so that when the main server fails for some reason, the backup machine can be used to process all queries. For such requirements, it is not difficult to configure two servers. I will discuss the entire process in detail, and also discuss how to use PHP to redirect queries when the main server fails.

The MySQL internal replication function is established between two or more servers and is implemented by setting the master-slave relationship between them. One of them serves as the master server, and the others serve as slave servers. I'll discuss specifically 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 and setup 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, I don't use this version yet on the actual website. 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 remainder 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 machine, so create an account on the host machine and give it only FILE permissions, as follows:

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

In order for the standby machine to connect to the main machine, FLUSH PRIVILEGES needs to be run on the main machine, but don't worry because we will stop the server in the next steps.

Now we need to take 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 add a line somewhere below the [mysqld] section: 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 effective, 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 the data, and a binary log recording any changes to the database since the snapshot was created. Please note that MySQL data files (*.MYD, *.MYI and *.frm) are file system dependent, so you cannot just transfer files, 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 us continue. Stop the MySQL service program on the standby machine, and move the database directory copied from the main 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 standby machine to receive changes from the host. 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 starting the standby service program, the standby 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 update records of the host machine. These records are received from the master.info file of the host machine. The status of the standby thread can be seen through the sql command SHOW SLAVE-STATUS. What if
is processed in the binary log on the standby machine?If an error occurs, it will cause the standby 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 this, 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, so that when machine A may crash, we are allowed to redirect all queries to machine B, but when machine A recovers, we do not have Method 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, add log-bin to the [mysqld] section in the my.cnf file on machine B, then restart mysqld, and then create a user account that can perform the replication 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 lines to its my.cnf:

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

After restarting the service program of machine A, now we have a mutual master-slave relationship between machine A and machine B. 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 fast 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. If the 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";

Use MySQL's built-in replication capabilities to optimize availability_PHP Tutorial