MySQL 5.6 Reference Manual Including MySQL NDB Cluster 7.3-7.4 Reference Guide
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 source 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 source server or replica. Example
my.cnf
file:
[mysqld] server-id=3
This section explains startup options for controlling replicas.
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 replica server starts.
Replication-related system variables are discussed later in this
section.
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
replication SQL thread has started,
value
log events are permitted to
be executed; after that, the replication SQL thread does not
receive any more events, just as if the network connection
from the source were cut. The 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.
This option is used internally by the MySQL test suite for replication testing and debugging. It is not intended for use in a production setting.
--disconnect-slave-event-count
Command-Line Format | --disconnect-slave-event-count=# |
---|---|
Type | Integer |
Default Value | 0 |
This option is used internally by the MySQL test suite for replication testing and debugging.
Command-Line Format | --log-slow-slave-statements[={OFF|ON}] (5.6.10) |
---|---|
Removed | 5.6.11 |
Type | Boolean |
Default Value | OFF |
This command-line option was removed in MySQL 5.6.11 and
replaced by the
log_slow_slave_statements
system variable. The system variable can be set on the
command line or in option files the same way as the option,
so there is no need for any changes at server startup, but
the system variable also makes it possible to examine or set
the value at runtime.
Command-Line Format | --log-warnings[=#] |
---|---|
System Variable | log_warnings |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Causes the server to record more messages to the error log
about what it is doing. With respect to replication, the
server generates warnings that it succeeded in reconnecting
after a network or connection failure, and provides
information about how each replication thread started. This
option is enabled (1) by default; to disable it, use
--log-warnings=0
. If the
value is greater than 1, aborted connections are written to
the error log, and access-denied errors for new connection
attempts are written. See
Section B.3.2.10, “Communication Errors and Aborted Connections”.
The effects of this option are not limited to replication. It affects diagnostic messages across a spectrum of server activities.
Command-Line Format | --master-info-file=file_name |
---|---|
Type | File name |
Default Value | master.info |
The name to use for the file in which the replica records
information about the replication source server. The default
name is master.info
in the data
directory. For information about the format of this file,
see Section 17.2.2.2, “Replication Metadata Repositories”.
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 replica tries to connect to the
replication source server before giving up. Reconnects are
attempted at intervals set by the
MASTER_CONNECT_RETRY
option of the
CHANGE MASTER TO
statement
(default 60). Reconnection attempts are triggered when the
replica reaches its connection timeout (specified by the
slave_net_timeout
system
variable) without receiving data from the source. The
default value is 86400. A value of 0 means
“infinite”; the replica attempts to connect
forever.
This option is deprecated; expect it be removed in a future
MySQL release. Applications should be updated to use the
MASTER_RETRY_COUNT
option of the
CHANGE MASTER TO
statement
instead.
Command-Line Format | --max-relay-log-size=# |
---|---|
System Variable | max_relay_log_size |
Scope | Global |
Dynamic | Yes |
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.2.1, “The Relay Log”.
Command-Line Format | --relay-log-purge[={OFF|ON}] |
---|---|
System Variable | relay_log_purge |
Scope | Global |
Dynamic | Yes |
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
puts data consistency at risk.
Command-Line Format | --relay-log-space-limit=# |
---|---|
System Variable | relay_log_space_limit |
Scope | Global |
Dynamic | 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 replica. A value of 0 means
“no limit”. This is useful for a replica server
host that has limited disk space. When the limit is reached,
the replication I/O thread stops reading binary log events
from the replication source 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.
Command-Line Format | --replicate-do-db=name |
---|---|
Type | String |
The effects of this option depend on whether statement-based or row-based replication is in use.
Statement-based replication.
Tell the replication 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 replica is started
with --replicate-do-db=sales
and you issue the following statements on the source, 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 replication 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 replica is started with
--replicate-do-db=sales
and
row-based replication is in effect, and then the following
statements are run on the source:
USE prices; UPDATE sales.february SET amount=amount+100;
The february
table in the
sales
database on the replica 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 source has
no effect on the replica 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 replica is started with
--replicate-do-db=db1
, and
the following statements are executed on the source:
USE db1; UPDATE db1.table1, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;
If you are using statement-based replication, then both
tables are updated on the replica. However, when using
row-based replication, only table1
is
affected on the replica; since table2
is
in a different database, table2
on the
replica 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, db2.table2 SET db1.table1.col1 = 10, db2.table2.col2 = 20;
In this case, the UPDATE
statement would have no effect on the replica when using
statement-based replication. However, if you are using
row-based replication, the
UPDATE
would change
table1
on the replica, 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.3, “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.
Command-Line Format | --replicate-ignore-db=name |
---|---|
Type | String |
As with --replicate-do-db
,
the effects of this option depend on whether statement-based
or row-based replication is in use.
Statement-based replication.
Tells the replication 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 replication 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
replica is started with
--replicate-ignore-db=sales
and you issue the following statements on the source:
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
replica, and the replica's copy of the
sales.january
table is unchanged; in this
instance,
--replicate-ignore-db=sales
causes all changes made to tables in
the source's copy of the sales
database to be ignored by the replica.
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 is treated as the name of a single database.
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.3, “How Servers Evaluate Replication Filtering Rules”.
If you need cross-database updates to work, use
--replicate-wild-ignore-table=
instead. See Section 17.2.3, “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
Command-Line Format | --replicate-do-table=name |
---|---|
Type | String |
Creates a replication filter by telling the replication 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.3, “How Servers Evaluate Replication Filtering Rules”.
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
Command-Line Format | --replicate-ignore-table=name |
---|---|
Type | String |
Creates a replication filter by telling the replication 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.3, “How Servers Evaluate Replication Filtering Rules”.
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
Command-Line Format | --replicate-rewrite-db=old_name->new_name |
---|---|
Type | String |
Tells the replica to create a replication filter that
translates the specified database to
to_name
if it was
from_name
on the source. Only
statements involving tables are affected, not statements
such as CREATE DATABASE
,
DROP DATABASE
, and
ALTER DATABASE
.
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 the
--replicate-rewrite-db
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
"
The effect of the
--replicate-rewrite-db
option
differs depending on whether statement-based or row-based
binary logging format is used for the query. With
statement-based format, DML statements are translated based
on the current database, as specified by the
USE
statement. With row-based
format, DML statements are translated based on the database
where the modified table exists. DDL statements are always
filtered based on the current database, as specified by the
USE
statement, regardless of
the binary logging format.
To ensure that rewriting produces the expected results,
particularly in combination with other replication filtering
options, follow these recommendations when you use the
--replicate-rewrite-db
option:
Create the from_name
and
to_name
databases manually on
the source and the replica with different names.
If you use statement-based or mixed binary logging
format, do not use cross-database queries, and do not
specify database names in queries. For both DDL and DML
statements, rely on the
USE
statement to specify
the current database, and use only the table name in
queries.
If you use row-based binary logging format exclusively,
for DDL statements, rely on the
USE
statement to specify
the current database, and use only the table name in
queries. For DML statements, you can use a fully
qualified table name
(db
.table
)
if you want.
If these recommendations are followed, it is safe to use the
--replicate-rewrite-db
option
in combination with table-level replication filtering
options such as
--replicate-do-table
.
Command-Line Format | --replicate-same-server-id[={OFF|ON}] |
---|---|
Type | Boolean |
Default Value | OFF |
To be used on replica servers. Usually you should use the
default setting of 0, to prevent infinite loops caused by
circular replication. If set to 1, the replica does not skip
events having its own server ID. Normally, this is useful
only in rare configurations. Cannot be set to 1 if
log_slave_updates
is
enabled. By default, the replication I/O thread does not
write binary log events to the relay log if they have the
replica's server ID (this optimization helps save disk
usage). If you want to use
--replicate-same-server-id
,
be sure to start the replica with this option before you
make the replica read its own events that you want the
replication SQL thread to execute.
--replicate-wild-do-table=
db_name.tbl_name
Command-Line Format | --replicate-wild-do-table=name |
---|---|
Type | String |
Creates a replication filter by telling the replication SQL
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.3, “How Servers Evaluate Replication Filtering Rules”.
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.
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
Command-Line Format | --replicate-wild-ignore-table=name |
---|---|
Type | String |
Creates a replication filter which keeps the replication SQL 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.3, “How Servers Evaluate Replication Filtering Rules”.
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.
Command-Line Format | --skip-slave-start[={OFF|ON}] |
---|---|
Type | Boolean |
Default Value | OFF |
Tells the replica server not to start the replication
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]
Command-Line Format | --slave-skip-errors=name |
---|---|
System Variable | slave_skip_errors |
Scope | Global |
Dynamic | No |
Type | String |
Default Value | OFF |
Valid Values |
|
Normally, replication stops when an error occurs on the replica. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the replication 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 replicas becoming hopelessly out of synchrony with the source, with you having no idea why this has occurred.
For error codes, you should use the numbers provided by the
error message in the replica's error log and in the output
of SHOW SLAVE STATUS
.
Appendix B, Error Messages and Common Problems, lists server error codes.
You can also (but should not) use the very nonrecommended
value of all
to cause the replica 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 replica's data is not anywhere close to what it
is on the source. You have been warned.
MySQL 5.6 as well as MySQL NDB Cluster 7.3 and
later support an additional shorthand value
ddl_exist_errors
, which is equivalent to
the error code list
1007,1008,1050,1051,1054,1060,1061,1068,1094,1146
.
Examples:
--slave-skip-errors=1062,1053 --slave-skip-errors=all --slave-skip-errors=ddl_exist_errors
--slave-sql-verify-checksum={0|1}
Command-Line Format | --slave-sql-verify-checksum[={OFF|ON}] |
---|---|
Type | Boolean |
Default Value | ON |
When this option is enabled, the replica examines checksums read from the relay log. In the event of a mismatch, replication stops with an error.
MySQL 5.6 and higher supports logging of replica status information to tables rather than files. Writing of the replica's connection metadata repository and applier metadata repository can be configured separately using these two system variables:
For information about these variables, see Section 17.1.4.3, “Replica Server Options and Variables”.
The replication metadata repositories 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.2, “Relay Log and Replication Metadata Repositories”.
The following options have been removed and replaced by the
functions of the CHANGE MASTER TO ...
statement (see Section 13.4.2.1, “CHANGE MASTER TO Statement”). If you
attempt to start mysqld with any of these
options in MySQL 5.6, the server aborts with an
unknown variable error.
The following list describes system variables for controlling
replica servers. They can be set at server startup and some of
them can be changed at runtime using
SET
.
Server options used with replicas are listed earlier in this
section.
Command-Line Format | --init-slave=name |
---|---|
System Variable | init_slave |
Scope | Global |
Dynamic | Yes |
Type | String |
This variable is similar to
init_connect
, but is a
string to be executed by a replica server each time the
replication SQL thread starts. The format of the string is
the same as for the
init_connect
variable.
The replication 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.5, “START SLAVE Statement”, for more
information.
Command-Line Format | --log-slow-slave-statements[={OFF|ON}] |
---|---|
Introduced | 5.6.11 |
System Variable | log_slow_slave_statements |
Scope | Global |
Dynamic | Yes |
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 replica. 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 replica'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 replica'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 replication SQL thread to
implement the change there (for example, by issuing
STOP SLAVE
and
START SLAVE
statements with
the SQL_THREAD
option).
Command-Line Format | --master-info-repository={FILE|TABLE} |
---|---|
System Variable | master_info_repository |
Scope | Global |
Dynamic | Yes |
Type | String |
Default Value | FILE |
Valid Values |
|
The setting of this variable determines whether the replica
server logs source status and connection information to a
FILE
(master.info
),
or to a TABLE
(mysql.slave_master_info
).
The setting of this variable also has a direct influence on
the effect had by the setting of the
sync_master_info
system
variable; see that variable description for further
information.
For FILE
logging, you can change the name
of the file using the
--master-info-file
server
option.
Command-Line Format | --max-relay-log-size=# |
---|---|
System Variable | max_relay_log_size |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 1073741824 |
If a write by a replica to its relay log causes the current
log file size to exceed the value of this variable, the
replica 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.1, “Replication Threads”.
Command-Line Format | --relay-log=file_name |
---|---|
System Variable | relay_log |
Scope | Global |
Dynamic | No |
Type | File name |
The base name for the relay log. The default base name is
.
host_name
-relay-bin
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.
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.
System Variable | relay_log_basename |
---|---|
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | datadir + '/' + hostname + '-relay-bin' |
Holds the base name and complete path to the relay log file. The maximum variable length is 256. This variable is set by the server and is read only.
Command-Line Format | --relay-log-index=file_name |
---|---|
System Variable | relay_log_index |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | *host_name*-relay-bin.index |
The name for the relay log index file. The maximum variable
length is 256. The default name is
in the data directory, where
host_name
-relay-bin.indexhost_name
is the name of the
replica server.
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”.
Command-Line Format | --relay-log-info-file=file_name |
---|---|
System Variable | relay_log_info_file |
Scope | Global |
Dynamic | No |
Type | File name |
Default Value | relay-log.info |
The name of the file in which the replica records
information about the relay logs. The default name is
relay-log.info
in the data directory.
For information about the format of this file, see
Section 17.2.2.2, “Replication Metadata Repositories”.
Command-Line Format | --relay-log-info-repository=value |
---|---|
System Variable | relay_log_info_repository |
Scope | Global |
Dynamic | Yes |
Type | String |
Default Value | FILE |
Valid Values |
|
This variable determines whether the replica's position
in the relay logs is written to a FILE
(relay-log.info
) or to a
TABLE
(mysql.slave_relay_log_info
).
The setting of this variable also has a direct influence on
the effect had by the setting of the
sync_relay_log_info
system
variable; see that variable descrption for further
information.
This option can be used to make a replica resilient to unexpected halts. See Section 17.3.2, “Handling an Unexpected Halt of a Replica Server”, for more information.
Command-Line Format | --relay-log-purge[={OFF|ON}] |
---|---|
System Variable | relay_log_purge |
Scope | Global |
Dynamic | Yes |
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
).
Command-Line Format | --relay-log-recovery[={OFF|ON}] |
---|---|
System Variable | relay_log_recovery |
Scope | Global |
Dynamic | 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 source then continues. This global variable is
read-only at runtime. Its value can set with the
--relay-log-recovery
option
at replica startup, which should be used following an
unexpected halt of a replica to ensure that no possibly
corrupted relay logs are processed. This option can be
enabled to make a replica resilient to unexpected halts. See
Section 17.3.2, “Handling an Unexpected Halt of a Replica Server”
for more information.
This variable also interacts with the
relay_log_purge
variable,
which controls purging of logs when they are no longer
needed. 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.
When relay_log_recovery
is
enabled and the replica has stopped due to errors
encountered while running in multithreaded mode, you cannot
execute CHANGE MASTER TO
if
there are any gaps in the log. The solution in this
situation is to issue
START SLAVE
UNTIL SQL_AFTER_MTS_GAPS
to ensure that all gaps
are processed before switching back to single-threaded mode
or executing a CHANGE MASTER TO
statement.
Command-Line Format | --relay-log-space-limit=# |
---|---|
System Variable | relay_log_space_limit |
Scope | Global |
Dynamic | 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.
Command-Line Format | --report-host=host_name |
---|---|
System Variable | report_host |
Scope | Global |
Dynamic | No |
Type | String |
The host name or IP address of the replica to be reported to
the replication source server during replica registration.
This value appears in the output of
SHOW SLAVE HOSTS
on the
source server. Leave the value unset if you do not want the
replica to register itself with the source.
It is not sufficient for the source to simply read the IP address of the replica from the TCP/IP socket after the replica connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the replica from the source or other hosts.
Command-Line Format | --report-password=name |
---|---|
System Variable | report_password |
Scope | Global |
Dynamic | No |
Type | String |
The account password to be reported to the source during
replica registration. This value appears in the output of
SHOW SLAVE HOSTS
on the
source if the source 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.
Command-Line Format | --report-port=port_num |
---|---|
System Variable | report_port |
Scope | Global |
Dynamic | No |
Type | Integer |
Default Value | [slave_port] |
Minimum Value | 0 |
Maximum Value | 65535 |
The TCP/IP port number for connecting to the replica, to be reported to the source during replica registration. Set this only if the replica is listening on a nondefault port or if you have a special tunnel from the source or other clients to the replica. If you are not sure, do not use this option.
The default value for this option is the port number
actually used by the replica. This is also the default value
displayed by SHOW SLAVE
HOSTS
.
Command-Line Format | --report-user=name |
---|---|
System Variable | report_user |
Scope | Global |
Dynamic | No |
Type | String |
The account user name of the replica to be reported to the
replication source server during replica registration. This
value appears in the output of SHOW
SLAVE HOSTS
on the source if the source 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.
Command-Line Format | --rpl-semi-sync-slave-enabled[={OFF|ON}] |
---|---|
System Variable | rpl_semi_sync_slave_enabled |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Controls whether semisynchronous replication is enabled on
the replica. 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 replica-side semisynchronous replication plugin is installed.
rpl_semi_sync_slave_trace_level
Command-Line Format | --rpl-semi-sync-slave-trace-level=# |
---|---|
System Variable | rpl_semi_sync_slave_trace_level |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 32 |
The semisynchronous replication debug trace level on the
replica. See
rpl_semi_sync_master_trace_level
for the permissible values.
This variable is available only if the replica-side semisynchronous replication plugin is installed.
Command-Line Format | --rpl-stop-slave-timeout=seconds |
---|---|
Introduced | 5.6.13 |
System Variable | rpl_stop_slave_timeout |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 31536000 |
Minimum Value | 2 |
Maximum Value | 31536000 |
In MySQL 5.6.13 and higher, 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 SQL statements using
different client connections to the replica.
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 replication threads to stop, but the
replication threads continue to try to stop, and the
STOP SLAVE
instruction remains in effect.
Once the replication threads are no longer busy, the
STOP SLAVE
statement is executed and the
replica stops.
Command-Line Format | --slave-checkpoint-group=# |
---|---|
System Variable | slave_checkpoint_group |
Scope | Global |
Dynamic | Yes |
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 replica before a checkpoint
operation is called to update its status as shown by
SHOW SLAVE STATUS
. Setting
this variable has no effect on replicas for which
multithreading is not enabled.
Multithreaded replicas are not currently supported by NDB Cluster, which silently ignores the setting for this variable. See Section 18.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.
Command-Line Format | --slave-checkpoint-period=# |
---|---|
System Variable | slave_checkpoint_period |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 300 |
Minimum Value | 1 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
Unit | milliseconds |
Sets the maximum time (in milliseconds) that is allowed to
pass before a checkpoint operation is called to update the
status of a multithreaded replica as shown by
SHOW SLAVE STATUS
. Setting
this variable has no effect on replicas for which
multithreading is not enabled.
Multithreaded replicas are not currently supported by NDB Cluster, which silently ignores the setting for this variable. See Section 18.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).
Command-Line Format | --slave-compressed-protocol[={OFF|ON}] |
---|---|
System Variable | slave_compressed_protocol |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | OFF |
Whether to use compression of the source/replica protocol if both source and replica support it. If this variable is disabled (the default), connections are uncompressed. See also Section 4.2.6, “Connection Compression Control”.
Command-Line Format | --slave-exec-mode=mode |
---|---|
System Variable | slave_exec_mode |
Scope | Global |
Dynamic | Yes |
Type | Enumeration |
Default Value |
|
Valid Values |
|
Controls how a replication 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-source replication, circular replication, and some
other special replication scenarios for NDB Cluster
Replication. (See
Section 18.6.10, “NDB Cluster Replication: Bidrectional and Circular Replication”,
and
Section 18.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 5.6, STRICT
mode is the default value.
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-source replication or circular
replication is employed, and is not recommended for use in
other cases.
Command-Line Format | --slave-load-tmpdir=dir_name |
---|---|
System Variable | slave_load_tmpdir |
Scope | Global |
Dynamic | No |
Type | Directory name |
Default Value | Value of --tmpdir |
The name of the directory where the replica 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 replication 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 source is huge, the temporary files on
the replica are huge, too. Therefore, it might be advisable
to use this option to tell the replica 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.
Command-Line Format | --slave-max-allowed-packet=# |
---|---|
System Variable | slave_max_allowed_packet |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 1073741824 |
Minimum Value | 1024 |
Maximum Value | 1073741824 |
This variable sets the maximum packet size for the
replication SQL and I/O threads, so that large updates using
row-based replication do not cause replication to fail
because an update exceeded
max_allowed_packet
.
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.
Command-Line Format | --slave-net-timeout=# |
---|---|
System Variable | slave_net_timeout |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 3600 |
Minimum Value | 1 |
Maximum Value | 4294967295 |
The number of seconds to wait for more data from the source
before the replica considers the connection broken, aborts
the read, and tries to reconnect. 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.
The default is 3600 seconds (one hour).
Command-Line Format | --slave-parallel-workers=# |
---|---|
System Variable | slave_parallel_workers |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value | 1024 |
Sets the number of worker threads on the replica for executing replication events (transactions) in parallel. Setting this variable to 0 (the default) disables parallel execution. The maximum is 1024.
Multithreaded replicas are not currently supported by NDB Cluster, which silently ignores the setting for this variable. See Section 18.6.3, “Known Issues in NDB Cluster Replication”, for more information.
When parallel execution is enabled, the replication SQL thread acts as the coordinator for the worker threads, among which transactions are distributed on a per-database basis. This means that a worker thread on the replica can process successive transactions on a given database without waiting for updates to other databases to complete. The current implementation of multithreading on the replica assumes that the data is partitioned per database, and that updates within a given database occur in the same relative order as they do on the source, in order to work correctly. However, transactions do not need to be coordinated between any two databases.
Due to the fact that transactions on different databases can
occur in a different order on the replica than on the
source, checking for the most recently executed transaction
does not guarantee that all previous transactions from the
source have been executed on the replica. This has
implications for logging and recovery when using a
multithreaded replica. For information about how to
interpret binary logging information when using
multithreading on the replica, see
Section 13.7.5.35, “SHOW SLAVE STATUS Statement”. In addition, this means
that START
SLAVE UNTIL
is not supported with a multithreaded
replica.
When multithreading is enabled,
slave_transaction_retries
is treated as equal to 0, and cannot be changed. (Currently,
retrying of transactions is not supported with multithreaded
replicas.)
You should also note that enforcing foreign key relationships between tables in different databases causes multithreaded replicas to use sequential rather than parallel mode, which can have a negative impact on performance. (Bug #14092635)
Command-Line Format | --slave-pending-jobs-size-max=# |
---|---|
System Variable | slave_pending_jobs_size_max |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 16M |
Minimum Value | 1024 |
Maximum Value | 16EiB |
Unit | bytes |
Block Size | 1024 |
For multithreaded replicas, this variable sets the maximum amount of memory (in bytes) available to worker queues on the replica holding events not yet applied. Setting this variable has no effect on replicas for which multithreading is not enabled.
The minimum possible value for this variable is 1024; the default is 16MB. The maximum possible value is 18446744073709551615 (16 exabytes). Values that are not exact multiples of 1024 are rounded down to the next-highest multiple of 1024 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 worker threads on the replica have empty queues, and then processed. All subsequent transactions are held until the large transaction has been completed.
Command-Line Format | --slave-rows-search-algorithms=value |
---|---|
System Variable | slave_rows_search_algorithms |
Scope | Global |
Dynamic | Yes |
Type | Set |
Default Value | TABLE_SCAN,INDEX_SCAN |
Valid Values |
|
When preparing batches of rows for row-based logging and replication, this variable controls how the rows are searched for matches—that is, whether or not hashing is used for searches using a primary or unique key, some other key, or using no key at all. Setting this variable takes effect for all replication channels immediately, including running channels. option.
Specify a comma-separated list of any 2 (or all 3) values
from the list INDEX_SCAN
,
TABLE_SCAN
, HASH_SCAN
.
The value is expected as a string, so if set at runtime
rather than at server startup, the value must be quoted. In
addition, the value must not contain any spaces. Possible
combinations (lists) and their effects are shown in the
following table:
Index used / option value | INDEX_SCAN,HASH_SCAN or
INDEX_SCAN,TABLE_SCAN,HASH_SCAN |
INDEX_SCAN,TABLE_SCAN |
TABLE_SCAN,HASH_SCAN |
---|---|---|---|
Primary key or unique key | Index scan | Index scan | Hash scan over index |
(Other) Key | Hash scan over index | Index scan | Hash scan over index |
No index | Hash scan | Table scan | Hash scan |
The order in which the algorithms are specified in the list
makes no difference to the order in which they are displayed
by a SELECT
or
SHOW VARIABLES
statement
(which is the same as that used in the table just shown
previously).
The default value is
TABLE_SCAN,INDEX_SCAN
, which means
that all searches that can use indexes do use them, and
searches without any indexes use table scans.
To use hashing for any searches that do not use a
primary or unique key, set this option to
INDEX_SCAN,HASH_SCAN
. Specifying
INDEX_SCAN,TABLE_SCAN,HASH_SCAN
has
the same effect as specifying
INDEX_SCAN,HASH_SCAN
.
To force hashing for all searches,
set this option to
TABLE_SCAN,HASH_SCAN
.
It is possible to specify a single value, but this is not
optimal, because setting a single value limits searches to
using only that algorithm. In particular, setting
INDEX_SCAN
alone is not recommended, as
in that case searches are unable to find rows at all if no
index is present.
There is a performance advantage for
INDEX_SCAN
and
HASH_SCAN
only if the row events are
big enough. The size of row events is configured using
--binlog-row-event-max-size
. For
example, suppose a DELETE
statement which deletes 25,000 rows generates large
Delete_row_event
events. In this case
if
slave_rows_search_algorithms
is set to INDEX_SCAN
or
HASH_SCAN
there is a performance
improvement. However, if there are 25,000
DELETE
statements and each
is represented by a separate event then setting
slave_rows_search_algorithms
to INDEX_SCAN
or
HASH_SCAN
provides no performance
improvement while executing these separate events.
Command-Line Format | --slave-skip-errors=name |
---|---|
System Variable | slave_skip_errors |
Scope | Global |
Dynamic | No |
Type | String |
Default Value | OFF |
Valid Values |
|
Normally, replication stops when an error occurs on the replica. This gives you the opportunity to resolve the inconsistency in the data manually. This variable tells the replication SQL thread to continue replication when a statement returns any of the errors listed in the variable value.
Command-Line Format | --slave-sql-verify-checksum[={OFF|ON}] |
---|---|
System Variable | slave_sql_verify_checksum |
Scope | Global |
Dynamic | Yes |
Type | Boolean |
Default Value | ON |
Cause the replication SQL thread to verify data using the checksums read from the relay log. In the event of a mismatch, the replica stops with an error.
The replication I/O thread always reads checksums if possible when accepting events from over the network.
Command-Line Format | --slave-transaction-retries=# |
---|---|
System Variable | slave_transaction_retries |
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 10 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
If a replication 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. The default value is
10.
Transactions cannot be retried when using a multithreaded
replica. In other words, whenever
slave_parallel_workers
is
greater than 0, slave_transaction_retries
is treated as equal to 0, and cannot be changed.
Command-Line Format | --slave-type-conversions=set |
---|---|
System Variable | slave_type_conversions |
Scope | Global |
Dynamic | Yes |
Type | Set |
Default Value |
|
Valid Values (≥ 5.6.13) |
|
Valid Values (≤ 5.6.12) |
|
Controls the type conversion mode in effect on the replica
when using row-based replication. In MySQL 5.6.13 and
higher, 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 source
and the replica. Changes require a restart of the replica to
take effect.
ALL_SIGNED
and
ALL_UNSIGNED
were added in MySQL 5.6.13
(Bug#15831300). 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.
System Variable | sql_slave_skip_counter |
---|---|
Scope | Global |
Dynamic | Yes |
Type | Integer |
Default Value | 0 |
Minimum Value | 0 |
Maximum Value (64-bit platforms) | 18446744073709551615 |
Maximum Value (32-bit platforms) | 4294967295 |
The number of events from the source that a replica server should skip.
This option is incompatible with GTID-based replication, and
must not be set to a nonzero value when
gtid_mode=ON
. In MySQL
5.6.10 and higher, trying to do so is specifically
disallowed. (Bug #15833516) If you need to skip transactions
when employing GTIDs, use
gtid_executed
from the
source 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 replica to begin in the middle of an event group, the replica 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.4, “SET GLOBAL sql_slave_skip_counter Statement”.
Command-Line Format | --sync-master-info=# |
---|---|
System Variable | sync_master_info |
Scope | Global |
Dynamic | Yes |
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 replica depend on whether
the replica'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 replica 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 replica updates its connection
metadata repository table after every
sync_master_info
events. If it is 0,
the table is never updated.
Command-Line Format | --sync-relay-log=# |
---|---|
System Variable | sync_relay_log |
Scope | Global |
Dynamic | Yes |
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 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 an unexpected exit 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).
Command-Line Format | --sync-relay-log-info=# |
---|---|
System Variable | sync_relay_log_info |
Scope | Global |
Dynamic | Yes |
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.
The effects of this variable on the replica 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 replica
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.