Shulou(Shulou.com)05/31 Report--

This article is about how to use MySQL built-in replication to optimize usability. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

The MySQL internal replication function is established between two or more servers and is achieved by setting a master-slave relationship between them. One of them is the master server and the other is the slave server. I will discuss in detail how to configure two servers, one as the master and the other as the slave. And describe the process of switching between them. I did the configuration process on version 3.23.23 of MySQL, and I also tested it on this version. MySQL developers recommend that it is best to use the latest version, and that both master and slave servers use the same version. At the same time, MySQL 3.23 is still a beta beta, and this version may not be backward compatible. So for this reason, in the actual website, I haven't used this version yet. One advantage of having fault tolerance is that the server is upgraded without interrupting any queries.

Step 1: configure the primary server

For the rest of this article, I will specify two servers. A (IP = 10.1.1.1) serves as the primary server (referred to as the host for short). B (IP is 10.1.1.2) serves as a backup server (referred to as standby for short).

The implementation process of the replication function of MySQL is as follows: the standby (B) connects with the host (A), then reads out the host's binary update log, and then merges the changes into its own database. The slave needs a user account to connect with the host, so create an account on the host and only give it FILE permission, as follows:

GRANT FILE ON *. * TO replicate@10.1.1.2 IDENTIFIED BY password

In order for the standby to be able to connect 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 allow binary update logs to be generated. Edit the my.cnf file first to allow binary updates to the log, so add a line under the [mysqld] section: log-bin. The next time the server starts, the host generates a binary update log (named:-bin.). To make the binary update log valid, close the MySQL service program, then put all the databases on the host into another directory, and then restart mysqld.

Make sure you have all the databases, otherwise when replicating, if a table exists on the host but does not exist on the standby, it will exit due to an error. Now you have a snapshot of the data and a binary log since the snapshot was created, which records any changes to the database. Please note that MySQL data files (* .MYD, * .MYI and * .frm) depend on the file system, so you can't just transfer files, 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 script to get a snapshot of the data.

Step 2: 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. Make sure that you change the owner and affiliate of the directory to the corresponding values for MySQL users, and change the file mode to 660 (readable and writable only to the owner and affiliate) and the directory itself to 770 (readable, writable and executable only to the owner and affiliate).

Go ahead. Start the MySQL service program on the standby and confirm that the MySQL is working properly. Run several select queries (no update or insert queries) to see if the snapshot of the data you got in the first step was successful. Next, turn off the MySQL service program after the test is successful.

Configure the host that needs to be accessed on the standby to receive changes from the host. So you need to edit the my.cnf file on the server, and add the following lines to 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 check the host specified in the my.cnf file to see if there are any changes, and merge these changes into its own database. The standby maintains the update records of the host, which are received from the master.info file of the host. The status of the standby thread can be seen through the sql command SHOW SLAVE-STATUS. In processing binary logs on the standby machine, if

If an error occurs, the standby thread will exit and a message will be generated in the log file of * .err. 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 where the host binary log processing is interrupted.

At this point, the data changes that have occurred 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.

Now we have this master-slave relationship from machine A to machine B, which allows us to redirect all queries to machine B when machine A may crash, but when machine An is restored, we have no way to restore the changes to machine A. To solve this problem, we create a master-slave relationship from machine B to machine A.

Step 3: 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, and then create a user account that can perform replication on it, 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 adding the replicated user, then go back to machine An 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 program of machine A, we now have a master-slave relationship between machine An and machine B. No matter which server you update a record or insert a record, it will be copied to another server. It's important to note that I'm not sure how fast a standby merges binary logs, so it may not be a good idea to load balance insert or update statements in this way.

Step 4: modify your database connection program

Now that you have established a mutual relationship between machine An and machine B, you need to modify the database connection program to benefit from this way. The following function first attempts to connect to machine A, or to machine B if a connection cannot be established.

/ *

Function db_connect ()

Returns a link identifier on success, or false on error

* * /

Function db_connect () {

  $username = "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

}

? >

I tested the database connection establishment process using the above techniques in two cases, one is that the main MySQL service program is closed, but the server is still running, and the other is that the main server is shut down. If only mysqld is off, the connection will immediately turn to the standby; but if the entire server is shut down, there will be an infinite wait (I give up tracking after two minutes-a short attention span) because PHP is looking for a server that doesn't exist. Unfortunately, unlike the fsockopen function, the mysql_connect function does not have a timeout argument, but we can use fsockopen to simulate a timeout handling.

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 () {

  $username = "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 what the mysql_connect function lacks. If the connection fails immediately, such as the machine is "alive", but the mysqld is "pawned", the function is immediately moved to the second server. The above function is quite robust. Test it before attempting to connect, see if the service is listening on the specified port, let your script time out after an acceptable period of time, and allow you to handle the error situation appropriately. If you change the default port 3306, be sure to change the port number.

Conclusions and opinions

First, make sure you have a complete snapshot of the data. Forgetting to copy a table or database will cause the standby line program to stop. The moment when taking a snapshot is critical. You should make sure that the binary logging function is invalid before copying the data file. If the binary logging feature is allowed before the snapshot is obtained, the standby thread may stop because when the thread tries to import an important record, it may stop due to duplicate primary keys. It is best to follow the approach discussed in part 2: turn off-copy-allow the binary logging function to restart.

You may want to configure the replication process in the initial way, and pay attention to the standby at the right time to make sure that the standby is synchronized with the host.

I have not tested the load balancing performance of a system that uses replication features, but I will flexibly use such a system to balance inserts and updates. For example, if both records give the same auto_ increment value on both servers, on which record will the standby thread stop? Problems like this will make load balancing read-only, with one server handling all inserts and updates, while a set of backups (yes, you can have multiple backups separate from the host) handles all choices.

I'm very happy that MySQL already has some of the features of a replication system, and it's easy to configure. With it, you can start providing additional security measures for events that get out of control. I only covered the replication feature, which I have tested and used, but is described in more detail in part 11 of MySQL's online documentation.

Thank you for reading! This is the end of the article on "how to use MySQL built-in replication to optimize usability". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.