MySQL Manual | 4.10.6 Replication Options in `my.cnf'

4 Database Administration
- 4.10 Replication in MySQL

Previous / Next / Up / Table of Contents

4.10.6 Replication Options in `my.cnf'

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 options you can use on the MASTER are all described there: see section 4.9.4 The Binary Log.

The following table describes the options you can use on the SLAVE. It is recommended to read the following paragraph; these options can help you customize replication to suit your needs.

NOTE: replication handles the following options :

master-host
master-user
master-password
master-port
master-connect-retry

in a special way. If no `master.info' file exists (replication is used for the very first time or you have run RESET SLAVE and shutdown/restarted the slave server), the slave uses values specified on the command-line or in `my.cnf'. But if `master.info' exists, the slave IGNORES any values specified on the command-line or in `my.cnf', and uses instead the values it reads from `master.info'. For example, if you have

master-host=this_host

in your `my.cnf', are using replication, then want to replicate from another host, modifying the above line in `my.cnf' will have no effect. You must use CHANGE MASTER TO instead. This holds true for master-host, master-user, master-password, master-port, master-connect-retry. Therefore, you may decide to put no master-* options in `my.cnf' and instead use only CHANGE MASTER TO (see section 4.10.7 SQL Commands Related to Replication).

Option	Description
`log-slave-updates`	Tells the slave to log the updates done by the slave SQL thread to the slave's binary log. Off by default. Of course, it requires that the slave be started with binary logging enabled (`log-bin` option). You have to use `log-slave-updates` to chain several slaves ; for example for the following setup to work A -> B ->C (C is a slave of B which is a slave of A) you need to start B with the `log-slave-updates` option.
`log-warnings`	Makes the slave print more messages 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.
`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. The value in `master.info' will take precedence if it can be read. Example: `master-connect-retry=60`
`master-ssl`	Planned to enable the slave to connect to the master using SSL. Does nothing yet! Example: `master-ssl`
`master-ssl-key=filename`	Master SSL keyfile name. Only applies if you have enabled `master-ssl`. Does nothing yet. Example: `master-ssl-key=SSL/master-key.pem`
`master-ssl-cert=filename`	Master SSL certificate file name. Only applies if you have enabled `master-ssl`. Does nothing yet. Example: `master-ssl-cert=SSL/master-cert.pem`
`master-ssl-capath`	Master SSL CA path. Only applies if you have enabled `master-ssl`. Does nothing yet.
`master-ssl-cipher`	Master SSL cipher. Only applies if you have enabled `master-ssl`. Does nothing yet.
`master-info-file=filename`	To give `master.info' another name and/or to put it in another directory than the data directory.
`relay-log=filename`	To specify the location and name that should be used for relay logs. You can use this to have hostname-independant 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 on some area different from the data directory, or if you want to increase speed by balancing load between disks.
`relay-log-index=filename`	To specify the location and name that should be used for the relay logs index file.
`relay-log-info-file=filename`	To give `relay-log.info' another name and/or to put it in another directory than the data directory.
`relay-log-purge=0\|1`	Available since MySQL 4.1.1. Disables/enables automatic purging of relay logs as soon as they are not needed anymore. This is a global variable which can be dynamically changed with `SET GLOBAL RELAY_LOG_PURGE=0\|1`. The default value is 1.
`relay-log-space-limit=#`	To put an upper limit on the total size of all relay logs on the slave (a value of 0 means ``unlimited''). This is useful if you have a small hard disk on your slave machine. When the limit is reached, the I/O thread pauses (does not read the master's binlog) until the SQL thread has catched up and deleted some now unused relay logs. Note that this limit is not absolute: there are cases where the SQL thread needs more events to be able to delete; in that case the I/O thread will overgo the limit until deletion becomes possible. Not doing so would cause a deadlock (which happens before MySQL 4.0.13). Users should not set `relay-log-space-limit` to less than twice the value of `max-binlog-size` because in that case there are chances that when the I/O thread waits for free space because `relay-log-space-limit` is exceeded, the SQL thread has no relay log to purge and so cannot satisfy the I/O thread, forcing the I/O thread to temporarily ignore `relay-log-space-limit`.
`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`. Please read notes which follow this table. 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-database updates, in contrast to `replicate-ignore-db`. Please read notes which follow this table. 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. Please read notes which follow this table. 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`. Note that if you do `replicate-wild-do-table=foo%.%` then the rule will be propagated to `CREATE DATABASE` and `DROP DATABASE`, that is, these two statements will be replicated if the database name matches the database pattern ('foo%' here) (this magic is triggered by '%' being the table pattern).
`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. Please read notes which follow this table. 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`. Note that if you do `replicate-wild-ignore-table=foo%.%` then the rule will be propagated to `CREATE DATABASE` and `DROP DATABASE`, that is, these two statements will not be replicated if the database name matches the database pattern ('foo%' here) (this magic is triggered by '%' being the table pattern).
`replicate-do-db=database_name`	Tells the slave to restrict replication to commands where the current database (that is, the one selected by `USE`) 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.%`. Please read notes which follow this table. Example: `replicate-do-db=some_db`. Example of what does not work as you could expect it: if the slave is started with `replicate-do-db=sales`, and you do `USE prices; UPDATE sales.january SET amount=amount+1000;`, this query will not be replicated. If you need cross database updates to work, use `replicate-wild-do-table=db_name.%` instead. The main reason for this ``just-check-the-current-database'' behaviour is that it's hard from the command alone to know if a query should be replicated or not ; for example if you are using multi-table-delete or multi-table-update commands that go across multiple databases. It's also very fast to just check the current database.
`replicate-ignore-db=database_name`	Tells the slave to not replicate any command where the current database (that is, the one selected by `USE`) 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. Please read notes which follow this table. Example: `replicate-ignore-db=some_db`. Example of what does not work as you could expect it: if the slave is started with `replicate-ignore-db=sales`, and you do `USE prices; UPDATE sales.january SET amount=amount+1000;`, this query will be replicated. If you need cross database updates to work, use `replicate-wild-ignore-table=db_name.%` instead.
`replicate-rewrite-db=from_name->to_name`	Tells the slave to translate the current database (that is, the one selected by `USE`) to `to_name` if it was `from_name` on the master. Only statements involving tables may be affected (`CREATE DATABASE`, `DROP DATABASE` won't), and only if `from_name` was the current database on the master. This will not work for cross-database updates. Note that the translation is done before `replicate-*` rules are tested. Example: `replicate-rewrite-db=master_db_name->slave_db_name`
`report-host=host`	Available after 4.0.0. Hostname or IP of the slave to be reported 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. For the moment this option has no real interest ; it is meant for failover replication which is not implemented yet. Example: `report-host=slave1.mycompany.com`
`report-port=portnumber`	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. For the moment this option has no real interest ; it is meant for failover replication which is not implemented yet.
`skip-slave-start`	Tells the slave server not to start the slave threads on server startup. The user can start them later with `START SLAVE`.
`slave_compressed_protocol=#`	If 1, then use compression on the slave/client protocol if both slave and master support this.
`slave-load-tmpdir=filename`	This option is by default equal to `tmpdir`. When the SQL slave replicates a `LOAD DATA INFILE` command, 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 you may wish/have to tell the slave to put the temporary files on some large disk different from `tmpdir`, using this option. In that case, you may also use the `relay-log` option, as relay logs will be huge too.
`slave-net-timeout=#`	Number of seconds to wait for more data from the master before aborting the read, considering the connection broken and retrying to connect, first time immediately, then every `master-connect-retry` seconds.
`slave-skip-errors= [err_code1,err_code2,... \| all]`	Tells the slave SQL 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`. A 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`

Some of these options, like all replicate-* options, can only be set at the slave server's startup, not on-the-fly. We plan to fix this.

Here is the order of evaluation of the replicate-* rules, to decide if the query is going to be executed by the slave or ignored by it:

Are there some replicate-do-db or replicate-ignore-db rules?
- Yes: test them like for binlog-do-db and binlog-ignore-db (see section 4.9.4 The Binary Log). What is the result of the test?
  - ignore the query: ignore it and exit.
  - execute the query: don't execute it immediately, defer the decision, go to step below.
- No: go to step below.
Are there some replicate-*-table rules?
- No: execute the query and exit.
- Yes: go to step below. Only tables which are to be updated will be compared to rules (INSERT INTO sales SELECT * from prices: only sales will be compared to rules). If several tables are to be updated (multi-table statement), the first matching table (matching ``do'' or ``ignore'') wins (i.e. the first table is compared to rules, then if no decision could be taken the second table is compared to rules etc).
Are there some replicate-do-table rules?
- Yes: does the table match any of them?
  - Yes: execute the query and exit.
  - No: go to step below.
- No: go to step below.
Are there some replicate-ignore-table rules?
- Yes: does the table match any of them?
  - Yes: ignore the query and exit.
  - No: go to step below.
- No: go to step below.
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 step below.
- No: go to step below.
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 step below.
- No: go to step below.
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, 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.

User Comments

Posted by kit chen on Monday September 16 2002, @1:42pm

[Delete] [Edit]

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.

Posted by Michael Babcock on Friday November 8 2002, @9:03am

[Delete] [Edit]

I was about to post the same comment, but as it
applies to replicate-wild-do-table.

replicate-wild-do-table = LocalTableName.%
replicate-rewrite-db = RemoteTableName ->
LocalTableName

Posted by Ken Allan on Monday November 25 2002, @5:51pm

[Delete] [Edit]

Be really careful with the use of the
replicate-wild-do-table=db_name.% configuration
option. In 4.0.4, this option caused updates to
any specified tables to not work for me.

I had read in the documentation that this was
needed for cross database updates, but it was
causing my same database updates to fail.

I had the following options set in my slave my.cnf:
server-id = 16
master-host = 64.xx.xx.xx
master-user = replicator
master-password = *****
replicate-wild-do-table = banner.%
replicate-do-db = banner
report-host = 64.xx.xx.xx

Also, worth mentioning is that there seems to be
some limit in the server-id's, initially i set my
server-id to 15001 and this caused replication to
fail silently to even start up. Changed it to 16,
and it works perfectly, all this despite the
alleged limit of 2^32-1.

Posted by Raul Deschamps on Thursday February 20 2003, @2:33pm

[Delete] [Edit]

Hi
I'm not an american so... what does daisy-chain means?

Hope somebody can post the answer

Posted by Paul Becker on Sunday February 23 2003, @11:54am

[Delete] [Edit]

"daisy-chain" means to connect one to another, then that one to yet another, and so on. For example, 1 connects to 2, 2 connects to 3, 3 connects to 4...

Paul

Posted by [name withheld] on Thursday May 1 2003, @12:53am

[Delete] [Edit]

how can we do this

A -> B -> A

Posted by wallace wong on Friday May 23 2003, @8:58pm

[Delete] [Edit]

How can i make a daisy-chain replication as below
db1->db2->db3, the db2 and db3 is in the same host.

Posted by Fajar Nugraha on Monday June 9 2003, @5:42am

[Delete] [Edit]

I have this setup working :

A -> B -> A

I got in running with mysql 4.0.13-max, using MyISAM and InnoDB tables.

Here's how I do it on A:
- enable bin-log (just add log-bin in /etc/my.cnf. Restart mysqld if necessary.)
- create a replication user on A (I give it all privileges. You probably shouldn't do that).
- execute query
FLUSH TABLES WITH READ LOCK;
- do
tar -cvf /tmp/mysql-snapshot.tar /path/to/data-dir
- execute query
SHOW MASTER STATUS;
write down the result for
- modify /etc/my.cnf to include
server-id=<number-of-your-choice>
- shutdown mysqld on A (my root is password-protected, and I do it from another terminal)
mysqladmin -uroot -p shutdown
- start it back up

on B (make sure there are NO update queries on B at this point):
- make sure mysqld is dead
- copy and untar mysql-snapshot.tar created earlier
- copy my.cnf from A, put DIFFERENT number in server_id.
- start mysqld (make sure binary log is enabled)
- execute queries (this is where you put the values you got earlier from SHOW MASTER STATUS on A):
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='<A host name>',
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;
START SLAVE;
- execute query
SHOW MASTER STATUS;
write down the values

At this point you got A->B replication

on A again:
- copy B's *.bin.* (binary logs), put it in A's data dir
- execute queries (this is where you put the values you got earlier from SHOW MASTER STATUS on B):
STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='<B host name>',
MASTER_USER='<replication user name>',
MASTER_PASSWORD='<replication password>',
MASTER_LOG_FILE='<recorded log file name>',
MASTER_LOG_POS=<recorded log offset>;
START SLAVE;

And you're done! If you do what I do, you will have the same user on both A and B, and this replication setup :

A -> B -> A

You can now execute any query on any of them, and it will appear on both. You can even call it a mysql cluster.

Posted by Steve Rapaport on Tuesday July 22 2003, @7:47am

[Delete] [Edit]

Very nice, but remember that in my experience, setting up a working replication is the EASY part. The hard part is always what to do after one machine fails, to reset both
and restart the replication properly.

With A->B replication this is easy -- either switch masters as described in the Replication FAQ, or copy the slave back to the master, reset all the logs, and start again.

With A->B->A replication I would never be certain that I had reset correctly, or even that all my last transactions before the failure were all on the same machine! So I wouldn't do it. It's a low-reliability system, which kind of defeats the purpose (for me) of replication.

Posted by [name withheld] on Wednesday August 6 2003, @7:11pm

[Delete] [Edit]

When using debian ensure that skip-networking is commented out in my.cnf on the master or else you will get an error on the update saying query died while connecting to...

Add your own comment.

Top / Previous / Next / Up / Table of Contents