In SoundBreak, we will play live audio and video 24 hours a day, so we can't make a very convincing test for MySQL's new replication feature. By testing us discover, this feature can be used to keep data synchronization with the backup database server, so that the primary server can use the backup machine to process all queries when handling the failure of some reason. For such requirements, configuring two servers is not difficult. I will discuss the entire process in detail, and I will discuss how to use PHP to redirect queries when the primary server is invalid.
The MySQL internal replication function is to be built between two or more servers, which is implemented by setting the main-rope relationship between them. One of them as a primary server, other as a slave server. I will discuss how to configure two servers, set one as the primary server, and the other is set from the server. And describe the processing procedure for switching between them. I am a configuration setting process on my mysql's 3.23.23, and is also a test on this version. MySQL developers recommend best to use the latest version and the same version is used from the server. At the same time, MySQL 3.23 is still a beta beta, and this version may not be compatible down. So because this reason, in the actual website, I have not used this version yet. Has a fault-tolerant ability has a benefit that the server is upgraded without interrupting any queries.
Step 1: Configure the primary server
In the remaining space left in this article, I will specify two servers. A (IP 10.1.1.1) is the primary server (referred to as host). B (IP 10.1.1.2) as a backup server (referred to as a standby).
The implementation process of mysql replication is: standby (b) and host (a), then read the binary update log of the host, and incorporate the changes in the own database. The spare machine requires a user account to connect to the host, so create an account on the host and only give it File permissions, as follows:
Grant File on *. * To replicate@10.1.1.1.2 identified by password;
In order to stand up 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, allowing the binary update log. First edit the My.cnf file so that binary update logs are allowed, so add a line in the next place in the [MySQLD] section: log-bin. When the next server is started, the host will generate a binary update log (name:
Please make sure all the databases are obtained, otherwise, if a table exists on the host, it does not exit on the standby machine. Now you have got a snapshot of the data, and a binary log since the snapshot, which records any changes to the database. Note that mysql data file (* .myd, *. Myi and * .frm) are dependent on the file system, so you can't transfer file transfer, such as from Solaris to Linux. If you are in a heterogeneous server environment, you will have to get a data snapshot using a MySQLDUMP utility or other custom ban.
Step 2: Configure the standby machine
let's continue. Stop the MySQL service program on the spare machine and move the database catalog of copied from the host to the Data directory on the standby. Please confirm that change the owner of the directory to the corresponding value of MySQL users, and modify the file mode is 660 (only to the owner and group readable, writable), the directory itself is 770 (only the owner and group Readable, writable and executable). carry on. Start the MySQL service program on the spare machine to confirm that mysql works fine. Run a few SELECT queries (do not update or insert query), see if the data snapshot obtained in the first step is successful. Then, turn off the MySQL service program after the test is successful.
Configure the host that needs to be accessed on the standby to receive the host changes. So you need to edit the My.cnf file on the machine, add the following lines in the [MySQLD] section:
Master-host = 10.1.1.1
Master-user = replicate
Master-password = password
After starting the standby server, the standby service program will view the host specified in the My.cnf file to see if there is change and these changes are merged into your own database. The spare machine maintains a host's update record, which is received from the master.info file of the host. The status of the spare machine thread can be seen from the SQL command show slave-status. Processing the binary log on the standby
An error will cause an exit of the standby thread and generate a message in the * .rr log file. Then the error can be corrected, then you can use the SQL statement slave start to restart the standby thread. The thread will continue processing from the host binary log processing.
At this point, the data change that occurs on the host should have been copied to the spare machine. To test it, you can insert or update a record on the host, and select this record on the standby.
Now we have this master from the A-machine to the B machine, which is just when the A machine may be machine, allow us to redirect all the queries to the B machine, but when the A machine is recovered, we don't The way will return to the change to the A machine. In order to solve this problem, we create the main-dependency from the B-machine to the A-machine.
Step 3: Create mutual master relationship
First, in the My.cnf file on the B machine, add log-bin in the [MySQLD] section, then restart MySQLD, then create a user account that can perform the copy function above, use:
Grant File on *. * To replicate@10.1.1.1.11.1 iDentified by password;
Run the Flush Privileg command on the B machine to load the new authorization form after the addition of the replicate user, then return to the plane, add the following lines in its my.cnf:
Master-host = 10.1.1.2
Master-user = replicate
Master-password = password
After restarting the A-machine service, we now have the relationship between the mutualism between the A-machine and B-machine. No matter which server updates a record or inserts a record, it will be copied to another server. It should be noted that: I don't dare to determine how fast a spare machine combined binary log change, so use this method to perform the load balancing of the insertion or update statement may not be a good way.
Step 4: Modify your database connection programs
Since you have established a mutual relationship between A and B-machines, you need to modify the database connection program to get the benefit from this way. The following function is first tried to connect to the A-machine, and if the connection cannot be established, it is connected to the B-machine.
/ ************************************************** *******
Function DB_Connect () Returns a Link Identifier on success, or false on error
*********************************************************** ****** /
Function DB_Connect () {
$ usrname = "repluser";
$ Password = "password";
$ primary = "10.1.1.1";
$ BACKUP = "10.1.1.2";
# Attempt Connection To Primary
IF ($ link_id = @MYSQL_CONNECT ($ PRIMARY, $ UserName, $ Password)
# Attempt Connection to Secondary
$ link_id = @MYSQL_CONNECT ($ Secondary, $ Username, $ Password)
Return $ link_id;
}
?>
In both cases, I tested the database connection establishment process used above the above technology. One is the main MySQL service program is turned off, but the server is still running, and the other is that the primary server is turned off. If just mysqld is closed, the connection will turn to the standby machine; but if the entire server is turned off, there is an unlimited waiting (I gave up the tracking - very short point of time after two minutes), because PHP is looking for a do not exist Server. Unfortunately, it is not like the fsockopen function, the mysql_connect function does not have a timeout parameter, however we can use FsockOpen to simulate a timeout process.
Step 5: An improved database connection program
/ ************************************************** *******
Function DB_Connect_plus ()
Returns a Link Identifier on Success, or false on error
*********************************************************** ****** /
Function DB_CONNECT_PLUS () {
$ usrname = "username";
$ Password = "password";
$ primary = "10.1.1.1";
$ BACKUP = "10.1.1.2";
$ TIMEOUT = 15; // Timeout in Seconds
IF ($ fp = fsockopen ($ PRIMARY, 3306, & $ Errno, & $ Errstr, $ TIMEOUT) {
Fclose ($ fp);
Return $ link = mysql_connect ($ PRIMARY, $ Username, $ Password);
}
IF ($ fp = fsockopen ($ SECONDARY, 3306, & $ Errno, & $ Errstr, $ TIMEOUT) {
Fclose ($ fp);
Return $ link = mysql_connect ($ Secondary, $ UserName, $ Password);
}
Return 0;
}
?>
This newly improved function provides us with an adjustable timeout feature, which is missing in the mysql_connect function. If the connection fails immediately, this situation is as "live", but mysqld "When", the function moves to the second server immediately. The above functions are quite robust, before testing, see if the service program is listening to the specified port, allowing your script to time out after a acceptable time period, allowing you to properly process the error. If you modify the default port 3306, please make sure the port number is modified. Conclusion and views
First, we must determine a complete data snapshot. If you forget to copy a table or a database will cause the spare machine to stop. It is very important to generate snapshots. You should make sure that the binary log function is invalid before copying the data file. If the binary log function is allowed before getting the snapshot, the spare machine thread may stop because it may stop when the thread attempts to import important records. It is best to follow the process discussed in the second part to do: Close - Copy - Allows the binary log function to restart.
You may want to formulate replication processing in the initial way, and pay attention to the spare machine at a suitable time, make sure the standby is synchronized with the host.
I have not tested a system for system using replication features, but I will flexibly use this system to balance insert and updates. For example, if both records on both servers give the same auto_increment value, which record is stopped on which one record is on? As such a problem, the load balance is used as a read-only process, and a server processes all inserts and updates, and a set of standby (yes, you can have multiple spare machines separated by host) Processing all the options .
I am very happy, MySQL already has some functions of the replication system, and the configuration is simple. With it, you can start providing additional security for outcoming events. I only involve replication characteristics, this I have tested and used, but in a more detailed description in Part 11 in MySQL's online documentation.
-------------------------------------------------- ------------------------------
The translator's words:
Since I originally used the version 3.22 MySQL, I had to download the latest programs of version 3.23.24. And because there is only one machine, I just increase the settings of the binary log. However, as this is said, there is indeed documentation. If you are interested in this, please test it. In addition, in the latest MySQL manual, I found that this replication function is only after the 3.23.15 version, please check the version of MySQL. At the same time, the setting of the binary log in the text is to set it in my.cnf. In the 3.23.24 version I am using, the manual says that there can be three files for parameter settings, which can be set in the My.ini file under the Windows directory, c: my.cnf and c: mysqldatamy.cnf can be set. When I set up log-bin (no need to set up a log parameter first) is the use of mysql
Winmysqladmin software is set, and in My.ini, it is different from the text, please test it yourself.
Source: phpbuilder.com
From: Very space
