MySQL synchronization (5)

6.9 synchronization FAQ: The master is still running. How can I configure slave without stopping it? A: Several option parameters need to be designed. If you have backup of the master and the binary log file name and offset location of the Data snapshot are recorded (run SHOWMASTERSTATUS to view the result), perform the following steps:

6.9 synchronization FAQ: The master is still running. How can I configure slave without stopping it? A: Several option parameters need to be designed. If you have backup of the master and the binary log file name and offset location of the Data snapshot are recorded (run show master status to view the result), perform the following steps: Make sure that slave specifies a unique

6.9 synchronization FAQ

Question: The master is still running. How can I configure slave without stopping it?

Answer:: You need to design several option parameters. If you have backup of the master and recorded the binary log file name and offset location of the Data snapshot (runSHOW MASTER STATUSView the results), perform the following steps:

1. Make sure that slave specifies a unique server number.
2. Run the following statement on slave to change some option values to actual values:

   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. RunSTART SLAVEStatement.
   

If you have not backed up the master data in advance, you can use the following method to quickly create a backup. All of the following operations are performed on the master.

1. Statement:

   mysql> FLUSH TABLES WITH READ LOCK;
   

2. Make sure the lock persists and run the following command (or something similar ):

   shell> tar zcf /tmp/backup.tar.gz /var/lib/mysql
   

3. Run the following statement to record the output result, which will be used later:

   mysql> SHOW MASTER STATUS;
   

4. Release lock:

   mysql> UNLOCK TABLES;

Another way to do this is to create an SQL dump file for the master. You only need to executemysqldump --master-dataCommand, and then load the exported SQL dump file into slave. However, this will make binary data snapshots a little slower.

No matter which method you use, you can create a master data snapshot and record the binary log file name and offset location. The same backup binary data snapshot can be used on several other slave instances. After obtaining the snapshot of the master, as long as the binary log of the master is intact, you can start to set the slave. The two limits on whether to wait for a long time are: Saving binary logs in the disk space on the master, and capturing update events from the master by slave.

You can also useLOAD DATA FROM MASTER. This statement can easily take data snapshots on the slave and immediately adjust the binary log file name and offset position. We recommend that you useLOAD DATA FROM MASTERTo set the slave. Warning, which can only be usedMyISAMTable, and may keep a long read lock. Because it has not achieved the expected efficiency, if the data table is large, it is best to finishFLUSH TABLES WITH READ LOCKAnd then directly create a binary data snapshot.

Question: Do slave always need to connect to the master?

Answer:: No, not required. Slave can be shut down or not connected to the master for several hours or even a few days, and then re-connect to obtain the update operation log. For example, you can set a mater/slave relationship on the dial-up link. The dial-up may only be sporadic and irregular connections. This practice implies that the slave cannot always be synchronized with the master at any specified time unless a special metric is used. In the future, there will be an option to block the master, unless at least one slave is in synchronization.

Question: How do I know what is later than the master? That is to say, how does one know the last synchronization time of slave?

Answer:: If the slave is 4.1.1 or higher, you only need to viewSHOW SLAVE STATUSIn the resultSeconds_Behind_MasterField. For earlier versions, you can use the following methods. If you executeSHOW PROCESSLISTThe statement results show that the SQL thread (For MySQL 3.23, It is the slave thread) is running, which means that the thread reads at least one update operation event from the master. For Details, see "6.3 Replication Implementation Details ".

When the SQL thread executes an update operation event read on the master, it changes its time to the event time (that isTIMESTAMP). In
SHOW PROCESSLISTIn the resultTimeIn the field, the number of seconds displayed by the slave SQL thread is the time difference between the last synchronization Timestamp and the actual time of the slave machine. The last synchronization time can be determined based on this value. Note: If the slave has been disconnected from the master for several hours and then reconnects, you can see
SHOW PROCESSLISTThe SQL thread in the resultTimeThe field value is similar to 3600. This is because slave is executing the statement one hour ago.

Question: How to force the master to stop the update operation before the slave catches up with all updates?

Answer:: Perform the following steps:

1. On the master node, run the following statement:

   mysql> FLUSH TABLES WITH READ LOCK;
   mysql> SHOW MASTER STATUS;  

   Record the log file name and offset location in the result. They are the synchronized coordinate values.
   

