Use the MySQL built-in replication function to optimize availability (1) _ MySQL

Limodou in Soundbreak we play live audio and video 24 hours a day, so we cannot make a convincing test of the new replication feature of MySQL. Through the test, we found that we can use this feature to maintain data synchronization with the backup database server. in this way, when the master server is a source author: Michael Tanoviceanu

In Soundbreak, we play live audio and video continuously 24 hours a day, so for the new replication feature of MySQL, we
Cannot make a convincing test. Through tests, we found that this feature can be used to maintain data synchronization with the backup database server,
In this way, when the primary server fails to process for some reason, it can use the backup machine to process all the queries. For such requirements, configure two servers
The server is not difficult. 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. Its
One is the master server, and the other is the slave server. I will discuss in detail how to configure two servers, set one as the master server and the other
Set one as the slave server. And describes the process of switching between them. I configured it on MySQL 3.23.23.
The configuration process is also tested in this version. MySQL developers are advised to use the latest version and master-slave server
Use the same version. At the same time, MySQL 3.23 is still a beta version, and this version may not be backward compatible. So this
Because I have not used this version on my website. One advantage of fault tolerance is that you do not need to interrupt any queries.
Upgrade the server.

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) 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), reading the binary update log of the host, and then
Changes are merged into your own database. The standby machine requires a user account to connect to the host. therefore, create an account on the host and only
The FILE permission is as follows:

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 step.

Now we need a snapshot of the host database and configure the host to generate binary update logs. Edit
'My. cnf 'file to allow Binary updates, so add a line below [mysqld]: 'Log-bin '. In
When the next Server is started, the host generates a binary update log named If the log is valid, close the MySQL service program, then store all the database directories on the host to another directory, and restart mysqld.
Make sure you have obtained all the databases. Otherwise, if a table exists on the host but does not exist on the slave machine during replication, the error will occur.
Exit. Now you have obtained a data snapshot and a binary log since the snapshot was created, which records any modifications to the database.
Change. Note that MySQL data files (*. MYD, *. MYI, and *. frm) depend on the file system.
From Solaris to Linux. If you are in a heterogeneous server environment, you will have to use the mysqldump utility or other custom
To get a data snapshot.

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 slave machine to the data directory on the slave machine.
Directory. Make sure to change the directory owner and group to the MySQL User Value and Change the file mode to 660 (only for the owner and group ).
Readable and writable). The directory is 770 (only readable, writable, and executable to the owner and group ).

Continue. In the Fuxi ySQL service program, check that MySQL works properly. Run several select queries (do not update or insert
Query) 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, in [mysqld]
Add the following lines:

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 and check whether the host has changed.
Merge these changes into your own database. The slave keeps the update records of the host. these records are stored in the 'master. info' file of the host.
Received. The STATUS of the standby thread can be seen through the SQL command 'show SLAVE-status. If
Errors will cause the standby thread to exit and generate a message in the *. err log file. Then the error can be corrected.
You can use the SQL statement 'slave start' to restart the standby 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 it on the host.
And select this record on the slave machine.

Now we have the master-slave relationship from host A to host B, so that when host A may be A machine, we can repeat all the queries.
To Machine B, but when machine A recovers, we cannot recover the change to machine. To solve this problem, we create
Master-slave relationship from Machine B to machine.

From: PHPBuilder.com