有沒有 MariaDB 5.5.56 對應 MySQL CONNECTION

有沒有 MariaDB 對應 MySQL CONNECTION_CONTROL 插件

背景

寫這篇文章的目的是因為昨晚半夜突然被call起來，有一套系統的mysql數據庫啟動失敗了。嘗試了重啟服務器也不行。讓我協助排查一下問題出在哪。

分析過程

一開始拿到服務器IP地址，就去數據庫清單里面查找有沒對應的數據庫，發現沒有，緊接著同事解釋了原因，所以沒有記錄在清單。同事反饋說有100多套這種數據庫，為了方便后續的維護管理，我還是建議同事幫忙梳理一份清單。

首先通過ps命令查看mysql進程，確實沒有進程起來。

緊接著通過history命令查看歷史操作記錄，發現實際是一套 mariadb 5.5.56 版本的數據庫。并通過歷史命令找到了數據庫的日志文件，其他也可以通過/etc/my.cnf查看日志文件路徑。

通過日志文件查看到錯誤信息，數據庫在啟動的時候加載了connection-control插件，但是提示錯誤，找不到參數connection-control-failed-connections-threshold，接著數據庫就aborting了。

250607 02:51:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
250607  2:51:53 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 34467 ...
250607  2:51:54 InnoDB: The InnoDB memory heap is disabled
250607  2:51:54 InnoDB: Mutexes and rw_locks use GCC atomic builtins
250607  2:51:54 InnoDB: Compressed tables use zlib 1.2.7
250607  2:51:54 InnoDB: Using Linux native AIO
250607  2:51:54 InnoDB: Initializing buffer pool, size = 128.0M
250607  2:51:54 InnoDB: Completed initialization of buffer pool
250607  2:51:54 InnoDB: highest supported file format is Barracuda.
250607  2:51:54  InnoDB: Waiting for the background threads to start
250607  2:51:55 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 75279716956
250607  2:51:55 [Note] Plugin 'FEEDBACK' is disabled.
250607  2:51:55 [ERROR] Function 'server_audit' already exists
250607  2:51:55 [Warning] Couldn't load plugin named 'server_audit' with soname 'server_audit.so'.
250607  2:51:55 server_audit: MariaDB Audit Plugin version 1.4.1 STARTED.
250607  2:51:55 server_audit: logging started to the file /var/log/mariadb/server_audit.log.
250607  2:51:55 [ERROR] /usr/libexec/mysqld: unknown variable 'connection-control-failed-connections-threshold=5'
250607  2:51:55 [ERROR] Aborting  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<250607  2:51:55 server_audit: STOPPED
250607  2:51:55  InnoDB: Starting shutdown...
250607  2:51:59  InnoDB: Shutdown completed; log sequence number 75279716956
250607  2:51:59 [Note] /usr/libexec/mysqld: Shutdown complete250607 02:51:59 mysqld_safe mysqld from pid file /var/run/mariadb/mariadb.pid ended

查看/etc/my.cnf配置信息，確實是增加連接控制的參數。同事反饋是由于去年底等保安全加固做的變更。

]# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
bind-address=127.0.0.1
#####MariaDB Audit plugin######
##加載審計插件
plugin_load=server_audit=server_audit.so
##啟用審計日志
server_audit_logging=on
##設置審計日志目錄
##server_audit_file_path='/usr/local/mysql/log'
server_audit_file_path='/var/log/mariadb/'
##默認記錄所有SQL語句
##server_audit_events=CONNECT,TABLE,QUERY_DDL,QUERY_DCL,QUERY_DML_NO_SELECT
##日志轉輪數量，默認9
server_audit_file_rotations=128
##現在審計日志文件大小，默認1000000byte
server_audit_file_rotate_size=1G
server_audit_file_rotate_now=ON#connection-control-failed-connections-threshold=5  <<<<<<
#connection-control-min-connection-delay=108000  <<<<<<<
#max_password_errors=5[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

為了盡快恢復業務以及保存現場的環境，備份了my.cnf文件后，把連接控制的參數注釋了，重啟啟動數據庫正常。
數據庫啟動成功了，緊接著讓業務重啟服務，連接數據庫成功，業務恢復正常。