2. Submit the following statement on slave,MASTER_POS_WAIT()The value of the function parameter is the synchronized coordinate value obtained earlier:

   mysql> SELECT MASTER_POS_WAIT('log_name', log_offset);
   

   SELECTThe statement will stop updates until the slave is synchronized to the preceding log file and location. At this time, slave will be synchronized with the master, and this statement will return.

3. On the master, execute the following statement to allow the master to reprocess the update operation:

   mysql> UNLOCK TABLES;

Question: What should I pay attention to when setting a bidirectional replication?

Answer:: MySQL synchronization currently does not support any distributed (cross-server) Update lock protocols on the master and slave to ensure the atomicity of operations. That is to say, there is A possibility that client A makes an update on the coexistence master 1, and before it synchronizes to the coexistence master 2, client B may also perform A different update operation on master 2 than Client A on master 1. Therefore, when the update done by Client A is synchronized to master 2, it will generate A different data table from master 1, even though all the update operations on master 2 are synchronized to master 1. This means that, unless all updates can be securely executed in any order, do not use bidirectional synchronization, or unless you are aware of unordered update operations that are unknown in the client program.

At the same time, we should also be aware that the two-way synchronization does not greatly improve the performance (or even not) on the update issues of interest ). Both servers need to perform the same number of update operations on one server. The only difference is that this may reduce lock contention because the update operations from other servers are serialized in the slave thread. Even this benefit can be compensated for network latency.

Question: How can I use synchronization to improve system performance?

Answer:: You need to install a server as the master and put all the write operations directly on it. Then configure multiple slave instances to use the rack disk at a low cost and allocate read operations to the master and slave instances. You can also use--skip-innodb,--skip-bdb,--low-priority-updates, And--delay-key-write=ALLTo improve the performance of slave. In this case, slave will use non-transactionalMyISAMTable insteadInnoDBAndBDBTable, which is faster.

Question: How to prepare the code of the client application to adapt to the synchronization application?

Answer:: If the part of the code that is responsible for accessing the database has been reasonably abstracted/modularized, it will be smooth and simple to convert them into suitable for running in the synchronization environment. You only need to modify the database access implementation, put all write operations on the master, and put all read operations on the master or slave. If your code is not abstracted at this level, it will become an opportunity and motivation for organizing the code. You can use a function similar to the following to create an encapsulated class library or module:

• safe_writer_connect()
  

• safe_reader_connect()
  

• safe_reader_statement()
  

• safe_writer_statement()
  

Thesafe_Indicates that they will handle all errors. You can use other function names. It is important to define a unified interface for read connections, write connections, read and write.

Then, convert the client code to the encapsulated class library. It may be painful and troublesome at first, but it will be rewarded in the future. All applications that use the preceding method have advantages in master/slave configuration, even if they contain multiple slave instances. This code will be easy to maintain, and there will be little extra trouble. Proudly, You need to modify one or two functions. For example, you want to record how long each statement has been executed, or which of the thousands of statements has encountered an error.

If you have already written a lot of code and want to convert them automatically, you can usereplaceOr write the conversion script by yourself. Ideally, your code has used a unified programming style. If not, you 'd better rewrite them, or you can traverse and check and manually standardize the code style.

Question: When and how much can MySQL synchronize to improve system performance?

Answer:: MySQL synchronization is good for systems that read frequently but do not write frequently. In theory, using a single master/multiple slave configuration can be used to measure the system: increase the number of slave instances until all network bandwidth or master update operations are used up to a point that cannot be processed.

If you want to know how many slave instances can be added to ensure stable performance and improve performance, you need to know the query mode and read the typical master and slave instances based on experience (read per second ormax_reads) And write (max_write) Benchmark Test to obtain the relationship between them. The following example shows a simple calculation method for the performance of an ideal system.

The Set system load consists of 10% write and 90% read. We have passed the benchmark test to determinemax_readsIt is 1200-2 *max_writes. In other words, the system can perform 1200 read operations without writing per second. The average write operation is twice the speed of read operations, and the relationship between them is linear. Let's assume that the master and each slave have the same capacity, with one master and N slave. Each server (master or slave ):

