(mysql.info) replication-options
Info Catalog
(mysql.info) replication-features
(mysql.info) replication
(mysql.info) replication-rules
6.8 Replication Startup Options
===============================
This section describes the options that you can use on slave
replication servers. You can specify these options either on the
command line or in an option file.
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'
Options that you can use on the master server for controlling binary
logging are described in binary-log.
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:
* -master-host
* -master-user
* -master-password
* -master-port
* -master-connect-retry
* -master-ssl
* -master-ssl-ca
* -master-ssl-capath
* -master-ssl-cert
* -master-ssl-cipher
* -master-ssl-key
The `master.info' file format in MySQL 5.0 includes values
corresponding to the SSL options. In addition, the file format
includes as its first line the number of lines in the file. If you
upgrade an older server (before MySQL 4.1.1) to a newer version, the
new server upgrades the `master.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 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.
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 change-master-to.
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
The following list describes startup options for controlling
replication. 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.
* -log-slave-updates
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.
* -log-warnings
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.
* -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.
* -master-host=HOST_NAME
The hostname 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.
* -master-info-file=FILE_NAME
The name to use for the file in which the slave records
information about the master. The default name is `mysql.info' in
the data directory.
* -master-password=PASSWORD
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.
* -master-port=PORT_NUMBER
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).
* -master-retry-count=COUNT
The number of times that the slave tries to connect to the master
before giving up.
* -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
ssl-options. The values in the `master.info' file take
precedence if they can be read.
* -master-user=USER_NAME
The username 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 the
`master.info' file, if it can be read, takes precedence. If the
master username is not set, the name `test' is assumed.
* -max-relay-log-size=SIZE
The size at which the server rotates relay log files
automatically. For more information, see slave-logs.
* -read-only
Cause the slave to allow no updates except from slave threads or
from users having the `SUPER' privilege. This enables you to
ensure that a slave server accepts no updates from clients. As of
MySQL 5.0.16, this option does not apply to `TEMPORARY' tables.
* -relay-log=FILE_NAME
The name for the relay log. The default name is
`HOST_NAME-relay-bin.NNNNNN', where HOST_NAME is the name of the
slave server host and NNNNNN indicates that relay logs are created
in numbered sequence. You can specify the option to create
hostname-independent relay log names, or if your relay logs tend
to be big (and you don't want to decrease `max_relay_log_size')
and you need to put them in some area different from the data
directory, or if you want to increase speed by balancing load
between disks.
* -relay-log-index=FILE_NAME
The name to use for the relay log index file. The default name is
`HOST_NAME-relay-bin.index' in the data directory, where HOST_NAME
is the name of the slave server.
* -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.
* -relay-log-purge={0|1}
Disable or enable automatic purging of relay logs as soon as they
are not needed any more. The default value is 1 (enabled). This is
a global variable that can be changed dynamically with `SET GLOBAL
relay_log_purge = N'.
* -relay-log-space-limit=SIZE
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
temporarily ignore -relay-log-space-limit.
* -replicate-do-db=DB_NAME
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 SOME_DB.SOME_TABLE SET
foo='bar'' while having selected a different database or no
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 `just check the default database'
behavior is that it is difficult from the statement alone to know
whether it should be replicated (for example, if you are using
multiple-table `DELETE' statements or multiple-table `UPDATE'
statements that act across multiple databases). It is also faster
to check only the default database rather than all databases if
there is no need.
If you need cross-database updates to work, use
-replicate-wild-do-table=DB_NAME.% instead. See
replication-rules.
* -replicate-do-table=DB_NAME.TBL_NAME
Tell 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 cross-database updates,
in contrast to -replicate-do-db. See replication-rules.
* -replicate-ignore-db=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 replication-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
_not_ replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
If you need cross-database updates to work, use
-replicate-wild-ignore-table=DB_NAME.% instead. See
replication-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
replication-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. 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"
* -replicate-same-server-id
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.
Note that 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). So 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 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 replication-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're using 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
replication-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.
* -report-host=SLAVE_NAME
The hostname 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.
* -report-port=SLAVE_PORT_NUM
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 non-default 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.
* -skip-slave-start
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.
* -slave-load-tmpdir=FILE_NAME
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 filesystem 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
filesystem.
The directory specified by this option should be located in a
disk-based filesystem (not a memory-based filesystem) 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.
* -slave-net-timeout=SECONDS
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.
* -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'. error-handling, lists server error codes.
You can also (but should not) use the very non-recommended 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
Info Catalog
(mysql.info) replication-features
(mysql.info) replication
(mysql.info) replication-rules
automatically generated byinfo2html