Shulou(Shulou.com)06/01 Report--

Releases of MySQL 5.7.8 or later include version tokens, which support the creation and synchronization of server tokens that applications can use to prevent access to incorrect or outdated data.

The version token interface has these characteristics:

. A version token is a pair consisting of a name and a value used as a key or identifier

. Version tokens can be locked. Applications can use token locks to indicate to other collaborative applications that tokens are being used and should not be modified.

. Each server establishes a list of version tokens; for example, specify the server assignment or operation status. In addition, an application communicating with the server can register its own list of tokens that represent the state it needs the server to be in. SQL statements sent by the application to a server that is not in the desired state will generate an error. This is a signal to the application that it should look for another server in the desired state to receive SQL statements.

The following sections describe the components of the version token, discuss how to install and use it, and provide reference information for its components.

Version identification component

A server-side plug-in named version_token holds a list of version tokens associated with the server and subscribes to notifications of statement execution events. The version_token plug-in uses the audit plug-in API to monitor incoming statements from the client and match each client's session-specific version token list with the server version token list. If there is a match, the plug-in allows the statement to pass and the server continues to process it. Otherwise, the plug-in will return an error to the client and the statement will fail.

A set of user-defined functions (udf) provides a sql-level API for manipulating and checking the list of server version tokens maintained by the plug-in. Call any token udf version

The system variable allows the client to specify a list of version tokens for the server status required for registration. If the server is in a different state when the client sends the statement, the client receives an error

Install or uninstall version token

Here is how to install or uninstall version tokens, which are implemented in plug-in library files that contain plug-ins and user-defined functions. General information about installing or uninstalling plug-ins and udf for the server to be available, the plug-in library file must be located in the MySQL plug-in directory (the directory named by the plugin_dir system variable). If necessary, set the value of plugin_dir when the server starts, telling the server plug-in directory location

The basic name of the plug-in library is version_token. The file name suffix varies from platform to platform (for example, for Unix and Unix-like systems, .dll is Windows).

To install the version token plug-in and udf, use install plugin and create a function statement (adjust the .so suffix as needed):

Mysql > INSTALL PLUGIN version_tokens SONAME 'version_token.so';ERROR 2006 (HY000): MySQL server has gone awayNo connection. Trying to reconnect...Connection id: 39Current database: mysqlQuery OK, 0 rows affected (0.07 sec) mysql > CREATE FUNCTION version_tokens_set RETURNS STRING SONAME 'version_token.so';Query OK, 0 rows affected (0.02 sec) mysql > CREATE FUNCTION version_tokens_show RETURNS STRING SONAME' version_token.so';Query OK, 0 rows affected (0.02 sec) mysql > CREATE FUNCTION version_tokens_edit RETURNS STRING SONAME 'version_token.so' Query OK, 0 rows affected (0.02 sec) mysql > CREATE FUNCTION version_tokens_delete RETURNS STRING SONAME 'version_token.so';CREATE FUNCTION version_tokens_lock_shared RETURNS STRING SONAME' version_token.so';Query OK, 0 rows affected (0.02 sec) mysql > CREATE FUNCTION version_tokens_lock_shared RETURNS STRING SONAME 'version_token.so';Query OK, 0 rows affected (0.00 sec) mysql > CREATE FUNCTION version_tokens_lock_exclusive RETURNS STRING SONAME' version_token.so' Query OK, 0 rows affected (0.00 sec) mysql > CREATE FUNCTION version_tokens_unlock RETURNS STRING SONAME 'version_token.so';Query OK, 0 rows affected (0.03 sec)

You must install udf to manage the list of version tokens for the server, but you must also install the plug-in, because udf will not work without it.

If you use plug-ins and udf on the master replication server, install them on all slave servers as well to avoid replication problems

As mentioned earlier, once the installation is complete, the version token plug-in and udf will remain installed until uninstalled. To delete them, use the UNINSTALL plug-in and the DROP FUNCTION statement:

