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
using SET
.
Server ID.
On the master and each slave, you must use the
server-id
option to establish a
unique replication ID. For each server, you should pick a unique
positive integer in the range from 1 to
232 – 1, and each ID must be
different from every other ID. Example:
server-id=3
.
Some slave server replication options are handled in a special
way, in the sense that each is ignored if a
master.info
file exists when the slave starts
and contains a value for the option. The following options are
handled this way:
As of MySQL 4.1.1, the following options also are handled specially:
The master.info
file format in 4.1.1 changed
to include values corresponding to the SSL options. In addition,
the 4.1.1 file format includes as its first line the number of
lines in the file. (See Section 14.3.1, “Replication Relay and Status Files”.) If you
upgrade an older server to 4.1.1, the new server upgrades the
master.info
file to the new format
automatically when it starts. However, if you downgrade a 4.1.1 or
newer server to a version older than 4.1.1, you should manually
remove the first line before starting the older server for the
first time. Note that, in this case, the downgraded server can no
longer use an SSL connection to communicate with the master.
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 run RESET
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 the
master.info
file, the different values have
no effect because the server continues to use the
master.info
file. To use different values,
you must either restart after removing the
master.info
file or (preferably) use the
CHANGE MASTER TO
statement to reset
the values while the slave is running.
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 the
master.info
file. The next time you start the
server, it reads the master host value from the
master.info
file only and ignores the value
in the option file. If you modify the my.cnf
file to specify a different master host of
some_other_host
, the change still has
no effect. You should use CHANGE MASTER
TO
instead.
MySQL Enterprise. For expert advice regarding slave startup options subscribe to the MySQL Enterprise Monitor. For more information, see http://www.mysql.com/products/enterprise/advisors.html.
Because the server gives an existing
master.info
file precedence over the startup
options just described, you might prefer not to use startup
options for these values at all, and instead specify them by using
the CHANGE MASTER TO
statement. See
Section 12.5.2.1, “CHANGE MASTER TO
Syntax”.
This example shows a more extensive use of startup options to configure a 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.
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 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
This option is used internally by the MySQL test suite for replication testing and debugging.
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 slave
B
, and B
serves as the
master for the slave C
. For this to work,
B
must be both a master
and a slave. You must start both
A
and B
with
--log-bin
to enable binary
logging, and B
with the
--log-slave-updates
option so
that updates received from A
are logged by
B
to its binary log.
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 as of MySQL 4.0.19 and 4.1.2; to disable
it, use
--skip-log-warnings
.
As of MySQL 4.0.21 and 4.1.3, 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.
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.
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.
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).
The number of times that the slave tries to connect to the
master before giving up. Reconnects are attempted at intervals
set by the CHANGE MASTER TO
statement or
--master-connect-retry
option
and reconnects are triggered when data reads by the slave time
out according to the
--slave-net-timeout
option. The
default value is 86400.
--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.6.6.3, “SSL Command Options”. The values in the
master.info
file take precedence if they
can be read.
These options are operational as of MySQL 4.1.1.
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. FILE
privilege
instead.) The value in the master.info
file takes precedence if it can be read. If the master user
name is not set, the name test
is assumed.
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 to
TEMPORARY
tables.
This option is available as of MySQL 4.0.14.
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-bin
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
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, where
host_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 is available as of MySQL 4.1.1.
The size at which the server rotates relay log files automatically. For more information, see Section 14.3.1, “Replication Relay and Status Files”. Default is 1GB.
This option is available as of MySQL 4.0.14.
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 (which
is what happens before MySQL 4.0.13). 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.
Tell the slave 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. Note that this does not replicate cross-database
statements such as UPDATE
while having selected a different database
or no database.
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 will be treated as the name of a single database.
An example of what does not work as you might expect: If the
slave is started with
--replicate-do-db=sales
and you
issue the following statements on the master, the
UPDATE
statement is
not replicated:
USE prices; UPDATE sales.january SET amount=amount+1000;
The main reason for this “check just the default
database” behavior is that it is difficult from the
statement alone to know whether it should be replicated (for
example, if you are using multiple-table
DELETE
or multiple-table
UPDATE
statements that go
across multiple databases). It is also faster to check only
the default database rather than all databases if there is no
need.
If you need cross-database updates to work, make sure that you
have MySQL 3.23.28 or later, and use
--replicate-wild-do-table=
instead. See Section 14.9, “How Servers Evaluate Replication Filtering Rules”.
db_name
.%
Tells the slave to not replicate any statement where the
default database (that is, the one selected by
USE
) is
db_name
. To specify more than one
database to ignore, use this option multiple times, once for
each 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 14.9, “How Servers Evaluate Replication Filtering Rules”.
An example of what does not work as you might expect: If the
slave is started with
--replicate-ignore-db=sales
and
you issue the following statements on the master, the
UPDATE
statement
is replicated:
USE prices; UPDATE sales.january SET amount=amount+1000;
In the preceding example the statement is replicated because
--replicate-ignore-db
only
applies to the default database (set through the
USE
statement). Because the
sales
database was specified explicitly
in the statement, the statement has not been filtered.
If you need cross-database updates to work, use
--replicate-wild-ignore-table=
instead. See Section 14.9, “How Servers Evaluate Replication Filtering Rules”.
db_name
.%
--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 14.9, “How Servers Evaluate Replication Filtering Rules”.
--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 14.9, “How Servers Evaluate Replication Filtering Rules”.
--replicate-rewrite-db=
from_name
->to_name
Tells the slave to translate the default database (that is,
the one selected by USE
) to
to_name
if it was
from_name
on the master. Only
statements involving tables are affected (not statements such
as CREATE DATABASE
,
DROP DATABASE
, and
ALTER DATABASE
), and only if
from_name
is the default database
on the master. This does not work for cross-database updates.
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.
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. Be
careful that starting from MySQL 4.1, by default the slave I/O
thread does not even write binary log events to the relay log
if they have the slave's server id (this optimization helps
save disk usage compared to 4.0). So if you want to use
--replicate-same-server-id
in
4.1 versions, 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 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 14.9, “How Servers Evaluate Replication Filtering Rules”.
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
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 14.9, “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.
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.
This option is available as of MySQL 4.0.0.
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.
This option is available as of MySQL 4.0.0.
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 a
LOAD 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 CHANGE 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.
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 A, 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 use all
, there are
no guarantees regarding the integrity of your data. Please do
not complain (or file bug reports) in this case if the slave's
data is not anywhere close to what it is on the master.
You have been warned.
Examples:
--slave-skip-errors=1062,1053 --slave-skip-errors=all
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.
This variable is similar to
init_connect
, but is a string
to be executed by a slave server each time the SQL thread
starts. The format of the string is the same as for the
init_connect
variable.
The SQL thread sends an acknowledgement 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 12.5.2.7, “START SLAVE
Syntax”, for more
information.
This variable was added in MySQL 4.1.2.
This variable is unused.
Whether to use compression of the master/slave protocol if both the slave and the master support it. This variable was added in MySQL 4.0.3.
The name of the directory where the slave creates temporary
files for replicating
LOAD DATA
INFILE
statements. This variable was added in MySQL
4.0.0.
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. This variable was added in MySQL 3.23.40.
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. This variable was added in MySQL 3.23.47.
If a replication slave SQL thread fails to execute a
transaction because of an InnoDB
deadlock or because the transaction's execution time
exceeded InnoDB
's
innodb_lock_wait_timeout
or
NDBCLUSTER
's
TransactionDeadlockDetectionTimeout
or
TransactionInactiveTimeout
, it
automatically retries
slave_transaction_retries
times before stopping with an error. The default in MySQL 4.1
is 0
. You must explicitly set the value to
greater than 0 to enable the “retry” behavior,
which is often desirable.
The number of events from the master that a slave server should skip. This variable was added in MySQL 3.23.33.
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”.
User Comments
To know more about relay-logs hop onto http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html
Add your own comment.