Mysql
Use the MySQL built-in replication feature to optimize availability (i)
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.
Original Author: Michael
Source: phpbuilder.com