Mysql > uninstall plugin version_tokens;Query OK, 0 rows affected, 1 warning (0.02 sec) mysql > drop function version_tokens_set;drop function version_tokens_show;Query OK, 0 rows affected (0.02 sec) mysql > drop function version_tokens_show;drop function version_tokens_edit;Query OK, 0 rows affected (0.00 sec) mysql > drop function version_tokens_edit;Query OK, 0 rows affected (0.00 sec) mysql > drop function version_tokens_delete Query OK, 0 rows affected (0.01 sec) mysql > drop function version_tokens_lock_shared;Query OK, 0 rows affected (0.00 sec) mysql > drop function version_tokens_lock_exclusive;Query OK, 0 rows affected (0.00 sec) mysql > drop function version_tokens_unlock;Query OK, 0 rows affected (0.02 sec)

Use version markup

One scenario where version tokens can be useful is when the system accesses a collection of MySQL servers, but needs to manage them by monitoring them and adjusting server allocation according to load changes to achieve load balancing. Such a system includes these components

. Collection of MySQL servers to manage

. A management or management application that communicates with servers and organizes them into high availability groups. Groups have different uses, and the servers in each group may have different assignments. The allocation of servers in a group can be changed at any time

. Client applications that access the server to retrieve and update data, and select the server based on the purpose assigned to them. For example, the client should not send updates to the read-only server

Version tokens allow server access to be managed based on allocation without requiring the client to repeatedly query the allocation of the server

. The administrative application performs server assignments and establishes version tokens on each server to reflect its allocation. The application caches this information to provide a central access point to it. If at some point the management application needs to change the server allocation (for example, changing it from write to read only), it will change the server's version token list and update its cache.

. To improve performance, client applications obtain cache information from management applications so that they do not have to retrieve information about server allocations for each statement. Based on the type of statement it will issue (for example, read and write), the client selects the appropriate server and connects to it

. In addition, the client sends its own client-specific version of the token to the server to register the server allocation it needs. For each statement sent by the client to the server, the server compares its own token list with the client token list. If the server token list contains all tokens with the same value in the client token list, there is a match and the server executes the statement

.

On the other hand, the management application may have changed the server assignment and its version token list. In this case, the new server allocation may now be incompatible with client requirements. The tokens between the server and client token list do not match and the server returns an error as a reply to the statement. This instructs the client to refresh its version token information from the management application cache and select a new server to communicate with.

The client logic for detecting version token errors and selecting a new server can be implemented in different ways:

. The client can handle all version token registration, mismatch detection and connection switching on its own.

. The logic for these operations can be implemented in a connector that manages the connection between the client and the MySQL server. Such a connector can handle mismatch error detection and statement retransmission itself, or it can pass the error to the application and leave it to the application to resend the statement.

The following example illustrates the previous discussion in a more specific form.

When a version token is initialized on a given server, the list of version tokens for the server is empty. Token list maintenance is performed by calling a user-defined function (udf). Calling any version token udf requires a super privilege, so administrative or administrative applications with that privilege need to modify the token list.

Suppose a management application communicates with a set of servers that the client queries to access the employee and product databases (named emp and prod, respectively). All servers are allowed to process data retrieval statements, but only some servers are allowed to update the database. To deal with this problem on a database-specific basis, the management application creates a list of version tokens on each server. In the token list for a given server, the token name represents the database name, and the read-write token value depends on whether the database must be read-only or can be read-write.

Client applications register a list of version tokens that they need to match with the server by setting the system variable. Variable setting is done on a client-specific basis, so different clients can register different requirements. By default, the client token list is empty and matches any server token list. "when a client sets its token list to a non-null value, the match may succeed or fail, depending on the server version token list."

To define a list of version tokens for the server, the management application calls version_token_set () UDF. (the udf used to modify and display the list of tokens will be described later.) For example, an application might send these statements to a group of three servers

Server 1:

Mysql > SELECT version_tokens_set ('emp=read;prod=read'); +-- + | version_tokens_set (' emp=read;prod=read') | +-+ | 2 version tokens set. | | +-+ 1 row in set (0.03 sec) |

Server 2:

Mysql > SELECT version_tokens_set ('emp=write;prod=read'); +-- + | version_tokens_set (' emp=write;prod=read') | +-- + | 2 version tokens set. | | +-+ 1 row in set (0.00 sec) |

Server 3:

Mysql > SELECT version_tokens_set ('emp=read;prod=write'); +-- + | version_tokens_set (' emp=read;prod=write') | +-- + | 2 version tokens set. | | +-+ 1 row in set (0.00 sec) |