reads = 1200 - 2 * writes

reads = 9 * writes / (N + 1) (Read is separated, but all writes are on all servers)

9 * writes / (N + 1) + 2 * writes = 1200

writes = 1200 / (2 + 9/(N+1))

The final equation illustrates the maximum number of writes to N slave, giving it the highest read frequency per minute of 1200 and the probability of one write 9 times.

The analysis conclusion ratio is as follows:

• If N = 0 (meaning there is no synchronization), the system can process 1200/11 = 109 writes per second.
• If N = 1, it is increased to 184 writes per second.
  
• If N = 8, it is increased to 400 writes per second.
• If N = 17, it is increased to 480 writes per second.
• In the end, as N approaches infinity (our budget is negative infinity), it can reach nearly 600 writes per second, increasing the system throughput by about 5.5 times. However, when there are eight servers, it has increased by 4 times.
  

Note that the above calculation assumes that the network bandwidth is infinite and ignores some large factors in the system. In many cases, when the system adds N synchronous slave instances, the above prediction results cannot be accurately calculated. However, first, let's take a look at the following questions to help you know if there are any improvements to the system performance:

• What is the system read/write ratio?
• How many write operations can be added to a server after the number of read operations is reduced?
• How much slave can your network bandwidth be used?
  

Question: How can I use synchronization to provide redundancy/high availability?

Answer:: With the currently available features, you can configure one master and one (or more) slave, and write a script to monitor whether the master is running. Then, the application and slave are notified to modify the master when an error is found. Some suggestions are as follows:

• UseCHANGE MASTER TOThe statement tells slave to modify the master.
• One way for the application to locate the host where the master is located is to use dynamic DNS for the master. For example, bind can be used'Nsdate'To dynamically update DNS.
  

• Use--log-binOption, not used
  --log-slave-updatesTo start slave. This allows slave to runSTOP SLAVE;RESET MASTERAfter the statement is executed, you can change it to the master at any time and run it on other slave instances.
  CHANGE MASTER TO. For example, the following configuration scheme is available:

         WC
          /
           v
   WC----> M
         / | /
        /  |  /
       v   v   v
      S1   S2  S3
  

  M indicates masetr, S indicates slave, WC indicates the client that submits the read/write operation, and only the client that submits the read operation is not displayed, because they do not need to be switched. S1, S2, and S3 are all used
  --log-binOption, not required--log-slave-updatesOption to run the slave. Unless specified--log-slave-updatesParameter. Otherwise, the update operations read from the master node are not recorded in the binary log. Therefore, the binary log on each slave is empty. If M cannot be used for some reason, you can specify a slave as the master. For example, if S1 is specified, all WC will be redirected to S1. both S2 and S3.

  Make sure that all slave logs have processed their respective relay logs. On each slave, submitSTOP SLAVE IO_THREADStatement, and then checkSHOW PROCESSLISTUntil you seeHas read all relay log. When all slave is like this, you can set it according to the new solution. Submit on slave S1

  STOP SLAVEAndRESET MASTERStatement to promote it to the master.

  On other slave S2 and S3, submitSTOP SLAVEAndCHANGE MASTER
TO MASTER_HOST='S1'('S1'Represents the true host name of S1) statement to modify the master. Attaches the parameters (user, password, port, etc.) for connecting S2 and S3 to S1CHANGE MASTER. In
  CHANGE MASTERYou do not need to specify the binary log file name and offset location of S1: BecauseCHANGE MASTERThe default value is the first binary log and offset position 4. Finally, submit on S2 and S3START SLAVEStatement.

  Then let all the WC redirect their statements to S1. At this time, all the update statements sent from WC to S1 will be written to the binary log of S1. They contain all the update statements sent to S1 after M is dead.

  The configuration result is as follows:

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

  After M is up again, you only need to submit the same on M as on S2 and S3.CHANGE MASTERStatement to convert it into a slave and read all the updates submitted by WC since it died. If you want to re-convert M to a master (for example, because of its better performance), execute operations similar to the above, regard S1 as invalid, and promote M to a new master. In this step, do not forget to run on M before modifying S2 and S3 to M's slave.RESET MASTERStatement. Otherwise, they will read the update operation logs submitted by WC from the time when M becomes invalid.
  

