CHANGE MASTER TO option [, option] ...
option:
MASTER_BIND = 'interface_name'
| MASTER_HOST = 'host_name'
| MASTER_USER = 'user_name'
| MASTER_PASSWORD = 'password'
| MASTER_PORT = port_num
| MASTER_CONNECT_RETRY = interval
| MASTER_HEARTBEAT_PERIOD = interval
| 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'
| MASTER_SSL_VERIFY_SERVER_CERT = {0|1}
| IGNORE_SERVER_IDS = (server_id_list)
server_id_list:
[server_id [, server_id] ... ]
IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log.
In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more than once. Thus, this option is useful in circular replication when one of the servers in the circle is removed. Suppose that you have a circular replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and server 3 fails. When bridging the gap by starting replication from server 2 to server 4, you can include IGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you issue on server 4 to tell it to use server 2 as its master instead of server 3. Doing so causes it to ignore and not to propagate any statements that originated with the server that is no longer in use.
The following table shows the maximum permissible length for the string-valued options.
| Option | Maximum Length |
| MASTER_HOST | 60 |
| MASTER_USER | 16 |
| MASTER_PASSWORD | 32 |
| MASTER_LOG_FILE | 255 |
| RELAY_LOG_FILE | 255 |
| MASTER_SSL_CA | 255 |
| MASTER_SSL_CAPATH | 255 |
| MASTER_SSL_CERT | 255 |
| MASTER_SSL_KEY | 255 |
| MASTER_SSL_CIPHER | 51 |
MASTER_POS_WAIT() Syntax
SELECT MASTER_POS_WAIT('master_log_file', master_log_pos [, timeout])
This is actually a function, not a statement. It is used to ensure that the slave has read and executed events up to a given position in the master's binary log. See Section 12.15, “Miscellaneous Functions”, for a full description.
- MASTER_POS_WAIT(log_name,log_pos[,timeout])
This function is useful for control of master/slave synchronization. It blocks until the slave has read and applied all updates up to the specified position in the master log. The return value is the number of log events the slave had to wait for to advance to the specified position. The function returns NULL if the slave SQL thread is not started, the slave's master information is not initialized, the arguments are incorrect, or an error occurs. It returns -1 if the timeout has been exceeded. If the slave SQL thread stops while MASTER_POS_WAIT() is waiting, the function returns NULL. If the slave is past the specified position, the function returns immediately.
If a timeout value is specified, MASTER_POS_WAIT() stops waiting when timeout seconds have elapsed. timeout must be greater than 0; a zero or negative timeout means no timeout.
This function is unsafe for statement-based replication. Beginning with MySQL 5.5.1, a warning is logged if you use this function when binlog_format is set to STATEMENT. (Bug #47995)
RESET SLAVE Syntax
RESET SLAVE [ALL]
RESET SLAVE makes the slave forget its replication position in the master's binary log. This statement is meant to be used for a clean start: It deletes the master.info and relay-log.info files, all the relay log files, and starts a new relay log file. To use RESET SLAVE, the slave replication threads must be stopped (use STOP SLAVE if necessary).
Note
All relay log files are deleted, even if they have not been completely executed by the slave SQL thread. (This is a condition likely to exist on a replication slave if you have issued a STOP SLAVE statement or if the slave is highly loaded.)
In MySQL 5.5 (unlike the case in MySQL 5.1 and earlier), RESET SLAVE does not change any replication connection parameters such as master host, master port, master user, or master password, which are retained in memory. This means that START SLAVE can be issued without requiring a CHANGE MASTER TO statement following RESET SLAVE. Connection parameters are reset if the slave mysqld is shut down following RESET SLAVE. In MySQL 5.5.16 and later, you can instead use RESET SLAVE ALL to reset these connection parameters (Bug #11809016).
If the slave SQL thread was in the middle of replicating temporary tables when it was stopped, and RESET SLAVE is issued, these replicated temporary tables are deleted on the slave.
SET GLOBAL sql_slave_skip_counter Syntax
SET GLOBAL sql_slave_skip_counter = N
This statement skips the next N events from the master. This is useful for recovering from replication stops caused by a statement.
This statement is valid only when the slave threads are not running. Otherwise, it produces an error.
When using this statement, it is important to understand that the binary log is actually organized as a sequence of groups known as event groups. Each event group consists of a sequence of events.
- For transactional tables, an event group corresponds to a transaction.
- For nontransactional tables, an event group corresponds to a single SQL statement.
Note
A single transaction can contain changes to both transactional and nontransactional tables.
When you use SET GLOBAL sql_slave_skip_counter to skip events and the result is in the middle of a group, the slave continues to skip events until it reaches the end of the group. Execution then starts with the next event group.
Beginning with MySQL 5.5.5, issuing this statement causes the previous values of RELAY_LOG_FILE, RELAY_LOG_POS, and sql_slave_skip_counter to be written to the error log.
SHOW BINLOG EVENTS Syntax
SHOW BINLOG EVENTS
[IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
Shows the events in the binary log. If you do not specify 'log_name', the first binary log is displayed.
The LIMIT clause has the same syntax as for the SELECT statement. See Section 13.2.9, “SELECT Syntax”.
Note
Issuing a SHOW BINLOG EVENTS with no LIMIT clause could start a very time- and resource-consuming process because the server returns to the client the complete contents of the binary log (which includes all statements executed by the server that modify data). As an alternative to SHOW BINLOG EVENTS, use the mysqlbinlog utility to save the binary log to a text file for later examination and analysis. See Section 4.6.7, “mysqlbinlog — Utility for Processing Binary Log Files”.
Note
Some events relating to the setting of user and system variables are not included in the output from SHOW BINLOG EVENTS. To get complete coverage of events within a binary log, use mysqlbinlog.
Note
SHOW BINLOG EVENTS does not work with relay log files. You can use SHOW RELAYLOG EVENTS for this purpose.
STOP SLAVE Syntax
STOP SLAVE [thread_types]
thread_types:
[thread_type [, thread_type] ... ]
thread_type: IO_THREAD | SQL_THREAD
