【Rocky Linux 9】15.MariaDB設定

MariaDB設定

データベースサーバーとして、MariaDBをインストールし、設定する

MariaDBインストール

dnf install mariadb-server

MariaDB設定

cp -a /etc/my.cnf.d/mariadb-server.cnf /etc/my.cnf.d/mariadb-server.cnf.org

/etc/my.cnf.d/mariadb-server.cnf

#
# These groups are read by MariaDB server.
# Use it for options that only the server (but not clients) should see
#
# See the examples of server my.cnf files in /usr/share/mysql/
#

# this is read by the standalone daemon and embedded servers
[server]

# this is only for the mysqld standalone daemon
# 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 mysqld/mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid-file = /run/mariadb/mariadb.pid

# 文字コード
character_set_server = utf8mb4
collation_server = utf8mb4_bin

# エラーログ
log_error = /var/log/mariadb/error.log
log_output = FILE
log_warnings = 1
long_query_time = 3
slow_query_log = 1
slow_query_log_file = /var/log/mariadb/slow.log
#log_queries_not_using_indexes = ON

# バイナリログ
binlog_format = mixed
expire_logs_days = 7
log_bin = mariadb-bin
log_bin_index = mariadb-bin
log_bin_trust_function_creators = 1

# 一般ログ
#general_log = 1
#general_log_file = /var/log/mariadb/general.log

# クエリキャッシュ（マルチコアでは無効にする）
query_cache_limit = 0M
query_cache_size = 0M
query_cache_type = 0

# InnoDB関連
# 実メモリの25%～80%程度
# アプリケーションサーバーと同一であれば少なめにする
# データベースのみ動作させるなら多めにする
innodb_buffer_pool_size = 256M

# innodb_buffer_pool_sizeの25%程度
innodb_log_file_size = 64M

# innodb_log_file_sizeの25%程度
innodb_log_buffer_size = 16M

# ログバッファの書き込み制限
# 0：ログファイルへの書き込み：毎秒　ディスクフラッシュ：毎秒
# 1：ログファイルへの書き込み：COMMIT時　ディスクフラッシュ：COMMIT時
# 2：ログファイルへの書き込み：COMMIT時　ディスクフラッシュ：毎秒
innodb_flush_log_at_trx_commit = 1

# InnoDBフラッシュメソッド
innodb_flush_method = O_DIRECT

# SSD向け
innodb_flush_neighbors = 0

# バッファプールに存在可能なダーティページの割合
innodb_max_dirty_pages_pct = 90

# バックグラウンド作業時に使用するI/Oサイズ
innodb_io_capacity = 1000

# innodb_io_capacityの最大サイズ
innodb_io_capacity_max = 4000

# I/Oスレッド数（デフォルト：4）
# CPUのコア数に合わせる
innodb_read_io_threads = 8
innodb_write_io_threads = 8

# InnoDBインデックスが作成されたときのデータをソートするためのバッファ
innodb_sort_buffer_size = 8M

# MEMORYテーブル・一時テーブル
# MEMORYテーブルの最大サイズ
max_heap_table_size = 32M

# 一時テーブルのサイズ
# max_heap_table_sizeに合わせる
tmp_table_size = 32M

# 接続関連
# 最大接続数
max_connections = 512

# max_connectionsに合わせる
thread_cache_size = 512

# max_connectionsの8～16倍程度
table_open_cache = 4096

# スレッドプール
thread_handling = pool-of-threads

# スレッドプール内のスレッドグループ数
# CPU数に合わせる
thread_pool_size = 2

# バッファ関連
join_buffer_size = 4M
key_buffer_size = 4M
myisam_sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 4M
sort_buffer_size = 4M

# その他
# DNS逆引き機能を無効化
skip_name_resolve = 1

# クエリの最大長
max_allowed_packet = 16M

# Unix Socket Pluginを使用しない
#unix_socket = 0

# リモートアクセスを無効化
#skip_networking = 1
#skip_bind_address = 1

#
# * Galera-related settings
#
[galera]
# Mandatory settings
#wsrep_on=ON
#wsrep_provider=
#wsrep_cluster_address=
#binlog_format=row
#default_storage_engine=InnoDB
#innodb_autoinc_lock_mode=2
#
# Allow server to accept connections on all interfaces.
#
#bind-address=0.0.0.0
#
# Optional setting
#wsrep_slave_threads=1
#innodb_flush_log_at_trx_commit=0

# 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.5]

/etc/my.cnf.d/client.cnf

[client]
+ port = 3306
+ socket = /var/lib/mysql/mysql.sock
+ default_character_set = utf8mb4

[client-mariadb]
+ port = 3306
+ socket = /var/lib/mysql/mysql.sock
+ default_character_set = utf8mb4

/etc/my.cnf.d/mysql-clients.cnf

[mysql]
+ default_character_set = utf8mb4
+ auto_rehash = 1
+ prompt = '\u@\p[\d]> '

[mysqldump]
+ default_character_set = utf8mb4
+ opt
+ flush_logs
+ single_transaction
+ max_allowed_packet = 32M

MariaDB起動・自動起動

systemctl enable --now mariadb

MariaDB初期設定

途中で「Unix Socket認証」をオフにしているが、オフにならない

mysql_secure_installation実行

Enter current password for root (enter for none):
Switch to unix_socket authentication [Y/n]n
Change the root password? [Y/n]Y
New password:
Re-enter new password:
Remove anonymous users? [Y/n]Y
Disallow root login remotely? [Y/n]Y
Remove test database and access to it? [Y/n]Y
Reload privilege tables now? [Y/n]Y

エラー対策

'proxies_priv' entry '@% root@[...]' ignored in --skip-name-resolve mode.というログが出るのを防ぐ

mysql -u root

[e-mail][(none)]> use mysql;
[e-mail][mysql]> delete from proxies_priv where host != "localhost";
[e-mail][mysql]> flush privileges;

Unix Socket認証の解除

/etc/my.cnf.d/mariadb-server.cnf

# Unix Socket Pluginsを使用しない
- #unix_socket = 0
+ unix_socket = 0

リモートアクセスを無効化

別サーバーからネットワーク越しに直接データベースに接続させないようにする
リモートアクセスを有効化する場合は、コメントアウトする

/etc/my.cnf.d/mariadb-server.cnf

# リモートアクセスを無効化
- #skip_networking = 1
- #skip_bind_address = 1
+ skip_networking = 1
+ skip_bind_address = 1

MariaDB再起動

systemctl restart mariadb

ログローテートスクリプト修正

/etc/logrotate.d/mariadb

# This logname can be set in /etc/my.cnf
# by setting the variable "log-error"
# in the [mysqld] section as follows:
#
# [mysqld]
# log-error=/var/log/mariadb/mariadb.log

- /var/log/mariadb/mariadb.log {
+ /var/log/mariadb/*.log {
        create 600 mysql mysql
        su mysql mysql
        notifempty
        daily
        rotate 3
        missingok
        compress
        delaycompress
        sharedscripts
    postrotate
        # just if mariadbd is really running
        if [ -e /run/mariadb/mariadb.pid ]
        then
           kill -1 $(</run/mariadb/mariadb.pid)
        fi
    endscript
}

参考サイト

https://hp.ofuton.org/61/
https://github.com/MariaDB/mariadb-docker/issues/164