FAQs about MySQL synchronization (with caution) and FAQs about mysql

FAQs about MySQL synchronization (with caution) and FAQs about mysql

I just registered a blog park a few days ago. I want to write some technical tutorials. Today I will share with you a faq about MySQL synchronization.

Q: If the master server is running and you do not want to stop it, how can I configure an slave server?

A: There are multiple methods. If you have backed up the MASTER server at a certain time point and recorded the binary log name and offset of the corresponding snapshot (output using the show master status Command), follow these steps:

1. Make sure that a unique server ID is assigned to the slave server.

2. Execute the following statement on the slave server and fill in the appropriate values for each option:
Mysql> CHANGE MASTER
-> MASTER_HOST = 'master _ host_name ',
-> MASTER_USER = 'master _ user_name ',
-> MASTER_PASSWORD = 'master _ pass ',
-> MASTER_LOG_FILE = 'recorded _ log_file_name ',
-> MASTER_LOG_POS = recorded_log_position;

3. Execute the start slave Statement on the SLAVE server.

If you have not backed up the master server, here is a fast program for creating backups. All steps should be performed on the master server host.

1. Issue the statement:
Mysql> flush tables with read lock;

2. Execute this command (or its variant) when the lock is still applied ):
Shell> tar zcf/tmp/backup.tar.gz/var/lib/mysql

3. Issue the statement and ensure that the output used later is recorded:
Mysql> show master status;

4. Release the lock:
Mysql> unlock tables;

An optional method is to dump the master server's SQL statement instead of the binary copy in the previous step. To do this, you can use mysqldump -- master-data on the master server and load the SQL to your slave server. However, this is slower than binary replication.

No matter which one of the two methods you use, when you have a snapshot and record the log name and offset, then follow the instructions. You can use the same snapshot to create multiple slave servers. Once you have a snapshot of the master server, you can wait to create an slave server, as long as the binary log of the master server is complete. The actual limits on the two waiting times refer to the available hard disk space for storing binary logs on the master server and the time used for synchronization from the slave server.

You can also use load data from master. This is a convenient statement that transfers a snapshot to the slave server and immediately adjusts the log name and offset. In the future, load data from master will be a recommended method for creating slave servers. However, it only works on MyISAM tables and may hold read locks for a long time. It is not executed as efficiently as we hope. If you have a large table and run the flush tables with read lock statement, the preferred method is to create a binary snapshot on the master server.

Q: Do I need to always connect to the master server from the slave server?

A: No, no. The slave server can be down or disconnected for several hours or even several days. After the slave server is reconnected, the new information is obtained. For example, you can set the relationship between the master server and slave server on the dial-up link, which only occasionally connects in a short time. This means that at any given time, the slave server cannot guarantee synchronization with the master server unless you execute some special methods. In the future, we will use the option to block the master server until there is a slave server synchronization.

Q: How do I know the latest comparison between the slave server and the master server? In other words, how do I know the date of the last query copied from the server?

A: You can view the results of the Seconds_Behind_Master column of the show slave status statement. When the SQL thread of the slave server executes an event read from the master server, it modifies its time based on the event TIMESTAMP (this is the reason why TIMESTAMP can be well copied ). In the Time column output by the show processlist statement, the number of seconds displayed for the SQL thread on the slave server is the number of seconds between the timestamp of the last replication event and the actual Time of the slave server host. You can use it to determine the date of the last replication event. NOTE: If your slave server is disconnected from the master server for an hour and then reconnects, In the show processlist result, you can immediately see that the Time value of the slave server SQL thread is 3600. This may be because the statement executed from the server is one hour ago.

Q: How can I force the master server to block updates until synchronization from the slave server?

A: Use the following steps:

1. Execute these statements on the master server:
Mysql> flush tables with read lock;
Mysql> show master status;

Records the log name and offset output by the SHOW statement. These are the copy coordinates.

2. on the slave server, issue the following statement. The parameter of the Master_POS_WAIT () function is the copy coordinate value obtained in the previous step:
Mysql> SELECT MASTER_POS_WAIT ('Log _ name', log_offset );

