(mysql.info) change-master-to

(mysql.info) replication-slave-sql
(mysql.info) 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'
 
 `CHANGE MASTER TO' changes the parameters that the slave server uses
 for connecting to and communicating with the master server. It also
 updates the contents of the `master.info' and `relay-log.info' files.
 
 `MASTER_USER', `MASTER_PASSWORD', `MASTER_SSL', `MASTER_SSL_CA',
 `MASTER_SSL_CAPATH', `MASTER_SSL_CERT', `MASTER_SSL_KEY', and
 `MASTER_SSL_CIPHER' provide information to the slave about how to
 connect to its master.
 
 The SSL options (`MASTER_SSL', `MASTER_SSL_CA', `MASTER_SSL_CAPATH',
 `MASTER_SSL_CERT', `MASTER_SSL_KEY', and `MASTER_SSL_CIPHER') can be
 changed 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:
 
      STOP SLAVE; -- if replication was running
      CHANGE MASTER TO MASTER_PASSWORD='new3cret';
      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 number might
 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 from 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 cannot 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 there is no discontinuity in replication, even if the slave SQL
 thread was late compared to the slave I/O thread, when you merely want
 to change, say, the password to use.
 
 `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; the `relay_log_purge' global variable is set
 silently to 0.
 
 `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.
 
 The following 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:
 
      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;
 
 The next example shows an operation that is less frequently employed.
 It is used 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 need only use `CHANGE MASTER TO' and start the SQL thread (`START
 SLAVE SQL_THREAD'):
 
      CHANGE MASTER TO
        RELAY_LOG_FILE='slave-relay-bin.006',
        RELAY_LOG_POS=4025;
 
 You can even use the second operation in a non-replication setup with a
 standalone, non-slave server for recovery following 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), named (for example) `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. Use `SET GLOBAL relay_log_purge=0' for additional safety.
 Then start the server without the -log-bin option, Instead, use the
 -replicate-same-server-id, -relay-log=myhost-bin (to make the server
 believe that these regular binary logs are relay logs) and
 -skip-slave-start options. After the server starts, issue these
 statements:
 
      CHANGE MASTER TO
        RELAY_LOG_FILE='myhost-bin.153',
        RELAY_LOG_POS=410,
        MASTER_HOST='some_dummy_string';
      START SLAVE SQL_THREAD;
 
 The server reads and executes its own binary logs, thus achieving crash
 recovery. Once the recovery is finished, run `STOP SLAVE', shut down
 the server, delete the `master.info' and `relay-log.info' files, and
 restart the server with its original options.
 
 Specifying the `MASTER_HOST' option (even with a dummy value) is
 required to make the server think it is a slave.
Info Catalog
(mysql.info) replication-slave-sql
(mysql.info) load-data-from-master
automatically generated byinfo2html