(mysql.info.gz) CHANGE MASTER TO
Info Catalog
(mysql.info.gz) Replication Slave SQL
(mysql.info.gz) Replication Slave SQL
(mysql.info.gz) LOAD DATA FROM MASTER
13.6.2.1 `CHANGE MASTER TO' Syntax
..................................
CHANGE MASTER TO master_def [, master_def] ...
master_def:
MASTER_HOST = 'HOST_NAME'
| MASTER_USER = 'USER_NAME'
| MASTER_PASSWORD = 'PASSWORD'
| MASTER_PORT = PORT_NUM
| MASTER_CONNECT_RETRY = COUNT
| MASTER_LOG_FILE = 'MASTER_LOG_NAME'
| MASTER_LOG_POS = MASTER_LOG_POS
| RELAY_LOG_FILE = 'RELAY_LOG_NAME'
| RELAY_LOG_POS = RELAY_LOG_POS
| MASTER_SSL = {0|1}
| MASTER_SSL_CA = 'CA_FILE_NAME'
| MASTER_SSL_CAPATH = 'CA_DIRECTORY_NAME'
| MASTER_SSL_CERT = 'CERT_FILE_NAME'
| MASTER_SSL_KEY = 'KEY_FILE_NAME'
| MASTER_SSL_CIPHER = 'CIPHER_LIST'
Changes the parameters that the slave server uses for connecting to and
communicating with the master server.
`MASTER_USER', `MASTER_PASSWORD', `MASTER_SSL', `MASTER_SSL_CA',
`MASTER_SSL_CAPATH', `MASTER_SSL_CERT', `MASTER_SSL_KEY', and
`MASTER_SSL_CIPHER' provide information for the slave about how to
connect to its master.
The relay log options (`RELAY_LOG_FILE' and `RELAY_LOG_POS') are
available beginning with MySQL 4.0.
The SSL options (`MASTER_SSL', `MASTER_SSL_CA', `MASTER_SSL_CAPATH',
`MASTER_SSL_CERT', `MASTER_SSL_KEY', and `MASTER_SSL_CIPHER') are
available beginning with MySQL 4.1.1. You can change these options
even on slaves that are compiled without SSL support. They are saved to
the `master.info' file, but are ignored until you use a server that has
SSL support enabled.
If you don't specify a given parameter, it keeps its old value, except
as indicated in the following discussion. For example, if the password
to connect to your MySQL master has changed, you just need to issue
these statements to tell the slave about the new password:
mysql> STOP SLAVE; -- if replication was running
mysql> CHANGE MASTER TO MASTER_PASSWORD='new3cret';
mysql> START SLAVE; -- if you want to restart replication
There is no need to specify the parameters that do not change (host,
port, user, and so forth).
`MASTER_HOST' and `MASTER_PORT' are the hostname (or IP address) of the
master host and its TCP/IP port. Note that if `MASTER_HOST' is equal to
`localhost', then, like in other parts of MySQL, the port may be
ignored (if Unix socket files can be used, for example).
If you specify `MASTER_HOST' or `MASTER_PORT', the slave assumes that
the master server is different than before (even if you specify a host
or port value that is the same as the current value.) In this case, the
old values for the master binary log name and position are considered
no longer applicable, so if you do not specify `MASTER_LOG_FILE' and
`MASTER_LOG_POS' in the statement, `MASTER_LOG_FILE=''' and
`MASTER_LOG_POS=4' are silently appended to it.
`MASTER_LOG_FILE' and `MASTER_LOG_POS' are the coordinates at which the
slave I/O thread should begin reading from the master the next time the
thread starts. If you specify either of them, you can't specify
`RELAY_LOG_FILE' or `RELAY_LOG_POS'. If neither of `MASTER_LOG_FILE'
or `MASTER_LOG_POS' are specified, the slave uses the last coordinates
of the _slave SQL thread_ before `CHANGE MASTER' was issued. This
ensures that replication has no discontinuity, even if the slave SQL
thread was late compared to the slave I/O thread, when you just want to
change, say, the password to use. This safe behavior was introduced
starting from MySQL 4.0.17 and 4.1.1. (Before these versions, the
coordinates used were the last coordinates of the slave I/O thread
before `CHANGE MASTER' was issued. This caused the SQL thread to
possibly lose some events from the master, thus breaking replication.)
`CHANGE MASTER' _deletes all relay log files_ and starts a new one,
unless you specify `RELAY_LOG_FILE' or `RELAY_LOG_POS'. In that case,
relay logs are kept; as of MySQL 4.1.1 the `relay_log_purge' global
variable is set silently to 0.
`CHANGE MASTER TO' updates the contents of the `master.info' and
`relay-log.info' files.
`CHANGE MASTER' is useful for setting up a slave when you have the
snapshot of the master and have recorded the log and the offset
corresponding to it. After loading the snapshot into the slave, you
can run `CHANGE MASTER TO MASTER_LOG_FILE='LOG_NAME_ON_MASTER',
MASTER_LOG_POS=LOG_OFFSET_ON_MASTER' on the slave.
Examples:
mysql> CHANGE MASTER TO
-> MASTER_HOST='master2.mycompany.com',
-> MASTER_USER='replication',
-> MASTER_PASSWORD='bigs3cret',
-> MASTER_PORT=3306,
-> MASTER_LOG_FILE='master2-bin.001',
-> MASTER_LOG_POS=4,
-> MASTER_CONNECT_RETRY=10;
mysql> CHANGE MASTER TO
-> RELAY_LOG_FILE='slave-relay-bin.006',
-> RELAY_LOG_POS=4025;
The first example changes the master and master's binary log
coordinates. This is used when you want to set up the slave to replicate
the master.
The second example shows an operation that is less frequently used. It
is done when the slave has relay logs that you want it to execute again
for some reason. To do this, the master need not be reachable. You just
have to use `CHANGE MASTER TO' and start the SQL thread (`START SLAVE
SQL_THREAD').
You can even use the second operation in a non-replication setup with a
standalone, non-slave server, to recover after a crash. Suppose that
your server has crashed and you have restored a backup. You want to
replay the server's own binary logs (not relay logs, but regular binary
logs), supposedly named `myhost-bin.*'. First, make a backup copy of
these binary logs in some safe place, in case you don't exactly follow
the procedure below and accidentally have the server purge the binary
logs. If using MySQL 4.1.1 or newer, use `SET GLOBAL
relay_log_purge=0' for additional safety. Then start the server
without the `--log-bin' option. Before MySQL 4.0.19, start it with a
new (different from before) server id; in newer versions there is no
need, just use the `--replicate-same-server-id' option. Start it with
`--relay-log=myhost-bin' (to make the server believe that these regular
binary logs are relay logs) and with `--skip-slave-start'. After the
server starts, issue these statements:
mysql> CHANGE MASTER TO
-> RELAY_LOG_FILE='myhost-bin.153',
-> RELAY_LOG_POS=410,
-> MASTER_HOST='some_dummy_string';
mysql> START SLAVE SQL_THREAD;
The server will read and execute its own binary logs, thus achieving
crash recovery. Once the recovery is finished, run `STOP SLAVE', shut
down the server, delete `master.info' and `relay-log.info', and restart
the server with its original options.
For the moment, specifying `MASTER_HOST' (even with a dummy value) is
required to make the server think it is a slave. In the future, we plan
to add options to get rid of these small constraints.
Info Catalog
(mysql.info.gz) Replication Slave SQL
(mysql.info.gz) Replication Slave SQL
(mysql.info.gz) LOAD DATA FROM MASTER
automatically generated byinfo2html