(mysql.info.gz) Replication Options
Info Catalog
(mysql.info.gz) Replication Features
(mysql.info.gz) Replication
(mysql.info.gz) Replication FAQ
6.8 Replication Startup Options
===============================
On both the master and the 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 2^32 - 1 for each master
and slave. Example: `server-id=3'
The options that you can use on the master server for controlling binary
logging are described in Binary log.
The following table describes the options you can use on slave
replication servers. You can specify them on the command line or in an
option file.
Some slave server replication options are handled in a special way, in
the sense that they are ignored if a `master.info' file exists when the
slave starts and contains values for the options. The following
options are handled this way:
* `--master-host'
* `--master-user'
* `--master-password'
* `--master-port'
* `--master-connect-retry'
As of MySQL 4.1.1, the following options also are handled specially:
* `--master-ssl'
* `--master-ssl-ca'
* `--master-ssl-capath'
* `--master-ssl-cert'
* `--master-ssl-cipher'
* `--master-ssl-key'
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. 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 no longer can use an SSL connection to communicate
with the master.
If no `master.info' file exists when the slave server starts, it uses
values for those options that are specified in option files or on the
command line. This will occur when you start the server as a
replication slave for the very first time, or when you have run `RESET
SLAVE' and shut down and restarted the slave server.
If the `master.info' file exists when the slave server starts, the
server ignores those options. Instead, it uses the values found in the
`master.info' file.
If you restart 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 will have 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' 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.
We plan to fix this.
`--log-slave-updates'
Normally, updates received from a master server by a slave are not
logged to its binary log. This option tells the slave to log the
updates performed by its SQL thread to the slave's 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 a setup like this:
A -> B -> C
That is, 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.
`--log-warnings'
Makes the slave print more messages to the error log about what it
is doing. For example, it will warn you that it succeeded in
reconnecting after a network/connection failure, and warn you
about 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.
This option is not limited to replication use only. It produces
warnings across a spectrum of server activities.
`--master-connect-retry=SECONDS'
The number of seconds the slave thread sleeps before retrying to
connect 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'
The hostname or IP number of the master replication server. If
this option is not given, the slave thread will not be started.
The value in `master.info' takes precedence if it can be read.
`--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 connecting 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 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. If you have not tinkered
with `configure' options, this should be 3306.
`--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 described in
SSL 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.
`--master-user=USERNAME'
The username of the account that the slave thread uses for
authentication when connecting to the master. The account must
have the `REPLICATION SLAVE' privilege. (Prior to MySQL 4.0.2, it
must have the `FILE' privilege instead.) The value in the
`master.info' file takes precedence if it can be read. If the
master user is not set, user `test' is assumed.
`--max-relay-log-size=#'
To rotate the relay log automatically. Server system
variables.
This option is available as of MySQL 4.0.14.
`--read-only'
This option causes the slave to allow no updates except from slave
threads or from users with the `SUPER' privilege. This can be
useful to ensure that a slave server accepts no updates from
clients.
This option is available as of MySQL 4.0.14.
`--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 location and name that should be used for the relay log index
file. The default name is `HOST_NAME-relay-bin.index', 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}'
Disables or enables 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'.
This option is available as of MySQL 4.1.1.
`--relay-log-space-limit=#'
Places an upper limit on the total size of all relay logs on the
slave (a value of 0 means "unlimited"). 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 will exceed the limit
until it becomes possible for the SQL thread to delete some relay
logs. 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 will wait for free space because
`--relay-log-space-limit' is exceeded, but the SQL thread will
have no relay log to purge and be 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'
Tells 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 will not replicate
cross-database statements such as `UPDATE SOME_DB.SOME_TABLE SET
foo='bar'' while having selected a different database or no
database. 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=DB_NAME.%'. Please read the notes that
follow this option list.
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 will
not be replicated:
USE prices;
UPDATE sales.january SET amount=amount+1000;
If you need cross-database updates to work, use
`--replicate-wild-do-table=DB_NAME.%' instead.
The main reason for this "just-check-the-default-database"
behavior is that it's difficult from the statement alone to know
whether or not it should be replicated (for example, if you are
using multiple-table `DELETE' or multiple-table `UPDATE' statements
that go across multiple databases). It's also very fast to just
check the default database.
`--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 will work for cross-database
updates, in contrast to `--replicate-do-db'. Please read the
notes that follow this option list.
`--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 don't want these
updates to be replicated. Please read the notes that follow this
option list.
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 will be
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.
`--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 will work
for cross-database updates, in contrast to `--replicate-ignore-db'.
Please read the notes that follow this option list.
`--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 will work
for cross-database updates. Please read the notes that follow
this option list.
Example: `--replicate-wild-do-table=foo%.bar%' will replicate 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 to not 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 will work for cross-database updates. Please read the
notes that follow this option list.
Example: `--replicate-wild-ignore-table=foo%.bar%' will 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.
`--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 was the default database on the
master. This will not work for cross-database updates. Note that
the database name translation is done before `--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 can should the default
setting of 0, to prevent infinite loops in circular replication.
If set to 1, this slave will 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 which you want the slave SQL thread to execute.
`--report-host=HOST'
The hostname or IP number of the slave to be reported to the
master during slave registration. This value will appear 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.
`--report-port=PORT_NUMBER'
The TCP/IP port for connecting to the slave, to be reported to the
master during slave registration. Set it 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, leave this option unset.
This option is available as of MySQL 4.0.0.
`--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 of 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 to-be-loaded file from the
relay log into temporary files, then loads these into the table.
If the file loaded on the master was huge, the temporary files on
the slave will be 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, you may also use the `--relay-log' option to
place the relay logs in that filesystem, because the relay logs
will be huge as well. `--slave-load-tmpdir' should point to a
disk-based filesystem, not a memory-based one: The slave needs the
temporary files used to replicate `LOAD DATA INFILE' to survive a
machine's restart. 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
aborting the read, considering the connection broken, and trying
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, which 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 the 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 will result in slaves becoming hopelessly out of sync
with the master, and you will have no idea why.
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'. The server error codes are listed in
Error-handling.
You can (but should not) also use the very non-recommended value
of `all' which will ignore all error messages and keep barging
along regardless of what happens. Needless to say, if you use it,
we make no promises regarding your data integrity. Please do not
complain if your data on the slave is not anywhere close to what
it is on the master in this case. You have been warned.
Examples:
--slave-skip-errors=1062,1053
--slave-skip-errors=all
The `--replicate-*' rules are evaluated as follows to determine whether
a statement will be executed by the slave or ignored:
1. Are there some `--replicate-do-db' or `--replicate-ignore-db'
rules?
* Yes: Test them as for `--binlog-do-db' and
`--binlog-ignore-db' ( Binary log). What is the result
of the test?
- Ignore the statement: Ignore it and exit.
- Execute the statement: Don't execute it immediately,
defer the decision, go to the next step.
* No: Go to the next step.
2. Are there some `--replicate-*-table' rules?
* No: Execute the query and exit.
* Yes: Go to the next step. Only tables that are to be updated
are compared to the rules (`INSERT INTO sales SELECT * FROM
prices': only `sales' will be compared to the rules). If
several tables are to be updated (multiple-table statement),
the first matching table (matching "do" or "ignore") wins.
That is, the first table is compared to the rules. Then, if
no decision could be mad, the second table is compared to the
rules, and so forth.
3. Are there some `--replicate-do-table' rules?
* Yes: Does the table match any of them?
- Yes: Execute the query and exit.
- No: Go to the next step.
* No: Go to the next step.
4. Are there some `--replicate-ignore-table' rules?
* Yes: Does the table match any of them?
- Yes: Ignore the query and exit.
- No: Go to the next step.
* No: Go to the next step.
5. Are there some `--replicate-wild-do-table' rules?
* Yes: Does the table match any of them?
- Yes: Execute the query and exit.
- No: Go to the next step.
* No: Go to the next step.
6. Are there some `--replicate-wild-ignore-table' rules?
* Yes: Does the table match any of them?
- Yes: Ignore the query and exit.
- No: Go to the next step.
* No: Go to the next step.
7. No `--replicate-*-table' rule was matched. Is there another table
to test against these rules?
* Yes: Loop.
* No: We have tested all tables to be updated and could not
match any rule. Are there `--replicate-do-table' or
`--replicate-wild-do-table' rules?
- Yes: Ignore the query and exit.
- No: Execute the query and exit.
Info Catalog
(mysql.info.gz) Replication Features
(mysql.info.gz) Replication
(mysql.info.gz) Replication FAQ
automatically generated byinfo2html