Jump to Navigation

MySQL: Administering replication

Replication threads

Replication uses two slave threads. One is the I/O thread: it connects to the master server, downloads transactions and writes them to the relay logs. The other is the SQL thread: it reads transaction from the relay logs and executes them.

Provided the network connection between master and slave is stable, any bottlenecks are in the SQL thread. On a very busy system it can lag behind the I/O thread.

Thread status

Using a SHOW SLAVE STATUS statement you can see exactly what the status of both threads is, even though the printed order is not very convenient:

  • Slave_IO_Running, Slave_SQL_Running: whether both threads are started
  • Slave_IO_State, Slave_SQL_Running_State: The I/O and SQL thread state.
  • Last_IO_Errno, Last_IO_Error, Last_SQL_Errno, Last_SQL_Error: The last I/O thread and SQL thread errors (Last_Errno and Last_Error are synonyms for Last_SQL_Errno and Last_SQL_Error)
  • Master_Log_File and Read_Master_Log_Pos : the I/O thread position in the master logs
  • Relay_Log_File and Relay_Log_Pos: The SQL thread position in the relay logs
  • Relay_Master_Log_File and Exec_Master_Log_Pos: The SQL thread position in the master logs
  • Seconds_Behind_Master: An indication how much the SQL thread is lagging behind.

Stopping and starting threads

You can stop and start the I/O and SQL threads separately:

  • START SLAVE: starts both threads
  • START SLAVE IO_THREAD: starts only the I/O thread
  • START SLAVE SQL_THREAD: starts only the SQL thread
  • STOP SLAVE: stops both threads
  • STOP SLAVE IO_THREAD: stops only the I/O thread
  • STOP SLAVE SQL_THREAD: stops only the SQL thread

Skipping transactions

Sometimes a replication problem occurs: a statement can not be executed. The solution is often to skip one or more transactions from the replication stream.

Skip one or more transactions (non-GTID)

To skip one or more transactions, do the following:

-- On the slave
STOP SLAVE;
-- To change more than one transaction, set the counter higher
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;

The most secure way to do this is to skip statements one by one, checking whether the SQL thread gives an error on the next one.

Alternatively, you can examine the binlogs on the master or the relay logs on the slave, determining at which point replication should resume. Then, stop the slave, set that position using a CHANGE MASTER TO MASTER_LOG_FILE=file, MASTER_LOG_POS=pos or CHANGE MASTER TO RELAY_LOG_FILE=file, RELAY_LOG_POS=pos and start the slave again.

Skip one transaction (using GTIDs)

You can not really skip transactions if you are using GTIDs; but you can register GTIDs as if they were executed, which means they will never be executed again. Effectively, you will be skipping them.

-- On the slave
STOP SLAVE;
-- Substitute the GTID below.
-- For example: a0dd7f98-dac2-11e4-9ab1-525400b6d1f8:12
SET GTID_NEXT=transaction_id;
START TRANSACTION;
COMMIT;
SET GTID_NEXT=AUTOMATIC;
START SLAVE;

This works fine if only a few transactions have to be skipped; if there are a lot, this gets old really fast. In MySQL Utilities 1.6 and later there is a tool mysqlslavetrx that should be able to handle this better.

Tags:


Technical_article | by Dr. Radut