Now we are running a complete MySQL synchronization system with automatic master selection, but before it is ready, we need to create our own monitoring tool.

6.10 synchronization troubleshooting

If the synchronization fails to work properly after the preceding steps are configured, first check the following content:

• Check the error log information.. Many users are not doing well enough to waste time.
• Is the master recording binary logs? UseSHOW MASTER STATUSCheck the status. If yes,PositionThe value is not zero; otherwise, make sure that the master is usedlog-binAndserver-id.
  
• Is slave running? RunSHOW SLAVE STATUSStatement check

  Slave_IO_RunningAndSlave_SQL_RunningAre all values. If not, determine whether to use the synchronization parameter to start the slave server.
  

• If slave is running, does it establish a connection to the master? Run
  SHOW PROCESSLISTStatement to checkStateField Value. For Details, see "6.3 Replication Implementation Details ". If the I/O thread status isConnecting to masterCheck whether the synchronization user's permissions on the master are correct, master's host name, DNS settings, whether the master is indeed running, and whether the slave can be connected to the master.
  
• If the server Load balancer runs before but stops now, it is usually because some statements can succeed on the master but fail on the server Load balancer. If salve has obtained all the snapshots of the master and does not modify its data except the slave thread, this should not happen. If this happens, it may be a bug or you have encountered one of the synchronization restrictions mentioned in "6.7 Replication Features and Known Problems. If it is a bug, report it according to the "6.11 Reporting Replication Bugs" instructions.
  
• If a statement succeeds on the master but fails on the slave, and a complete database cannot be synchronized (that is, the data on the slave can be deleted, copy the snapshot of the master again), then try:
  
  1. Determine whether the slave data table is different from the master data table. Try to find out how this happens, and then run the Server Load balancer table after it is the same as the master table.START SLAVE.
  2. If the preceding steps do not take effect or are not executed, try to manually run the statement safely (if necessary) and then ignore the next statement of the master.
     
  3. If you decide to ignore the next statement of the master, you only need to submit the following statement on the slave:

     mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = n;
     mysql> START SLAVE;

     If the next statement is not usedAUTO_INCREMENTOrLAST_INSERT_ID(), ThennThe value must be1. Otherwise, its value is2. Set2BecauseAUTO_INCREMENTOrLAST_INSERT_ID()Two logs are occupied in the binary log of the master node.

  4. If it is determined that slave has precisely synchronized the master, and there is no update operation on the data table except the slave thread, it is inferred that this is due to the difference caused by the bug. If you are using the latest version, please report this problem. If you are using the old version, try to upgrade it.
     
  
  

6.11 report synchronization Bugs

If you are sure that the error does not contain the user and the synchronization still fails or is unstable, you can report the bug. We need as much bug tracking information as possible. Please spend some time and effort preparing a good bug report.

If you have a reproducible test case that demonstrates bugs, go to our bug database http://bugs.mysql.com /. If you encounter a phantom problem (which cannot be reproduced), follow these steps:

1. Are you sure there are no user errors included. For example, if the slave data is updated outside the slave thread, the data cannot be synchronized, or the unique key issue during the update may occur. This is a synchronization failure caused by external interference.
   
2. Use--log-slave-updatesAnd--log-binTo start slave. This causes slave to write the update operations read from the master to its own binary log.
   
3. Save all evidence before resetting the synchronization status. If we do not have any information or only have rough information, it will be difficult or impossible to trace this problem. The following evidence must be collected:
   
   
   
   • All binary logs on the master
     
   • All binary logs on slave
     
   • When a problem is found, run the command on the master.SHOW MASTER STATUSResult
     
   • When a problem is found, run the command on the master.SHOW SLAVE STATUSResult
     
   • Logs of master and slave errors
     
   
   
4. UsemysqlbinlogTo check binary logs. For example, you can use the following method to locate a problematic query:

   shell> mysqlbinlog -j pos_from_slave_status /
              /path/to/log_from_slave_status | head
   

Once the evidence of the problem is collected, it is first isolated to an independent test system. Then, go to our bug database http://bugs.mysql.com/and submit a detailed report.

Synchronize MySQL (4) Up