Use the MySQL built-in replication function to optimize the actual operation

The following articles mainly discuss the actual operation steps for optimizing the MySQL built-in replication function, including the configuration of the master server and the actual operation process for configuring the slave server, and create a master-slave relationship and other related content, the following is the main content description. MySQL internal replication function is built between two or more servers, through

The following articles mainly discuss the actual operation steps for optimizing the MySQL built-in replication function, including the configuration of the master server and the actual operation process for configuring the slave server, and create a master-slave relationship and other related content, the following is the main content description. MySQL internal replication function is built between two or more servers, through

The following articles mainly discuss the actual operation steps for optimizing the MySQL built-in replication function, including the configuration of the master server and the actual operation process for configuring the slave server, and create a master-slave relationship and other related content, the following is the main content description.

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.

The built-in replication function of MySQL is used to optimize availability. In Soundbreak, we continuously play live audio and video 24 hours a day. Therefore, we cannot make convincing tests on the new replication features 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. The following operations are performed: 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 'Log-bin' somewhere below [mysqld '. 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 internal replication function 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 is viewed in 'my. the host specified in the cnf file, check whether there are changes, and merge these changes into your own database. The slave server maintains the host 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 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 the SQL statement 'slave start' can be used to restart the backup thread. 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, in the my. cnf file on machine B, add 'Log-bin' to the [mysqld] section, restart mysqld, and create

The user account that performs the copy function above, using: 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 copied user is added, and then return to machine A, in its 'my. add the following lines to 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.

The above content is an introduction to the discussion of the MySQL built-in replication function to optimize availability. I hope you will gain some benefits.