| ||||
![]() | ||||
| ||||
If you are using replication, we recommend that you use MySQL Version 3.23.30 or later. Older versions work, but they do have some bugs and are missing some features. Some of the options mentioned here may not be available in your version if it is not the most recent one. For all options specific to the 4.0 branch, there is a note indicating so. Otherwise, if you discover that the option you are interested in is not available in your 3.23 version, and you really need it, please upgrade to the most recent 3.23 branch.
Please be aware that 4.0 branch is still in alpha, so some things may not be working as smoothly as you would like. If you really would like to try the new features of 4.0, we recommend you do it in such a way that in case there is a problem your mission critical applications will not be disrupted.
On both master and slave you need to use the server-id
option.
This sets a unique replication id. You should pick a unique value in the range
between 1 to 2^32-1 for each master and slave. Example: server-id=3
The following table describes the options you can use for the
MASTER
:
Option | Description |
log-bin=filename |
Write to a binary update log to the specified location. Note that if
you give it a parameter with an extension (for example,
log-bin=/mysql/logs/replication.log ) versions up to 3.23.24
will not work right during replication if you do FLUSH LOGS .
The problem is fixed in Version 3.23.25. If you are using this kind of log
name, FLUSH LOGS will be ignored on binlog. To clear the log,
run FLUSH MASTER , and do not forget to run FLUSH
SLAVE on all slaves. In Versions 3.23.26 and later, you should use
RESET MASTER and RESET SLAVE |
log-bin-index=filename |
Because the user could issue the FLUSH LOGS command, we
need to know which log is currently active and which ones have been
rotated out and in what sequence. This information is stored in the binary
log index file. The default is ``hostname`.index'. You should not
need to change this. Example: log-bin-index=db.index |
sql-bin-update-same |
If set, setting SQL_LOG_BIN to a value will automatically
set SQL_LOG_UPDATE to the same value and vice versa. |
binlog-do-db=database_name |
Tells the master that it should log updates to the binary log if the
current database is database_name . All other databases are
ignored. Note that if you use this, you should ensure that you do updates
only in the current database. Example: binlog-do-db=sales
|
binlog-ignore-db=database_name |
Tells the master that updates where the current database is
database_name should not be stored in the binary log. Note
that if you use this, you should ensure that you do updates only in the
current database. Example: binlog-ignore-db=accounting
|
The following table describes the options you can use for the
SLAVE
:
Option | Description |
master-host=host |
Master hostname or IP address for replication. If not set, the slave
thread will not be started. Note that the setting of
master-host will be ignored if there exists a valid
`master.info' file. Probably a better name for this options would
have been something like bootstrap-master-host , but it is too
late to change now. Example:
master-host=db-master.mycompany.com |
master-user=username |
The username the slave thread will use for authentication when
connecting to the master. The user must have the FILE
privilege. If the master user is not set, user test is
assumed. The value in `master.info' will take precedence if it
can be read. Example: master-user=scott |
master-password=password |
The password the slave thread will authenticate with when connecting
to the master. If not set, an empty password is assumed.The value in
`master.info' will take precedence if it can be read. Example:
master-password=tiger |
master-port=portnumber |
The port the master is listening on. If not set, the compiled setting
of MYSQL_PORT is assumed. If you have not tinkered with
configure options, this should be 3306. The value in
`master.info' will take precedence if it can be read. Example:
master-port=3306 |
master-connect-retry=seconds |
The number of seconds the slave thread will sleep before retrying to
connect to the master in case the master goes down or the connection is
lost. Default is 60. Example: master-connect-retry=60 |
master-ssl |
Available after 4.0.0. Turn SSL on for replication. Be warned that is
this is a relatively new feature. Example: master-ssl |
master-ssl-key |
Available after 4.0.0. Master SSL keyfile name. Only applies if you
have enabled master-ssl . Example:
master-ssl-key=SSL/master-key.pem |
master-ssl-cert |
Available after 4.0.0. Master SSL certificate file name. Only applies
if you have enabled master-ssl . Example:
master-ssl-key=SSL/master-cert.pem |
master-info-file=filename |
The location of the file that remembers where we left off on the
master during the replication process. The default is
`master.info' in the data directory. You should not need to
change this. Example: master-info-file=master.info |
report-host |
Available after 4.0.0. Hostname or IP of the slave to be reported to
to the master during slave registration. Will appear in the output of
SHOW SLAVE HOSTS . Leave 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 of the slave off the socket once 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.
Example: report-host=slave1.mycompany.com |
report-port |
Available after 4.0.0. Port for connecting to slave 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 not sure, leave this option unset. |
replicate-do-table=db_name.table_name |
Tells the slave thread to restrict replication to the specified table.
To specify more than one table, use the directive multiple times, once for
each table. This will work for cross-database updates, in contrast to
replicate-do-db . Example:
replicate-do-table=some_db.some_table |
replicate-ignore-table=db_name.table_name |
Tells the slave thread to not replicate any command that updates the
specified table (even if any other tables may be update by the same
command). To specify more than one table to ignore, use the directive
multiple times, once for each table. This will work for cross-datbase
updates, in contrast to replicate-ignore-db . Example:
replicate-ignore-table=db_name.some_table |
replicate-wild-do-table=db_name.table_name |
Tells the slave thread to restrict replication to queries where any of
the updated tables match the specified wildcard pattern. To specify more
than one table, use the directive multiple times, once for each table.
This will work for cross-database updates. Example:
replicate-wild-do-table=foo%.bar% will replicate only updates
that uses a table in any databases that start with foo and
whose table names start with bar . |
replicate-wild-ignore-table=db_name.table_name |
Tells the slave thread to not replicate a query where any table
matches the given wildcard pattern. To specify more than one table to
ignore, use the directive multiple times, once for each table. This will
work for cross-database updates. Example:
replicate-wild-ignore-table=foo%.bar% will not do updates to
tables in databases that start with foo and whose table names
start with bar . |
replicate-ignore-db=database_name |
Tells the slave thread to not replicate any command where the current
database is database_name . To specify more than one database
to ignore, use the directive multiple times, once for each database. You
should not use this directive if you are using cross table updates and you
don't want these update to be replicated. The main reason for this
behaviour is that it's hard from the command alone know if a query should
be replicated or not; For example if you are using multi-table-delete or
multi-table-update commands in MySQL 4.x that goes across multiple
databases. It's also very fast to just check the current database, as this
only has to be done once at connect time or when the database changes. If
you need cross database updates to work, make sure you have 3.23.28 or
later, and use replicate-wild-ignore-table=db_name.% .
Example: replicate-ignore-db=some_db |
replicate-do-db=database_name |
Tells the slave thread to restrict replication to commands where the
current database is database_name . To specify more than one
database, use the directive multiple times, once for each database. Note
that this will not replicate cross-database queries such as UPDATE
some_db.some_table SET foo='bar' while having selected a different
or no database. If you need cross database updates to work, make sure you
have 3.23.28 or later, and use
replicate-wild-do-table=db_name.% . Example:
replicate-do-db=some_db |
log-slave-updates |
Tells the slave to log the updates from the slave thread to the binary log. Off by default. You will need to turn it on if you plan to daisy-chain the slaves. |
replicate-rewrite-db=from_name->to_name |
Updates to a database with a different name than the original.
Example:
replicate-rewrite-db=master_db_name->slave_db_name |
slave-skip-errors= [err_code1,err_code2,... | all] |
Available only in 3.23.47 and later. Tells the slave thread to
continue replication when a query returns an error from the provided list.
Normally, replication will discontinue when an error is encountered,
giving the user a chance to resolve the inconsistency in the data
manually. 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, you should never get an
abort with error. Indiscriminate use of this option will result in slaves
being hopelessly out of sync with the master and you having no idea how
the problem happened. 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 . Full list of error messages can be found
in the source distribution in `Docs/mysqld_error.txt'. You can
(but should not) also use a very non-recommended value of all
which will ignore all error messages and keep barging along regardless.
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. Example: slave-skip-errors=1062,1053 or
slave-skip-errors=all |
skip-slave-start |
Tells the slave server not to start the slave on the startup. The user
can start it later with SLAVE START . |
slave_compressed_protocol=# |
If 1, then use compression on the slave/client protocol if both slave and master support this. |
slave_net_timeout=# |
Number of seconds to wait for more data from the master before aborting the read. |
kit chen: If you're attempting to use both replicate-do-db=from_name and replicate-rewrite-db=from_name->to_name be aware that you need to actually say replicate-do-db=to_name because the rewrite rule apparently happens before the do-db rule. thanks to Therion on opn/freenode for troubleshooting this with me.
![]() | ||
| ||
suggest this page
to a friend | contact us | privacy policy
© 1995-2002 MySQL AB | ||
![]() | ||