從啟動日志中，可以看到還是有幾個ERROR的出現，提示mysql.user, mysql.proc表應該是有損壞了。由于沒有影響到業務，所以就沒有繼續處理。

250607 03:01:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
250607  3:01:53 [Note] /usr/libexec/mysqld (mysqld 5.5.56-MariaDB) starting as process 54307 ...
250607  3:01:53 InnoDB: The InnoDB memory heap is disabled
250607  3:01:53 InnoDB: Mutexes and rw_locks use GCC atomic builtins
250607  3:01:53 InnoDB: Compressed tables use zlib 1.2.7
250607  3:01:53 InnoDB: Using Linux native AIO
250607  3:01:53 InnoDB: Initializing buffer pool, size = 128.0M
250607  3:01:53 InnoDB: Completed initialization of buffer pool
250607  3:01:53 InnoDB: highest supported file format is Barracuda.
250607  3:01:53  InnoDB: Waiting for the background threads to start
250607  3:01:54 Percona XtraDB (http://www.percona.com) 5.5.52-MariaDB-38.3 started; log sequence number 75279716956
250607  3:01:54 [Note] Plugin 'FEEDBACK' is disabled.
250607  3:01:54 [ERROR] Function 'server_audit' already exists
250607  3:01:54 [Warning] Couldn't load plugin named 'server_audit' with soname 'server_audit.so'.
250607  3:01:54 server_audit: MariaDB Audit Plugin version 1.4.1 STARTED.
250607  3:01:54 server_audit: logging started to the file /var/log/mariadb/server_audit.log.
250607  3:01:54 [Note] Server socket created on IP: '127.0.0.1'.
250607  3:01:54 [ERROR] mysqld: Table './mysql/user' is marked as crashed and should be repaired
250607  3:01:54 [Warning] Checking table:   './mysql/user'
250607  3:01:54 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly
250607  3:01:54 [Note] Event Scheduler: Loaded 0 events
250607  3:01:54 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.56-MariaDB'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MariaDB Server
250607 11:51:36 [ERROR] mysqld: Table './mysql/proc' is marked as crashed and should be repaired
250607 11:51:36 [Warning] Checking table:   './mysql/proc'

第二天繼續排查連接控制參數的問題，發現plugins目錄下沒有connection_control.so插件，應該是當時同事為了等保，直接在my.cnf文件里面添加了配置，而沒有考慮到需要先安裝connection_control.so插件。

在測試環境安裝了一套同樣版本的mariadb數據庫，安裝完同樣發現是沒有connection_control.so插件的。為了驗證這個問題，需要找到mariadb對應的插件。

找到以下一個案例:
https://mariadb.com/kb/en/is-there-a-mariadb-equivalent-to-mysql-connection_control-plugin/

在這里插入圖片描述
經過一番搜索，mysql在5.6.35版本開始加入了這個連接插件。

從其他網站上找到相關的案例 MySQL 與 mariadb的 connection_control插件并不通用。
在這里插入圖片描述

嘗試安裝MySQL的connection_control.so插件

嘗試從mysql5.6.35版本的lib目錄下的connection_control.so文件拷貝到到mariadb的lib/plugin目錄，但是在安裝的時候出現錯誤了，驗證不能通用。

[root@exam01 plugin]# mysql -uroot -hexam01 -proot
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.5.56-MariaDB MariaDB ServerCopyright (c) 2000, 2021, Oracle and/or its affiliates.Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
ERROR 1126 (HY000): Can't open shared library '/mariadb/lib/plugin/connection_control.so' (errno: 2, undefined symbol: my_getsystime)
mysql> 
mysql> INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
ERROR 1126 (HY000): Can't open shared library '/mariadb/lib/plugin/connection_control.so' (errno: 2, undefined symbol: my_getsystime)
mysql> 
mysql>

參考文檔：
chrome-extension://jkhojcaggkaojlhfddocjkkphfdkejeg/pdf/viewer.html?file=https%3A%2F%2Fdownloads.mysql.com%2Fdocs%2Fmysql-5.6-relnotes-en.a4.pdf

https://dev.mysql.com/doc/refman/5.7/en/connection-control-plugin-installation.html

https://docs.oracle.com/cd/E19957-01/mysql-refman-5.5/