MySQL – Master Slave Replication – log_bin_trust_function_creators

測試環境為 CentOS 7 / CentOS 8 x86_64 (虛擬機)

在建立 Stored Function 時卻得到以下的錯誤訊息.

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)  

奇怪另外一台相同的版本 MySQL (其實是 MariaDB) 可以建立自訂函數,這是為什麼?

主要原因是當你有使用 Binlog 的機制時,會被限制建立 儲存程序 ( Stored Routines ) 包含 Procedure 與 Function .

這一台的 MySQL (其實是 MariaDB) 使用了 Master Slave Replication 的技術.其 Master 的資料庫會透過 Binlog 的機制把所有會變動到資料的指令儲存到 Binlog ,其中包含 CREATE , ALTER TABLE , INSERT , UPDATE , DELETE … (不包含 SELECT 和 SHOW 這一類不影響資料的) .

Slave 透過 IO Thread 的方式 ( IO Thread 會請 Master 發送 Binlog 有更新的部分,並儲存至 Slave 的 Relay Log )
Slave 再透過 SQL Thread 讀取剛剛 IO Thread 儲存的 Relay Log 來執行有更新的部分(指令), 讓兩邊透過非同步的方式來同步資料.

更多關於主從式資料庫 (Master Slave Replication) 請參考 https://benjr.tw/102278

如需建立 儲存程序 ( Stored Routines ) 需開啟 log_bin_trust_function_creators 功能,建議先在 Slave 設定,再到 Master 設定.

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 263
Server version: 5.5.64-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

暫時 開啟 log_bin_trust_function_creators

以下是暫時 開啟 log_bin_trust_function_creators 功能的方式,服務重啟就會失效了.

MariaDB [(none)]> SHOW VARIABLES LIKE '%FUN%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+
1 row in set (0.00 sec)

MariaDB [(none)]> SET GLOBAL log_bin_trust_function_creators = 1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW VARIABLES LIKE '%FUN%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin_trust_function_creators | ON    |
+---------------------------------+-------+
1 row in set (0.00 sec)

永久有效 log_bin_trust_function_creators

CentOS 7 檔案位於 /etc/my.cnf , CentOS 8 檔案位於 /etc/my.cnf.d/mariadb-server.cnf 設定檔 [mysqld] 區塊加入以下相關設定.

[root@localhost ~]# vi /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
log_bin_trust_function_creators=1

重啟資料庫服務.

[root@localhost ~]# systemctl restart mariadb