Debian 12 (bookworm) - MariaDB 10.11 サーバ構築（ソースビルド）！
Updated:
Debian GNU/Linux 12 (bookworm) 上に DB サーバ MariaDB（10.11系）、全文検索エンジン Mroonga を構築する方法についての記録です。
以前古いバージョンでの作業時に残していた記録を参考に作業を行い、今回更新した作業記録を貼付する形式の内容となっています。
（当然ながら、興味がなければスルーしてください）
0. 前提条件
- Debian GNU/Linux 12 (bookworm; 64bit) での作業を想定。
- 接続元のマシンも Debian GNU/Linux 12 (bookworm; 64bit) を想定。
- インストールする MariaDB は、当記事執筆時点、 10.11 系で最新の 10.11.5 とする。
- 全文検索エンジン Mroonga プラグインもインストールする。
- ここで紹介する MariaDB + Mroonga のインストール方法は、 Mroonga 公式サイトで案内されている Mroonga 込の MariaDB をインストールする方法や、ソールをビルドしているインストールする方法ではない）
- MariaDB とは言っても中身は MySQL が元になっているので、各所で
mysqlのキーワードが出現する。 - データディレクトリは
/var/lib/mysqlディレクトリ配下とする。 - （今回は）一般ユーザでの作業を想定。
1. リポジトリキーのインポート
$ sudo apt -y install apt-transport-https curl
$ sudo mkdir -p /etc/apt/keyrings
$ sudo curl -o /etc/apt/keyrings/mariadb-keyring.pgp 'https://mariadb.org/mariadb_release_signing_key.pgp'
2. リポジトリリストファイルの作成
File: /etc/apt/sources.list.d/mariadb.sources
# MariaDB 10.11 repository list - created 2023-10-27 02:05 UTC
# https://mariadb.org/download/
X-Repolib-Name: MariaDB
Types: deb
# deb.mariadb.org is a dynamic mirror if your preferred mirror goes offline. See https://mariadb.org/mirrorbits/ for details.
# URIs: https://deb.mariadb.org/10.11/debian
URIs: https://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/repo/10.11/debian
Suites: bookworm
Components: main
Signed-By: /etc/apt/keyrings/mariadb-keyring.pgp
3. MariaDB サーバのインストール
$ sudo apt -y update
$ sudo apt -y install mariadb-server
4. 全文検索エンジン Mroonga プラグインのインストール
$ sudo apt -y install mariadb-plugin-mroonga
5. MariaDB の初期設定
$ 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): # <= root ユーザの既存パスワード: 空エンター
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] # <= Unix ソケット認証設定: Y 応答
Enabled successfully!
Reloading privilege tables..
... Success!
You already have your root account protected, so you can safely answer 'n'.
Change the root password? [Y/n] # <= root パスワード変更: Y 応答
New password: # <= root パスワード
Re-enter new password: # <= root パスワード確認入力
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] n # <= root のリモートログイン: リモート接続したいので n 応答（Y でもよい）
... skipping.
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!
6. 動作確認
インストール直後は、サーバは起動している。
ログインしてみる。
$ mariadb -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.11.5-MariaDB-1:10.11.5+maria~deb12 mariadb.org binary distribution
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> show engines;
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
| CSV | YES | Stores tables as CSV files | NO | NO | NO |
| MRG_MyISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| Aria | YES | Crash-safe tables with MyISAM heritage. Used for internal temporary tables and privilege tables | NO | NO | NO |
| MyISAM | YES | Non-transactional engine with good performance and small data footprint | NO | NO | NO |
| SEQUENCE | YES | Generated tables filled with sequential values | YES | NO | YES |
| Mroonga | YES | CJK-ready fulltext search, column store | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, foreign keys and encryption for tables | YES | YES | YES |
+--------------------+---------+-------------------------------------------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.000 sec)
MariaDB [(none)]> exit
Bye
- 全文検索エンジン Mroonga もインストールされている。
- 上記の
mariadb -u root -pはmysql -u root -pでもよい。
もし、全文検索エンジン Mroonga が前述の apt でのインストール方法でインストールされていなければ、 MariaDB コンソールで以下を実行してみる。
（MariaDB をインストールする過程で、なかなかうまくいかず色々とイジっているうちに、 apt インストールできない状態になるかもしれない（当方の経験談））
INSTALL SONAME 'ha_mroonga';
7. 起動・再起動・ステータス確認・停止のテスト
$ sudo systemctl start mariadb
$ sudo systemctl restart mariadb
$ sudo systemctl stop mariadb
$ systemctl status mariadb
$ systemctl is-enabled mariadb
mariadbはmysqldでもよい。
8. 設定編集
必要に応じて、各種設定ファイルを編集する。（以下は、一例）
/etc/mysql/my.cnf が /etc/mysql/conf.d や /etc/mysql/mariadb.conf.d ディレクトリ配下の cnf ファイルを読み込むようになっている。（以前のように /etc/my.cnf ファイルにまとめて記述する方法にしてもよいだろう）
（[mysqld] の innodb_data_file_path や innodb_log_file_size を変更する際は、細心の注意を）
File: /etc/mysql/mariadb.conf.d/50-client.cnf
#
# This group is read by the client library
# Use it for options that affect all clients, but not the server
#
[client]
# Default is Latin1, if you need UTF-8 set this (also in server section)
port = 3306
socket = /var/run/mysqld/mysqld.sock
default-character-set = utf8mb4
# Example of client certificate usage
#ssl-cert=/etc/mysql/client-cert.pem
#ssl-key=/etc/mysql/client-key.pem
#
# Allow only TLS encrypted connections
#ssl-verify-server-cert=on
# This group is *never* read by mysql client library, though this
# /etc/mysql/mariadb.cnf.d/client.cnf file is not read by Oracle MySQL
# client anyway.
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]
File: /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf
#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#
[mysql]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8mb4
no-auto-rehash
show-warnings
prompt=\u@\h:\d\_\R:\m:\\s>\_
pager="less -n -i -F -X -E"
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
[mysqlimport]
[mysqlshow]
[mysqlslap]
File: /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf
# NOTE: This file is read only by the traditional SysV init script, not systemd.
# MARIADB SYSTEMD DOES _NOT_ UTILIZE MYSQLD_SAFE NOR READ THIS FILE.
#
# For similar behaviour, systemd users should create the following file:
# /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
#
# To achieve the same result as the default 50-mysqld_safe.cnf, please create
# /etc/systemd/system/mariadb.service.d/migrated-from-my.cnf-settings.conf
# with the following contents:
#
# [Service]
# User=mysql
# StandardOutput=syslog
# StandardError=syslog
# SyslogFacility=daemon
# SyslogLevel=err
# SyslogIdentifier=mysqld
#
# For more information, please read https://mariadb.com/kb/en/mariadb/systemd/
#
[mysqld_safe]
# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with ticks/quotes
# especially if they contain "#" chars...
socket = /var/run/mysqld/mysqld.sock
nice = 0
skip_log_error
syslog
File: /etc/mysql/mariadb.conf.d/50-server.cnf
#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
# this is read by the standalone daemon and embedded servers
[server]
# this is only for the mysqld standalone daemon
[mysqld]
#
# Recovery
#
#innodb_force_recovery = 11 #クラッシュリカバリ時のみ有効化
#
# * Basic Settings
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
lc_messages = en_US # Default: en_US
skip-external-locking
performance_schema = OFF # Default: ON
sql_mode = ''
default_storage_engine = InnoDB # Default: InnoDB
skip-character-set-client-handshake
# Broken reverse DNS slows down connections considerably and name resolve is
# safe to skip if there are no "host by domain name" access grants
#skip-name-resolve
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 0.0.0.0 # 127.0.0.1
#
# * Fulltext Index
#
ft_min_word_len = 2
innodb_ft_min_token_size = 2
innodb_ft_enable_stopword = OFF
#
# * Fine Tuning
#
key_buffer_size = 8M # Default: 8M (for MyISAM)
myisam_sort_buffer_size = 8M # Default: 8M (for MyISAM)
sort_buffer_size = 8M # Default: 2M (通常時:8M, ALTER TABLE 時:64M)
read_buffer_size = 8M # Default: 128K
read_rnd_buffer_size = 8M # Default: 256K
join_buffer_size = 8M # Default: 128K
max_allowed_packet = 128M # Default: 1M
net_buffer_length = 1M # Default: 16K
thread_stack = 288K # Default: 192K(32bit), 288K(64bit)
thread_cache_size = 40 # Default: 0 (max_connections の 1/3 程度?)
table_open_cache = 400 # Default: 400 (同時接続数 * テーブル数?)
table_definition_cache = 400 # Default: -1:autosized = 400 + (table_open_cache / 2
tmp_table_size = 16M # Default: System 依存 (for Memory, <= max_heap_table_size)
max_heap_table_size = 32M # Default: 16M (for Memory, >= tmp_table_size)
bulk_insert_buffer_size = 64M
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam_recover_options = BACKUP
max_connections = 100 # Default: 151
max_connect_errors = 100 # Default: 100
max_user_connections = 0 # Default: 0
#table_cache = 64
#thread_concurrency = 10
wait_timeout = 300 # Default: 28800 (通常時:300, データインポート時:3600程度)
interactive_timeout = 3600 # Default: 28800
connect_timeout = 30 # Default: 10
net_read_timeout = 3600 # Default: 30
net_write_timeout = 3600 # Default: 60
#
# * Query Cache Configuration
#
query_cache_limit = 8M # Default: 1M
query_cache_size = 32M # Default: 0 (通常時: 32M, データインポート時: 0)
#
# * Logging and Replication
#
log_output = FILE # Default: FILE
log_warnings = 1 # Default: 1
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# Recommend only changing this at runtime for short testing periods if needed
#general_log_file = /var/log/mysql/mysql.log # デバッグ時に有効化
#general_log = 1 # デバッグ時に有効化
# When running under systemd, error logging goes via stdout/stderr to journald
# and when running legacy init error logging goes to syslog due to
# /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf
# Enable this if you want to have error logging into a separate file
log_error = /var/log/mysql/error.log
# Enable the slow query log to see queries with especially long duration
#slow_query_log_file = /var/log/mysql/mariadb-slow.log # デバッグ時に有効>化
#slow_query_log = 1 # デバッグ時に有効>化
#long_query_time = 30 # 10 # デバッグ時に有効>化
#log_slow_verbosity = query_plan,explain
#log-queries-not-using-indexes
#min_examined_row_limit = 1000
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
# other settings you may need to change.
#server-id = 101 # 1 # レプリケーション時 (1 〜 2^32 - 1)
#log_bin = mysql-bin # /var/log/mysql/mysql-bin.log # レプリケーション、ポイントインタイムリカバリを利用したい場合のみ有効化
expire_logs_days = 10
max_binlog_size = 100M
binlog_format = MIXED
log_bin_trust_function_creators = 1 # Default: 0 (for TRIGGER)
#binlog_do_db = include_database_name
#binlog_ignore_db = exclude_database_name
#
# * SSL/TLS
#
# For documentation, please read
# https://mariadb.com/kb/en/securing-connections-for-client-and-server/
#ssl-ca = /etc/mysql/cacert.pem
#ssl-cert = /etc/mysql/server-cert.pem
#ssl-key = /etc/mysql/server-key.pem
#require-secure-transport = on
#
# * Character sets
#
# MySQL/MariaDB default is Latin1, but in Debian we rather default to the full
# utf8 4-byte character set. See also client.cnf
#
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
#
# * Event Scheduler
#
event_scheduler = 1 # Default: 0
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
# Most important is to give InnoDB 80 % of the system RAM for buffer use:
# https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size
#innodb_buffer_pool_size = 8G
# this is only for embedded server
[embedded]
# This group is only read by MariaDB servers, not by MySQL.
# If you use the same .cnf file for MySQL and MariaDB,
# you can put MariaDB-only options here
[mariadb]
# This group is only read by MariaDB-10.5 servers.
# If you use the same .cnf file for MariaDB of different versions,
# use this group for options that older servers don't understand
[mariadb-10.11]
innodb_data_file_path = ibdata1:1G #:autoextend
innodb_autoextend_increment = 64 # Default: 64
innodb_file_per_table = 1
innodb_file_format = Barracuda # Default: Antelope
innodb_large_prefix = 1 # Default: 0
innodb_buffer_pool_size = 768M # Default: 128M (innodb_log_files_in_group * innodb_log_file_size < innodb_buffer_pool_size)
innodb_buffer_pool_instances = 16 # Default: autosized(32bit), 8(64bit) (innodb_buffer_pool_size >= 1G の場合にのみ有効)
innodb_sort_buffer_size = 8M # Default: 1M (通常時:8M, ALTER TABLE 時:256M)
innodb_thread_concurrency = 4 # Default: 0 (Defalut 推奨? CPU数 * ディスク数 * 2 が最適?)
innodb_thread_sleep_delay = 10000 # Default: 10000 (単位:マイクロ秒)
innodb_commit_concurrency = 4 # Default: 0 (Default 推奨?)
innodb_write_io_threads = 8 # Default: 4 (1 - 64)
innodb_read_io_threads = 8 # Default: 4 (1 - 64)
innodb_log_buffer_size = 32M # Default: 8M (通常時:32M, データインポート時:256M程度)
innodb_log_group_home_dir = /var/lib/mysql
innodb_log_files_in_group = 2 # Default: 2 (変更注意!)
innodb_log_file_size = 256M # Default: 48M (変更注意!)
# <= innodb_log_files_in_group innodb_log_file_size < innodb_buffer_pool_size
# [変更方法](http://dev.mysq.com/doc/refman/5.6/ja/innodb-data-log-reconfiguration.html)
innodb_max_dirty_pages_pct = 90 # Default: 75(%)
# <= 小さい値：低速＆安定、大き 値：高速＆不安定(?)
innodb_io_capacity = 256 # Default: 200 (100 - 2^64-1)
innodb_io_capacity_max = 512 # Default: 200 (100 - 2^64-1)
innodb_lru_scan_depth = 2048 # Default: 1024 (100 - 2^32-1(32bit))
innodb_flush_method = O_DIRECT # Default: Not set
innodb_flush_log_at_trx_commit = 1 # Default: 1 (データインポート時: 2 or 0)
innodb_doublewrite = 1 # Default: 1 (データインポート時: 0)
ibdata1,ib_lofile0,ib_logfile1のサイズを変更した場合は、MariaDB サーバ停止後にibdata1,ib_lofile0,ib_logfile1を削除してから再度起動すること。
9. サービスの自動起動設定
インストール直後はマシン起動時に自動で MariaDB サーバが起動するようになっている。
自動起動しないようにしたければ以下のようにする。
$ sudo systemctl disable mariadb
Synchronizing state of mariadb.service with SysV service script with /lib/systemd/systemd-sysv-install.
Executing: /lib/systemd/systemd-sysv-install disable mariadb
Removed "/etc/systemd/system/multi-user.target.wants/mariadb.service".
$ systemctl is-enabled mariadb
disabled
mariadbはmysqldでもよい（mysqlはダメ）
10. ファイアウォール(ufw)の設定
リモートで MariaDB サーバにアクセスする場合（GUI ツールを使用する場合等）は、TCP ポート 3306 を開放する必要がある。
$ sudo ufw allow 3306/tcp
Rule added
$ sudo ufw status
:
3306/tcp ALLOW Anywhere
:
11. 参考サイト
以上。
Comments