Test the replication feature of New MySQL features (1)

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. AIP is 10.1.1.1 ). BIP is 10.1.1.2 ).

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 <Host Name>-bin. <incremental sequence number #> ). 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.

Step 3: Create a master-slave relationship

First, my. in the cnf file, add log-bin to the [mysqld] section, restart mysqld, and create a user account on which the copy function can be executed. 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 the user is copied. 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 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.