In each case, the token list is specified as a semicolon-delimited name = value pair list. The resulting token list values cause these server connections:

. Any server will accept reading from either of the two databases

. Only server 2 accepts updates to the emp database

. Only server 3 accepts updates to the prod database

In addition to assigning a list of version tokens to each server, the management application maintains a cache that reflects the server allocation.

Before communicating with the server, the client application contacts the management application and retrieves information about the server allocation. The client then selects the server based on these allocations. Suppose the client wants to read and write to the emp database at the same time. According to the previous allocation, only server 2 is qualified. The client is connected to server 2 and sets the

The version_tokens_session system variable registers server requirements on server 2:

Mysql > SET @ @ session.version_tokens_session = 'emp=write';Query OK, 0 rows affected (0.00 sec)

For subsequent statements sent by the client to server 2, the server compares its own version token list with the client list to see if they match. If so, the statement executes normally:

Mysql > UPDATE emp.employee SET salary = salary * 1.1 WHERE id = 4981 * query OK, 1 row affected (0.07 sec) Rows matched: 1 Changed: 1 Warnings: 0mysql > SELECT last_name, first_name FROM emp.employee WHERE id = 4981 +-+-+ | last_name | first_name | +-+-+ | Smith | Abe | +-+-+ 1 row in set (0.01sec)

Differences between server and client version token lists can occur in two ways:

The token name in the .version _ tokens_ session value does not exist in the server token list. An ER_VTOKEN_PLUGIN_TOKEN_NOT_FOUND error occurs in this case

The token value in the .version _ tokens_ session value is different from the value of the corresponding token in the server token list. An ER_VTOKEN_PLUGIN_TOKEN_MISMATCH error will occur in this case

As long as the allocation of server 2 does not change, the client will continue to use it for reading and writing. However, suppose the management application wants to change the server allocation so that writes to the emp database must be sent to server 1 instead of server 2. To do this, it uses version_tokens_edit () to modify the emp token value on both servers (and update its server allocation cache):

Server 1:

Mysql > SELECT version_tokens_edit ('emp=write'); +-- + | version_tokens_edit (' emp=write') | +-+ | 1 version tokens updated. | | +-+ 1 row in set (0.00 sec) |

Server 2:

Mysql > SELECT version_tokens_edit ('emp=read'); +-- + | version_tokens_edit (' emp=read') | +-+ | 1 version tokens updated. | | +-+ 1 row in set (0.00 sec) |

Version_tokens_edit () modifies the specified token in the server token list while other tokens do not change.

The next time the client sends a statement to server 2, its own token list will no longer match the server token list and an error will occur

Mysql > UPDATE emp.employee SET salary = salary * 1.1ERROR 3136 (42000): Version token mismatch for emp. Correct value read

In this case, the client should contact the management application to get updated information about the server allocation, select a new server, and send the failed statement to the new server

Note: each client must collaborate with the version token and send statements only based on the list of tokens it registers on a given server. For example, if the client registers a list of 'emp=read' tokens, there is nothing in the version token that prevents the client from sending updates to the emp database. Customers themselves must avoid doing so.

The server implicitly uses locks for each statement received from the client, as follows:

. Acquire a shared lock for each token named in the client token list (that is, the version_tokens_ session value)

. Perform a comparison between server and client token lists

. Execute statements or generate errors based on comparison results

. Release lock

The server uses shared locks so that multiple sessions can be compared without blocking, while preventing changes to any session that attempts to acquire an exclusive lock before operating a token with the same name in the server token list

The previous example uses only some of the user definitions contained in the version tag plug-in library, but there are others. A set of udf allows you to manipulate and check the server's list of version tokens. Another set of udf allows you to lock and unlock version tags

These udf allow you to create, change, delete, and check the list of version tags for the server:

Version _ tokens_set () completely replaces the current list and assigns a new list. A parameter is a semicolon-separated list of name = value pairs.

Version _ tokens_edit () supports partial modifications to the current list. It can add a new token or change the value of an existing token. Parameter is a semicolon-delimited list of name = value pairs

.version _ tokens_delete () removes the token from the current list. Parameter is a semicolon-separated list of token names

