MySQL 8.0 Reference Manual Including MySQL NDB Cluster 8.0
This section explains the server options and system variables that apply to slave replication servers and contains the following:
Specify the options either on the
command line or in an
option file. Many of the
options can be set while the server is running by using the
CHANGE MASTER TO
statement. Specify
system variable values using
SET
.
Server ID.
On the master and each slave, you must set the
server_id
system variable to
establish a unique replication ID in the range from 1 to
232 − 1. “Unique”
means that each ID must be different from every other ID in use
by any other replication master or slave. Example
my.cnf
file:
[mysqld] server-id=3
This section explains startup options for controlling
replication slave servers. Many of these options can be set
while the server is running by using the
CHANGE MASTER TO
statement.
Others, such as the --replicate-*
options, can
be set only when the slave server starts. Replication-related
system variables are discussed later in this section.
Property | Value |
---|---|
Command-Line Format | --master-info-file=file_name |
Deprecated | 8.0.18 |
Type | File name |
Default Value | master.info |
The name for the master info log, if
master_info_repository=FILE
is set. The default name is master.info
in the data directory.
--master-info-file
and the
setting
master_info_repository=FILE
are deprecated because the use of a file for the master info
log has been superseded by crash-safe slave tables. For
information about the master info log, see
Section 17.2.4.2, “Slave Status Logs”.
Property | Value |
---|---|
Command-Line Format | --master-retry-count=# |
Deprecated | Yes |
Type | Integer |
Default Value | 86400 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The number of times that the slave tries to reconnect to the
master before giving up. The default value is 86400 times. A
value of 0 means “infinite”, and the slave
attempts to connect forever. Reconnection attempts are
triggered when the slave reaches its connection timeout
(specified by the
slave_net_timeout
system
variable) without receiving data or a heartbeat signal from
the master. Reconnection is attempted at intervals set by
the MASTER_CONNECT_RETRY
option of the
CHANGE MASTER TO
statement
(which defaults to every 60 seconds).
This option is deprecated and will be removed in a future
MySQL release. Use the MASTER_RETRY_COUNT
option of the CHANGE MASTER
TO
statement instead.
Property | Value |
---|---|
Command-Line Format | --max-relay-log-size=# |
System Variable | max_relay_log_size |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 1073741824 |
The size at which the server rotates relay log files
automatically. If this value is nonzero, the relay log is
rotated automatically when its size exceeds this value. If
this value is zero (the default), the size at which relay
log rotation occurs is determined by the value of
max_binlog_size
. For more
information, see Section 17.2.4.1, “The Slave Relay Log”.
Property | Value |
---|---|
Command-Line Format | --relay-log-purge[={OFF|ON}] |
System Variable | relay_log_purge |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | ON |
Disable or enable automatic purging of relay logs as soon as
they are no longer needed. The default value is 1 (enabled).
This is a global variable that can be changed dynamically
with SET GLOBAL relay_log_purge =
. Disabling purging of
relay logs when enabling the
N
--relay-log-recovery
option
risks data consistency and is therefore not crash-safe.
Property | Value |
---|---|
Command-Line Format | --relay-log-space-limit=# |
System Variable | relay_log_space_limit |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
This option places an upper limit on the total size in bytes
of all relay logs on the slave. A value of 0 means “no
limit”. This is useful for a slave server host that
has limited disk space. When the limit is reached, the I/O
thread stops reading binary log events from the master
server until the SQL thread has caught up and deleted some
unused relay logs. Note that this limit is not absolute:
There are cases where the SQL thread needs more events
before it can delete relay logs. In that case, the I/O
thread exceeds the limit until it becomes possible for the
SQL thread to delete some relay logs because not doing so
would cause a deadlock. You should not set
--relay-log-space-limit
to
less than twice the value of
--max-relay-log-size
(or
--max-binlog-size
if
--max-relay-log-size
is 0).
In that case, there is a chance that the I/O thread waits
for free space because
--relay-log-space-limit
is
exceeded, but the SQL thread has no relay log to purge and
is unable to satisfy the I/O thread. This forces the I/O
thread to ignore
--relay-log-space-limit
temporarily.
Property | Value |
---|---|
Command-Line Format | --replicate-do-db=name |
Type | String |
Creates a replication filter using the name of a database.
Such filters can also be created using
CHANGE
REPLICATION FILTER REPLICATE_DO_DB
.
This option supports channel specific replication filters,
enabling multi-source replication slaves to use specific
filters for different sources. To configure a channel
specific replication filter on a channel named
channel_1
use
--replicate-do-db:
.
In this case, the first colon is interpreted as a separator
and subsequent colons are literal colons. See
Section 17.2.5.4, “Replication Channel Based Filters”
for more information.
channel_1
:db_name
Global replication filters cannot be used on a MySQL
server instance that is configured for Group
Replication, because filtering transactions on some
servers would make the group unable to reach agreement
on a consistent state. Channel specific replication
filters can be used on replication channels that are not
directly involved with Group Replication, such as where
a group member also acts as a replication slave to a
master that is outside the group. They cannot be used on
the group_replication_applier
or
group_replication_recovery
channels.
The precise effect of this replication filter depends on whether statement-based or row-based replication is in use.
Statement-based replication.
Tell the slave SQL thread to restrict replication to
statements where the default database (that is, the one
selected by USE
) is
db_name
. To specify more than
one database, use this option multiple times, once for
each database; however, doing so does
not replicate cross-database
statements such as UPDATE
while a different database (or no
database) is selected.
some_db.some_table
SET
foo='bar'
To specify multiple databases you must use multiple instances of this option. Because database names can contain commas, if you supply a comma separated list then the list is treated as the name of a single database.
An example of what does not work as you might expect when
using statement-based replication: If the slave is started
with --replicate-do-db=sales
and you issue the following statements on the master, the
UPDATE
statement is
not replicated:
USE prices; UPDATE sales.january SET amount=amount+1000;
The main reason for this “check just the default
database” behavior is that it is difficult from the
statement alone to know whether it should be replicated (for
example, if you are using multiple-table
DELETE
statements or
multiple-table UPDATE
statements that act across multiple databases). It is also
faster to check only the default database rather than all
databases if there is no need.
Row-based replication.
Tells the slave SQL thread to restrict replication to
database db_name
. Only tables
belonging to db_name
are
changed; the current database has no effect on this.
Suppose that the slave is started with
--replicate-do-db=sales
and
row-based replication is in effect, and then the following
statements are run on the master:
USE prices; UPDATE sales.february SET amount=amount+100;
The february
table in the
sales
database on the slave is changed in
accordance with the UPDATE
statement; this occurs whether or not the
USE
statement was issued.
However, issuing the following statements on the master has
no effect on the slave when using row-based replication and
--replicate-do-db=sales
:
USE prices; UPDATE prices.march SET amount=amount-25;
Even if the statement USE prices
were
changed to USE sales
, the
UPDATE
statement's
effects would still not be replicated.
Another important difference in how
--replicate-do-db
is handled
in statement-based replication as opposed to row-based
replication occurs with regard to statements that refer to
multiple databases. Suppose that the slave is started with
--replicate-do-db=db1
, and
the following statements are executed on the master:
USE db1; UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
If you are using statement-based replication, then both
tables are updated on the slave. However, when using
row-based replication, only table1
is
affected on the slave; since table2
is in
a different database, table2
on the slave
is not changed by the UPDATE
.
Now suppose that, instead of the USE db1
statement, a USE db4
statement had been
used:
USE db4; UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
In this case, the UPDATE
statement would have no effect on the slave when using
statement-based replication. However, if you are using
row-based replication, the
UPDATE
would change
table1
on the slave, but not
table2
—in other words, only tables
in the database named by
--replicate-do-db
are
changed, and the choice of default database has no effect on
this behavior.
If you need cross-database updates to work, use
--replicate-wild-do-table=
instead. See Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”.
db_name
.%
This option affects replication in the same manner that
--binlog-do-db
affects
binary logging, and the effects of the replication format
on how --replicate-do-db
affects replication behavior are the same as those of the
logging format on the behavior of
--binlog-do-db
.
This option has no effect on
BEGIN
,
COMMIT
, or
ROLLBACK
statements.
Property | Value |
---|---|
Command-Line Format | --replicate-ignore-db=name |
Type | String |
Creates a replication filter using the name of a database.
Such filters can also be created using
CHANGE
REPLICATION FILTER REPLICATE_IGNORE_DB
.
This option supports channel specific replication filters,
enabling multi-source replication slaves to use specific
filters for different sources. To configure a channel
specific replication filter on a channel named
channel_1
use
--replicate-ignore-db:
.
In this case, the first colon is interpreted as a separator
and subsequent colons are literal colons. See
Section 17.2.5.4, “Replication Channel Based Filters”
for more information.
channel_1
:db_name
Global replication filters cannot be used on a MySQL
server instance that is configured for Group
Replication, because filtering transactions on some
servers would make the group unable to reach agreement
on a consistent state. Channel specific replication
filters can be used on replication channels that are not
directly involved with Group Replication, such as where
a group member also acts as a replication slave to a
master that is outside the group. They cannot be used on
the group_replication_applier
or
group_replication_recovery
channels.
To specify more than one database to ignore, use this option multiple times, once for each database. Because database names can contain commas, if you supply a comma separated list then the list will be treated as the name of a single database.
As with --replicate-do-db
,
the precise effect of this filtering depends on whether
statement-based or row-based replication is in use, and are
described in the next several paragraphs.
Statement-based replication.
Tells the slave SQL thread not to replicate any statement
where the default database (that is, the one selected by
USE
) is
db_name
.
Row-based replication.
Tells the slave SQL thread not to update any tables in the
database db_name
. The default
database has no effect.
When using statement-based replication, the following
example does not work as you might expect. Suppose that the
slave is started with
--replicate-ignore-db=sales
and you issue the following statements on the master:
USE prices; UPDATE sales.january SET amount=amount+1000;
The UPDATE
statement
is replicated in such a case because
--replicate-ignore-db
applies
only to the default database (determined by the
USE
statement). Because the
sales
database was specified explicitly
in the statement, the statement has not been filtered.
However, when using row-based replication, the
UPDATE
statement's
effects are not propagated to the
slave, and the slave's copy of the
sales.january
table is unchanged; in this
instance,
--replicate-ignore-db=sales
causes all changes made to tables in
the master's copy of the sales
database to be ignored by the slave.
You should not use this option if you are using cross-database updates and you do not want these updates to be replicated. See Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”.
If you need cross-database updates to work, use
--replicate-wild-ignore-table=
instead. See Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”.
db_name
.%
This option affects replication in the same manner that
--binlog-ignore-db
affects
binary logging, and the effects of the replication format
on how
--replicate-ignore-db
affects replication behavior are the same as those of the
logging format on the behavior of
--binlog-ignore-db
.
This option has no effect on
BEGIN
,
COMMIT
, or
ROLLBACK
statements.
--replicate-do-table=
db_name.tbl_name
Property | Value |
---|---|
Command-Line Format | --replicate-do-table=name |
Type | String |
Creates a replication filter by telling the slave SQL thread
to restrict replication to a given table. To specify more
than one table, use this option multiple times, once for
each table. This works for both cross-database updates and
default database updates, in contrast to
--replicate-do-db
. See
Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create
such a filter by issuing a
CHANGE
REPLICATION FILTER REPLICATE_DO_TABLE
statement.
This option supports channel specific replication filters,
enabling multi-source replication slaves to use specific
filters for different sources. To configure a channel
specific replication filter on a channel named
channel_1
use
--replicate-do-table:
.
In this case, the first colon is interpreted as a separator
and subsequent colons are literal colons. See
Section 17.2.5.4, “Replication Channel Based Filters”
for more information.
channel_1
:db_name.tbl_name
Global replication filters cannot be used on a MySQL
server instance that is configured for Group
Replication, because filtering transactions on some
servers would make the group unable to reach agreement
on a consistent state. Channel specific replication
filters can be used on replication channels that are not
directly involved with Group Replication, such as where
a group member also acts as a replication slave to a
master that is outside the group. They cannot be used on
the group_replication_applier
or
group_replication_recovery
channels.
This option affects only statements that apply to tables. It
does not affect statements that apply only to other database
objects, such as stored routines. To filter statements
operating on stored routines, use one or more of the
--replicate-*-db
options.
--replicate-ignore-table=
db_name.tbl_name
Property | Value |
---|---|
Command-Line Format | --replicate-ignore-table=name |
Type | String |
Creates a replication filter by telling the slave SQL thread
not to replicate any statement that updates the specified
table, even if any other tables might be updated by the same
statement. To specify more than one table to ignore, use
this option multiple times, once for each table. This works
for cross-database updates, in contrast to
--replicate-ignore-db
. See
Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create
such a filter by issuing a
CHANGE
REPLICATION FILTER REPLICATE_IGNORE_TABLE
statement.
This option supports channel specific replication filters,
enabling multi-source replication slaves to use specific
filters for different sources. To configure a channel
specific replication filter on a channel named
channel_1
use
--replicate-ignore-table:
.
In this case, the first colon is interpreted as a separator
and subsequent colons are literal colons. See
Section 17.2.5.4, “Replication Channel Based Filters”
for more information.
channel_1
:db_name.tbl_name
Global replication filters cannot be used on a MySQL
server instance that is configured for Group
Replication, because filtering transactions on some
servers would make the group unable to reach agreement
on a consistent state. Channel specific replication
filters can be used on replication channels that are not
directly involved with Group Replication, such as where
a group member also acts as a replication slave to a
master that is outside the group. They cannot be used on
the group_replication_applier
or
group_replication_recovery
channels.
This option affects only statements that apply to tables. It
does not affect statements that apply only to other database
objects, such as stored routines. To filter statements
operating on stored routines, use one or more of the
--replicate-*-db
options.
--replicate-rewrite-db=
from_name
->to_name
Property | Value |
---|---|
Command-Line Format | --replicate-rewrite-db=old_name->new_name |
Type | String |
Tells the slave to create a replication filter that
translates the default database (that is, the one selected
by USE
) to
to_name
if it was
from_name
on the master. Only
statements involving tables are affected (not statements
such as CREATE DATABASE
,
DROP DATABASE
, and
ALTER DATABASE
), and only if
from_name
is the default database
on the master. To specify multiple rewrites, use this option
multiple times. The server uses the first one with a
from_name
value that matches. The
database name translation is done
before the
--replicate-*
rules are tested. You can
also create such a filter by issuing a
CHANGE
REPLICATION FILTER REPLICATE_REWRITE_DB
statement.
If you use this option on the command line and the
>
character is special to your command
interpreter, quote the option value. For example:
shell> mysqld --replicate-rewrite-db="olddb
->newdb
"
This option supports channel specific replication filters,
enabling multi-source replication slaves to use specific
filters for different sources. Specify the channel name
followed by a colon, followed by the filter specification.
The first colon is interpreted as a separator, and any
subsequent colons are interpreted as literal colons. For
example, to configure a channel specific replication filter
on a channel named channel_1
,
use:
shell> mysqld --replicate-rewrite-db=channel_1
:db_name1
->db_name2
If you use a colon but do not specify a channel name, the option configures the replication filter for the default replication channel. See Section 17.2.5.4, “Replication Channel Based Filters” for more information.
Global replication filters cannot be used on a MySQL
server instance that is configured for Group
Replication, because filtering transactions on some
servers would make the group unable to reach agreement
on a consistent state. Channel specific replication
filters can be used on replication channels that are not
directly involved with Group Replication, such as where
a group member also acts as a replication slave to a
master that is outside the group. They cannot be used on
the group_replication_applier
or
group_replication_recovery
channels.
Statements in which table names are qualified with database
names when using this option do not work with table-level
replication filtering options such as
--replicate-do-table
. Suppose
we have a database named a
on the master,
one named b
on the slave, each containing
a table t
, and have started the master
with --replicate-rewrite-db='a->b'
. At a
later point in time, we execute
DELETE FROM
a.t
. In this case, no relevant filtering rule
works, for the reasons shown here:
--replicate-do-table=a.t
does not work
because the slave has table t
in
database b
.
--replicate-do-table=b.t
does not match
the original statement and so is ignored.
--replicate-do-table=*.t
is handled
identically to
--replicate-do-table=a.t
, and thus does
not work, either.
Similarly, the --replication-rewrite-db
option does not work with cross-database updates.
Property | Value |
---|---|
Command-Line Format | --replicate-same-server-id[={OFF|ON}] |
Type | Boolean |
Default Value | OFF |
This option is for use on replication slaves. The default is
0 (FALSE
). With this option set to 1
(TRUE
), the slave does not skip events
that have its own server ID. This setting is normally useful
only in rare configurations.
When binary logging is enabled on a replication slave, the
combination of the
--replicate-same-server-id
and --log-slave-updates
options on the slave can cause infinite loops in replication
if the server is part of a circular replication topology.
(In MySQL 8.0, binary logging is enabled by default, and
slave update logging is the default when binary logging is
enabled.) However, the use of global transaction identifiers
(GTIDs) prevents this situation by skipping the execution of
transactions that have already been applied. If
gtid_mode=ON
is set on the
slave, you can start the server with this combination of
options, but you cannot change to any other GTID mode while
the server is running. If any other GTID mode is set, the
server does not start with this combination of options.
By default, the slave I/O thread does not write binary log
events to the relay log if they have the slave's server ID
(this optimization helps save disk usage). If you want to
use
--replicate-same-server-id
,
be sure to start the slave with this option before you make
the slave read its own events that you want the slave SQL
thread to execute.
--replicate-wild-do-table=
db_name.tbl_name
Property | Value |
---|---|
Command-Line Format | --replicate-wild-do-table=name |
Type | String |
Creates a replication filter by telling the slave thread to
restrict replication to statements where any of the updated
tables match the specified database and table name patterns.
Patterns can contain the %
and
_
wildcard characters, which have the
same meaning as for the LIKE
pattern-matching operator. To specify more than one table,
use this option multiple times, once for each table. This
works for cross-database updates. See
Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create
such a filter by issuing a
CHANGE
REPLICATION FILTER REPLICATE_WILD_DO_TABLE
statement.
This option supports channel specific replication filters,
enabling multi-source replication slaves to use specific
filters for different sources. To configure a channel
specific replication filter on a channel named
channel_1
use
--replicate-wild-do-table:
.
In this case, the first colon is interpreted as a separator
and subsequent colons are literal colons. See
Section 17.2.5.4, “Replication Channel Based Filters”
for more information.
channel_1
:db_name.tbl_name
Global replication filters cannot be used on a MySQL
server instance that is configured for Group
Replication, because filtering transactions on some
servers would make the group unable to reach agreement
on a consistent state. Channel specific replication
filters can be used on replication channels that are not
directly involved with Group Replication, such as where
a group member also acts as a replication slave to a
master that is outside the group. They cannot be used on
the group_replication_applier
or
group_replication_recovery
channels.
This option applies to tables, views, and triggers. It does
not apply to stored procedures and functions, or events. To
filter statements operating on the latter objects, use one
or more of the --replicate-*-db
options.
As an example,
--replicate-wild-do-table=foo%.bar%
replicates only updates that use a table where the database
name starts with foo
and the table name
starts with bar
.
If the table name pattern is %
, it
matches any table name and the option also applies to
database-level statements (CREATE
DATABASE
, DROP
DATABASE
, and ALTER
DATABASE
). For example, if you use
--replicate-wild-do-table=foo%.%
,
database-level statements are replicated if the database
name matches the pattern foo%
.
To include literal wildcard characters in the database or
table name patterns, escape them with a backslash. For
example, to replicate all tables of a database that is named
my_own%db
, but not replicate tables from
the my1ownAABCdb
database, you should
escape the _
and %
characters like this:
--replicate-wild-do-table=my\_own\%db
.
If you use the option on the command line, you might need to
double the backslashes or quote the option value, depending
on your command interpreter. For example, with the
bash shell, you would need to type
--replicate-wild-do-table=my\\_own\\%db
.
--replicate-wild-ignore-table=
db_name.tbl_name
Property | Value |
---|---|
Command-Line Format | --replicate-wild-ignore-table=name |
Type | String |
Creates a replication filter which keeps the slave thread
from replicating a statement in which any table matches the
given wildcard pattern. To specify more than one table to
ignore, use this option multiple times, once for each table.
This works for cross-database updates. See
Section 17.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create
such a filter by issuing a
CHANGE
REPLICATION FILTER REPLICATE_WILD_IGNORE_TABLE
statement.
This option supports channel specific replication filters,
enabling multi-source replication slaves to use specific
filters for different sources. To configure a channel
specific replication filter on a channel named
channel_1
use
--replicate-wild-ignore:
.
In this case, the first colon is interpreted as a separator
and subsequent colons are literal colons. See
Section 17.2.5.4, “Replication Channel Based Filters”
for more information.
channel_1
:db_name.tbl_name
Global replication filters cannot be used on a MySQL
server instance that is configured for Group
Replication, because filtering transactions on some
servers would make the group unable to reach agreement
on a consistent state. Channel specific replication
filters can be used on replication channels that are not
directly involved with Group Replication, such as where
a group member also acts as a replication slave to a
master that is outside the group. They cannot be used on
the group_replication_applier
or
group_replication_recovery
channels.
As an example,
--replicate-wild-ignore-table=foo%.bar%
does not replicate updates that use a table where the
database name starts with foo
and the
table name starts with bar
. For
information about how matching works, see the description of
the --replicate-wild-do-table
option. The rules for including literal wildcard characters
in the option value are the same as for
--replicate-wild-ignore-table
as well.
Property | Value |
---|---|
Command-Line Format | --skip-slave-start[={OFF|ON}] |
Type | Boolean |
Default Value | OFF |
Tells the slave server not to start the slave threads when
the server starts. To start the threads later, use a
START SLAVE
statement.
--slave-skip-errors=[
err_code1
,err_code2
,...|all|ddl_exist_errors]
Property | Value |
---|---|
Command-Line Format | --slave-skip-errors=name |
System Variable | slave_skip_errors |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | String |
Default Value | OFF |
Valid Values |
|
Normally, replication stops when an error occurs on the slave, which gives you the opportunity to resolve the inconsistency in the data manually. This option causes the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.
Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.
For error codes, you should use the numbers provided by the
error message in your slave error log and in the output of
SHOW SLAVE STATUS
.
Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.
The shorthand value ddl_exist_errors
is
equivalent to the error code list
1007,1008,1050,1051,1054,1060,1061,1068,1094,1146
.
You can also (but should not) use the very nonrecommended
value of all
to cause the slave to ignore
all error messages and keeps going regardless of what
happens. Needless to say, if you use all
,
there are no guarantees regarding the integrity of your
data. Please do not complain (or file bug reports) in this
case if the slave's data is not anywhere close to what it is
on the master. You have been warned.
Examples:
--slave-skip-errors=1062,1053 --slave-skip-errors=all --slave-skip-errors=ddl_exist_errors
--slave-sql-verify-checksum={0|1}
Property | Value |
---|---|
Command-Line Format | --slave-sql-verify-checksum[={OFF|ON}] |
Type | Boolean |
Default Value | ON |
When this option is enabled, the slave examines checksums read from the relay log, in the event of a mismatch, the slave stops with an error.
The following options are used internally by the MySQL test suite for replication testing and debugging. They are not intended for use in a production setting.
Property | Value |
---|---|
Command-Line Format | --abort-slave-event-count=# |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
When this option is set to some positive integer
value
other than 0 (the default)
it affects replication behavior as follows: After the slave
SQL thread has started, value
log
events are permitted to be executed; after that, the slave
SQL thread does not receive any more events, just as if the
network connection from the master were cut. The slave
thread continues to run, and the output from
SHOW SLAVE STATUS
displays
Yes
in both the
Slave_IO_Running
and the
Slave_SQL_Running
columns, but no further
events are read from the relay log.
--disconnect-slave-event-count
Property | Value |
---|---|
Command-Line Format | --disconnect-slave-event-count=# |
Type | Integer |
Default Value | 0 |
Replication slave status information is logged to an InnoDB
table in the mysql
database. Before MySQL
8.0, this information could alternatively be logged to a file in
the data directory, but the use of that format is now
deprecated. Writing of the master info log and the relay log
info log can be configured separately using these two system
variables:
For information about these variables, see Section 17.1.6.3, “Replication Slave Options and Variables”.
The slave status log tables and their contents are considered local to a given MySQL Server. They are not replicated, and changes to them are not written to the binary log.
For more information, see Section 17.2.4, “Replication Relay and Status Logs”.
The following list describes system variables for controlling
replication slave servers. They can be set at server startup and
some of them can be changed at runtime using
SET
.
Server options used with replication slaves are listed earlier
in this section.
Property | Value |
---|---|
Command-Line Format | --init-slave=name |
System Variable | init_slave |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | String |
This variable is similar to
init_connect
, but is a
string to be executed by a slave server each time the SQL
thread starts. The format of the string is the same as for
the init_connect
variable.
The setting of this variable takes effect for subsequent
START SLAVE
statements.
The SQL thread sends an acknowledgment to the client
before it executes
init_slave
. Therefore, it
is not guaranteed that
init_slave
has been
executed when START SLAVE
returns. See Section 13.4.2.6, “START SLAVE Statement”, for more
information.
Property | Value |
---|---|
Command-Line Format | --log-slow-slave-statements[={OFF|ON}] |
System Variable | log_slow_slave_statements |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | OFF |
When the slow query log is enabled, this variable enables
logging for queries that have taken more than
long_query_time
seconds to
execute on the slave. Note that if row-based replication is
in use (binlog_format=ROW
),
log_slow_slave_statements
has no effect. Queries are only added to the slave's slow
query log when they are logged in statement format in the
binary log, that is, when
binlog_format=STATEMENT
is
set, or when
binlog_format=MIXED
is set
and the statement is logged in statement format. Slow
queries that are logged in row format when
binlog_format=MIXED
is set,
or that are logged when
binlog_format=ROW
is set,
are not added to the slave's slow query log, even if
log_slow_slave_statements
is enabled.
Setting
log_slow_slave_statements
has no immediate effect. The state of the variable applies
on all subsequent START SLAVE
statements. Also note that the global setting for
long_query_time
applies for
the lifetime of the SQL thread. If you change that setting,
you must stop and restart the slave's SQL thread to
implement the change there (for example, by issuing
STOP SLAVE
and
START SLAVE
statements with
the SQL_THREAD
option).
Property | Value |
---|---|
Command-Line Format | --master-info-repository={FILE|TABLE} |
System Variable | master_info_repository |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | String |
Default Value (>= 8.0.2) | TABLE |
Default Value (<= 8.0.1) | FILE |
Valid Values |
|
The setting of this variable determines whether the slave
server logs master status and connection information to an
InnoDB
table in the
mysql
system database, or to a file in
the data directory.
The default setting is TABLE
. As an
InnoDB
table, the master info log is
named mysql.slave_master_info
. The
TABLE
setting is required when multiple
replication channels are configured.
The FILE
setting is deprecated, and will
be removed in a future release. As a file, the master info
log is named master.info
by default.
You can change this name using the
--master-info-file
option.
The setting for the location of this slave status log has a
direct influence on the effect had by the setting of the
sync_master_info
system
variable. You can change the setting only when no
replication threads are executing.
Property | Value |
---|---|
Command-Line Format | --max-relay-log-size=# |
System Variable | max_relay_log_size |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 1073741824 |
If a write by a replication slave to its relay log causes
the current log file size to exceed the value of this
variable, the slave rotates the relay logs (closes the
current file and opens the next one). If
max_relay_log_size
is 0,
the server uses
max_binlog_size
for both
the binary log and the relay log. If
max_relay_log_size
is
greater than 0, it constrains the size of the relay log,
which enables you to have different sizes for the two logs.
You must set
max_relay_log_size
to
between 4096 bytes and 1GB (inclusive), or to 0. The default
value is 0. See
Section 17.2.2, “Replication Implementation Details”.
Property | Value |
---|---|
Command-Line Format | --relay-log=file_name |
System Variable | relay_log |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | File name |
The base name for relay log files. For the default
replication channel, the default base name for relay logs is
.
For non-default replication channels, the default base name
for relay logs is
host_name
-relay-bin
,
where host_name
-relay-bin-channel
channel
is the name of the
replication channel recorded in this relay log.
The server writes the file in the data directory unless the base name is given with a leading absolute path name to specify a different directory. The server creates relay log files in sequence by adding a numeric suffix to the base name.
The relay log and relay log index on a replication server
cannot be given the same names as the binary log and binary
log index, whose names are specified by the
--log-bin
and
--log-bin-index
options. The
server issues an error message and does not start if the
binary log and relay log file base names would be the same.
Due to the manner in which MySQL parses server options, if
you specify this variable at server startup, you must supply
a value; the default base name is used only if the
option is not actually specified. If you specify
the relay_log
system
variable at server startup without specifying a value,
unexpected behavior is likely to result; this behavior
depends on the other options used, the order in which they
are specified, and whether they are specified on the command
line or in an option file. For more information about how
MySQL handles server options, see
Section 4.2.2, “Specifying Program Options”.
If you specify this variable, the value specified is also
used as the base name for the relay log index file. You can
override this behavior by specifying a different relay log
index file base name using the
relay_log_index
system
variable.
When the server reads an entry from the index file, it
checks whether the entry contains a relative path. If it
does, the relative part of the path is replaced with the
absolute path set using the
relay_log
system variable.
An absolute path remains unchanged; in such a case, the
index must be edited manually to enable the new path or
paths to be used.
You may find the relay_log
system variable useful in performing the following tasks:
Creating relay logs whose names are independent of host names.
If you need to put the relay logs in some area other
than the data directory because your relay logs tend to
be very large and you do not want to decrease
max_relay_log_size
.
To increase speed by using load-balancing between disks.
You can obtain the relay log file name (and path) from the
relay_log_basename
system
variable.
Property | Value |
---|---|
System Variable | relay_log_basename |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | File name |
Default Value | datadir + '/' + hostname + '-relay-bin' |
Holds the name and complete path to the relay log file. This variable is set by the server and is read only.
Property | Value |
---|---|
Command-Line Format | --relay-log-index=file_name |
System Variable | relay_log_index |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | File name |
Default Value | *host_name*-relay-bin.index |
The name for the relay log index file. If you do not specify
this variable, but the
relay_log
system variable
is specified, its value is used as the default base name for
the relay log index file. If
relay_log
is also not
specified, then for the default replication channel, the
default name is
,
using the name of the host machine. For non-default
replication channels, the default name is
host_name
-relay-bin.index
,
where host_name
-relay-bin-channel
.indexchannel
is the name of the
replication channel recorded in this relay log index.
The default location for relay log files is the data
directory, or any other location that was specified using
the relay_log
system
variable. You can use the
relay_log_index
system
variable to specify an alternative location, by adding a
leading absolute path name to the base name to specify a
different directory.
The relay log and relay log index on a replication server
cannot be given the same names as the binary log and binary
log index, whose names are specified by the
--log-bin
and
--log-bin-index
options. The
server issues an error message and does not start if the
binary log and relay log file base names would be the same.
Due to the manner in which MySQL parses server options, if
you specify this variable at server startup, you must supply
a value; the default base name is used only if the
option is not actually specified. If you specify
the relay_log_index
system
variable at server startup without specifying a value,
unexpected behavior is likely to result; this behavior
depends on the other options used, the order in which they
are specified, and whether they are specified on the command
line or in an option file. For more information about how
MySQL handles server options, see
Section 4.2.2, “Specifying Program Options”.
Property | Value |
---|---|
Command-Line Format | --relay-log-info-file=file_name |
Deprecated | 8.0.18 |
System Variable | relay_log_info_file |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | File name |
Default Value | relay-log.info |
The name of the file in which the slave records information
about the relay logs, when
relay_log_info_repository=FILE
.
If
relay_log_info_repository=TABLE
,
it is the file name that would be used in case the
repository was changed to FILE
). The
default name is relay-log.info
in the
data directory.
relay_log_info_file
and the
setting
relay_log_info_repository=FILE
are deprecated, as the use of a file for the relay log info
log has been superseded by crash-safe slave tables. For
information about the relay log info log, see
Section 17.2.4.2, “Slave Status Logs”.
Property | Value |
---|---|
Command-Line Format | --relay-log-info-repository=value |
System Variable | relay_log_info_repository |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | String |
Default Value (>= 8.0.2) | TABLE |
Default Value (<= 8.0.1) | FILE |
Valid Values |
|
The setting of this variable determines whether the slave
server logs its position in the relay logs to an
InnoDB
table in the
mysql
system database, or to a file in
the data directory.
The default setting is TABLE
. As an
InnoDB
table, the relay log info log is
named mysql.slave_relay_log_info
. The
TABLE
setting is required when multiple
replication channels are configured. The
TABLE
setting for the relay log info log
is also required to make replication resilient to unexpected
halts, for which the
--relay-log-recovery
option
must also be enabled. See
Making replication resilient to unexpected halts for
more information.
The FILE
setting is deprecated, and will
be removed in a future release. As a file, the relay log
info log is named relay-log.info
by
default, and you can change this name using the
relay_log_info_file
system
variable.
The setting for the location of this slave status log has a
direct influence on the effect had by the setting of the
sync_relay_log_info
system
variable. You can change the setting only when no
replication threads are executing.
Property | Value |
---|---|
Command-Line Format | --relay-log-purge[={OFF|ON}] |
System Variable | relay_log_purge |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | ON |
Disables or enables automatic purging of relay log files as
soon as they are not needed any more. The default value is 1
(ON
).
Property | Value |
---|---|
Command-Line Format | --relay-log-recovery[={OFF|ON}] |
System Variable | relay_log_recovery |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | OFF |
If enabled, this variable enables automatic relay log
recovery immediately following server startup. The recovery
process creates a new relay log file, initializes the SQL
thread position to this new relay log, and initializes the
I/O thread to the SQL thread position. Reading of the relay
log from the master then continues. This global variable is
read-only at runtime. Its value can set with the
--relay-log-recovery
option
at slave startup, which should be used following an
unexpected halt of a replication slave to ensure that no
possibly corrupted relay logs are processed, and must be
used in order to guarantee a crash-safe slave. The default
value is 0 (disabled).
To provide a crash-safe slave, this variable must be enabled
(set to 1),
relay_log_info_repository
must be set to TABLE
, and
relay_log_purge
must be
enabled. Enabling
relay_log_recovery
when
relay_log_purge
is disabled
risks reading the relay log from files that were not purged,
leading to data inconsistency, and is therefore not
crash-safe. See
Making replication resilient to unexpected halts, for
more information.
When using a multithreaded slave (in other words
slave_parallel_workers
is
greater than 0), inconsistencies such as gaps can occur in
the sequence of transactions that have been executed from
the relay log. Enabling
relay_log_recovery
when
there are inconsistencies causes an error and the option has
no effect. The solution in this situation is to issue
START SLAVE
UNTIL SQL_AFTER_MTS_GAPS
, which brings the server
to a more consistent state, then issue
RESET SLAVE
to remove the
relay logs. See
Section 17.5.1.33, “Replication and Transaction Inconsistencies”
for more information.
This variable does not affect the following Group Replication channels:
group_replication_applier
group_replication_recovery
Any other channels running on a group are affected, such as a channel which is replicating from an outside master or another group.
Property | Value |
---|---|
Command-Line Format | --relay-log-space-limit=# |
System Variable | relay_log_space_limit |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The maximum amount of space to use for all relay logs.
Property | Value |
---|---|
Command-Line Format | --report-host=host_name |
System Variable | report_host |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | String |
The host name or IP address of the slave to be reported to
the master during slave registration. This value appears in
the output of SHOW SLAVE
HOSTS
on the master server. Leave the value unset
if you do not want the slave to register itself with the
master.
It is not sufficient for the master to simply read the IP address of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts.
Property | Value |
---|---|
Command-Line Format | --report-password=name |
System Variable | report_password |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | String |
The account password of the slave to be reported to the
master during slave registration. This value appears in the
output of SHOW SLAVE HOSTS
on
the master server if the master was started with
--show-slave-auth-info
.
Although the name of this variable might imply otherwise,
report_password
is not
connected to the MySQL user privilege system and so is not
necessarily (or even likely to be) the same as the password
for the MySQL replication user account.
Property | Value |
---|---|
Command-Line Format | --report-port=port_num |
System Variable | report_port |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | [slave_port] |
Minimum Value | 0 |
Maximum Value | 65535 |
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a nondefault port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.
The default value for this option is the port number
actually used by the slave. This is also the default value
displayed by SHOW SLAVE
HOSTS
.
Property | Value |
---|---|
Command-Line Format | --report-user=name |
System Variable | report_user |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | String |
The account user name of the slave to be reported to the
master during slave registration. This value appears in the
output of SHOW SLAVE HOSTS
on
the master server if the master was started with
--show-slave-auth-info
.
Although the name of this variable might imply otherwise,
report_user
is not
connected to the MySQL user privilege system and so is not
necessarily (or even likely to be) the same as the name of
the MySQL replication user account.
Property | Value |
---|---|
Command-Line Format | --rpl-read-size=# |
Introduced | 8.0.11 |
System Variable | rpl_read_size |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 8192 |
Minimum Value | 8192 |
Maximum Value | 4294967295 |
The rpl_read_size
system
variable controls the minimum amount of data in bytes that
is read from the binary log files and relay log files. If
heavy disk I/O activity for these files is impeding
performance for the database, increasing the read size might
reduce file reads and I/O stalls when the file data is not
currently cached by the operating system.
The minimum and default value for
rpl_read_size
is 8192
bytes. The value must be a multiple of 4KB. Note that a
buffer the size of this value is allocated for each thread
that reads from the binary log and relay log files,
including dump threads on masters and coordinator threads on
slaves. Setting a large value might therefore have an impact
on memory consumption for servers.
Property | Value |
---|---|
Command-Line Format | --rpl-semi-sync-slave-enabled[={OFF|ON}] |
System Variable | rpl_semi_sync_slave_enabled |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | OFF |
Controls whether semisynchronous replication is enabled on
the slave. To enable or disable the plugin, set this
variable to ON
or OFF
(or 1 or 0), respectively. The default is
OFF
.
This variable is available only if the slave-side semisynchronous replication plugin is installed.
rpl_semi_sync_slave_trace_level
Property | Value |
---|---|
Command-Line Format | --rpl-semi-sync-slave-trace-level=# |
System Variable | rpl_semi_sync_slave_trace_level |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 32 |
The semisynchronous replication debug trace level on the
slave. See
rpl_semi_sync_master_trace_level
for the permissible values.
This variable is available only if the slave-side semisynchronous replication plugin is installed.
Property | Value |
---|---|
Command-Line Format | --rpl-stop-slave-timeout=seconds |
System Variable | rpl_stop_slave_timeout |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 31536000 |
Minimum Value | 2 |
Maximum Value | 31536000 |
You can control the length of time (in seconds) that
STOP SLAVE
waits before
timing out by setting this variable. This can be used to
avoid deadlocks between STOP SLAVE
and
other slave SQL statements using different client
connections to the slave.
The maximum and default value of
rpl_stop_slave_timeout
is 31536000
seconds (1 year). The minimum is 2 seconds. Changes to this
variable take effect for subsequent
STOP SLAVE
statements.
This variable affects only the client that issues a
STOP SLAVE
statement. When the timeout is
reached, the issuing client returns an error message stating
that the command execution is incomplete. The client then
stops waiting for the slave threads to stop, but the slave
threads continue to try to stop, and the STOP
SLAVE
instruction remains in effect. Once the
slave threads are no longer busy, the STOP
SLAVE
statement is executed and the slave stops.
Property | Value |
---|---|
Command-Line Format | --slave-checkpoint-group=# |
System Variable | slave_checkpoint_group |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 512 |
Minimum Value | 32 |
Maximum Value | 524280 |
Block Size | 8 |
Sets the maximum number of transactions that can be
processed by a multithreaded slave before a checkpoint
operation is called to update its status as shown by
SHOW SLAVE STATUS
. Setting
this variable has no effect on slaves for which
multithreading is not enabled. Setting this variable has no
immediate effect. The state of the variable applies on all
subsequent START SLAVE
commands.
Multithreaded slaves are not currently supported by NDB Cluster, which silently ignores the setting for this variable. See Section 22.6.3, “Known Issues in NDB Cluster Replication”, for more information.
This variable works in combination with the
slave_checkpoint_period
system variable in such a way that, when either limit is
exceeded, the checkpoint is executed and the counters
tracking both the number of transactions and the time
elapsed since the last checkpoint are reset.
The minimum allowed value for this variable is 32, unless
the server was built using
-DWITH_DEBUG
, in which case
the minimum value is 1. The effective value is always a
multiple of 8; you can set it to a value that is not such a
multiple, but the server rounds it down to the next lower
multiple of 8 before storing the value.
(Exception: No such rounding is
performed by the debug server.) Regardless of how the server
was built, the default value is 512, and the maximum allowed
value is 524280.
Property | Value |
---|---|
Command-Line Format | --slave-checkpoint-period=# |
System Variable | slave_checkpoint_period |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 300 |
Minimum Value | 1 |
Maximum Value | 4G |
Sets the maximum time (in milliseconds) that is allowed to
pass before a checkpoint operation is called to update the
status of a multithreaded slave as shown by
SHOW SLAVE STATUS
. Setting
this variable has no effect on slaves for which
multithreading is not enabled. Setting this variable takes
effect for all replication channels immediately, including
running channels.
Multithreaded slaves are not currently supported by NDB Cluster, which silently ignores the setting for this variable. See Section 22.6.3, “Known Issues in NDB Cluster Replication”, for more information.
This variable works in combination with the
slave_checkpoint_group
system variable in such a way that, when either limit is
exceeded, the checkpoint is executed and the counters
tracking both the number of transactions and the time
elapsed since the last checkpoint are reset.
The minimum allowed value for this variable is 1, unless the
server was built using
-DWITH_DEBUG
, in which case
the minimum value is 0. Regardless of how the server was
built, the default value is 300, and the maximum possible
value is 4294967296 (4GB).
Property | Value |
---|---|
Command-Line Format | --slave-compressed-protocol[={OFF|ON}] |
Deprecated | 8.0.18 |
System Variable | slave_compressed_protocol |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | OFF |
Whether to use compression of the master/slave protocol if
both master and slave support it. If this variable is
disabled (the default), connections are uncompressed.
Changes to this variable take effect on subsequent
connection attempts; this includes after issuing a
START SLAVE
statement, as
well as reconnections made by a running I/O thread (for
example, after setting the
MASTER_RETRY_COUNT
option for the
CHANGE MASTER TO
statement).
As of MySQL 8.0.18, if
slave_compressed_protocol
is enabled, it takes precedence over any
MASTER_COMPRESSION_ALGORITHMS
option
specified for the CHANGE MASTER
TO
statement. In this case, connections to the
master use zlib
compression if both the
master and slave support that algorithm. If
slave_compressed_protocol
is disabled, the value of
MASTER_COMPRESSION_ALGORITHMS
applies.
For more information, see
Section 4.2.6, “Connection Compression Control”.
As of MySQL 8.0.18, this system variable is deprecated. It will be removed in a future MySQL version. See Legacy Connection Compression Configuration.
Property | Value |
---|---|
Command-Line Format | --slave-exec-mode=mode |
System Variable | slave_exec_mode |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Enumeration |
Default Value |
|
Valid Values |
|
Controls how a slave thread resolves conflicts and errors
during replication. IDEMPOTENT
mode
causes suppression of duplicate-key and no-key-found errors;
STRICT
means no such suppression takes
place.
IDEMPOTENT
mode is intended for use in
multi-master replication, circular replication, and some
other special replication scenarios for NDB Cluster
Replication. (See
Section 22.6.10, “NDB Cluster Replication: Multi-Master and Circular Replication”,
and
Section 22.6.11, “NDB Cluster Replication Conflict Resolution”,
for more information.) NDB Cluster ignores any value
explicitly set for
slave_exec_mode
, and always
treats it as IDEMPOTENT
.
In MySQL Server 8.0, STRICT
mode is the default value.
Setting this variable takes immediate effect for all replication channels, including running channels.
For storage engines other than
NDB
,
IDEMPOTENT
mode should be used
only when you are absolutely sure that duplicate-key errors
and key-not-found errors can safely be ignored.
It is meant to be used in fail-over scenarios for NDB
Cluster where multi-master replication or circular
replication is employed, and is not recommended for use in
other cases.
Property | Value |
---|---|
Command-Line Format | --slave-load-tmpdir=dir_name |
System Variable | slave_load_tmpdir |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | Directory name |
Default Value | Value of --tmpdir |
The name of the directory where the slave creates temporary
files. Setting this variable takes effect for all
replication channels immediately, including running
channels. The variable value is by default equal to the
value of the tmpdir
system
variable, or the default that applies when that system
variable is not specified.
When the slave SQL thread replicates a
LOAD DATA
statement, it
extracts the file to be loaded from the relay log into
temporary files, and then loads these into the table. If the
file loaded on the master is huge, the temporary files on
the slave are huge, too. Therefore, it might be advisable to
use this option to tell the slave to put temporary files in
a directory located in some file system that has a lot of
available space. In that case, the relay logs are huge as
well, so you might also want to set the
relay_log
system variable
to place the relay logs in that file system.
The directory specified by this option should be located in
a disk-based file system (not a memory-based file system) so
that the temporary files used to replicate
LOAD DATA
statements can
survive machine restarts. The directory also should not be
one that is cleared by the operating system during the
system startup process. However, replication can now
continue after a restart if the temporary files have been
removed.
Property | Value |
---|---|
Command-Line Format | --slave-max-allowed-packet=# |
System Variable | slave_max_allowed_packet |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 1073741824 |
Minimum Value | 1024 |
Maximum Value | 1073741824 |
This option sets the maximum packet size in bytes that the
slave SQL and I/O threads can handle. Setting this variable
takes effect for all replication channels immediately,
including running channels. It is possible for a replication
master to write binary log events longer than its
max_allowed_packet
setting
once the event header is added. The setting for
slave_max_allowed_packet
must be larger than the
max_allowed_packet
setting
on the master, so that large updates using row-based
replication do not cause replication to fail.
This global variable always has a value that is a positive
integer multiple of 1024; if you set it to some value that
is not, the value is rounded down to the next highest
multiple of 1024 for it is stored or used; setting
slave_max_allowed_packet
to 0 causes 1024
to be used. (A truncation warning is issued in all such
cases.) The default and maximum value is 1073741824 (1 GB);
the minimum is 1024.
Property | Value |
---|---|
Command-Line Format | --slave-net-timeout=# |
System Variable | slave_net_timeout |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 60 |
Minimum Value | 1 |
The number of seconds to wait for more data or a heartbeat
signal from the master before the slave considers the
connection broken, aborts the read, and tries to reconnect.
Setting this variable has no immediate effect. The state of
the variable applies on all subsequent
START SLAVE
commands.
The default value is 60 seconds (one minute). The first
retry occurs immediately after the timeout. The interval
between retries is controlled by the
MASTER_CONNECT_RETRY
option for the
CHANGE MASTER TO
statement,
and the number of reconnection attempts is limited by the
MASTER_RETRY_COUNT
option for the
CHANGE MASTER TO
statement.
The heartbeat interval, which stops the connection timeout
occurring in the absence of data if the connection is still
good, is controlled by the
MASTER_HEARTBEAT_PERIOD
option for the
CHANGE MASTER TO
statement.
The heartbeat interval defaults to half the value of
slave_net_timeout
, and it
is recorded in the master info log and shown in the
replication_connection_configuration
Performance Schema table. Note that a change to the value or
default setting of
slave_net_timeout
does not
automatically change the heartbeat interval, whether that
has been set explicitly or is using a previously calculated
default. If the connection timeout is changed, you must also
issue CHANGE MASTER TO
to
adjust the heartbeat interval to an appropriate value so
that it occurs before the connection timeout.
Property | Value |
---|---|
Command-Line Format | --slave-parallel-type=value |
System Variable | slave_parallel_type |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Enumeration |
Default Value | DATABASE |
Valid Values |
|
When using a multithreaded slave
(slave_parallel_workers
is
greater than 0), this variable specifies the policy used to
decide which transactions are allowed to execute in parallel
on the slave. The variable has no effect on slaves for which
multithreading is not enabled. The possible values are:
LOGICAL_CLOCK
: Transactions that are
part of the same binary log group commit on a master are
applied in parallel on a slave. The dependencies between
transactions are tracked based on their timestamps to
provide additional parallelization where possible. When
this value is set, the
binlog_transaction_dependency_tracking
system variable can be used on the master to specify
that write sets are used for parallelization in place of
timestamps, if a write set is available for the
transaction and gives improved results compared to
timestamps.
DATABASE
: Transactions that update
different databases are applied in parallel. This value
is only appropriate if data is partitioned into multiple
databases which are being updated independently and
concurrently on the master. There must be no
cross-database constraints, as such constraints may be
violated on the slave.
When
slave_preserve_commit_order=1
is set, you can only use LOGICAL_CLOCK
.
If your replication topology uses multiple levels of slaves,
LOGICAL_CLOCK
may achieve less
parallelization for each level the slave is away from the
master. You can reduce this effect by using
binlog_transaction_dependency_tracking
on the master to specify that write sets are used instead of
timestamps for parallelization where possible.
Property | Value |
---|---|
Command-Line Format | --slave-parallel-workers=# |
System Variable | slave_parallel_workers |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 1024 |
Enables multithreading on the slave and sets the number of slave applier threads for executing replication transactions in parallel. When the value is a number greater than 0, the slave is a multithreaded slave with the specified number of applier threads, plus a coordinator thread to manage them. If you are using multiple replication channels, each channel has this number of threads.
Multithreaded slaves are not currently supported by NDB Cluster, which silently ignores the setting for this variable. See Section 22.6.3, “Known Issues in NDB Cluster Replication”, for more information.
Retrying of transactions is supported when multithreading is
enabled on a slave. When
slave_preserve_commit_order=1
,
transactions on a slave are externalized on the slave in the
same order as they appear in the slave's relay log. The way
in which transactions are distributed among applier threads
is configured by
slave_parallel_type
.
To disable parallel execution, set this option to 0, which
gives the slave a single applier thread and no coordinator
thread. With this setting, the
slave_parallel_type
and
slave_preserve_commit_order
system variables have no effect and are ignored.
Setting
slave_parallel_workers
has
no immediate effect. The state of the variable applies on
all subsequent START SLAVE
statements.
Property | Value |
---|---|
Command-Line Format | --slave-pending-jobs-size-max=# |
System Variable | slave_pending_jobs_size_max |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value (>= 8.0.12) | 128M |
Default Value (<= 8.0.11) | 16M |
Minimum Value | 1024 |
Maximum Value | 16EiB |
Block Size | 1024 |
For multithreaded slaves, this variable sets the maximum
amount of memory (in bytes) available to slave worker queues
holding events not yet applied. Setting this variable has no
effect on slaves for which multithreading is not enabled.
Setting this variable has no immediate effect. The state of
the variable applies on all subsequent
START SLAVE
commands.
The minimum possible value for this variable is 1024 bytes; the default is 128MB. The maximum possible value is 18446744073709551615 (16 exbibytes). Values that are not exact multiples of 1024 bytes are rounded down to the next lower multiple of 1024 bytes prior to being stored.
The value of this variable is a soft limit and can be set to match the normal workload. If an unusually large event exceeds this size, the transaction is held until all the slave workers have empty queues, and then processed. All subsequent transactions are held until the large transaction has been completed.
Property | Value |
---|---|
Command-Line Format | --slave-preserve-commit-order[={OFF|ON}] |
System Variable | slave_preserve_commit_order |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | OFF |
For multithreaded slaves, the setting 1 for this variable
ensures that transactions are externalized on the slave in
the same order as they appear in the slave's relay log,
and prevents gaps in the sequence of transactions that have
been executed from the relay log. This variable has no
effect on slaves for which multithreading is not enabled.
Note that
slave_preserve_commit_order=1
does not preserve the order of non-transactional DML
updates, so these might commit before transactions that
precede them in the relay log, which might result in gaps.
slave_preserve_commit_order=1
requires that --log-bin
and
--log-slave-updates
are
enabled on the slave, and
slave_parallel_type
is set
to LOGICAL_CLOCK
. Before changing this
variable, all replication threads (for all replication
channels if you are using multiple replication channels)
must be stopped.
With
slave_preserve_commit_order
enabled, the executing thread waits until all previous
transactions are committed before committing. While the
slave thread is waiting for other workers to commit their
transactions it reports its status as Waiting for
preceding transaction to commit
. With this mode, a
multithreaded slave never enters a state that the master was
not in. This supports the use of replication for read
scale-out. See
Section 17.4.5, “Using Replication for Scale-Out”.
If
slave_preserve_commit_order=0
is set, the transactions that the slave applies in parallel
may commit out of order. Therefore, checking for the most
recently executed transaction does not guarantee that all
previous transactions from the master have been executed on
the slave. There is a chance of gaps in the sequence of
transactions that have been executed from the slave's relay
log. This has implications for logging and recovery when
using a multithreaded slave. Note that the setting
slave_preserve_commit_order=1
prevents gaps, but does not prevent master log position lag
(where Exec_master_log_pos
is behind the
position up to which transactions have been executed). See
Section 17.5.1.33, “Replication and Transaction Inconsistencies”
for more information.
Property | Value |
---|---|
Command-Line Format | --slave-rows-search-algorithms=value |
Deprecated | 8.0.18 |
System Variable | slave_rows_search_algorithms |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Set |
Default Value (>= 8.0.2) | INDEX_SCAN,HASH_SCAN |
Default Value (<= 8.0.1) | TABLE_SCAN,INDEX_SCAN |
Valid Values |
|
When preparing batches of rows for row-based logging and
replication, this system variable controls how the rows are
searched for matches, in particular whether hash scans are
used. The use of this system variable is now deprecated. The
default setting INDEX_SCAN,HASH_SCAN
is
optimal for performance and works correctly in all
scenarios.
Property | Value |
---|---|
Command-Line Format | --slave-skip-errors=name |
System Variable | slave_skip_errors |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | String |
Default Value | OFF |
Valid Values |
|
Normally, replication stops when an error occurs on the slave, which gives you the opportunity to resolve the inconsistency in the data manually. This variable causes the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value.
Property | Value |
---|---|
Command-Line Format | --slave-sql-verify-checksum[={OFF|ON}] |
System Variable | slave_sql_verify_checksum |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Boolean |
Default Value | ON |
Cause the slave SQL thread to verify data using the checksums read from the relay log. In the event of a mismatch, the slave stops with an error. Setting this variable takes effect for all replication channels immediately, including running channels.
The slave I/O thread always reads checksums if possible when accepting events from over the network.
Property | Value |
---|---|
Command-Line Format | --slave-transaction-retries=# |
System Variable | slave_transaction_retries |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 10 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Sets the maximum number of times for replication slave SQL threads on a single-threaded or multithreaded slave to automatically retry failed transactions before stopping. Setting this variable takes effect for all replication channels immediately, including running channels. The default value is 10. Setting the variable to 0 disables automatic retrying of transactions.
If a replication slave SQL thread fails to execute a
transaction because of an
InnoDB
deadlock or because the
transaction's execution time exceeded
InnoDB
's
innodb_lock_wait_timeout
or
NDB
's
TransactionDeadlockDetectionTimeout
or
TransactionInactiveTimeout
,
it automatically retries
slave_transaction_retries
times before stopping with an error. Transactions with a
non-temporary error are not retried.
The Performance Schema table
replication_applier_status
shows the number of retries that took place on each
replication channel, in the
COUNT_TRANSACTIONS_RETRIES
column. The
Performance Schema table
replication_applier_status_by_worker
shows detailed information on transaction retries by
individual applier threads on a single-threaded or
multithreaded replication slave, and identifies the errors
that caused the last transaction and the transaction
currently in progress to be reattempted.
Property | Value |
---|---|
Command-Line Format | --slave-type-conversions=set |
System Variable | slave_type_conversions |
Scope | Global |
Dynamic | No |
SET_VAR Hint Applies |
No |
Type | Set |
Default Value |
|
Valid Values |
|
Controls the type conversion mode in effect on the slave
when using row-based replication. Its value is a
comma-delimited set of zero or more elements from the list:
ALL_LOSSY
,
ALL_NON_LOSSY
,
ALL_SIGNED
,
ALL_UNSIGNED
. Set this variable to an
empty string to disallow type conversions between the master
and the slave. Setting this variable takes effect for all
replication channels immediately, including running
channels.
For additional information on type conversion modes applicable to attribute promotion and demotion in row-based replication, see Row-based replication: attribute promotion and demotion.
Property | Value |
---|---|
System Variable | sql_slave_skip_counter |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
The number of events from the master that a slave server
should skip. Setting the option has no immediate effect. The
variable applies to the next START
SLAVE
statement; the next
START SLAVE
statement also
changes the value back to 0. When this variable is set to a
nonzero value and there are multiple replication channels
configured, the START SLAVE
statement can only be used with the FOR CHANNEL
clause.
channel
This option is incompatible with GTID-based replication, and
must not be set to a nonzero value when
gtid_mode=ON
. If you need
to skip transactions when employing GTIDs, use
gtid_executed
from the
master instead. See
Injecting empty transactions, for
information about how to do this.
If skipping the number of events specified by setting this variable would cause the slave to begin in the middle of an event group, the slave continues to skip until it finds the beginning of the next event group and begins from that point. For more information, see Section 13.4.2.5, “SET GLOBAL sql_slave_skip_counter Statement”.
Property | Value |
---|---|
Command-Line Format | --sync-master-info=# |
System Variable | sync_master_info |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 10000 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The effects of this variable on a replication slave depend
on whether the slave's
master_info_repository
is
set to FILE
or TABLE
,
as explained in the following paragraphs.
master_info_repository = FILE.
If the value of sync_master_info
is
greater than 0, the slave synchronizes its
master.info
file to disk (using
fdatasync()
) after every
sync_master_info
events. If it is 0,
the MySQL server performs no synchronization of the
master.info
file to disk; instead,
the server relies on the operating system to flush its
contents periodically as with any other file.
master_info_repository = TABLE.
If the value of sync_master_info
is
greater than 0, the slave updates its master info
repository table after every
sync_master_info
events. If it is 0,
the table is never updated.
The default value for sync_master_info
is
10000. Setting this variable takes effect for all
replication channels immediately, including running
channels.
Property | Value |
---|---|
Command-Line Format | --sync-relay-log=# |
System Variable | sync_relay_log |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 10000 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
If the value of this variable is greater than 0, the MySQL
server synchronizes its relay log to disk (using
fdatasync()
) after every
sync_relay_log
events are written to the
relay log. Setting this variable takes effect for all
replication channels immediately, including running
channels.
Setting sync_relay_log
to 0 causes no
synchronization to be done to disk; in this case, the server
relies on the operating system to flush the relay log's
contents from time to time as for any other file.
A value of 1 is the safest choice because in the event of a crash you lose at most one event from the relay log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).
Property | Value |
---|---|
Command-Line Format | --sync-relay-log-info=# |
System Variable | sync_relay_log_info |
Scope | Global |
Dynamic | Yes |
SET_VAR Hint Applies |
No |
Type | Integer |
Default Value | 10000 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The default value for sync_relay_log_info
is 10000. Setting this variable takes effect for all
replication channels immediately, including running
channels.
The effects of this variable on the replication slave depend
on the server's
relay_log_info_repository
setting (FILE
or
TABLE
). If the setting is
TABLE
, the effects of the variable also
depend on whether the storage engine used by the relay log
info table is transactional (such as
InnoDB
) or not transactional
(MyISAM
). The effects of these
factors on the behavior of the server for
sync_relay_log_info
values of zero and
greater than zero are as follows:
sync_relay_log_info = 0
If
relay_log_info_repository
is set to FILE
, the MySQL
server performs no synchronization of the
relay-log.info
file to disk;
instead, the server relies on the operating system
to flush its contents periodically as with any
other file.
If
relay_log_info_repository
is set to TABLE
, and the
storage engine for that table is transactional,
the table is updated after each transaction. (The
sync_relay_log_info
setting is
effectively ignored in this case.)
If
relay_log_info_repository
is set to TABLE
, and the
storage engine for that table is not
transactional, the table is never updated.
sync_relay_log_info =
N
> 0
If
relay_log_info_repository
is set to FILE
, the slave
synchronizes its
relay-log.info
file to disk
(using fdatasync()
) after every
N
transactions.
If
relay_log_info_repository
is set to TABLE
, and the
storage engine for that table is transactional,
the table is updated after each transaction. (The
sync_relay_log_info
setting is
effectively ignored in this case.)
If
relay_log_info_repository
is set to TABLE
, and the
storage engine for that table is not
transactional, the table is updated after every
N
events.