一、问题描述:
MySQL [(none)]> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 100.100.101.211
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000011
Read_Master_Log_Pos: 840319815
Relay_Log_File: prd-carry-mysql-centos-02-relay-bin.000016
Relay_Log_Pos: 6594972
Relay_Master_Log_File: mysql-bin.000007
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1418
Last_Error: Error '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)' on query. Default database: 'carry_admin'. Query: 'CREATE DEFINER=`root`@`%` FUNCTION `F_GET_SEQ_NO`(in_seq_id VARCHAR(50)) RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
Skip_Counter: 0
Exec_Master_Log_Pos: 6594757
Relay_Log_Space: 3465214023
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1418
Last_SQL_Error: Error '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)' on query. Default database: 'carry_admin'. Query: 'CREATE DEFINER=`root`@`%` FUNCTION `F_GET_SEQ_NO`(in_seq_id VARCHAR(50)) RETURNS varchar(255) CHARSET utf8mb4 COLLATE utf8mb4_general_ci
BEGIN
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: ab09d6b7-7cb5-11eb-942e-fefcfea23161
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 210310 22:58:38
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
二、故障原因
当二进制日志启用后,这个变量(log_bin_trust_function_creators)就会启用。它控制是否可以信任存储函数创建者,不会创建写入二进制日志引起不安全事件的存储函数。
如果设置为0(默认值),用户不得创建或修改存储函数,除非它们具有除CREATE ROUTINE或ALTER ROUTINE特权之外的SUPER权限。设置为0还强制使用DETERMINISTIC特性或READS SQL DATA或NO SQL特性声明函数的限制。
如果变量设置为1,MySQL不会对创建存储函数实施这些限制。此变量也适用于触发器的创建
为什么MySQL有这样的限制呢?因为二进制日志的一个重要功能是用于主从复制,而存储函数有可能导致主从的数据不一致。所以当开启二进制日志后,参数log_bin_trust_function_creators就会生效,限制存储函数的创建、修改、调用。
log_bin_trust_function_creators 最终目的就是保持mysql主从复制的一致性~
三、解决方案
MySQL [(none)]> show variables like '%function%';
+---------------------------------+-------+
| Variable_name | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF |
+---------------------------------+-------+
1 row in set (0.00 sec)
MySQL [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)
MySQL [(none)]> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MySQL [(none)]> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]>
(这个动态设置的方式会在服务重启后失效,所以我们还必须在my.cnf中设置,加上log_bin_trust_function_creators=1,这样就会永久生效)
成功解决~