.version _ tokens_show () displays the current token list. No argument is needed.

Each of these functions, if successful, returns a binary string indicating that the operation occurred. The following example establishes a list of server tokens, modifies them by adding new tokens, removes some tokens, and displays a list of generated tokens:

Mysql > SELECT version_tokens_set ('tok1=a;tok2=b'); +-- + | version_tokens_set (' tok1=a;tok2=b') | +-+ | 2 version tokens set. | | +-+ 1 row in set (0.00 sec) mysql > SELECT version_tokens_edit ('tok3=c') | +-- + | version_tokens_edit ('tok3=c') | +-+ | 1 version tokens updated. | | +-+ 1 row in set (0.00 sec) mysql > SELECT version_tokens_delete ('tok2;tok1'); +-- + | version_tokens_delete (' tok2) | Tok1') | +-+ | 2 version tokens deleted. | | +-+ 1 row in set (0.00 sec) mysql > SELECT version_tokens_show (); +-- + | version_tokens_show () | +-- + | tok3=c | | | +-+ 1 row in set (0.00 sec) |

"if the token list is not formatted correctly, a warning appears:"

Mysql > SELECT version_tokens_set ('tok1=a; = c'); +-- + | version_tokens_set ('tok1=a; = c') | +-- + | 1 version tokens set. | +-+ 1 row in set, 1 warning (0.00 sec) mysql > show warnings\ row * 1. Row * * Level: Warning Code: 42000Message: Invalid version token pair encountered. The list provided is only partially updated.1 row in set (0.00 sec)

As mentioned earlier, the version tag is defined using a semicolon-separated list of name = value pairs. Consider the call to version_tokens_set ():

Mysql > SELECT version_tokens_set ('tok1=b;;; tok2= a = b; tok1= 1\' 2 3 "4'); +-- + | version_tokens_set ('tok1=b;;; tok2= a = b) Tok1 = 1\'2 3 "4') | +-+ | 3 version tokens set. | | +-+ 1 row in set (0.00 sec) |

The version tag explains the parameters as follows:

. Whitespace around the name and value is ignored. Spaces are allowed in names and values. (for version_tokens_delete (), it accepts a list of names with no values, and the whitespace around the name is ignored.)

. There is no quotation mechanism

. The order of tokens does not matter, unless the token list contains multiple instances of a given token name, otherwise the last value takes precedence over the previous value

According to these rules, the previous call to version_tokens_set () produces a list of tokens with two tokens: the value of tok1 is 1, 2, 3, 4, and tok2 is a = b.

Mysql > SELECT version_tokens_show (); +-- + | version_tokens_show () | +-- + | tok2=a = bscape tok1 / 2 / 3 "4; | +-+ 1 row in set (0.01 sec)

If the token list contains two tokens, why does version_tokens_set () return the set value of version 3 tokens? This is because the original token list contains two tok1 definitions, while the second definition replaces the first definition.

The version tag token operation udf places these constraints on the token name and value:

. Token name cannot contain = or; characters, with a maximum length of 64 characters

. The token value cannot contain the; character. The length of the value is limited by the value of the max_allowed_packet system variable

. Version tokens treat token names and values as binary strings, so comparisons are case-sensitive

Version tokens also include a set of udf that allows tokens to be locked and unlocked:

.version _ tokens_lock_exclusive () acquires the exclusive version token lock. It accepts a list of one or more lock names and timeout values

.version _ tokens_lock_shared () gets the shared version token lock. It accepts a list of one or more lock names and timeout values

Version _ tokens_unlock () releases version token locks (exclusive and shared). No argument is needed.

Each lock function returns a non-zero to indicate success. Otherwise, an error will occur:

Mysql > SELECT version_tokens_lock_shared ('lock1',' lock2', 0) +-+ | version_tokens_lock_shared ('lock1',' lock2' 0) | +-- + | 1 | +-- + mysql > SELECT version_tokens_lock_shared (NULL, 0) ERROR 3131 (42000): Incorrect locking service lock name'(null)'.

Locking using the version tag locking feature is recommended; the application must agree to cooperate.

You can lock a token name that does not exist. This does not create a token.

For the version token locking function, the token name parameter is used exactly as specified. The surrounding white space is not ignored, and the = and; characters are allowed. This is because tokens are just like passing

Pass the name of the token to be locked as to the locking service.

Version tag reference

Version marking function

The version token plug-in library contains several user-defined functions. A set of udf allows you to manipulate and check the server's list of version tokens. Another set of udf allows you to lock and unlock version tags. Calling any version token UDF requires super privileges.

The following udf allows you to create, change, delete, and check the list of version tokens for the server:

.version _ tokens_delete (name_list)

Use the name_list parameter to remove the token from the server's list of version tokens and return a binary string indicating the result of the operation. Name_list is a semicolon-delimited list of version token names to delete.

Mysql > SELECT version_tokens_delete ('tok1;tok3'); +-- + | version_tokens_delete (' tok1;tok3') | +-+ | 2 version tokens deleted. | | +-+ 1 row in set (0.00 sec) |

Starting with MySQL 5.7.9, the NULL parameter is treated as an empty string, which has no effect on the token list.

Version_tokens_delete () removes the tags specified in its parameters, if they exist. (it is not an error to delete a token that does not exist.) To completely clear the token list without knowing which tokens are in the list, pass NULL or a string that does not contain tokens to version_tokens_set ():

Mysql > SELECT version_tokens_set (NULL); +-- + | version_tokens_set (NULL) | +-- + | Version tokens list cleared. | | +-+ 1 row in set (0.00 sec) mysql > SELECT version_tokens_set ('') | +-- + | version_tokens_set ('') | +-- + | Version tokens list cleared. | | +-+ 1 row in set (0.00 sec) |

.version _ tokens_edit (token_list)

Use the token_list parameter to modify the server's version token list and return a binary string indicating the result of the operation. Token_list is a semicolon-delimited list of name = value pairs that specifies the name and value of each token to be defined. If a token exists, its value is updated with the given value. If the tag does not exist, it is created with the given value. If the parameter is NULL or the string does not contain tokens, the token list remains unchanged.

Mysql > SELECT version_tokens_set ('tok1=value1;tok2=value2'); +-- + | version_tokens_set (' tok1=value1;tok2=value2') | +-- + | 2 version tokens set. | | +-- + 1 row in set (0.00 sec) mysql > SELECT version_tokens_edit ('tok2=new_value2;tok3=new_value3') | +-+ | version_tokens_edit ('tok2=new_value2 Tok3=new_value3') | +-- + | 2 version tokens updated. | | +-+ 1 row in set (0.00 sec) |

.version _ tokens_set (token_list)

Replaces the server's version token list with the token defined in the token_list parameter and returns a binary string indicating the result of the operation. Token_list is a semicolon-delimited list of name = value pairs that specifies the name and value of each token to be defined. If the parameter is empty or the string does not contain tokens, clear the token list.

Mysql > SELECT version_tokens_set ('tok1=value1;tok2=value2'); +-- + | version_tokens_set (' tok1=value1;tok2=value2') | +-- + | 2 version tokens set. | | +-+ 1 row in set (0.00 sec) |

.version _ tokens_show ()

Returns a list of version tags for the server as a binary string containing a semicolon-delimited list of name = value pairs.

Mysql > SELECT version_tokens_show (); +-- + | version_tokens_show () | +-- + | tok2=value2;tok1=value1; | +-- + 1 row in set (0.00 sec)

The following udf allows you to lock and unlock version tags:

.version _ tokens_lock_exclusive (token_name [token_name]... Timeout)

Gets an exclusive lock on one or more version tokens (specified as a string by name). If the lock is not acquired within the given timeout value, it times out and reports an error

Mysql > SELECT version_tokens_lock_exclusive ('lock1',' lock2', 10) +-+ | version_tokens_lock_exclusive ('lock1',' lock2' 10) | +-+ | 1 | +-+

This function was added in MySQL 5.7.8, named vtoken_get_write_locks (), and renamed to version_tokens_lock_exclusive () in 5.7.9.

.version _ tokens_lock_shared (token_name [token_name]... Timeout)

Gets a shared lock on one or more version tokens (specified as a string by name). If the lock is not acquired within the given timeout value, it times out and reports an error

Mysql > SELECT version_tokens_lock_shared ('lock1',' lock2', 10) +-+ | version_tokens_lock_shared ('lock1',' lock2' 10) | +-+ | 1 | +-+

