Optimize availability with MySQL built-in replication capabilities

In Soundbreak we play live audio and video 24 hours a day without interruption, so we can't make a convincing test of MySQL's new replication features. By testing, we found that this feature can be used to keep data synchronized with the backup database server, so that when the primary server fails for some reason, it can use the backup machine to process all queries. For such a requirement, it is not difficult to configure two servers. I'll discuss the entire process in detail and discuss how to use PHP to redirect queries when the primary server fails.

The MySQL internal replication feature is built between two or more servers and is implemented by setting up the master-relationship between them. One of them as the primary server, the other as from the server. I'll discuss in detail how to configure two servers, one as the primary and the other from the server. and describe the process of switching between them. I was in the MySQL 3.23.23 version of the configuration setup process, and also in this version of the test. MySQL developers recommend that you use the latest version, and that the primary-from server uses the same version. Meanwhile, the MySQL 3.23 version is still beta, and this version may not be backward compatible. So for this reason, I'm not using this version in the actual website. The advantage of having fault tolerance is that you can upgrade the server without interrupting any queries.

First step: Configure the primary server

In the remainder of this article, I will specify two servers. A (IP is 10.1.1.1) as the primary server (referred to as the host). B (IP is 10.1.1.2) as the backup server (for short, standby).

The implementation of the MySQL replication function is: Standby (B) and host (A) connection, and then read out of the host binary update log, and then merge the changes occurred in the database. Standby requires a user account to connect with the host, so create an account on the host, and give it only file permissions, the following operations:

GRANT FILE on *.* to replicate@10.1.1.2 identified by ' password ';

To keep the machine connected to the host, run ' FLUSH privileges ' on the host, but don't worry, because we'll stop the server in the next step.

Now we need a snapshot of the host database and configure the host to allow the binary update log to be generated. First edit the ' my.cnf ' file to allow the binary update log, so add one line below the [mysqld] section: ' Log-bin '. The next time the server starts, the host generates a binary update log (named:< hostname >-bin.< Delta ordinal #>). To make the binary update log valid, close the MySQL service program, and then all the database directories on the host to another directory, and then restart Mysqld.

Make sure you get all the databases, otherwise, if a table exists on the host but does not exist on the standby when replication occurs, it will exit because of an error. Now you have a snapshot of the data, and a binary log from the time the snapshot was created, which records any changes to the database. Please note the MySQL data file (*. myd,*. Myi and *.FRM) are dependent on the file system, so you can't just 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 Two: Configure the standby machine

Let's move on. Stop the MySQL service program on the standby and move the database directory from the host to the data directory on the standby machine. Please make sure that the owner and group of the directory are changed to the corresponding value of the MySQL user, and that the modified file mode is 660 (for both the owner and the group of readable, writable), and the directory itself is 770 (for the owner and the group of groups that are readable, writable, and executable).

Go on. Start the MySQL service on standby to confirm that MySQL is working properly. Run several select queries (no update or insert query) to see if the snapshot of the data you obtained in the first step was successful. Then, after the test is successful, turn off the MySQL service program.

Configure the host that needs to be accessed on the standby to receive changes to the host. So you need the ' my.cnf ' file on the edit machine and add the following lines in the [mysqld] section:

master-host=10.1.1.1

Master-user=replicate

Master-password=password

After the standby service program is started, the standby service program looks at the host specified in the ' my.cnf ' file to see if there are any changes and merges the changes into the database. The standby maintains an updated record of the host, which is received from the host's ' Master.info ' file. The status of the standby thread can be seen through the SQL command ' show Slave-status '. Processing binary logs on a standby if

An error occurs, which causes the standby thread to exit and generates a message in the *.err log file. The error can then be corrected, and then the standby thread can be restarted using the SQL statement ' SLAVE start '. The thread will continue processing from the place where the host binary log processing is interrupted.

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

Now we have this master-from-from-machine to B-relationship, this allows us to redirect all queries to the B machine when a machine is possible, but when a machine is restored, we have no way to restore the change to the a machine. To solve this problem, we create the master-from relationship from B to machine A.

Step three: Create a master-slave relationship with each other

First, in the my.cnf file on B-machine, add ' log-bin ' in the [mysqld] section, then restart Mysqld, and then create an

It performs the replication function on the user account, using:

GRANT FILE on *.* to replicate@10.1.1.1 identified by ' password ';

Run the ' FLUSH Privileges ' command on the B-machine to mount the new authorization table after joining the replicated user, then back to machine A,

Its ' my.cnf ' adds the following lines:

master-host=10.1.1.2

Master-user=replicate Master-password=password

After restarting the service program of a machine, we now have the mutual master-from relationship between a machine and B machine. No matter which server updates a record or inserts a record, it is copied to another server. Note: I'm not sure how fast a standby can merge binary logs, so it might not be a good idea to insert or update a statement's load balancing in this way.

Step Fourth: Modify your database connection program

Now that you have established a relationship between A and B, you need to modify the database connection program to benefit from this approach. The following function first attempts to connect to a machine, if the connection is not established, it is connected to the B machine.

/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 Sec Ondary $link _id = @mysql_connect ($secondary, $username, $password) return $link _id; }

>

In both cases, I tested the process of establishing a database connection using the above technology, one of which was that the main MySQL service was shut down, but the server was still running, and the main server was down. If the mysqld is closed, the connection will immediately turn to standby, but if the entire server is shut down, there is an infinite wait (two minutes later I give up the trace-a short notice span), because PHP is looking for a non-existent server. Unfortunately, unlike the Fsockopen function, the mysql_connect function does not have a time-out parameter, but we can use Fsockopen to simulate a timeout process.

Fifth step: 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 ($prim ary, $username, $password); } if ($fp = Fsockopen ($secondary, 3306, & $errno, & $errstr, $timeout)) {fclose ($fp); return $link = mysql_connect ($s Econdary, $username, $password); }

return 0; }

>

This new improved function provides us with an adjustable timeout feature that is missing from the mysql_connect function. If the connection fails immediately, such as the Machine "live", but Mysqld "when" dropped, the function immediately moved to the second server. The above function is quite robust, and before attempting to connect, test to see if the service program listens on the specified port, allowing your script to timeout after an acceptable period of time, allowing you to properly handle the error situation. If you have modified the default port 3306, make sure that you make changes to port number.

Conclusions and observations

First, be sure to get a complete snapshot of the data. If you forget to copy a table or database, you will cause the standby line program to stop. The time to generate snapshots is critical. You should make sure that the binary log function is not valid until you copy the data file. If binary logging is allowed before the snapshot is taken, the standby thread may stop because the thread may stop because of a duplicate primary key when it attempts to import important records. It's best to take the approach discussed in Part two: Close-copy-allows the binary log to reboot.

You may want to configure the replication process in the first way, and focus on the standby at the right time to ensure that the standby is synchronized with the host.

I have not tested the load balancing processing performance of a system that uses a replication feature, but I am flexible in using such systems to balance inserts and updates. For example, if the same auto_increment value is given for two records on both servers, which record will the standby thread stop on? Problems like this will allow load balancing as read-only processing, one server handling all inserts and updates, and a set of spares (yes, You can have multiple standby machines separated from the host to handle all the options.

I am very happy that MySQL already has some features of the replication system, and the configuration is simple. Using it, you can start to provide additional security for runaway events. I've only covered the copy feature, which I've tested and used, but in more detail in part 11th of the MySQL online documentation.

Translator's words: Because I used to use the 3.22 version of MySQL, so in order to test I had to download the 3.23.24 version of the latest program. And because there is only one machine, I just added the binary log settings. However, as this article says, there is indeed a file generation. If you are interested in this, you have to test yourself. In addition, in the latest MySQL manual, I found that the copy function is in the 3.23.15 version, please check your own version of MySQL. At the same time, the text about the binary log setting is said to be set in the MY.CNF. In the 3.23.24 version I used, the manual says that there can be three files for parameter settings, respectively, for My.ini files in the Windows directory, C:MY.CNF and C:MYSQLDATAMY.CNF. I am setting ' log-bin ' (do not need to set log parameters first) is the use of MySQL with the Winmysqladmin software set up, and in the My.ini set, and the text is different, please test yourself.

Optimize availability with MySQL built-in replication capabilities