okmdb之外部存取

背景

• openKM基本上是以一個mysql的資料庫(okmdb)為核心在運作，
• 其動態資料內容則是另存在/var/lib/mysql/ibdata1及$TOMCAT_HOME/repository下，
• 因此只要service tomcat啟動，任何時間讀取其資料庫，只有讀取的權限，如果要寫入必須在openKM系統內寫入。
• 能夠存取mysql資料庫的方式有很多，直接以mysql程式讀取，其指令如網友介紹¹，或其他第三方軟體。

主程式

• 以下介紹python2的[MYSQLdb]²模組之使用。
• 範例中使用localhost，python程式與mysqld伺服器同一機器的情況。如有不同，請參考資料庫伺服器之對外服務。
• 程式如下：

程式碼

kuang\@sino4 \~/MyPrograms/openkm
$ cat rd_okmdb.py
import **MySQLdb**
from pandas import *
db = MySQLdb.connect(host="localhost", user="kuang",
    passwd="****", db="okmdb",
    charset='utf8',**port=8080,unix_socket="/var/lib/mysql/mysql.sock"**
    )
cursor = db.**cursor**()
cursor.execute("SELECT * from OKM_ACTIVITY")
data = cursor.fetchall()
cursor.execute("describe OKM_ACTIVITY")
cols = cursor.**fetchall**()
cols=[cols[i][0] for i in xrange(len(cols))]
dd={}
for i in xrange(len(cols)):
  dd.update({cols[i]:[data[j][i] for j in xrange(len(data))]})
df=DataFrame(dd)

pivot_table(df.loc[df.ACT_ACTION=='LOGIN'],
    index='ACT_USER',
    values='ACT_ACTION',aggfunc='count').reset_index().sort_values('ACT_ACTION',ascending=False).reset_index(drop=True)
pivot_table(df.loc[df.ACT_ACTION.map(lambda x:'DOCUMENT' in x)],
    index='ACT_PATH',
    values='dum',
    aggfunc='count').reset_index().sort_values('dum',ascending=False).reset_index(drop=True).head(10)

程式碼下載與說明。

MySQLdb的安裝³

python2

• 如果pip不能成功，可以嘗試conda install。
• MySQLdb模組的使用方式²。
• 注意：MySQLdb還是python2版本，目前還沒有python3的版本。

python3

在 Python 3.9 及更高版本中，MySQLdb 不再被广泛使用，因为它不支持 Python 3.x。相反，建议使用 mysqlclient 或 PyMySQL，它们是用于 Python 3 的 MySQL 客户端库。

要安装 mysqlclient，您可以运行以下命令：

pip install mysqlclient

要安装 PyMySQL，您可以运行以下命令：

pip install PyMySQL

或以conda安裝conda install mysqlclient。一樣使用MySQLdb的名稱。

okmdb之連結應用

db之連結

• 如果沒有特別指定，port會選3306，而socket會設在/temp目錄下。
• 由於okmdb會有自己的設定方式，而不是內設位置，因此必須要按照okmdb的內容來連結。

cursor

• 類似DOS的prompt(c:/>)概念，此處稱之為cursor(mysql>)。
• 括弧內的命令即為一般mysql指令。

okmdb所有表格的項目

• 在okmdb目錄下(連原創之使用者也無法讀取，須sudo ls /var/lib/mysql/okmdb)，由某個表中讀取內容，
• 須先在mysql內SELECT，再將cursor的內容fetchall()出來。存成pandas dataframe形式。

程式之自動執行

crontab

• 每小時執行讀檔、統計、更新

# read the okmdb content, analysis and report, which is read by pyforms
0  *  *  *  * openkm /home/openkm/tomcat/scripts/rd_okmdb/rd_okmdb.bsh>& /home/openkm/tomcat/scripts/rd_okmdb/rd_okmdb.rst

rd_okmdb.bsh腳本

• 只在週一~週五之上班時間執行(避免週末、下班後無變化)
• 每小時執行：rd_okmdb4.py
• 每日17時執行
  • 每日分析：$pth ./daily_ana.py
  • 登入總數變化情形：$pth ./tot_log.py
  • 有效登入(登入並進行存取、閱讀)：$pth ./eff_log.py
  • 按照層級區分：$pth ./eff_lev.py

$ sudo cat /home/openkm/tomcat/scripts/rd_okmdb/rd_okmdb.bsh
pth=/cluster/miniconda/envs/py37/bin/python
DOW=$(date +%u)
H=$(date +%H)
if [ $DOW -le 5 ];then
  if [ $H -ge 8 ] && [ $H -le 17 ];then
    cd /home/openkm/tomcat/scripts/rd_okmdb
    if [ -e rd_okmdb.txt ]; then rm -f rd_okmdb.txt;fi
    LC_ALL="zh_TW.UTF-8" $pth ./rd_okmdb4.py
    if [ $H -eq 17 ];then
      ymd=`date  --rfc-3339='date'`
      cp rd_okmdb.txt rd_okmdb.txt_$ymd
      cp /home/openkm/tomcat/logs/du.csv du.csv_$ymd