This function was added in MySQL 5.7.8, named vtoken_get_read_locks (), and renamed to version_tokens_lock_shared () in 5.7.9.

.version _ tokens_unlock ()

Use version_tokens_lock_exclusive () and version_tokens_lock_shared () to release all locks acquired in the current session.

Mysql > SELECT version_tokens_unlock (); +-+ | version_tokens_unlock () | +-+ | 1 | +-+

This function was added in MySQL 5.7.8, named vtoken_release_locks (), and renamed to version_tokens_unlock () in 5.7.9.

The locking feature shares these features:

. For success, the return value is non-zero. Otherwise, an error will occur

. Token name is a string

. Unlike the parameter handling of the udf that manipulates the server token list, the whitespace around the token name parameter is not ignored and the = and; characters are allowed

. You can lock a token name that does not exist. This does not create a token

. The timeout value is a non-negative integer that represents the time in seconds it takes to wait for the lock to be acquired before an error timeout occurs. If the timeout is 0, there is no wait, and if the lock cannot be acquired immediately, the function will generate an error

. The version token locking feature is described in the Section 28.3.1 lock service based on the lock service described.

Version token system variabl

The version tag supports the following system variables. These variables are not available unless the version token plug-in is installed

System variable:

.version _ tokens_session

Command line format:-- version-tokens-session=value

Variable range: Global,Session

Dynamic: Yes

Allowed values: type is string, default is NULL

The session value of this variable specifies the client version token list and indicates the token that the client session requires the server version token list to have.

"if the version_tokens_session variable is NULL (the default) or the value is empty, any server version token list matches." (in fact, null values disable matching requirements.)

If the version_tokens_session variable has a non-null value, any mismatch between its value and the list of server version tokens will cause any statement sent by the session to the server to go wrong. In this case, a mismatch can occur:

The token name in the .version _ tokens_ session value does not exist in the server token list. In this example, an ER_VTOKEN_PLUGIN_TOKEN_NOT_FOUND error occurred

The token value in the .version _ tokens_ session value is different from the value of the corresponding token in the server token list. In this example, an ER_VTOKEN_PLUGIN_TOKEN_MISMATCH error will occur

It is not mismatched that the server version token list contains tokens that are not named in the version_tokens_session value

Suppose a management application sets the list of server tokens as follows:

Mysql > SELECT version_tokens_set ('tok1=a;tok2=b;tok3=c'); +-+ | version_tokens_set (' tok1=a;tok2=b;tok3=c') | +-+ | 3 version tokens set. | | +-+

The client registers the token it needs to match with the server by setting its version_tokens_session value. Then, for each subsequent statement sent by the client, the server will, according to the customer

The end version_tokens_ session value checks its token list, and if there is a mismatch, an error is generated:

Mysql > SET @ @ session.version_tokens_session = 'tok1=a;tok2=b';mysql > SELECT 1 + | 1 | +-- + | 1 | +-- + mysql > SET @ @ session.version_tokens_session =' tok1=b';mysql > SELECT 1 political error 3136 (42000): Version token mismatch for tok1. Correct value a

The first choice was successful because the client tokens tok1 and tok2 appear in the server token list, and each token has the same value in the server list. The second SELECT fails because, although tok1 appears in the server token list, its value is different from the value specified by the client

At this point, any statements sent by the client will fail unless the server token list changes to match again. Suppose the management application changes the server token list as follows:

Mysql > SELECT version_tokens_edit ('tok1=b'); +-- + | version_tokens_edit (' tok1=b') | +-+ | 1 version tokens updated. | | +-- + mysql > SELECT version_tokens_show (); +-+ | version_tokens_show () | +-- + | tok3=c;tok1=b;tok2=b; | +-+ |

Now that the client version_tokens_ session value matches the server token list, the client can successfully execute the statement again:

Mysql > SELECT 1 + | 1 | +-+ | 1 | +-+

This variable is added in MySQL 5.7.8

.version _ tokens_session_number

Command line format:-- version-tokens-session-number=N

Variable range: Global,Session

Dynamic: NO

Allowed values: type is integer, default is 0

This variable is for internal use.

This variable was added in MySQL 5.7.8.

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.