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

This article introduces the knowledge of "how to use MySQL built-in replication to optimize usability". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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.

How to use MySQL built-in replication to optimize availability

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 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. First edit the 'my.cnf' file to allow binary updates to the log, so add a line somewhere below 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 look at 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 host's' master.info' file. 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.

How to use MySQL built-in replication to optimize availability

Step 3: create a master-slave relationship with each other

First, in the my.cnf file on machine B, add 'log-bin',' to the [mysqld] section, then restart mysqld, and then create a file that can be used in the

Its user account that performs the copy function above, 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, and then go back to machine A, in

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 A machine and B machine, 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 in step with the host.

That's all for "how to optimize usability with MySQL built-in replication". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.