#     plot the eff_loging figs
      $pth ./daily_ana.py
      $pth ./tot_log.py
      $pth ./eff_log.py
      $pth ./eff_lev.py
    fi
  fi
fi

• 注意
  1. 因crontab執行時沒有環境設定，因此無法辨識字形，需另指定$LC_ALL。
  2. 程式會寫出圖檔

執行pivot_table之結果

• 範例中將印出登入最多之使用者，以及最熱門檔案。
• 登入最多之使用者、登入次數

• 最熱門檔案

資料庫伺服器之對外服務

mysql Ver 5.6.45

如果在您的 MySQL 設定檔(/etc/my.cnf)中找不到bind-address的設定項，這表示 MySQL 正在監聽所有網路接口，而不僅僅是本機。 在某些 MySQL 的預設設定中，如果未明確指定bind-address，它會被設定為預設值0.0.0.0，這表示 MySQL 將接受來自任何 IP 位址的連線。

在這種情況下，您無需進行任何額外的配置以允許遠端連接，因為 MySQL 已經配置為允許遠端連接。

請確保 MySQL 伺服器正在運行，並嘗試使用遠端 MySQL 用戶端連接到伺服器。 如果連線失敗，請檢查防火牆規則以確保連接埠3306（MySQL 預設連接埠）未被阻止，並確保已為遠端使用者指派適當的權限。 如果有其他問題，可以提供更多詳細資訊以獲得更多協助。

若要確保連接埠3306（MySQL 預設連接埠）未被阻止，您需要在防火牆上開啟此連接埠。 以下是在不同 Linux 發行版上如何設定防火牆規則的範例。

使用 iptables（CentOS 7 或早期版本）

如果您使用的是 CentOS 7 或早期版本，可以使用 iptables 來管理防火牆規則。 以下是如何開放連接埠3306的範例：

# 開放埠3306
sudo iptables -A INPUT -p tcp --dport 3306 -j ACCEPT

# 保存規則
sudo service iptables save

# 重新啟動防火牆
sudo service iptables restart

使用 firewalld（CentOS 7 或更新版本）

如果您使用的是 CentOS 7 或更新版本，通常會使用 firewalld 來管理防火牆規則。 以下是如何開放連接埠3306的範例：

# 開放埠3306
sudo firewall-cmd --zone=public --add-port=3306/tcp --permanent

# 重新載入防火牆規則
sudo firewall-cmd --reload

使用 ufw（Ubuntu）

如果您使用的是 Ubuntu，可以使用 ufw（Uncomplicated Firewall）來管理防火牆規則。 以下是如何開放連接埠3306的範例：

# 開放埠3306
sudo ufw allow 3306/tcp

# 啟用防火牆
sudo ufw enable

無論您使用哪種防火牆工具，請確保已經允許存取連接埠3306。 如果已經設定了正確的防火牆規則，並且 MySQL 已配置為接受遠端連接，那麼應該可以從遠端電腦連接到 MySQL 伺服器了。 如果仍遇到連線問題，請檢查網路設定以及 MySQL 使用者權限。

較新版的mysql

要修改 MySQL 配置以允許遠端連接，您需要按照以下步驟進行操作：

1. 編輯 MySQL 設定檔：開啟 MySQL 設定檔以進行編輯。 在大多數 Linux 系統上，MySQL 設定檔通常位於/etc/mysql/mysql.conf.d/mysqld.cnf或/etc/my.cnf。 您可以使用文字編輯器（如nano或vim）開啟此檔案：

    sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
   

2. 找到綁定位址（Bind Address）：在設定檔中尋找名為bind-address的參數。 預設情況下，它通常設定為127.0.0.1，這表示 MySQL 僅監聽本機。

3. 更改綁定位址：將bind-address的值更改為0.0.0.0，這將允許 MySQL 監聽所有網路接口，從而允許遠端連接。 修改後的行應如下所示：

    bind-address = 0.0.0.0
   

   或者，如果您希望 MySQL 僅監聽特定的網路接口，可以將bind-address設定為特定介面的 IP 位址。

4. 儲存和關閉文件：儲存文件並關閉文字編輯器。

5. 重新啟動 MySQL 服務：重新啟動 MySQL 以使變更生效：

    sudo systemctl restart mysql
   

6. 指派遠端使用者權限：預設情況下，MySQL 可能不允許遠端使用者使用資料庫。 您需要為遠端使用者指派適當的權限。 在 MySQL 中，遠端使用者是根據使用者名稱和來源 IP 位址進行驗證的。 以下是一個範例，示範如何建立一個允許遠端存取的使用者：
   • 在mysqld server運作的情況下，以root登入mysql：mysql -u root -p

    CREATE USER 'remoteuser'@'%' IDENTIFIED BY 'password';
    GRANT ALL PRIVILEGES ON *.* TO 'remoteuser'@'%' WITH GRANT OPTION;
   

   這將建立一個名為'remoteuser'的用戶，允許從任何 IP 位址連接，並賦予該用戶所有資料庫的完全存取權。 請務必使用強密碼來保護您的 MySQL 使用者。

7. 刷新權限：在進行更改後，刷新 MySQL 的權限以確保新使用者和權限生效：

    FLUSH PRIVILEGES;