GNU Info
(mysql.info)Replication SQL
SQL Commands Related to Replication
-----------------------------------
Replication can be controlled through the SQL interface. Below is the
summary of commands:
*Command* *Description*
`SLAVE START' Starts the slave thread. (Slave)
`SLAVE STOP' Stops the slave thread. (Slave)
`SET SQL_LOG_BIN=0' Disables update logging if the user has process
privilege. Ignored otherwise. (Master)
`SET SQL_LOG_BIN=1' Re-enables update logging if the user has process
privilege. Ignored otherwise. (Master)
`SET Skip the next `n' events from the master. Only
SQL_SLAVE_SKIP_COUNTER=n'valid when the slave thread is not running,
otherwise, gives an error. Useful for recovering
from replication glitches.
`RESET MASTER' Deletes all binary logs listed in the index file,
resetting the binlog index file to be empty. In
pre-3.23.26 versions, `FLUSH MASTER' (Master)
`RESET SLAVE' Makes the slave forget its replication position
in the master logs. In pre 3.23.26 versions the
command was called `FLUSH SLAVE'(Slave)
`LOAD TABLE tblname Downloads a copy of the table from master to the
FROM MASTER' slave. (Slave)
`CHANGE MASTER TO Changes the master parameters to the values
master_def_list' specified in `master_def_list' and restarts the
slave thread. `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'. 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;
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 change
the host or the port. In that case, the slave
will assume that since you are connecting to a
different host or a different port, the master is
different. Therefore, the old values of log and
position are not applicable anymore, and will
automatically be reset to an empty string and 0,
respectively (the start values). Note that if you
restart the slave, it will remember its last
master. If this is not desirable, you should
delete the `master.info' file before restarting,
and the slave will read its master from `my.cnf'
or the command line. (Slave)
`SHOW MASTER STATUS' Provides status information on the binlog of the
master. (Master)
`SHOW SLAVE STATUS' Provides status information on essential
parameters of the slave thread. (Slave)
`SHOW MASTER LOGS' Only available starting in Version 3.23.28. Lists
the binary logs on the master. You should use
this command prior to `PURGE MASTER LOGS TO' to
find out how far you should go.
`PURGE MASTER LOGS TO Available starting in Version 3.23.28. Deletes
'logname'' all the replication logs that are listed in the
log index as being prior to the specified log,
and removed them from the log index, so that the
given log now becomes first. Example:
PURGE MASTER LOGS TO 'mysql-bin.010'
This command will do nothing and fail with an
error if you have an active slave that is
currently reading one of the logs you are trying
to delete. However, if you have a dormant slave,
and happen to purge one of the logs it wants to
read, the slave will be unable to replicate once
it comes up. The command is safe to run while
slaves are replicating - you do not need to stop
them.
You must first check all the slaves with `SHOW
SLAVE STATUS' to see which log they are on, then
do a listing of the logs on the master with `SHOW
MASTER LOGS', find the earliest log among all the
slaves (if all the slaves are up to date, this
will be the last log on the list), backup all the
logs you are about to delete (optional) and purge
up to the target log.
automatically generated by info2www version 1.2.2.9