GNU Info
(mysql.info)Replication Options
Replication Options in my.cnf
-----------------------------
If you are using replication, we recommend you to use MySQL Version
3.23.30 or later. Older versions work, but they do have some bugs and
are missing some features.
On both master and slave you need to use the `server-id' option. This
sets an 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 has 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 Version 3.23.26 and in later
versions 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 can use this option if you
want to be a rebel.
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 others database are ignored.
Note that if you use this you should ensure that
you only do updates in the current database.
Example: `binlog-do-db=some_database'.
`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 only do updates in the
current database.
Example: `binlog-ignore-db=some_database'
The following table has 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.
Example: `master-host=db-master.mycompany.com'.
`master-user=username' The user the slave thread will us for
authentication when connecting to the master. The
user must have `FILE' privilege. If the master
user is not set, user `test' is assumed.
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.
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.
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-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. Sasha: The only reason I see for ever
changing the default is the desire to be
rebelious.
Example: `master-info-file=master.info'.
`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 to the
specified table. 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 the tables that 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 to tables in all 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 to the
tables that match the given wild card 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 to the
specified database. To specify more than one
database to ignore, use the directive multiple
times, once for each database. This option will
not work if you use cross database updates. 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 the specified database. To specify more than
one database, use the directive multiple times,
once for each database. Note that this will only
work if you do not use 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,..' 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_read_timeout=#' Number of seconds to wait for more data from the
master before aborting the read.
automatically generated by info2www version 1.2.2.9