The SELECT statement is blocked until the slave server reaches the specified log file and offset. At this time, the slave server synchronizes with the master server, and the statement is returned.

3. On the master server, issue the following statement to allow the master server to start processing updates again:
Mysql> unlock tables;

Q: What statements should I know when I set bidirectional replication?

A: currently, MySQL replication does not support any locking protocols between the master server and slave server to ensure the atomicity of distributed (cross-server) updates. In other words, this is possible: Customer A updates data based on collaboration-master server 1, and before it passes data to collaboration-master server 2, customer B is able to update based on collaboration-master server 2, so that customer A's updates are different from those in collaboration-master server 1. In this way, when customer A updates the data based on collaboration-master server 2, the tables it generates are different from those on collaboration-master server 1, even if all updates based on collaboration-master server 2 have been passed in. This means that you should not concatenate two servers in a two-way replication relationship unless you are sure that updates in any order are safe, or, unless you pay attention to how to avoid updating order errors in the client code.

You must also realize that from the update perspective, bidirectional replication does not significantly improve performance (or simply does not improve performance ). Both servers need to perform the same number of updates, as in one server. The only difference is that there is less lock competition, because updates from another server are serialized in one slave thread. Even if this benefit may be offset by network latency.

Q: How to improve system performance through replication?

A: You should set A server as the master server and direct all writes to the server. Configure as many slave servers and stack space as possible based on the budget, and distribute read operations between the master server and slave server. You can also use the -- skip-innodb, -- skip-bdb, -- low-priority-updates, and -- delay-key-write = ALL options to start the slave server, to increase the speed on the slave server. In this case, the slave server uses non-transaction MyISAM tables instead of InnoDB and BDB tables to improve the speed.

Q: How can I prepare client code in my own application to use high-performance replication?

A: If the database access part of your code is correctly modularized, it should be able to smoothly and easily convert it to the code that runs in the copy step. You only need to change the database access execution to send all write operations to the master server, and send read operations to the master server or a slave server. If your code does not have this level, set a replication system to clear it. The following function should be used to create a packaging library or module:

· Safe _ writer_connect ()
· Safe _ reader_connect ()
· Safe _ reader_statement ()
· Safe _ writer_statement ()

The safe _ of each function name means that the function handles all errors with caution. You can use functions with different names. It is important to have a unified interface for read connection, write connection, read and write.

Then, you should convert the client code to use the packaging library. At the beginning, this may be a painful and panic process, but in the long run it is worthwhile. All applications using the method discussed earlier can take advantage of the master server/slave server configuration, even if it contains multiple slave server configurations. The code is very easy to maintain, and it is also easy to add troubleshooting options. You only need to modify one or two functions. For example, you need to record the execution time of each statement, or which of your thousands of statements has an error.

If you have already compiled a lot of code, you may want to use the replace tool for automatic conversion. This tool is released along with standard MySQL, Or you can write your own conversion script. Ideally, your code uses the same program conversion style. Otherwise, it may be better to rewrite the code, or at least manually normalize it to use a consistent style.

Q: When and how much can MySQL replication improve system performance?

A: MySQL replication is of the greatest benefit to systems with frequent reads and writes. Theoretically, you can use a single master server/multiple slave server to expand the system by adding more slave servers until the network bandwidth is used up, or your update load has grown to a point that the master server cannot handle.

Before you get the benefits, you need to know the query mode to determine how many slave servers can be used and how much performance can be improved for your site, in addition, the relationship between the throughput and write (max_writes) throughput of a typical master server and the read (read per second, or max_reads) from the slave server should be determined based on experience. Through a hypothetical system with replication, this example provides a very simple computing result.

Assuming that the system load includes 10% write and 90% read, and the benchmark test shows that max_reads is 1200-2 × max_writes. In other words, if there is no write operation, the system can perform 1,200 read operations per second. The average write operation is twice the average read operation time, and the relationship is linear. We assume that the master server has the same performance as each slave server, and we have one master server and N slave servers. For each server (master server or slave server), we have:

Reads = 1200-2 × writes
Reads = 9 × writes/(N + 1) (read is separated, but written to all servers)
9 × writes/(N + 1) + 2 × writes = 1200
Writes = 1200/(2 + 9/(N + 1 ))

