Rechercher dans le manuel MySQL
17.1.6.2 Replication Master Options and Variables
This section describes the server options and system variables
that you can use on replication master servers. You can specify
the options either on the
command line or in an
option file. You can specify
system variable values using
SET
.
On the master and each slave, you must use the
server-id
option to establish a
unique replication ID. For each server, you should pick a unique
positive integer in the range from 1 to
232 − 1, and each ID must be
different from every other ID in use by any other replication
master or slave. Example: server-id=3
.
For options used on the master for controlling binary logging, see Section 17.1.6.4, “Binary Logging Options and Variables”.
Startup Options for Replication Masters
The following list describes startup options for controlling replication master servers. Replication-related system variables are discussed later in this section.
-
Property Value Command-Line Format --show-slave-auth-info[={OFF|ON}]
Type Boolean Default Value OFF
Display slave user names and passwords in the output of
SHOW SLAVE HOSTS
on the master server for slaves started with the--report-user
and--report-password
options.
The following system variables are used for or by replication masters:
-
Property Value Command-Line Format --auto-increment-increment=#
System Variable auto_increment_increment
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesYes Type Integer Default Value 1
Minimum Value 1
Maximum Value 65535
auto_increment_increment
andauto_increment_offset
are intended for use with master-to-master 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.As of MySQL 8.0.14, setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
Noteauto_increment_increment
is also supported for use withNDB
tables.When Group Replication is started on a server, the value of
auto_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. From MySQL 8.0, the 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:auto_increment_increment
controls the interval between successive column values. For example:- +--------------------------+-------+
- +--------------------------+-------+
- | auto_increment_increment | 1 |
- | auto_increment_offset | 1 |
- +--------------------------+-------+
- Query OK, 0 rows affected (0.04 sec)
- Query OK, 0 rows affected (0.00 sec)
- +--------------------------+-------+
- +--------------------------+-------+
- | auto_increment_increment | 10 |
- | auto_increment_offset | 1 |
- +--------------------------+-------+
- Query OK, 4 rows affected (0.00 sec)
- +-----+
- | col |
- +-----+
- | 1 |
- | 11 |
- | 21 |
- | 31 |
- +-----+
auto_increment_offset
determines the starting point for theAUTO_INCREMENT
column value. Consider the following, assuming that these statements are executed during the same session as the example given in the description forauto_increment_increment
:- Query OK, 0 rows affected (0.00 sec)
- +--------------------------+-------+
- +--------------------------+-------+
- | auto_increment_increment | 10 |
- | auto_increment_offset | 5 |
- +--------------------------+-------+
- Query OK, 0 rows affected (0.06 sec)
- Query OK, 4 rows affected (0.00 sec)
- +-----+
- | col |
- +-----+
- | 5 |
- | 15 |
- | 25 |
- | 35 |
- +-----+
When the value of
auto_increment_offset
is greater than that ofauto_increment_increment
, the value ofauto_increment_offset
is ignored.
If either of these variables is changed, and then new rows inserted into a table containing an
AUTO_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
where
N
is a positive integer value in the series [1, 2, 3, ...]. For example:- +--------------------------+-------+
- +--------------------------+-------+
- | auto_increment_increment | 10 |
- | auto_increment_offset | 5 |
- +--------------------------+-------+
- +-----+
- | col |
- +-----+
- | 1 |
- | 11 |
- | 21 |
- | 31 |
- +-----+
- Query OK, 4 rows affected (0.00 sec)
- +-----+
- | col |
- +-----+
- | 1 |
- | 11 |
- | 21 |
- | 31 |
- | 35 |
- | 45 |
- | 55 |
- | 65 |
- +-----+
The values shown for
auto_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 all
AUTO_INCREMENT
columns in all 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 until mysqld 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 of
auto_increment_increment
is 1. See Section 17.4.1.1, “Replication and AUTO_INCREMENT”. -
Property Value Command-Line Format --auto-increment-offset=#
System Variable auto_increment_offset
Scope Global, Session Dynamic Yes SET_VAR
Hint AppliesYes Type Integer Default Value 1
Minimum Value 1
Maximum Value 65535
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 for
auto_increment_increment
.As of MySQL 8.0.14, setting the session value of this system variable is a restricted operation. The session user must have privileges sufficient to set restricted session variables. See Section 5.1.9.1, “System Variable Privileges”.
Noteauto_increment_offset
is also supported for use withNDB
tables. -
Property Value Introduced 8.0.14 System Variable immediate_server_version
Scope Session Dynamic Yes SET_VAR
Hint AppliesNo Type Integer For internal use by replication. This session system variable holds the MySQL Server release number of the server that is the immediate master in a replication topology (for example,
80014
for a MySQL 8.0.14 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 master to a slave. With this information the slave can correctly process data originating from a master 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 the
Gtid_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. See Section 5.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.
-
Property Value Introduced 8.0.14 System Variable original_server_version
Scope Session Dynamic Yes SET_VAR
Hint AppliesNo Type Integer 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,
80014
for a MySQL 8.0.14 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 the
immediate_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. See Section 5.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.
-
Property Value Command-Line Format --rpl-semi-sync-master-enabled[={OFF|ON}]
System Variable rpl_semi_sync_master_enabled
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value OFF
Controls whether semisynchronous replication is enabled on the master. To enable or disable the plugin, set this variable to
ON
orOFF
(or 1 or 0), respectively. The default isOFF
.This variable is available only if the master-side semisynchronous replication plugin is installed.
-
Property Value Command-Line Format --rpl-semi-sync-master-timeout=#
System Variable rpl_semi_sync_master_timeout
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 10000
A value in milliseconds that controls how long the master waits on a commit for acknowledgment from a slave before timing out and reverting to asynchronous replication. The default value is 10000 (10 seconds).
This variable is available only if the master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_trace_level
Property Value Command-Line Format --rpl-semi-sync-master-trace-level=#
System Variable rpl_semi_sync_master_trace_level
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 32
The semisynchronous replication debug trace level on the master. Four levels are defined:
1 = general level (for example, time function failures)
16 = detail level (more verbose information)
32 = net wait level (more information about network waits)
64 = function level (information about function entry and exit)
This variable is available only if the master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_wait_for_slave_count
Property Value Command-Line Format --rpl-semi-sync-master-wait-for-slave-count=#
System Variable rpl_semi_sync_master_wait_for_slave_count
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Integer Default Value 1
Minimum Value 1
Maximum Value 65535
The number of slave acknowledgments the master must receive per transaction before proceeding. By default
rpl_semi_sync_master_wait_for_slave_count
is1
, meaning that semisynchronous replication proceeds after receiving a single slave acknowledgment. Performance is best for small values of this variable.For example, if
rpl_semi_sync_master_wait_for_slave_count
is2
, then 2 slaves must acknowledge receipt of the transaction before the timeout period configured byrpl_semi_sync_master_timeout
for semisynchronous replication to proceed. If less slaves acknowledge receipt of the transaction during the timeout period, the master reverts to normal replication.NoteThis behavior also depends on
rpl_semi_sync_master_wait_no_slave
This variable is available only if the master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_wait_no_slave
Property Value Command-Line Format --rpl-semi-sync-master-wait-no-slave[={OFF|ON}]
System Variable rpl_semi_sync_master_wait_no_slave
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Boolean Default Value ON
Controls whether the master waits for the timeout period configured by
rpl_semi_sync_master_timeout
to expire, even if the slave count drops to less than the number of slaves configured byrpl_semi_sync_master_wait_for_slave_count
during the timeout period.When the value of
rpl_semi_sync_master_wait_no_slave
isON
(the default), it is permissible for the slave count to drop to less thanrpl_semi_sync_master_wait_for_slave_count
during the timeout period. As long as enough slaves acknowledge the transaction before the timeout period expires, semisynchronous replication continues.When the value of
rpl_semi_sync_master_wait_no_slave
isOFF
, if the slave count drops to less than the number configured inrpl_semi_sync_master_wait_for_slave_count
at any time during the timeout period configured byrpl_semi_sync_master_timeout
, the master reverts to normal replication.This variable is available only if the master-side semisynchronous replication plugin is installed.
rpl_semi_sync_master_wait_point
Property Value Command-Line Format --rpl-semi-sync-master-wait-point=value
System Variable rpl_semi_sync_master_wait_point
Scope Global Dynamic Yes SET_VAR
Hint AppliesNo Type Enumeration Default Value AFTER_SYNC
Valid Values AFTER_SYNC
AFTER_COMMIT
This variable controls the point at which a semisynchronous replication master waits for slave acknowledgment of transaction receipt before returning a status to the client that committed the transaction. These values are permitted:
AFTER_SYNC
(the default): The master writes each transaction to its binary log and the slave, and syncs the binary log to disk. The master waits for slave acknowledgment of transaction receipt after the sync. Upon receiving acknowledgment, the master commits the transaction to the storage engine and returns a result to the client, which then can proceed.AFTER_COMMIT
: The master writes each transaction to its binary log and the slave, syncs the binary log, and commits the transaction to the storage engine. The master waits for slave acknowledgment of transaction receipt after the commit. Upon receiving acknowledgment, the master returns a result to the client, which then can proceed.
The replication characteristics of these settings differ as follows:
With
AFTER_SYNC
, all clients see the committed transaction at the same time: After it has been acknowledged by the slave and committed to the storage engine on the master. Thus, all clients see the same data on the master.In the event of master failure, all transactions committed on the master have been replicated to the slave (saved to its relay log). A crash of the master and failover to the slave is lossless because the slave is up to date. Note, however, that the master cannot be restarted in this scenario and must be discarded, because its binary log might contain uncommitted transactions that would cause a conflict with the slave when externalized after binary log recovery.
With
AFTER_COMMIT
, the client issuing the transaction gets a return status only after the server commits to the storage engine and receives slave acknowledgment. After the commit and before slave acknowledgment, other clients can see the committed transaction before the committing client.If something goes wrong such that the slave does not process the transaction, then in the event of a master crash and failover to the slave, it is possible that such clients will see a loss of data relative to what they saw on the master.
This variable is available only if the master-side semisynchronous replication plugin is installed.
With the addition of
rpl_semi_sync_master_wait_point
in MySQL 5.7, a version compatibility constraint was created because it increments the semisynchronous interface version: Servers for MySQL 5.7 and higher do not work with semisynchronous replication plugins from older versions, nor do servers from older versions work with semisynchronous replication plugins for MySQL 5.7 and higher.
Nederlandse vertaling
U hebt gevraagd om deze site in het Nederlands te bezoeken. Voor nu wordt alleen de interface vertaald, maar nog niet alle inhoud.Als je me wilt helpen met vertalingen, is je bijdrage welkom. Het enige dat u hoeft te doen, is u op de site registreren en mij een bericht sturen waarin u wordt gevraagd om u toe te voegen aan de groep vertalers, zodat u de gewenste pagina's kunt vertalen. Een link onderaan elke vertaalde pagina geeft aan dat u de vertaler bent en heeft een link naar uw profiel.
Bij voorbaat dank.
Document heeft de 26/06/2006 gemaakt, de laatste keer de 26/10/2018 gewijzigd
Bron van het afgedrukte document:https://www.gaudry.be/nl/mysql-rf-replication-options-master.html
De infobrol is een persoonlijke site waarvan de inhoud uitsluitend mijn verantwoordelijkheid is. De tekst is beschikbaar onder CreativeCommons-licentie (BY-NC-SA). Meer info op de gebruiksvoorwaarden en de auteur.
Referenties
Deze verwijzingen en links verwijzen naar documenten die geraadpleegd zijn tijdens het schrijven van deze pagina, of die aanvullende informatie kunnen geven, maar de auteurs van deze bronnen kunnen niet verantwoordelijk worden gehouden voor de inhoud van deze pagina.
De auteur Deze site is als enige verantwoordelijk voor de manier waarop de verschillende concepten, en de vrijheden die met de referentiewerken worden genomen, hier worden gepresenteerd. Vergeet niet dat u meerdere broninformatie moet doorgeven om het risico op fouten te verkleinen.