Movatterモバイル変換

--show-replica-auth-info

Use--show-replica-auth-info, which displays replication user names and passwords in the output ofSHOW REPLICAS on the source for replicas started with the--report-user and--report-password options.

--show-slave-auth-info

Deprecated alias for--show-replica-auth-info.

auto_increment_increment

auto_increment_increment andauto_increment_offset are intended for use with circular (source-to-source) replication, and can be used to control the operation ofAUTO_INCREMENT columns. Both variables have global and session values, and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the value of either of these two variables to an integer greater than 65,535 or less than 0 causes its value to be set to 65,535 instead. Attempting to set the value ofauto_increment_increment orauto_increment_offset to a noninteger value produces an error, and the actual value of the variable remains unchanged.

When Group Replication is started on a server, the value ofauto_increment_increment is changed to the value ofgroup_replication_auto_increment_increment, which defaults to 7, and the value ofauto_increment_offset is changed to the server ID. The changes are reverted when Group Replication is stopped. These changes are only made and reverted ifauto_increment_increment andauto_increment_offset each have their default value of 1. If their values have already been modified from the default, Group Replication does not alter them. These system variables are also not modified when Group Replication is in single-primary mode, where only one server writes.

auto_increment_increment andauto_increment_offset affectAUTO_INCREMENT column behavior as follows:

If either of these variables is changed, and then new rows inserted into a table containing anAUTO_INCREMENT column, the results may seem counterintuitive because the series ofAUTO_INCREMENT values is calculated without regard to any values already present in the column, and the next value inserted is the least value in the series that is greater than the maximum existing value in theAUTO_INCREMENT column. The series is calculated like this:

auto_increment_offset +N ×auto_increment_increment

whereN is a positive integer value in the series [1, 2, 3, ...]. For example:

mysql> SHOW VARIABLES LIKE 'auto_inc%';+--------------------------+-------+| Variable_name            | Value |+--------------------------+-------+| auto_increment_increment | 10    || auto_increment_offset    | 5     |+--------------------------+-------+2 rows in set (0.00 sec)mysql> SELECT col FROM autoinc1;+-----+| col |+-----+|   1 ||  11 ||  21 ||  31 |+-----+4 rows in set (0.00 sec)mysql> INSERT INTO autoinc1 VALUES (NULL), (NULL), (NULL), (NULL);Query OK, 4 rows affected (0.00 sec)Records: 4  Duplicates: 0  Warnings: 0mysql> SELECT col FROM autoinc1;+-----+| col |+-----+|   1 ||  11 ||  21 ||  31 ||  35 ||  45 ||  55 ||  65 |+-----+8 rows in set (0.00 sec)

The values shown forauto_increment_increment andauto_increment_offset generate the series 5 +N × 10, that is, [5, 15, 25, 35, 45, ...]. The highest value present in thecol column prior to theINSERT is 31, and the next available value in theAUTO_INCREMENT series is 35, so the inserted values forcol begin at that point and the results are as shown for theSELECT query.

It is not possible to restrict the effects of these two variables to a single table; these variables control the behavior of allAUTO_INCREMENT columns inall tables on the MySQL server. If the global value of either variable is set, its effects persist until the global value is changed or overridden by setting the session value, or untilmysqld is restarted. If the local value is set, the new value affectsAUTO_INCREMENT columns for all tables into which new rows are inserted by the current user for the duration of the session, unless the values are changed during that session.

The default value ofauto_increment_increment is 1. SeeSection 19.5.1.1, “Replication and AUTO_INCREMENT”.

auto_increment_offset

This variable has a default value of 1. If it is left with its default value, and Group Replication is started on the server in multi-primary mode, it is changed to the server ID. For more information, see the description forauto_increment_increment.

immediate_server_version

For internal use by replication. This session system variable holds the MySQL Server release number of the server that is the immediate source in a replication topology (for example,90200 for a MySQL 9.2.0 server instance). If this immediate server is at a release that does not support the session system variable, the value of the variable is set to 0 (UNKNOWN_SERVER_VERSION).

The value of the variable is replicated from a source to a replica. With this information the replica can correctly process data originating from a source at an older release, by recognizing where syntax changes or semantic changes have occurred between the releases involved and handling these appropriately. The information can also be used in a Group Replication environment where one or more members of the replication group is at a newer release than the others. The value of the variable can be viewed in the binary log for each transaction (as part of theGtid_log_event, orAnonymous_gtid_log_event if GTIDs are not in use on the server), and could be helpful in debugging cross-version replication issues.

Setting the session value of this system variable is a restricted operation. The session user must have either theREPLICATION_APPLIER privilege (seeSection 19.3.3, “Replication Privilege Checks”), or privileges sufficient to set restricted session variables (seeSection 7.1.9.1, “System Variable Privileges”). However, note that the variable is not intended for users to set; it is set automatically by the replication infrastructure.

original_server_version

