MySQL Sync FAQs (your own tips)

A few days ago just registered the blog park, I would like to write some technical tutorials, today to share a MySQL Sync FAQ.

Q: If the primary server is running and you do not want to stop the primary server, how do I configure a slave server?

A: There are several ways. If you have made a primary server backup at some point and recorded the binary log name and offset of the corresponding snapshot (through the output of the show MASTER Status command), take the following steps:

1. Ensure that a unique server ID number is assigned from the server.

2. In the Execute the following statement from the server, fill in the appropriate values for each option:
Mysql> Change MASTER to
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 from the server.

If you do not have a backup home server, here is a quick program to create a backup. All steps should be performed on the master server host.

1. Issue the statement:
Mysql> FLUSH TABLES with READ LOCK;

2. When still locked, execute the command (or its variant):
Shell> Tar Zcf/tmp/backup.tar.gz/var/lib/mysql

3. Issue the statement and make sure to record the output that is used later:
Mysql>show MASTER STATUS;

4. Release the Lock:
Mysql> UNLOCK TABLES;

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

Regardless of which of these 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 build multiple slave servers. Once you have a snapshot of the primary server, you can wait to create one from the server as long as the primary server's binary log is complete. Two time to wait the actual limit is the amount of free hard disk space to save the binary log on the primary server and the time it takes to synchronize from the server.

You can also use the load DATA from MASTER. This is a handy statement that transmits a snapshot to the slave server and adjusts the log name and offset immediately. In the future, the LOAD DATA from master will be the recommended method for creating a Slave server. However, it is important to note that it only works on MyISAM tables and can hold read locks for long periods of time. It is not carried out as efficiently as we would like it to be. If you have a large table, after you execute the flush TABLES with READ lock statement, the preferred method is still to make a binary snapshot on the primary server.

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

A: No, not required. From the server can be down or disconnected for several hours or even days, after reconnecting to get updated information. For example, you can set up a master server/slave server relationship on a dial-up link, where only occasionally a short period of time is connected. This means that at any given time, from the server is not guaranteed to synchronize with the primary server unless you perform certain special methods. In the future, we will use the option to block the master server until there is a synchronization from the server.

Q: How do I know the latest comparison from the server to the primary 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 performing an event read from the primary server from the server SQL thread, it modifies its own time based on the event timestamp (this is why timestamp can replicate well). In the time column of the show processlist statement output, the number of seconds to display for the SQL thread from the server is the timestamp of the last replication event and the number of seconds between the actual time from the server host. You can use it to determine the date of the last replication event. Note that if your connection from the server to the primary server is disconnected for one hours and then reconnected, in the show processlist results, you can immediately see the time value from the server SQL thread to 3600. This could be because the statement executed from the server was an hour ago.

Q: How do I force the primary server to block updates until it synchronizes from the server?

A: Use the following steps:

1. On the primary server, execute these statements:
Mysql> FLUSH TABLES with READ LOCK;
mysql> SHOW MASTER STATUS;

Logs the log name and offset of the output of the show statement. These are the replication coordinates.

2. On the slave server, issue the following statement, where the parameter of the master_pos_wait () function is the resulting copy coordinate value in the previous step:
mysql> SELECT master_pos_wait (' Log_name ', log_offset);

The SELECT statement blocks until the specified log file and offset is reached from the server. At this point, the statement is returned from the server in sync with the primary server.

3. On the primary server, issue the following statement to allow the primary server to restart processing the update:
Mysql> UNLOCK TABLES;

Q: When setting up bidirectional replication, should I know which statements to issue?

A:mysql replication does not currently support the atomicity of distributed (cross-server) updates between the primary server and any locking protocols from the server. In other words, it is possible to do this: customer A is updated according to collaboration-master server 1, and before it is passed to collaboration-master server 2, Customer B is able to update according to the collaboration-master server 2, so that the update for customer a differs from its update in collaboration-master server 1. Thus, when customer A is updated according to the collaboration-master server 2, it produces tables that differ from those on the collaboration-master server 1, even if all updates based on collaboration-master server 2 have been sent. This means that you should not concatenate two servers in a two-way replication relationship unless you are sure that any sequential updates are secure, or unless you notice in the client code how to avoid updating the order of errors.

You must also realize that two-way replication does not actually significantly improve performance (or not improve performance at all) from an update perspective. Two servers need to do the same amount of updates as they do on a server. The only difference is that the lock competes less, because updates originating from another server are serialized in one from the thread. Even this benefit may be offset by network delays.

Q: How to improve the performance of the system through replication?

A: You should set up a server as the primary server and point all writes to that server. The server and stack space are then configured as much as possible from the budget, and read operations are distributed between the primary server and the slave server. You can also start from the server with--skip-innodb 、--skip-bdb 、--low-priority-updates and--delay-key-write=all options to increase speed from the server side. In this case, the non-transactional MyISAM table is used instead of the InnoDB and BDB tables in order to increase the speed.

Q: In order to use high-performance replication, how do I prepare the client code in my own application?

A: If the database access portion of your code has been properly modularized, you should be able to smoothly and easily convert to code that runs in the copy step. Only the database access execution section needs to be changed to send all write operations to the primary server, as well as to send read operations to the primary server or to a slave server. If your code does not have this level, set up a replication system to clear. You should first create a wrapper library or module by using the following function:

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 care. You can use a function with a different name. It is important to have a unified interface for read connections, write connections, read and write.

Then, you should convert the client code using the wrapper library. At first it may be a process of pain and panic, but it is worthwhile in the long run. All applications that use the method just discussed can take advantage of the primary server/server configuration, even if it contains multiple slave servers. The code is very easy to maintain, and it's easy to add troubleshooting options. You only need to modify one or two functions, for example, to record the time each statement executes, or which statement in your thousands of statements has an error.

