MySQL Failover Notes: Application-Aware Design Detailed Explanation
|
1. Introduction As we all know, in the application scenario of database middleware read-write separation, how to ensure that when a faulty node occurs in the underlying database, the middleware can quickly disconnect or migrate the database connection so that users are unaware. In the MySQL database, a session_track_transaction_info parameter is provided to provide a solution. Because there is no description of this parameter in the official documentation, this article specifically introduces the optional values of this parameter and verifies the actual impact. Let’s not talk too much, let’s take a look at the detailed introduction with the editor 2. session_track_transaction_info parameter 2.1 Parameter Introduction In MySQL 5.7, you can track the status of transactions by setting the session_track_transaction_info variable.
/**
Transaction tracking level
*/
enum enum_session_track_transaction_info {
TX_TRACK_NONE = 0, ///< do not send tracker items on transaction info
TX_TRACK_STATE = 1, ///< track transaction status
TX_TRACK_CHISTICS = 2 ///< track status and characteristics
};The allowed values for this parameter are 0, 1, and 2.
2.2 Impact of parameter settings When the session_track_transaction_info parameter is turned on, the transaction status record cannot be directly queried in the database. According to [WL#4797], MySQL records transaction status tracking information in the OK packet returned by each Query request. You can view transaction status information by capturing packets. 2.2.1 Native MySQL OK packet format OK Packet data packet format definition
The lenenc in int<lenenc> and string<lenenc> represents LengthEcode. The code for encapsulating the OK packet in MySQL-5.7.19 is in the net_send_ok() function in the protocol_classic.cc file. 2.2.3 session_track_transaction_info Additional information session_track_transaction_info uses 8 characters to represent transaction information, and these 8 characters are stored in the return data packet of the COM_QUERY request statement (when the client executes a statement, it will be encapsulated into a COM_QUERY request in the MySQL protocol and sent to the server. After the server parses and executes it, it will encapsulate the result in a data packet and return it).
2.2.2 OK packet format analysis when session_track_transaction_info = 0 session_track_transaction_info=0 means that transaction information is not recorded, and there is no transaction status tracking information in all data packets returned from the server. ## session_track_transaction_info = 0 The client executes begin; encapsulated data packet 06 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 62 65 67 69 6e # begin Data packet returned by the server: response 07 00 00 # payload_length 01 # sequence_id 00 # Header 0x00 indicates an OK packet 00 # Affected rows 0 00 # The last inserted id 03000000 The client executes insert into t1 values(55) to encapsulate the data packet 1a 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 696e7365727420696e746f2074312076616c75657328353529 # insert into t1 values(55) Data packet returned by the server: response 07 00 00 # payload_length 01 # sequence_id 00010003000000 The client executes commit; the encapsulated data packet is 07 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 636f6d6d6974 # commit Database package returned by the server: response 07 00 00 # payload_length 01 # sequence_id 00000002000000 2.2.4 OK packet format analysis when session_track_transaction_info = 1 ## session_track_transaction_info = 1 The client executes begin; encapsulated data packet 06 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 626567696e#begin Data packet returned by the server: response 14 00 00 # payload_length 01 # sequence_id 00 # Header 0x00 indicates an OK packet 00 # Affected rows 0 00 # The last inserted id 03400000000b050908 54 5f 5f 5f 5f 5f 5f # Transaction status information T_______ # Place 1: 54 //Open a transaction explicitly # Place 2: 5f //No non-transactional storage engine tables are read in the current transaction # Place 3: 5f //No transactional storage engine tables are read in the current transaction # Place 4: 5f //No non-transactional storage engine tables are written in the current transaction # Place 5: 5f //No transactional storage engine tables are written in the current transaction # Place 6: 5f //No unsafe statements are used in the current transaction # Place 7: 5f //No result set # Place 8: 5f //No table lock Client executes insert into t1 values(111) encapsulated data packet 1b 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 696e7365727420696e746f2074312076616c7565732831313129 # insert into t1 values(111) Data packet returned by the server: response 14 00 00 # payload_length 01 # sequence_id 00010003400000000b050908 54 5f 5f 5f 57 5f 5f 5f # Transaction status information T___W___ # Place 1: 54 //Open a transaction explicitly # Place 2: 5f //No non-transactional storage engine tables are read in the current transaction # Place 3: 5f //No transactional storage engine tables are read in the current transaction # Place 4: 5f //No non-transactional storage engine tables are written in the current transaction # Place 5: 57 //There are transactional storage engine tables written in the current transaction # Place 6: 5f //No unsafe statements are used in the current transaction # Place 7: 5f //No result set # Place 8: 5f //Client executes commit without locking the table; encapsulated data packet 07 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 636f6d6d6974 # commit Data packet returned by the server: response 1400000100000002400000000b050908 5f 5f 5f 5f 5f 5f 5f 5f # Transaction status information________ # Place 1: 5f //No active transaction# Place 2: 5f //No non-transactional storage engine tables are read in the current transaction# Place 3: 5f //No transactional storage engine tables are read in the current transaction# Place 4: 5f //No non-transactional storage engine tables are written in the current transaction# Place 5: 5f //No transactional storage engine tables are written in the current transaction# Place 6: 5f //No unsafe statements are used in the current transaction# Place 7: 5f //No result set# Place 8: 5f //No table lock 2.2.5 OK packet format analysis when session_track_transaction_info = 2 When the session_track_transaction_info parameter is set to 2, more detailed transaction status information will be displayed. The client executes begin; encapsulated data packet 06 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 626567696e#begin Data packet returned by the server: response 29 00 00 # payload_length 01 # sequence_id 000000034000000020050908 54 5f 5f 5f 5f 5f 5f 5f # Transaction status information T_______ 0413125354415254205452414e53414354494f4e3b # START TRANSACTION; # Place 1: 54 //Open a transaction explicitly # Place 2: 5f //No non-transactional storage engine tables are read in the current transaction # Place 3: 5f //No transactional storage engine tables are read in the current transaction # Place 4: 5f //No non-transactional storage engine tables are written in the current transaction # Place 5: 5f //No transactional storage engine tables are written in the current transaction # Place 6: 5f //No unsafe statements are used in the current transaction # Place 7: 5f //No result set # Place 8: 5f //No table lock Client executes insert into t1 values(222) encapsulated data packet 1b 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 696e7365727420696e746f2074312076616c7565732832323229 # insert into t1 values(222) Data packet returned by the server: response 14 00 00 # payload_length 01 # sequence_id 00010003400000000b050908 54 5f 5f 5f 57 5f 5f 5f # Transaction status information T___W___ # Place 1: 5f //No active transaction# Place 2: 5f //No non-transactional storage engine table is read in the current transaction# Place 3: 5f //No transactional storage engine table is read in the current transaction# Place 4: 5f //No non-transactional storage engine table is written in the current transaction# Place 5: 5f //No transactional storage engine table is written in the current transaction# Place 6: 5f //No unsafe statements are used in the current transaction# Place 7: 5f //No result set# Place 8: 5f //No table is lockedClient executes commit; encapsulated data packet 07 00 00 # payload_length 00 # sequence_id 03 # command_type COM_QUERY 636f6d6d6974 # commit Data packet returned by the server: response 17 00 00 # payload_length 01 # sequence_id 00000002400000000e050908 5f 5f 5f 5f 5f 5f 5f 5f # Transaction status information________ 040100 # Place 1: 5f //No active transaction# Place 2: 5f //No non-transactional storage engine tables are read in the current transaction# Place 3: 5f //No transactional storage engine tables are read in the current transaction# Place 4: 5f //No non-transactional storage engine tables are written in the current transaction# Place 5: 5f //No transactional storage engine tables are written in the current transaction# Place 6: 5f //No unsafe statements are used in the current transaction# Place 7: 5f //No result set# Place 8: 5f //No table lock 3. Conclusion After setting the session_track_transaction_info parameter, the transaction status information of the current connection can be obtained in the return data packet of MySQL. This feature can be used in database middleware to automatically migrate connections in the event of a MySQL failure, reducing the impact on users. In some scenarios, the underlying MySQL node failure can be switched, and the application can switch over without noticing. Well, the above is the full content of this article. I hope that the content of this article will have certain reference learning value for your study or work. If you have any questions, you can leave a message to communicate. Thank you for your support of 123WORDPRESS.COM. You may also be interested in:
|
>>: Solution to 700% CPU usage of Linux process that cannot be killed
Recommend
WeChat applet to obtain mobile phone number step record
Preface Recently, I encountered such a problem wh...
Markup language - CSS layout
Click here to return to the 123WORDPRESS.COM HTML ...
Detailed explanation of fuser command usage in Linux
describe: fuser can show which program is current...
Detailed explanation of Apache+Tomcat7 load balancing configuration method under Windows
Preparation Windows Server 2008 R2 Enterprise (2....
Simple implementation of ignoring foreign key constraints when deleting MySQL tables
Deleting a table is not very common, especially f...
Three ways to realize the horizontal centering of elements and the understanding of the concepts of fixed layout and flow layout
The property of centering text in CSS is very simp...
Common properties of frameset (dividing frames and windows)
A frame is a web page screen divided into several ...
VMware ESXi 5.5 deployment and configuration diagram process
Table of contents 1. Installation requirements 2....
Example of how to install nginx to a specified directory
Due to company requirements, two nginx servers in...
How to run Spring Boot application in Docker
In the past few days, I have studied how to run s...
HTML table layout example explanation
The elements in an HTML document are arranged one...
What is ssh? How to use? What are the misunderstandings?
Table of contents Preface What is ssh What is ssh...
Super detailed MySQL8.0.22 installation and configuration tutorial
Hello everyone, today we are going to learn about...
Solution to ONLY_FULL_GROUP_BY error in Mysql5.7 and above
Recently, during the development process, the MyS...
Explanation of nginx load balancing and reverse proxy
Table of contents Load Balancing Load balancing c...