背景:主从未加监控,过了几天刚发现主从已经挂了好几天了,show slave status \G; 看到sql和IO进程均为NO
详细错误信息:
[ERROR] Slave I/O: Relay log write failure: could not queue event from master, Error_code: 1595
binlog 卡在pos 122上
查看主库binlog 和post 对应的sql 语句,显示:
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#151022 17:59:23 server id 10 end_log_pos 122 CRC32 0xb21fd3a8 Start: binlog v 4, server v 5.7.6-m16-log created 151022 17:59:23
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
e7MoVg8KAAAAdgAAAHoAAAABAAQANS43LjYtbTE2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXgAEGggAAAAICAgCAAAACgoKKioAEjUB
qNMfsg==
'/*!*/;
# at 122
#151022 17:59:23 server id 10 end_log_pos 153 CRC32 0x4db53c0f Previous-GTIDs
# [empty]
# at 153
解决办法:从库中修改binlog 和pos ,直接post 定位到153后,启动后IO进程成功。
SQL进程报错:错误码1418
详细错误:
[ERROR] Slave SQL: Error 'This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is ena
bled (you *might* want to use the less safe log_bin_trust_function_creators variable)' on query. Default database: 'billplatmp'. Query: 'CREATE DEFINER=`ydhlpay`@`%` F
UNCTION `currval`(seq_name VARCHAR(50)) RETURNS int(11)
BEGIN
DECLARE value INTEGER;
SET value = 0;
SELECT current_value INTO value
FROM sequence
WHERE name = seq_name;
RETURN value;
END', Error_code: 1418
2015-10-22T03:23:19.401095Z 5 [Warning] Slave: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you
*might* want to use the less safe log_bin_trust_function_creators variable) Error_code: 1418
查询原因,为从库无创建函数或存储过程权限,配置文件增加:
log-bin-trust-function-creators=1
启动后成功