In Soundbreak we play live audio and video 24 hours a day without interruption, so we can't make a very convincing test of MySQL's new copy feature. 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 this 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 function is built between two or more servers and is implemented by setting the master-slave relationship between them. One of them acts as the primary server and the other as the slave server. I'll discuss specifically how to configure two servers, one set as the primary and the other from the server. and describe the process of switching between them. I am in the 3.23.23 version of MySQL configuration setup process, and also in this version of the test. MySQL developers recommend that you use the latest version, and that the master-slave server uses the same version. MySQL version 3.23 is still beta, and this version may not be backwards compatible. So for this reason, in the actual website, I haven't used this version yet. One of the benefits of having fault tolerance is that you upgrade your server without interrupting any queries.
First step: Configure the master server
In the remainder of this article, I'll 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 a backup server (for short, standby).
The implementation of MySQL's replication function is: Standby (B) connect with host (A), then read out the host's binary update log, and then merge the changes that occurred into the database itself. The standby machine needs a user account to connect with the host, so create an account on the host and give it file permissions, as follows:
GRANT FILE on *. replicate@10.1.1.2 identified by password;
In order to be able to connect with the host computer, to run flush privileges on the host, but do not 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 a binary update log. First edit the My.cnf file to allow the binary update log, so add one line to the following side of the [mysqld] section: Log-bin. On the next server startup, the host will generate a binary update log (named: <主机名> -bin. <增量序号#> )。 In order for the binary update log to be valid, close the MySQL service program, then all the database directories on the host to another directory, and then restart Mysqld.
Make sure you have all the databases, otherwise, if a table exists on the host but does not exist on the standby at the time of replication, it will exit because of an error. Now that you have a snapshot of the data and a binary log from the snapshot, any modifications to the database are recorded. Please pay attention to MySQL data file (*. myd,*. Myi and *.FRM) are dependent on the file system, so you can't just file transfers, 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 copied from the host to the data directory on the standby machine. Please make sure to change the owner and the group of the directory to the corresponding value of the MySQL user, and modify the file mode to 660 (only the owner and the group is readable, writable), the directory itself is 770 (only for the owner and the group is readable, writable and executable).
Go on. Start the MySQL service on the standby and verify that MySQL is working properly. Run several select queries (do not update or insert queries) to see if the data snapshot you obtained in the first step is successful. Next, turn off the MySQL service program after the test is successful.
Configure the host to be accessed on the standby to receive changes to the host. So you need to edit the my.cnf file on the machine and add the following lines in the [mysqld] section:
master-host=10.1.1.1
Master-user=replicate
Master-password=password
After starting the standby service program, the standby service program will look at the host specified in the my.cnf file to see if there are any changes and merge those changes into its own database. The standby machine keeps the host's update record, which is received from the host's Master.info file. The standby thread status can be seen through the SQL command show Slave-status. In the binary log processing on the standby machine, if
An error occurs that causes the standby thread to exit and generates a message in the log file of the *.err. The error can then be corrected, and the standby thread can then be restarted using the SQL statement slave start. The thread will continue processing from the side of the host binary log processing interrupt.
At this point, the data changes on the host should have been copied to the standby, to test it, you can insert or update a record on the host, and select this record on the standby machine.
Now we have the master-slave relationship from A to B, so that when a machine is possible, we are promised to redirect all queries to the B machine, but when the a machine recovers, we have no way to restore the changes to the a machine. To solve this problem, we create a master-slave relationship from Machine B to machine A.
Step three: Create a master-slave relationship with each other
First, in the my.cnf file on machine B, add Log-bin in the [mysqld] section, then restart Mysqld, then create a user account that can perform the copy function on top of it, using:
GRANT FILE on *. replicate@10.1.1.1 identified by password;
Run the flush Privileges command on the B machine to mount the new authorization form after the copy user is added, then go back to the a machine 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 of a machine, we now have the mutual master-slave relationship between A and B machines. Any update of a record or the insertion of a record on any server will be replicated to the other server. It's important to note that I'm not sure how fast a standby merge binary log changes, so it might not be a good idea to use this method to load balance an INSERT or UPDATE statement.
Fourth step: Modify your database connection program
Now that you have established a relationship between A and B machines, you need to modify the database connection program to get the benefit from this approach. The following function first attempts to connect with a machine, if it is not possible to establish a connection with the B machine.
/********************************************************
function Db_connect ()
Returns a link identifier on success, or false On error
********************************************************/
function Db_connect () {
$username = "Repluser";
$password = "password";
http://www.bkjia.com/PHPjc/630908.html www.bkjia.com true http://www.bkjia.com/PHPjc/630908.html techarticle in Soundbreak we play live audio and video 24 hours a day without interruption, so we can't make a very convincing test of MySQL's new copy feature. By testing US ...
