mysql 主從問題 errorno 1519 ，1418

標籤：

背景：主從未加監控，過了幾天剛發現主從已經掛了好幾天了，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 @[email protected]@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

啟動後成功

 

mysql 主從問題 errorno 1519 ，1418