- MySQL 5.1 Reference Manual :: 16 Replication :: 16.1 Replication Configuration :: 16.1.3 Replication and Binary Logging Options and Variables :: 16.1.3.3 Replication Slave Options and Variables
-
- MySQL 5.1 Reference Manual
- Preface, Notes, Licenses
- 1 General Information
- 2 Installing and Upgrading MySQL
- 3 Tutorial
- 4 MySQL Programs
- 5 MySQL Server Administration
- 6 Backup and Recovery
- 7 Optimization
- 8 Language Structure
- 9 Internationalization and Localization
- 10 Data Types
- 11 Functions and Operators
- 12 SQL Statement Syntax
- 13 Storage Engines
- 14 High Availability and Scalability
- 15 MySQL Enterprise Monitor
- 16 Replication
- 17 MySQL Cluster NDB 6.X/7.X
- 18 Partitioning
- 19 Stored Programs and Views
- 20 INFORMATION_SCHEMA Tables
- 21 Connectors and APIs
- 22 Extending MySQL
- A MySQL 5.1 Frequently Asked Questions
- B Errors, Error Codes, and Common Problems
- C MySQL Change History
- D Restrictions and Limits
- Index
- Standard Index
- C Function Index
- Command Index
- Function Index
- INFORMATION_SCHEMA Index
- Transaction Isolation Level Index
- JOIN Types Index
- Operator Index
- Option Index
- Privileges Index
- SQL Modes Index
- Status Variable Index
- Statement/Syntax Index
- System Variable Index
This section describes the server options and system variables that you can use on slave replication servers. You can specify the options either on the command line or in an option file. Many of the options can be reset while the server is running by using the
CHANGE MASTER TO
statement. You can specify system variable values usingSET
.Server ID. On the master and each slave, you must use the
server-id
option to establish a unique replication ID in the range from 1 to 232 – 1. “Unique”, means that each ID must be different from every other ID in use by any other replication master or slave. Example:server-id=3
.Important
Certain options are handled in a special way in order to ensure that the active replication configuration is not inadvertently altered or affected:
In MySQL 5.1.16 and earlier, these options are ignored if the
master.info
file exists (that is, when the MySQL server has already previously been configured for replication). If the file exists and these options are present in themy.cnf
or as options on the command line to mysqld, they are silently ignored and the information inmaster.info
used instead.Options deprecated. Beginning with MySQL 5.1.17, these options are deprecated. They are removed in MySQL 5.5. In MySQL 5.1.17 and later, these options have no effect when mysqld is started and an appropriate warning is written to the error log. To set the replication parameters associated with these you must use the
CHANGE MASTER TO ...
statement (see Section 12.5.2.1, “CHANGE MASTER TO
Syntax”).
The options affected are shown in this list:
The
master.info
file format in MySQL 5.1 includes values corresponding to the SSL options. In addition, the file format includes as its first line the number of lines in the file. (See Section 16.2.2, “Replication Relay and Status Files”.) If you upgrade an older server (before MySQL 4.1.1) to a newer version, the new server upgrades themaster.info
file to the new format automatically when it starts. However, if you downgrade a newer server to an older version, you should remove the first line manually before starting the older server for the first time.If no
master.info
file exists when the slave server starts, it uses the values for those options that are specified in option files or on the command line. This occurs when you start the server as a replication slave for the very first time, or when you have runRESET SLAVE
and then have shut down and restarted the slave.If the
master.info
file exists when the slave server starts, the server uses its contents and ignores any options that correspond to the values listed in the file. Thus, if you start the slave server with different values of the startup options that correspond to values in themaster.info
file, the different values have no effect because the server continues to use themaster.info
file. To use different values, the preferred method is to use theCHANGE MASTER TO
statement to reset the values while the slave is running. Alternatively, you can stop the server, remove themaster.info
file, and restart the server with different option values.Note
Because the server gives an existing
master.info
file precedence over the startup options just described, you might elect not to use startup options for these values at all, and instead to specify them by using theCHANGE MASTER TO
statement. Beginning with MySQL 5.1.17, you must useCHANGE MASTER TO
to set the values corresponding to the deprecated options listed earlier in this section.Suppose that you specify this option in your
my.cnf
file:[mysqld] master-host=
some_host
The first time you start the server as a replication slave, it reads and uses that option from the
my.cnf
file. The server then records the value in themaster.info
file. The next time you start the server, it reads the master host value from themaster.info
file only and ignores the value in the option file. If you modify themy.cnf
file to specify a different master host ofsome_other_host
, the change still has no effect. You should useCHANGE MASTER TO
instead.This example shows a more extensive use of startup options to configure a pre-5.1.17 slave server:
[mysqld] server-id=2 master-host=db-master.mycompany.com master-port=3306 master-user=pertinax master-password=freitag master-connect-retry=60 report-host=db-slave.mycompany.com
Startup options for replication slaves. The following list describes startup options for controlling replication slave servers. Many of these options can be reset while the server is running by using the
CHANGE MASTER TO
statement. Others, such as the--replicate-*
options, can be set only when the slave server starts. Replication-related system variables are discussed later in this section.-
Command-Line Format --abort-slave-event-count=#
Config-File Format abort-slave-event-count
Permitted Values Type numeric
Default 0
Min Value 0
When this option is set to some positive integer
value
other than 0 (the default) it affects replication behavior as follows: After the slave SQL thread has started,value
log events are allowed to be executed; after that, the slave SQL thread does not receive any more events, just as if the network connection from the master were cut. The slave thread continues to run, and the output fromSHOW SLAVE STATUS
displaysYes
in both theSlave_IO_Running
and theSlave_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=#
Config-File Format disconnect-slave-event-count
Permitted Values Type numeric
Default 0
This option is used internally by the MySQL test suite for replication testing and debugging.
-
Command-Line Format --log-slave-updates
Config-File Format log-slave-updates
Option Sets Variable Yes, log_slave_updates
Variable Name log_slave_updates
Variable Scope Global Dynamic Variable No Permitted Values Type boolean
Default FALSE
Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the slave to log the updates performed by its SQL thread to its own binary log. For this option to have any effect, the slave must also be started with the
--log-bin
option to enable binary logging.--log-slave-updates
is used when you want to chain replication servers. For example, you might want to set up replication servers using this arrangement:A -> B -> C
Here,
A
serves as the master for the slaveB
, andB
serves as the master for the slaveC
. For this to work,B
must be both a master and a slave. You must start bothA
andB
with--log-bin
to enable binary logging, andB
with the--log-slave-updates
option so that updates received fromA
are logged byB
to its binary log.When using MySQL Cluster Replication prior to MySQL Cluster NDB 6.2.16 and MySQL Cluster NDB 6.3.13, records for “empty” epochs—that is, epochs in which no changes to
NDBCLUSTER
data or tables took place—were inserted into thendb_apply_status
andndb_binlog_index
tables on the slave even when--log-slave-updates
was disabled (Bug#37472). Beginning with MySQL Cluster NDB 6.3.33, MySQL Cluster NDB 7.0.14, and MySQL Cluster NDB 7.1.3, it is possible to re-enable the older behavior by using the--ndb-log-empty-epochs
option.Note
The
--ndb-log-empty-epochs
option was first implemented in MySQL Cluster NDB 6.3.21 and MySQL Cluster NDB 6.4.1, but did not work correctly before the versions cited previously. -
Version Introduced 5.1.21 Command-Line Format --log-slow-slave-statements
Config-File Format log-slow-slave-statements
Permitted Values Type boolean
Default off
When the slow query log is enabled, this option enables logging for queries that have taken more than
long_query_time
seconds to execute on the slave.This option was added in MySQL 5.1.21.
-
Command-Line Format --log-warnings[=#]
Config-File Format log-warnings
Option Sets Variable Yes, log_warnings
Variable Name log_warnings
Variable Scope Both Dynamic Variable Yes Disabled by skip-log-warnings
Permitted Values Platform Bit Size 64
Type numeric
Default 1
Range 0-18446744073709547520
This option causes a server to print 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/connection failure, and informs you as to how each slave thread started. This option is enabled by default; to disable it, use
--skip-log-warnings
. Aborted connections are not logged to the error log unless the value is greater than 1.Note that the effects of this option are not limited to replication. It produces warnings across a spectrum of server activities.
-
--master-connect-retry=
seconds
The number of seconds that the slave thread sleeps before trying to reconnect to the master in case the master goes down or the connection is lost. The value in the
master.info
file takes precedence if it can be read. If not set, the default is 60. Connection retries are not invoked until the slave times out reading data from the master according to the value of--slave-net-timeout
. The number of reconnection attempts is limited by the--master-retry-count
option.This option is deprecated as of MySQL 5.1.17 and is removed in MySQL 5.5.
-
The host name or IP number of the master replication server. The value in
master.info
takes precedence if it can be read. If no master host is specified, the slave thread does not start.This option is deprecated as of MySQL 5.1.17 and is removed in MySQL 5.5.
-
Command-Line Format --master-info-file=name
Config-File Format master-info-file
Permitted Values Type filename
Default master.info
The name to use for the file in which the slave records information about the master. The default name is
master.info
in the data directory. -
The password of the account that the slave thread uses for authentication when it connects to the master. The value in the
master.info
file takes precedence if it can be read. If not set, an empty password is assumed.This option is deprecated as of MySQL 5.1.17 and is removed in MySQL 5.5.
-
The TCP/IP port number that the master is listening on. The value in the
master.info
file takes precedence if it can be read. If not set, the compiled-in setting is assumed (normally 3306).This option is deprecated as of MySQL 5.1.17 and is removed in MySQL 5.5.
-
Command-Line Format --master-retry-count=#
Config-File Format master-retry-count
Permitted Values Type numeric
Default 86400
The number of times that the slave tries to connect to the master before giving up. Reconnects are attempted at intervals set by
--master-connect-retry
and reconnects are triggered when data reads by the slave time out according to the--slave-net-timeout
option. The default value is 86400.You can also set the retry count by using the
MASTER_CONNECT_RETRY
option for theCHANGE MASTER TO
statement. -
--master-ssl
,--master-ssl-ca=
,file_name
--master-ssl-capath=
,directory_name
--master-ssl-cert=
,file_name
--master-ssl-cipher=
,cipher_list
--master-ssl-key=
file_name
These options are used for setting up a secure replication connection to the master server using SSL. Their meanings are the same as the corresponding
--ssl
,--ssl-ca
,--ssl-capath
,--ssl-cert
,--ssl-cipher
,--ssl-key
options that are described in Section 5.5.6.3, “SSL Command Options”. The values in themaster.info
file take precedence if they can be read.These options are deprecated as of MySQL 5.1.17 and are removed in MySQL 5.5.
-
The user name of the account that the slave thread uses for authentication when it connects to the master. This account must have the
REPLICATION SLAVE
privilege. The value in themaster.info
file takes precedence if it can be read. If the master user name is not set, the nametest
is assumed.This option is deprecated as of MySQL 5.1.17 and is removed in MySQL 5.5.
-
The size at which the server rotates relay log files automatically. For more information, see Section 16.2.2, “Replication Relay and Status Files”. The default size is 1GB.
-
Cause the slave to allow no updates except from slave threads or from users having the
SUPER
privilege. On a slave server, this can be useful to ensure that the slave accepts updates only from its master server and not from clients. This variable does not apply toTEMPORARY
tables. -
The basename for the relay log. The default basename is
. The server writes the file in the data directory unless the basename 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 basename.host_name
-relay-binDue to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the
--relay-log
option 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.3, “Specifying Program Options”.If you specify this option, the value specified is also used as the basename for the relay log index file. You can override this behavior by specifying a different relay log index file basename using the
--relay-log-index
option.You may find the
--relay-log
option 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.
-
The name to use for the relay log index file. The default name is
in the data directory, wherehost_name
-relay-bin.indexhost_name
is the name of the slave server.Due to the manner in which MySQL parses server options, if you specify this option, you must supply a value; the default basename is used only if the option is not actually specified. If you use the
--relay-log-index
option 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.3, “Specifying Program Options”.If you specify this option, the value specified is also used as the basename for the relay logs. You can override this behavior by specifying a different relay log file basename using the
--relay-log
option. -
--relay-log-info-file=
file_name
The name to use for the file in which the slave records information about the relay logs. The default name is
relay-log.info
in the data directory. -
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 =
.N
-
This option places an upper limit on the total size in bytes of all relay logs on the slave. A value of 0 means “no limit.” This is useful for a slave server host that has limited disk space. When the limit is reached, the I/O thread stops reading binary log events from the master server until the SQL thread has caught up and deleted some unused relay logs. Note that this limit is not absolute: There are cases where the SQL thread needs more events before it can delete relay logs. In that case, the I/O thread exceeds the limit until it becomes possible for the SQL thread to delete some relay logs because not doing so would cause a deadlock. You should not set
--relay-log-space-limit
to less than twice the value of--max-relay-log-size
(or--max-binlog-size
if--max-relay-log-size
is 0). In that case, there is a chance that the I/O thread waits for free space because--relay-log-space-limit
is exceeded, but the SQL thread has no relay log to purge and is unable to satisfy the I/O thread. This forces the I/O thread to ignore--relay-log-space-limit
temporarily. -
The effects of this option depend on whether statement-based or row-based replication is in use.
Statement-based replication. Tell the slave 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'Warning
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 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 slave is started with
--replicate-do-db=sales
and you issue the following statements on the master, 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 slave 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 slave is started with--replicate-do-db=sales
and row-based replication is in effect, and then the following statements are run on the master:USE prices; UPDATE sales.february SET amount=amount+100;
The
february
table in thesales
database on the slave is changed in accordance with theUPDATE
statement; this occurs whether or not theUSE
statement was issued. However, issuing the following statements on the master has no effect on the slave when using row-based replication and--replicate-do-db=sales
:USE prices; UPDATE prices.march SET amount=amount-25;
Even if the statement
USE prices
were changed 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 the slave is started with--replicate-do-db=db1
, and the following statements are executed on the master:USE db1; UPDATE db1.table1 SET col1 = 10, db2.table2 SET col2 = 20;
If you are using statement-based replication, then both tables are updated on the slave. However, when using row-based replication, only
table1
is affected on the slave; sincetable2
is in a different database,table2
on the slave 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 slave when using statement-based replication. However, if you are using row-based replication, theUPDATE
would changetable1
on the slave, 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.3, “How Servers Evaluate Replication Filtering Rules”.db_name
.%Note
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
.Beginning with MySQL 5.1.35, this option has no effect on
BEGIN
,COMMIT
, orROLLBACK
statements. (Bug#43263) -
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 slave to not replicate any statement where the default database (that is, the one selected by
USE
) isdb_name
.Row-based replication. Tells the slave not to update any tables in the database
db_name
. The default database has no effect.When using statement-based replication, the following example does not work as you might expect. Suppose that the slave is started with
--replicate-ignore-db=sales
and you issue the following statements on the master:USE prices; UPDATE sales.january SET amount=amount+1000;
The
UPDATE
statement is replicated in such a case because--replicate-ignore-db
applies only to the default database (determined by 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 slave, and the slave's copy of thesales.january
table is unchanged; in this instance,--replicate-ignore-db=sales
causes all changes made to tables in the master's copy of thesales
database to be ignored by the slave.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.3, “How Servers Evaluate Replication Filtering Rules”.
If you need cross-database updates to work, use
--replicate-wild-ignore-table=
instead. See Section 16.2.3, “How Servers Evaluate Replication Filtering Rules”.db_name
.%Note
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
.Beginning with MySQL 5.1.35, this option has no effect on
BEGIN
,COMMIT
, orROLLBACK
statements. (Bug#43263) -
--replicate-do-table=
db_name.tbl_name
Tells the slave thread to restrict replication to the specified 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.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
Tells the slave thread to not 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.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
Tells the slave to translate the default database (that is, the one selected by
USE
) toto_name
if it wasfrom_name
on the master. Only statements involving tables are affected (not statements such asCREATE DATABASE
,DROP DATABASE
, andALTER DATABASE
), and only iffrom_name
is the default database on the master. This does not work for cross-database updates. To specify multiple rewrites, use this option multiple times. The server uses the first one with afrom_name
value that matches. The database name translation is done before the--replicate-*
rules are tested.If you use this option on the command line and the “
>
” character is special to your command interpreter, quote the option value. For example:shell>
mysqld --replicate-rewrite-db="
olddb
->newdb
" -
To be used on slave servers. Usually you should use the default setting of 0, to prevent infinite loops caused by circular replication. If set to 1, the slave 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 used. By default, the slave I/O thread does not write binary log events to the relay log if they have the slave's server ID (this optimization helps save disk usage). If you want to use--replicate-same-server-id
, be sure to start the slave with this option before you make the slave read its own events that you want the slave SQL thread to execute. -
--replicate-wild-do-table=
db_name.tbl_name
Tells the slave thread to restrict replication to statements where any of the updated tables match the specified database and table name patterns. Patterns can contain the “
%
” and “_
” wildcard characters, which have the same meaning as for 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.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 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
Tells the slave thread not to replicate a statement where 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.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 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. -
The host name or IP number of the slave to be reported to the master during slave registration. This value appears in the output of
SHOW SLAVE HOSTS
on the master server. Leave the value unset if you do not want the slave to register itself with the master. Note that it is not sufficient for the master to simply read the IP number of the slave from the TCP/IP socket after the slave connects. Due to NAT and other routing issues, that IP may not be valid for connecting to the slave from the master or other hosts. -
The account password of the slave to be reported to the master during slave registration. This value appears in the output of
SHOW SLAVE HOSTS
on the master server if the--show-slave-auth-info
option is given. -
The TCP/IP port number for connecting to the slave, to be reported to the master during slave registration. Set this only if the slave is listening on a nondefault port or if you have a special tunnel from the master or other clients to the slave. If you are not sure, do not use this option.
-
The account user name of the slave to be reported to the master during slave registration. This value appears in the output of
SHOW SLAVE HOSTS
on the master server if the--show-slave-auth-info
option is given. -
Display slave user names and passwords in the output of
SHOW SLAVE HOSTS
on the master server for slaves started with the--report-user
and--report-password
options. -
Tells the slave server not to start the slave threads when the server starts. To start the threads later, use a
START SLAVE
statement. -
--slave_compressed_protocol={0|1}
If this option is set to 1, use compression for the slave/master protocol if both the slave and the master support it. The default is 0 (no compression).
-
The name of the directory where the slave creates temporary files. This option is by default equal to the value of the
tmpdir
system variable. When the slave SQL thread replicates aLOAD DATA INFILE
statement, it extracts the file to be loaded from the relay log into temporary files, and then loads these into the table. If the file loaded on the master is huge, the temporary files on the slave are huge, too. Therefore, it might be advisable to use this option to tell the slave to put temporary files in a directory located in some file system that has a lot of available space. In that case, the relay logs are huge as well, so you might also want to use the--relay-log
option 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) because the temporary files used to replicate
LOAD DATA INFILE
must survive machine restarts. The directory also should not be one that is cleared by the operating system during the system startup process. -
The number of seconds to wait for more data from the master before the slave 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 theCHANGE MASTER TO
statement or--master-connect-retry
option, and the number of reconnection attempts is limited by the--master-retry-count
option. The default is 3600 seconds (one hour). -
--slave-skip-errors=[
err_code1
,err_code2
,...|all]Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This option tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the option value.
Do not use this option unless you fully understand why you are getting errors. If there are no bugs in your replication setup and client programs, and no bugs in MySQL itself, an error that stops replication should never occur. Indiscriminate use of this option results in slaves becoming hopelessly out of synchrony with the master, with you having no idea why this has occurred.
Note
Prior to MySQL 5.1.35, this option had no effect with row-based logging. (Bug#39393)
For error codes, you should use the numbers provided by the error message in your slave error log and in the output of
SHOW SLAVE STATUS
. Appendix B, Errors, Error Codes, and Common Problems, lists server error codes.You can also (but should not) use the very nonrecommended value of
all
to cause the slave to ignore all error messages and keeps going regardless of what happens. Needless to say, if you useall
, there are no guarantees regarding the integrity of your data. Please do not complain (or file bug reports) in this case if the slave's data is not anywhere close to what it is on the master. You have been warned.Examples:
--slave-skip-errors=1062,1053 --slave-skip-errors=all
System variables used on replication slaves. The following list describes system variables for controlling replication slave servers. They can be set at server startup and some of them can be changed at runtime using
SET
. Server options used with replication slaves are listed earlier in this section.-
Command-Line Format --init-slave=name
Config-File Format init_slave
Option Sets Variable Yes, init_slave
Variable Name init_slave
Variable Scope Global Dynamic Variable Yes Permitted Values Type string
This variable is similar to
init_connect
, but is a string to be executed by a slave server each time the SQL thread starts. The format of the string is the same as for theinit_connect
variable.Note
The SQL thread sends an acknowledgement to the client before it executes
init_slave
. Therefore, it is not guaranteed thatinit_slave
has been executed whenSTART SLAVE
returns. See Section 12.5.2.7, “START SLAVE
Syntax”, for more information. -
This variable is unused.
-
Command-Line Format --slave_compressed_protocol
Config-File Format slave_compressed_protocol
Option Sets Variable Yes, slave_compressed_protocol
Variable Name slave_compressed_protocol
Variable Scope Global Dynamic Variable Yes Permitted Values Type boolean
Default OFF
Whether to use compression of the slave/master protocol if both the slave and the master support it.
-
Version Introduced 5.1.24 Variable Name slave_exec_mode
Variable Scope Global Dynamic Variable Yes Permitted Values Type enumeration
Default STRICT
(ALL)Default IDEMPOTENT
(NDB)Valid Values IDEMPOTENT
,STRICT
Controls whether
IDEMPOTENT
orSTRICT
mode is used in replication conflict resolution and error checking.IDEMPOTENT
mode causes suppression of duplicate-key and no-key-found errors. Beginning with MySQL 5.1.23-ndb-6.2.14 and MySQL 5.1.24, this mode should be employed in multi-master replication, circular replication, and some other special replication scenarios.STRICT
mode is the default, and is suitable for most other cases.Note
MySQL Cluster ignores any value explicitly set for
slave_exec_mode
, and always treats it asIDEMPOTENT
. -
Command-Line Format --slave-load-tmpdir=name
Config-File Format slave-load-tmpdir
Option Sets Variable Yes, slave_load_tmpdir
Variable Name slave_load_tmpdir
Variable Scope Global Dynamic Variable No Permitted Values Type filename
Default /tmp
The name of the directory where the slave creates temporary files for replicating
LOAD DATA INFILE
statements. -
Command-Line Format --slave-net-timeout=#
Config-File Format slave-net-timeout
Option Sets Variable Yes, slave_net_timeout
Variable Name slave_net_timeout
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
Default 3600
Min Value 1
The number of seconds to wait for more data from a master/slave connection before aborting the read. This timeout applies only to TCP/IP connections, not to connections made via Unix socket files, named pipes, or shared memory.
-
Command-Line Format --slave-skip-errors=name
Config-File Format slave-skip-errors
Option Sets Variable Yes, slave_skip_errors
Variable Name slave_skip_errors
Variable Scope Global Dynamic Variable No Normally, replication stops when an error occurs on the slave. This gives you the opportunity to resolve the inconsistency in the data manually. This variable tells the slave SQL thread to continue replication when a statement returns any of the errors listed in the variable value.
-
Command-Line Format --slave_transaction_retries=#
Config-File Format slave_transaction_retries
Option Sets Variable Yes, slave_transaction_retries
Variable Name slave_transaction_retries
Variable Scope Global Dynamic Variable Yes Permitted Values Platform Bit Size 32
Type numeric
Default 10
Range 0-4294967295
Permitted Values Platform Bit Size 64
Type numeric
Default 10
Range 0-18446744073709547520
If a replication slave SQL thread fails to execute a transaction because of an
InnoDB
deadlock or because the transaction's execution time exceededInnoDB
'sinnodb_lock_wait_timeout
orNDBCLUSTER
'sTransactionDeadlockDetectionTimeout
orTransactionInactiveTimeout
, it automatically retriesslave_transaction_retries
times before stopping with an error. The default value is 10. -
Version Introduced 5.1.44-ndb-7.1.3 Command-Line Format --slave_type_conversions=set
Config-File Format slave_type_conversions
Option Sets Variable Yes, slave_type_conversions
Variable Name slave_type_conversions
Variable Scope Global Dynamic Variable No Permitted Values Type string
Default Valid Values ALL_LOSSY
,ALL_NON_LOSSY
,ALL_LOSSY,ALL_NON_LOSSY
Controls the type conversion mode in effect on the slave when using MySQL Cluster Replication. Its value is a comma-delimited set of zero or more elements from the list:
ALL_LOSSY
,ALL_NON_LOSSY
. Set this variable to an empty string to disallow type conversions between the master and the slave. Changes require a restart of the slave to take effect.For additional information on type conversion modes applicable to MySQL Cluster replication attribute promotion and demotion, see Attribute promotion and demotion (MySQL Cluster).
This variable was added in MySQL Cluster NDB 6.3.33, 7.0.14, and 7.1.3.
-
Variable Name sql_slave_skip_counter
Variable Scope Global Dynamic Variable Yes Permitted Values Type numeric
The number of events from the master that a slave server should skip.
Important
If skipping the number of events specified by setting this variable would cause the slave to begin in the middle of an event group, the slave continues to skip until it finds the beginning of the next event group and begins from that point. For more information, see Section 12.5.2.6, “
SET GLOBAL sql_slave_skip_counter
Syntax”.