The final equation indicates the maximum write operations of N slave servers. Assuming that the maximum possible read rate is 1,200 times per minute, the ratio of read operations to write operations is 9.

The following conclusions can be obtained from the above analysis:

· If N = 0 (this indicates there is no replication), the system can process about 1200/11 = 109 write operations per second.

· If N = 1, 184 write operations are performed per second.

· If N = 8, 400 write operations are performed per second.

· If N = 17, 480 write operations are performed per second.

· Finally, when N tends to be infinite (and our budget is negative), we can get very close to 600 write operations per second, increasing the system throughput by nearly 5.5 times. However, if only eight servers are used, the increase is nearly 4 times.

Note that these calculations assume that the network bandwidth is infinite and ignore other factors, which may have an important impact on the system. In many cases, similar calculations cannot be executed, that is, if N replication slave servers are added, the impact of the system should be accurately predicted. The following questions should help you determine whether or not replication can improve the system performance:

· What is the read/write ratio on the system?

· If read operations are reduced, how much write load can a server handle?

· How many slave servers can the network bandwidth meet?

Q: How to Use replication to provide redundancy/high availability?

A: To use the current available features, you must set one master server and one slave server (or multiple slave servers), and write A script to monitor whether the master server is started. If the master server fails, the application and slave server are notified to switch to the master server. The following are some suggestions:

· Notify the slave server to change its MASTER server and use the change master to statement.

· A good way to notify the application of the master server location is to provide a dynamic DNS entry to the master server. With bind, you can use nsupdate to dynamically update DNS.

· Use the -- logs-bin option instead of the -- logs-slave-updates option to run the slave server. In this way, once you issue the stop slave, reset master, and change master to Statement on other SLAVE servers, the SLAVE server can be switched TO the MASTER server. For example, suppose there are the following settings:

WC
\
V
WC ----> M
/| \
/| \
V
S1 S2 S3

M represents the master server, S represents the slave server, and WC represents the customers who send database write and read operations; only the customers who send database read operations do not give, because they do not need to switch. S1, S2, and S3 are slave servers. The -- logs-bin option is used instead of -- logs-slave-updates. Because the updates of the master server received by the slave server are not recorded in the binary log, unless the -- logs-slave-updates option is specified, the binary log on each slave server is empty. If M becomes unavailable for some reason, you can select a master server from the server to the new one. For example, if S1 is selected, all WC data points to S1 and S2 again, and S3 should then be copied from S1.

Make sure that all slave servers have processed all the statements in the relay log. Kingfield issues the stop slave IO_THREAD statement on each SLAVE server, then checks the output of the show processlist statement until you see the Has read all relay log. When all slave servers perform these operations, they can be reconfigured as a new setting. Issue the stop slave and reset master statements on the SLAVE server S1 promoted to the MASTER server.

On other SLAVE servers S2 and S3, use stop slave and change master to MASTER_HOST = 's1' ('s1' indicates the actual Host Name of S1 ). Add all information (user, password, port) about how to connect to S1 ). In the change master command, you do not need to specify the binary log name or binary log location of S1 read from it: we know that it is 1st binary logs, location is 4, this is the default value of the change master command. Finally, run the start slave command on S2 and S3.

Then, it instructs all WC to point their statements to S1. After that, all update statements sent by WC to S1 are written to the binary log of S1, and S1 contains every update statement sent to S1 after M is dead.

The result is the following Configuration:

WC
/
|
WC | M (unavailable)
\ |
\ |
V
S1 <-- S2 S3
^ |
+ ------- +

After M restarts, you must issue the same change master Statement on M, which is the same as the statement on S2 and S3, so that M becomes the slave server of S1 and all the WC write operations lost after it goes down. To use M as the master server again (for example, because it is the most powerful machine), use the previous steps as if S1 is unavailable and M is changed to a new master server. In this process, do not forget to run the reset master on M before S1, S2, and S3 are used as M slave servers. Otherwise, they may pick up M and become unavailable before the old WC write operation.

We are currently integrating MySQL with an automatic master server selection system, but before you are ready, you must create your own monitoring tool.