16.1.6.3 Replica Server Options and Variables
This section explains the server options and system variables that apply to replicas 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 source and each replica, 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 source or replica in the replication topology. Example my.cnf
file:
[mysqld]
server-id=3
This section explains startup options for controlling replica 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 replica server starts. Replication-related system variables are discussed later in this section.
-
Property Value Command-Line Format --log-warnings[=#]
Deprecated Yes System Variable log_warnings
Scope Global Dynamic Yes Type Integer Default Value 2
Minimum Value 0
Maximum Value (64-bit platforms) 18446744073709551615
Maximum Value (32-bit platforms) 4294967295
NoteThe
log_error_verbosity
system variable is preferred over, and should be used instead of, the--log-warnings
option orlog_warnings
system variable. For more information, see the descriptions oflog_error_verbosity
andlog_warnings
. The--log-warnings
command-line option andlog_warnings
system variable are deprecated and will be removed in a future MySQL release.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 variable is set to 2 by default. To disable it, set it to 0. The server logs messages about statements that are unsafe for statement-based logging if the value is greater than 0. Aborted connections and access-denied errors for new connection attempts are logged if the value is greater than 1. See Section B.4.2.10, “Communication Errors and Aborted Connections”.
NoteThe effects of this option are not limited to replication. It affects diagnostic messages across a spectrum of server activities.
-
Property Value 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 source. The default name is
master.info
in the data directory. For information about the format of this file, see Section 16.2.4.2, “Replication Applier Metadata Repositories”. -
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 replica tries to reconnect to the source before giving up. The default value is 86400 times. A value of 0 means “infinite”, and the replica attempts to connect forever. Reconnection attempts are triggered when the replica reaches its connection timeout (specified by the
slave_net_timeout
system variable) without receiving data or a heartbeat signal from the source. Reconnection is attempted at intervals set by theMASTER_CONNECT_RETRY
option of theCHANGE 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 theCHANGE MASTER TO
statement instead. -
Property Value 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 16.2.4.1, “The Relay Log”. -
Property Value 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 theN
--relay-log-recovery
option puts data consistency at risk. -
Property Value 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 source until the replication 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
. 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.NoteReplication 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.
Statement-based replication. Tell the replication SQL thread to restrict replication to statements where the default database (that is, the one selected by
USE
) isdb_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 asUPDATE
while a different database (or no database) is selected.some_db.some_table
SET foo='bar'WarningTo 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 will be 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, theUPDATE
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-tableUPDATE
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 todb_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 thesales
database on the replica is changed in accordance with theUPDATE
statement; this occurs whether or not theUSE
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 toUSE sales
, theUPDATE
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 SET col1 = 10, db2.table2 SET 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; sincetable2
is in a different database,table2
on the replica is not changed by theUPDATE
. Now suppose that, instead of theUSE db1
statement, aUSE 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 replica when using statement-based replication. However, if you are using row-based replication, theUPDATE
would changetable1
on the replica, but nottable2
—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 16.2.5, “How Servers Evaluate Replication Filtering Rules”.db_name
.%NoteThis 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
, orROLLBACK
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
. 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.NoteReplication 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.
Statement-based replication. Tells the replication SQL thread not to replicate any statement where the default database (that is, the one selected by
USE
) isdb_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 theUSE
statement). Because thesales
database was specified explicitly in the statement, the statement has not been filtered. However, when using row-based replication, theUPDATE
statement's effects are not propagated to the replica, and the replica's copy of thesales.january
table is unchanged; in this instance,--replicate-ignore-db=sales
causes all changes made to tables in the source's copy of thesales
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 will be 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 16.2.5, “How Servers Evaluate Replication Filtering Rules”.
If you need cross-database updates to work, use
--replicate-wild-ignore-table=
instead. See Section 16.2.5, “How Servers Evaluate Replication Filtering Rules”.db_name
.%NoteThis 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
, orROLLBACK
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 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 16.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create such a filter by issuing aCHANGE REPLICATION FILTER REPLICATE_DO_TABLE
statement.NoteReplication 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.
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 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 16.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create such a filter by issuing aCHANGE REPLICATION FILTER REPLICATE_IGNORE_TABLE
statement.NoteReplication 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.
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 replica to create a replication filter that translates the specified database to
to_name
if it wasfrom_name
on the source. Only statements involving tables are affected, not statements such asCREATE DATABASE
,DROP DATABASE
, andALTER 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 aCHANGE 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 theUSE
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 theUSE
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
andto_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
.NoteGlobal 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.
-
Property Value 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
Property Value 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 theLIKE
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 16.2.5, “How Servers Evaluate Replication Filtering Rules”. You can also create such a filter by issuing aCHANGE REPLICATION FILTER REPLICATE_WILD_DO_TABLE
statement.NoteReplication 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.
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 withfoo
and the table name starts withbar
.If the table name pattern is
%
, it matches any table name and the option also applies to database-level statements (CREATE DATABASE
,DROP DATABASE
, andALTER DATABASE
). For example, if you use--replicate-wild-do-table=foo%.%
, database-level statements are replicated if the database name matches the patternfoo%
.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 themy1ownAABCdb
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 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 16.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.NoteReplication 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.
As an example,
--replicate-wild-ignore-table=foo%.bar%
does not replicate updates that use a table where the database name starts withfoo
and the table name starts withbar
.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 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]Property Value Command-Line Format --slave-skip-errors=name
System Variable slave_skip_errors
Scope Global Dynamic No Type String Default Value OFF
Valid Values OFF
[list of error codes]
all
ddl_exist_errors
Normally, replication stops when an error occurs on the replica, which gives you the opportunity to resolve the inconsistency in the data manually. This option causes 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.The shorthand value
ddl_exist_errors
is equivalent to the error code list1007,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 replica to ignore all error messages and keeps going regardless of what happens. Needless to say, if you useall
, 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.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 replica examines checksums read from the relay log,. In the event of a mismatch, the replica 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 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 replication SQL thread continues to run, and the output fromSHOW SLAVE STATUS
displaysYes
in both theSlave_IO_Running
and theSlave_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
MySQL 5.7 supports logging of replication metadata to tables rather than files. Writing of the source metadata repository and the replica metadata repository can be configured separately using these two system variables:
For information about these variables, see Section 16.1.6.3, “Replica Server Options and Variables”.
These variables can be used to make a replica resilient to unexpected halts. See Section 16.3.2, “Handling an Unexpected Halt of a Replica”, for more information.
The info 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 16.2.4, “Relay Log and Replication Applier Metadata Repositories”.
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.
-
Property Value 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 theinit_connect
variable. The setting of this variable takes effect for subsequentSTART SLAVE
statements.NoteThe replication SQL thread sends an acknowledgment to the client before it executes
init_slave
. Therefore, it is not guaranteed thatinit_slave
has been executed whenSTART SLAVE
returns. See Section 13.4.2.5, “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 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, whenbinlog_format=STATEMENT
is set, or whenbinlog_format=MIXED
is set and the statement is logged in statement format. Slow queries that are logged in row format whenbinlog_format=MIXED
is set, or that are logged whenbinlog_format=ROW
is set, are not added to the replica's slow query log, even iflog_slow_slave_statements
is enabled.Setting
log_slow_slave_statements
has no immediate effect. The state of the variable applies on all subsequentSTART SLAVE
statements. Also note that the global setting forlong_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 issuingSTOP SLAVE
andSTART SLAVE
statements with theSQL_THREAD
option). -
Property Value Command-Line Format --master-info-repository={FILE|TABLE}
System Variable master_info_repository
Scope Global Dynamic Yes Type String Default Value FILE
Valid Values FILE
TABLE
The setting of this variable determines whether the replica server logs source status and connection information to a
FILE
(master.info
), or to aTABLE
(mysql.slave_master_info
). You can change the value of this variable only when no replication threads are executing.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.This variable must be set to
TABLE
before configuring multiple replication channels. If you are using multiple replication channels, you cannot set the value back toFILE
. -
Property Value 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 usesmax_binlog_size
for both the binary log and the relay log. Ifmax_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 setmax_relay_log_size
to between 4096 bytes and 1GB (inclusive), or to 0. The default value is 0. See Section 16.2.2, “Replication Implementation Details”. -
Property Value Command-Line Format --relay-log=file_name
System Variable relay_log
Scope Global Dynamic 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 ishost_name
-relay-bin
, wherehost_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.
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 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 Type File name Default Value *host_name*-relay-bin.index
The name for the relay log index file. For the default replication channel, the default name is
. For non-default replication channels, the default name ishost_name
-relay-bin.index
, wherehost_name
-relay-bin-channel
.indexchannel
is the name of the replication channel recorded in this relay log index.The server writes the file in the data directory unless the name is given with a leading absolute path name to specify a different directory. 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_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
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, when
relay_log_info_repository=FILE
. Ifrelay_log_info_repository=TABLE
, it is the file name that would be used in case the repository was changed toFILE
). The default name isrelay-log.info
in the data directory. -
Property Value 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 FILE
TABLE
This variable determines whether the replica's position in the relay logs is written to a
FILE
(relay-log.info
) or to aTABLE
(mysql.slave_relay_log_info
). You can change the value of this variable only when no replication threads are executing.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 description for further information.This variable must be set to
TABLE
before configuring multiple replication channels. If you are using multiple replication channels then you cannot set the value back toFILE
. -
Property Value 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
). -
Property Value 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 16.3.2, “Handling an Unexpected Halt of a Replica” for more information.This variable also interacts with the
relay_log_purge
variable, which controls purging of logs when they are no longer needed. Enablingrelay_log_recovery
whenrelay_log_purge
is disabled risks reading the relay log from files that were not purged, leading to data inconsistency.When using a multithreaded replica (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. Enablingrelay_log_recovery
when there are inconsistencies causes an error and the option has no effect. The solution in this situation is to issueSTART SLAVE UNTIL SQL_AFTER_MTS_GAPS
, which brings the server to a more consistent state, then issueRESET SLAVE
to remove the relay logs. See Section 16.4.1.32, “Replication and Transaction Inconsistencies” for more information.NoteThis 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 source or another group.
-
Property Value 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.
-
Property Value Command-Line Format --report-host=host_name
System Variable report_host
Scope Global Dynamic No Type String The host name or IP addr