原來 MySQL、MariaDB 都是以作者的小孩命名的，真有趣。
不過說到 Maria 就想到與黑++的這個影片呢

Cowire 的監測數據預設儲存為 json 格式與一般 log 檔案，但為了更好的分析，輸出還有支援更多的方式。這裡就先以資料庫為主，介紹如何按照官方的輸出至 MySQL 的文件來安裝設定。

注意因為我們使用的作業系統為 Debian 11 ，預設沒有 MySQL，所以用 MariaDB 來代替。因此在過程中，不同的地方有安裝 MariaDB 及支援其連接的修改細節(皆在本篇)。

環境

• Debian 11
• Cowrie 2.4.0
• MariaDB: mysql Ver 15.1 Distrib 10.5.15-MariaDB

內容會分為 2 篇，本篇會先記錄安裝 MariDB 至相關的修改。下一篇才是開始按照官方的文件建立 Cowrie 用的資料庫及測試。

先安裝資料庫 MySQL(MariaDB)

官方的安裝文件會假定系統已經有安裝 MySQL，這裡我們必須先手動安裝 MySQL。

sudo apt-get install mysql-server python-mysqldb
debian@debian:~$ sudo apt-get install mysql-server
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
Package mysql-server is not available, but is referred to by another package.
This may mean that the package is missing, has been obsoleted, or
is only available from another source

E: Package 'mysql-server' has no installation candidate

結果發現 MySQL 已經不在預設的 atp 安裝項目了。因此轉向安裝 MariaDB

debian@debian:~$ sudo apt install mariadb-server
Reading package lists... Done
Building dependency tree... Done
Reading state information... Done
The following additional packages will be installed:
  galera-4 gawk libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mariadb-perl libdbi-perl
  libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-template-perl libmariadb3 libsigsegv2 libterm-readkey-perl
  mariadb-client-10.5 mariadb-client-core-10.5 mariadb-common mariadb-server-10.5 mariadb-server-core-10.5
  mysql-common rsync socat
Suggested packages:
  gawk-doc libmldbm-perl libnet-daemon-perl libsql-statement-perl libipc-sharedcache-perl mailx mariadb-test
  netcat-openbsd
The following NEW packages will be installed:
  galera-4 gawk libcgi-fast-perl libcgi-pm-perl libconfig-inifiles-perl libdbd-mariadb-perl libdbi-perl
  libfcgi-bin libfcgi-perl libfcgi0ldbl libhtml-template-perl libmariadb3 libsigsegv2 libterm-readkey-perl
  mariadb-client-10.5 mariadb-client-core-10.5 mariadb-common mariadb-server mariadb-server-10.5
  mariadb-server-core-10.5 mysql-common rsync socat
0 upgraded, 23 newly installed, 0 to remove and 0 not upgraded.
Need to get 17.0 MB of archives.
After this operation, 157 MB of additional disk space will be used.

再來需要設定資料庫的安全性。這裡最主要是仍設定 root 的密碼，以讓後續的測試簡單一點

sudo mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
 ... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!

Madiadb 需要額外修改的語法

雖然 MariaDB 號稱能相容 MySQL，如果就這樣開始照官方步驟安裝下去，會在 log 中發現錯誤訊息:

 output_mysql: MySQL Error: (1273, "1273 (HY000): Unknown collation: 'utf8mb4_0900_ai_ci'", 'HY000')

畢竟兩者還是不同的產品，MariaDB 預設的編碼與 MySQL 不同。這時候就要修改 cowrie 的 mysql 輸出 python 檔案。

nano /home/cowrie/cowrie/src/cowrie/output/mysql.py

找到 charset 的地方，並且在下一行新增指定的 collation 為 MariaDB 支援的 「utf8mb4_general_ci」

                charset="utf8mb4",
                collation="utf8mb4_general_ci",

至此先解決資料庫安裝的問題，並提早解決非安裝 MySQL 而後續可能會發生的問題。

參考資料

How to Add a MySQL Database to a Cowrie ssh Honeypot
How To Install MariaDB on Debian 11
Mysql - Unknown collation: 'utf8mb4_0900_ai_ci'", 'HY000'
mysql to mariadb: unknown collation utf8mb4_0900_ai_ci

延伸資料

MySQL 的前世今生