4.10.7.9 CHANGE MASTER TO master_def_list (slave)

CHANGE MASTER is a ``brutal'' command, it is recommended to read this whole description before using it in production.

Changes the master parameters (connection and binlog information) to the values specified in master_def_list. master_def_list is a comma-separated list of master_def where master_def is one of the following: MASTER_HOST, MASTER_USER, MASTER_PASSWORD, MASTER_PORT, MASTER_CONNECT_RETRY, MASTER_LOG_FILE, MASTER_LOG_POS, RELAY_LOG_FILE, RELAY_LOG_POS (these last two only starting from MySQL 4.0). For example:

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;

CHANGE MASTER TO
  RELAY_LOG_FILE='slave-relay-bin.006',
  RELAY_LOG_POS=4025;

You only need to specify the values that need to be changed. The values that you omit will stay the same with the exception of when you specify (not necessarily change) the host or port. In that case, the slave will assume that the master is different from before. Therefore, the old values of master's binlog name and position are considered no longer applicable, thus if you didn't specify MASTER_LOG_FILE and MASTER_LOG_POS in the command, MASTER_LOG_FILE='' and MASTER_LOG_POS=4 will silently be appended to it.

MASTER_LOG_FILE and MASTER_LOG_POS are the coordinates from which the I/O slave thread will start reading from the master, next time this thread is started. As CHANGE MASTER deletes relay logs (see below), they are also the coordinates from which the SQL slave thread will start executing next time it is started.

CHANGE MASTER deletes all relay logs (and starts a new one), unless you specified RELAY_LOG_FILE or RELAY_LOG_POS (in that case relay logs will be kept; since MySQL 4.1.1 the RELAY_LOG_PURGE global variable will silently be set to 0). CHANGE MASTER updates `master.info' and `relay-log.info'.

Note: if, just before you issue CHANGE MASTER, the SQL slave thread is late by one or more queries compared to the I/O thread (a very common case when replication is running in high-load environments), then as CHANGE MASTER deletes relay logs containing these non-executed queries, and so replication then restarts from the coordinates of the I/O thread, the SQL thread will have ``lept'' over the non-executed queries. Therefore, unless these queries were not important, you should, before issuing CHANGE MASTER, either:

• ensure that the SQL slave thread has read all existing relay logs; you can achive this by stopping only the I/O slave thread (STOP SLAVE IO_THREAD), then monitoring the progress of the running SQL slave thread with SHOW SLAVE STATUS and SELECT MASTER_POS_WAIT(), until it has caught up. This way there will be no leap for the SQL slave thread.
• run STOP SLAVE, check where the SQL slave thread is in the master's binlog (using SHOW SLAVE STATUS, columns Relay_Master_Log_File and Exec_master_log_pos), and add a specification of these coordinates to the CHANGE MASTER command (MASTER_LOG_FILE=..., MASTER_LOG_POS=...). This way, you will instruct the I/O slave thread to start replication from the former coordinates of the SQL slave thread, so there will be no leap for the SQL slave thread.

If you don't take care of this issue, even a simple STOP SLAVE; CHANGE MASTER TO MASTER_USER='repl'; START SLAVE; run in the middle of an highly-loaded replication could break this replication and spoil the slave's data.

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 on the master that the snapshot corresponds to. You can run CHANGE MASTER TO MASTER_LOG_FILE='log_name_on_master', MASTER_LOG_POS=log_offset_on_master on the slave after restoring the snapshot.

The first example above (CHANGE MASTER TO MASTER_HOST='master2.mycompany.com' etc) changes the master and master's binlog coordinates. This is when you want the slave to replicate the master. The second example, less frequently used, is when the slave has relay logs which, for some reason, you want the slave to execute again; to do this the master needn't be reachable, you just have to do CHANGE MASTER TO and start the SQL thread (START SLAVE SQL_THREAD). You can even use this out of a replication setup, on a standalone, slave-of-nobody server, to recover after a crash. Suppose your server has crashed and you have restored a backup. You want to replay the server's own binlogs (not relay logs, but regular binary logs), supposedly named `myhost-bin.*'. First make a backup copy of these binlogs in some safe place, in case you don't exactly follow the procedure below and accidentally have the server purge the binlogs. If using MySQL 4.1.1 or newer, do SET GLOBAL RELAY_LOG_PURGE=0 for additional safety. Then start the server without log-bin, with a new (different from before) server id, with relay-log=myhost-bin (to make the server believe that these regular binlogs are relay logs) and skip-slave-start, then issue

CHANGE MASTER TO RELAY_LOG_FILE='myhost-bin.153',RELAY_LOG_POS=410, MASTER_HOST='some_dummy_string';
START SLAVE SQL_THREAD;

Then the server will read and execute its own binlogs, thus achieving crash recovery. Once the recovery is finished, run STOP SLAVE, shutdown 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 compulsory to make the server think he is a slave, and giving the server a new, different from before, server id is also compulsory otherwise the server will see events with its id and think it is in a circular replication setup and skip the events, which is unwanted. In the future we plan to add options to get rid of these small constraints.

User Comments

Add your own comment.