If you've written a lot of code, you might want to use the Replace tool to automate the conversion, which is published with standard MySQL, or you can write your own conversion scripts. Ideally, your code uses a consistent program transformation style. Otherwise, it might be best to rewrite the code, or at least manually rule it to use a consistent style.

When and how far can q:mysql replication improve system performance?

A:mysql replication has the greatest benefit for systems that read frequently and write frequently. Theoretically, by using a single master server/multi-slave server Setup, you can augment the system by adding more slave servers until you run out of network bandwidth, or your update load has grown to points that the primary server cannot handle.

Before getting the proceeds to start eating flat, in order to determine how much you can get from the server, and how much you can improve the performance of your site, you need to know the query pattern, and to pass the benchmark and experience to determine a typical master server and read from the server (read per second, or max_ Reads) The relationship between throughput and write (max_writes) throughput. With a hypothetical system with replication, this example gives a very simple calculation result.

Suppose the system load consists of 10% writes and 90% reads, and we determine by benchmarking that max_reads is 1200–2xmax_writes. In other words, if there is no write operation, the system can perform 1,200 reads per second, and the average write is twice times the time spent on the average read operation, and the relationship is linear. We assume that the primary server and each slave server have the same performance, and we have a primary server and n slave servers. So, for each server (master server or slave server), we have:

Reads = 1200–2xwrites
Reads = 9xwrites/(N + 1) (read is detached but written to all servers)
9xwrites/(N + 1) + 2xwrites = 1200
Writes =/(2 + 9/(n+1))

The final equation indicates the maximum number of write operations from the server, assuming that the maximum possible read rate is 1,200 times per minute, and that the ratio of read to write operations is 9.

The above analysis can be concluded as follows:

• If n = 0 (which indicates no replication), the system can process approximately 1200/11 = 109 writes per second.

• If n = 1, get 184 write operations per second.

• If n = 8, get 400 write operations per second.

• If n = 17, get 480 write operations per second.

• Finally, when n tends to infinity (and the negative infinity of our budget), it can get very close to 600 writes per second and system throughput increases by nearly 5.5 times times. However, if only 8 servers are used, the increase is nearly 4 times times.

Note that these calculations assume that the network bandwidth is infinite and that some other factors are ignored, and those factors can have a significant impact on the system. In many cases, it is not possible to perform calculations similar to what has just been done, that is, if you add n copies from the server, you should accurately predict what will happen to the system. Answering the following questions should help you determine if and to what extent replication can improve the performance of your system:

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

• If you reduce read operations, how much write load can one server handle?

• How much network bandwidth can be met from the server's needs?

Q: How can I use replication to provide redundancy/high availability?

A: With the current available features, you must set up a master server and a Slave server (or multiple slave servers), and write a script to monitor whether the primary server is started. If the primary server fails, notify the application and switch the primary server from the server. Here are some suggestions:

• Inform the server from changing its primary server, using the change master to statement.

• A good way to notify the application home server location is to provide a dynamic DNS entry to the primary server. Use BIND to dynamically update DNS with Nsupdate.

• The slave server should be run with the--logs-bin option instead of the--logs-slave-updates option. This way, once you issue a stop SLAVE on another slave server; RESET master, and the change master to statement, the slave server can switch to the primary server. For example, suppose you have the following settings:

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

M represents the primary server, s represents the client from the server, WC represents the database write and read operations, and only the clients that issue the database read operations are not given because they do not need to switch. S1, S2, and S3 are from the server, using the--logs-bin option and not running with--logs-slave-updates. Because updates to the primary server received from the server are not recorded in the binary log, each binary log from the server is empty unless the--logs-slave-updates option is specified. If for some reason m becomes unavailable, you can choose one from the server to the new primary server. For example, if you select S1, all WC should point back to S1 and S2, and S3 should then be copied from S1.

Make sure all the statements in the trunk log have been processed by all slave servers. Golden Silk Court on each slave server, issue the Stop SLAVE io_thread statement, and then check the output of the show Processlist statement until you see the has read all relay log. When all of these are done from the server, they can be reconfigured to a new setting. The stop slave and reset master statements are issued on the slave server S1 that is promoted to the primary server.

On other slave servers S2 and S3, use stop slave and change MASTER to master_host= ' S1 ' (where ' S1 ' represents S1 actual hostname). For change master, add all the information about how to connect to S1 from S2 or S3 (user, password, port). In the Change Master command, you do not need to specify the binary log name or binary log location of the S1 read from: We know it is the 1th binary log, the location is 4, which is the default value of the Change Master command. Finally, use the start SLAVE command on S2 and S3.

Then, instruct all the WC to point their statements to S1. Thereafter, all the update statements sent to S1 by the WC are written to the S1 binary log, and S1 contains each UPDATE statement sent to S1 after M dead.

The result is the following configuration:

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

When M restarts, you must issue the same change master statement on M, just like the statements issued on S2 and S3, so that m becomes the slave of the S1 and restores all the WC writes that were lost after its outage. To make M the primary server again (for example, because it is the most powerful machine), use the previous steps as if S1 is unavailable and M becomes a new primary server. In this process, do not forget to run reset MASTER on M before S1, S2, and S3 as the slave server for M. Otherwise, they may pick up the old WC write operation before m becomes unavailable.

We are currently in MySQL integrated Automatic master server selection system, but before you are ready, you must create your own monitoring tools.

MySQL Sync FAQs (your own tips)