For internal use by replication. This session system variable holds the MySQL Server release number of the server where a transaction was originally committed (for example,90200 for a MySQL 9.2.0 server instance). If this original server is at a release that does not support the session system variable, the value of the variable is set to 0 (UNKNOWN_SERVER_VERSION). Note that when a release number is set by the original server, the value of the variable is reset to 0 if the immediate server or any other intervening server in the replication topology does not support the session system variable, and so does not replicate its value.

The value of the variable is set and used in the same ways as for theimmediate_server_version system variable. If the value of the variable is the same as that for theimmediate_server_version system variable, only the latter is recorded in the binary log, with an indicator that the original server version is the same.

In a Group Replication environment, view change log events, which are special transactions queued by each group member when a new member joins the group, are tagged with the server version of the group member queuing the transaction. This ensures that the server version of the original donor is known to the joining member. Because the view change log events queued for a particular view change have the same GTID on all members, for this case only, instances of the same GTID might have a different original server version.

Setting the session value of this system variable is a restricted operation. The session user must have either theREPLICATION_APPLIER privilege (seeSection 19.3.3, “Replication Privilege Checks”), or privileges sufficient to set restricted session variables (seeSection 7.1.9.1, “System Variable Privileges”). However, note that the variable is not intended for users to set; it is set automatically by the replication infrastructure.

rpl_semi_sync_master_enabled

Deprecated synonym forrpl_semi_sync_source_enabled.

rpl_semi_sync_master_timeout

Deprecated synonym forrpl_semi_sync_source_timeout.

rpl_semi_sync_master_trace_level

Deprecated synonym forrpl_semi_sync_source_trace_level.

rpl_semi_sync_master_wait_for_slave_count

Deprecated synonym forrpl_semi_sync_source_wait_for_replica_count.

rpl_semi_sync_master_wait_no_slave

Deprecated synonym forrpl_semi_sync_source_wait_no_replica.

rpl_semi_sync_master_wait_point

Deprecated synonym forrpl_semi_sync_source_wait_point.

rpl_semi_sync_source_enabled

rpl_semi_sync_source_enabled is available when therpl_semi_sync_source (semisync_source.so library) plugin was installed on the replica to set up semisynchronous replication.

rpl_semi_sync_source_enabled controls whether semisynchronous replication is enabled on the source server. To enable or disable the plugin, set this variable toON orOFF (or 1 or 0), respectively. The default isOFF.

rpl_semi_sync_source_timeout

rpl_semi_sync_source_timeout is available when therpl_semi_sync_source (semisync_source.so library) plugin is installed on the replica.

rpl_semi_sync_source_timeout controls how long the source waits on a commit for acknowledgment from a replica before timing out and reverting to asynchronous replication. The value is specified in milliseconds, and the default value is 10000 (10 seconds).

rpl_semi_sync_source_trace_level

rpl_semi_sync_source_trace_level is available when therpl_semi_sync_source (semisync_source.so library) plugin is installed on the replica.

rpl_semi_sync_source_trace_level specifies the semisynchronous replication debug trace level on the source server. Four levels are defined:

rpl_semi_sync_source_wait_for_replica_count

rpl_semi_sync_source_wait_for_replica_count is available when therpl_semi_sync_source (semisync_source.so library) plugin is installed on the replica to set up semisynchronous replication.

rpl_semi_sync_source_wait_for_replica_count specifies the number of replica acknowledgments the source must receive per transaction before proceeding. By defaultrpl_semi_sync_source_wait_for_replica_count is1, meaning that semisynchronous replication proceeds after receiving a single replica acknowledgment. Performance is best for small values of this variable.

For example, ifrpl_semi_sync_source_wait_for_replica_count is2, then 2 replicas must acknowledge receipt of the transaction before the timeout period configured byrpl_semi_sync_source_timeout for semisynchronous replication to proceed. If fewer replicas acknowledge receipt of the transaction during the timeout period, the source reverts to normal replication.

rpl_semi_sync_source_wait_no_replica

rpl_semi_sync_source_wait_no_replica is available when therpl_semi_sync_source (semisync_source.so library) plugin is installed on the replica.

rpl_semi_sync_source_wait_no_replica controls whether the source waits for the timeout period configured byrpl_semi_sync_source_timeout to expire, even if the replica count drops to less than the number of replicas configured byrpl_semi_sync_source_wait_for_replica_count during the timeout period.

When the value ofrpl_semi_sync_source_wait_no_replica isON (the default), it is permissible for the replica count to drop to less thanrpl_semi_sync_source_wait_for_replica_count during the timeout period. As long as enough replicas acknowledge the transaction before the timeout period expires, semisynchronous replication continues.

When the value ofrpl_semi_sync_source_wait_no_replica isOFF, if the replica count drops to less than the number configured inrpl_semi_sync_source_wait_for_replica_count at any time during the timeout period configured byrpl_semi_sync_source_timeout, the source reverts to normal replication.

rpl_semi_sync_source_wait_point

rpl_semi_sync_source_wait_point is available when therpl_semi_sync_source (semisync_source.so library) plugin is installed on the replica.

rpl_semi_sync_source_wait_point controls the point at which a semisynchronous replication source server waits for replica acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:

The replication characteristics of these settings differ as follows: