Test the new replication feature of MySQL

In Soundbreak, we play live audio and video continuously 24 hours a day, so we cannot make a convincing test of the new replication feature 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 be processed by the backup machine.

In Soundbreak, we play live audio and video continuously 24 hours a day, so we cannot make a convincing test of the new replication feature 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 be processed by the backup machine.

In Soundbreak, we play live audio and video continuously 24 hours a day, so we cannot make a convincing test of the new replication feature 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 below [mysqld]: log-bin. 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 will view the host specified in the my. cnf file, check whether there are changes, and merge these changes into your own database. The slave keeps the host update records, which are received from the master.info file of the host. The STATUS of the standby thread can be seen through the SQL command SHOW SLAVE-STATUS. If an error occurs when processing binary logs on the slave machine, the slave machine thread exits and a message is generated in the *. err log file. Then the error can be corrected, and then the SLAVE thread can be restarted using the SQL statement SLAVE START. 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.