MySQL – How to see the queries executing by slave io thread

MySQLreplication

We have a strange problem with MySQL replication. We have observed huge load and IO wait on server while replication is in progress. We can have information of the queries from bin-log by using mysqlbinlog utility. But we would like to see on the slave server what queries it is executing in particular time. We have enabled general log to see the queries but we have commit statements only.

How to see the queries executing by replication thread?

We are using MYSQL 5.6.10 for both master and slave.

Best Answer

Queries on slave are being applied by system user, so doing show full processlist and queries running with system user are the ones executed by slave sql thread.

Moreover you can view relay logs with the help of mysqlbinlog utlity and replacing binlog file with relay log file.

You can also introduce triggers on processlist table in information_schema database in this way you would be able to capture queries.

To get current query as string use following statement. 

SELECT info FROM INFORMATION_SCHEMA.PROCESSLIST WHERE id = CONNECTION_ID()

To get queries using trigger a sample trigger is as follows.

First create table logging_table with column as varchar(1024).

DELIMITER |

CREATE TRIGGER log_queries_sample BEFORE INSERT ON `table_name`  
FOR EACH ROW  
BEGIN  
    DECLARE original_query_string VARCHAR(1024);   
    SET original_query_string = (SELECT info
                                 FROM INFORMATION_SCHEMA.PROCESSLIST
                                 WHERE id >= CONNECTION_ID());  
    INSERT INTO `logging_table`(`query`) VALUES (original_query_string);  
END;  
|
DELIMITER ;

Create triggers of your choice accordingly.

Hope it helps.

Related Question