2025年12月7日 / 最終更新日時 : 2025年12月21日 admin サーバー・ミドルウェア

Debian 12 (Bookworm)を使いやすく・セキュアに構築する の続き（Ansibleで構成管理を止めた為）で、MariaDBをセキュアにインストールして、デフォルト設定を確認しながら高速に動くようにチューニングと、運用しやすいように自動バックアップや落ちたら自動起動するようにcronを作成します。

MariaDBをインストール

# apt update && apt install -y mariadb-server
# systemctl enable --now mariadb

# 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: 【新しいrootのパスワード】
Re-enter new password: 【新しいrootのパスワード】
Remove anonymous users? [Y/n] y
Disallow root login remotely? [Y/n] y
Removye test database and access to it? [Y/n] y
Reload privilege tables now? [Y/n] y

動作確認

# mysql
> exit

# su - admin
$ mysql
ERROR 1698 (28000): Access denied for user 'admin'@'localhost'

$ mysql -u root
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

$ mysql -u root -p
: 【rootのパスワード】
> exit
$ exit

デフォルトの設定確認

# cat /etc/mysql/my.cnf

[client-server]
socket = /run/mysqld/mysqld.sock
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

# ll /etc/mysql/conf.d
-rw-r--r-- 1 root root  8 Oct 20  2020 mysql.cnf
-rw-r--r-- 1 root root 55 Oct 20  2020 mysqldump.cnf

# cat /etc/mysql/conf.d/mysql.cnf

[mysql]

# cat /etc/mysql/conf.d/mysqldump.cnf

[mysqldump]
quick
quote-names
max_allowed_packet	= 16M

# ll /etc/mysql/mariadb.conf.d
-rw-r--r-- 1 root root  575 May 27  2025 50-client.cnf
-rw-r--r-- 1 root root  231 Aug 29 08:33 50-mysql-clients.cnf
-rw-r--r-- 1 root root  927 Aug 26 15:38 50-mysqld_safe.cnf
-rw-r--r-- 1 root root 3769 Aug 29 08:33 50-server.cnf
-rw-r--r-- 1 root root  570 Aug 29 08:33 60-galera.cnf
-rw-r--r-- 1 root root   76 Jul 29 01:12 provider_bzip2.cnf
-rw-r--r-- 1 root root   72 Jul 29 01:12 provider_lz4.cnf
-rw-r--r-- 1 root root   74 Jul 29 01:12 provider_lzma.cnf
-rw-r--r-- 1 root root   72 Jul 29 01:12 provider_lzo.cnf
-rw-r--r-- 1 root root   78 Jul 29 01:12 provider_snappy.cnf

# cat /etc/mysql/mariadb.conf.d/50-client.cnf

[client]
[client-mariadb]

# cat /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf

[mysql]
[mysql_upgrade]
[mysqladmin]
[mysqlbinlog]
[mysqlcheck]
[mysqldump]
[mysqlimport]
[mysqlshow]
[mysqlslap]

# cat /etc/mysql/mariadb.conf.d/50-mysqld_safe.cnf

[mysqld_safe]
nice = 0
skip_log_error
syslog

# cat /etc/mysql/mariadb.conf.d/50-server.cnf

[server]
[mysqld]
pid-file                = /run/mysqld/mysqld.pid
basedir                 = /usr
bind-address            = 127.0.0.1
expire_logs_days        = 10
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci
[embedded]
[mariadb]
[mariadb-10.11]

# cat /etc/mysql/mariadb.conf.d/60-galera.cnf

[galera]

# cat /etc/mysql/mariadb.conf.d/provider_*

[server]
plugin_load_add=provider_bzip2
provider_bzip2=force_plus_permanent
[server]
plugin_load_add=provider_lz4
provider_lz4=force_plus_permanent
[server]
plugin_load_add=provider_lzma
provider_lzma=force_plus_permanent
[server]
plugin_load_add=provider_lzo
provider_lzo=force_plus_permanent
[server]
plugin_load_add=provider_snappy
provider_snappy=force_plus_permanent

設定チューニング

設定は後にincludeで、ファイル名も後のもので上書きされるので、下記ファイル名にしています。
メモリ2GB想定なので、異なる場合はコメント参照してください。

# vi /etc/mysql/mariadb.conf.d/zz-custom.cnf

### START ###
[mysqld]
skip-character-set-client-handshake
# character-set-server=utf8mb4
# collation-server=utf8mb4_general_ci
init-connect=SET NAMES utf8mb4
max_allowed_packet=32M			# 2GB:32M, 4GB:64M, before:16M <- 16M, before:1M
key_buffer_size=32M			# before:128M <- 128M, before:8M
thread_cache_size=16			# 2GB:16, 4GB:32 <- 151, before:0
innodb_buffer_pool_size=512M		# 2GB:512M, 4GB:1G, before:256M <- 128M, before:8M
innodb_log_file_size=128M		# 2GB:128M, 4GB:256M, before:64M <- 96M, before:5M
innodb_log_buffer_size=16M		# 2GB:16M, 4GB:32M, before:8M <- 16M, before:1M
innodb_flush_log_at_trx_commit=2	# <- 1
sort_buffer_size=2M			# 2GB:2M, 4GB:4M, before:4GB <- 2M
read_buffer_size=1M			# <- 128K
join_buffer_size=256K			# 2GB:256K, 4GB:512K <- 256K, before:128K
query_cache_size=16M			# 2GB:16M, 4GB:32M, before:32M <- 1M, before:0
query_cache_limit=2M			# 2GB:2M, 4GB:4M, before:4M <- 1M
tmp_table_size=32M			# 2GB:32M, 4GB:64M, before:64M <- 16M
max_heap_table_size=32M			# 2GB:32M, 4GB:64M, before:64M <- 16M
table_open_cache=512			# 2GB:512, 4GB:1024, before:1024 <- 2000, before:256

log-error=/var/log/mariadb/error.log
log_output=FILE
slow_query_log=ON			# <- OFF
slow_query_log_file=/var/log/mariadb/slow_query.log
long_query_time=1			# <- 10

[mysqld_safe]
log-error=/var/log/mariadb/error.log
### END ###

# mkdir /var/log/mariadb
# chmod 740 /var/log/mariadb
# chown mysql:adm /var/log/mariadb

# systemctl restart mariadb

# cd /var/log/mariadb
# chmod 640 *.log
# chown mysql:adm *.log

# ll
-rw-r----- 1 mysql adm 4288 Dec  7 15:25 error.log
-rw-r----- 1 mysql adm  334 Dec  7 15:25 slow_query.log
# cat error.log
# cat slow_query.log

logrotateの設定変更

# vi /etc/logrotate.d/mariadb

# This is the MariaDB configuration for the logrotate utility
#
# Note that on most Linux systems logs are written to journald, which has its
# own rotation scheme.
#
# Read https://mariadb.com/kb/en/error-log/ to learn more about logging and
# https://mariadb.com/kb/en/rotating-logs-on-unix-and-linux/ about rotating logs.

### START ###
# /var/lib/mysql/mysqld.log /var/lib/mysql/mariadb.log /var/log/mysql/*.log {
/var/log/mariadb/*.log {
  create 644 mysql adm
### END ###

  # Depends on a mysql@localhost unix_socket authenticated user with RELOAD privilege
  #su mysql mysql

  # If any of the files listed above is missing, skip them silently without
  # emitting any errors
  missingok

  # If file exists but is empty, don't rotate it
### START ###
#  notifempty
### END ###

  # Run monthly
### START ###
#  monthly
  daily
### END ###

  # Keep 6 months of logs
### START ###
#  rotate 6
  rotate 30
### END ###

  # If file is growing too big, rotate immediately
### START ###
#  maxsize 500M
### END ###

  # If file size is too small, don't rotate at all
### START ###
#  minsize 50M
### END ###

  # Compress logs, as they are text and compression will save a lot of disk space
  compress

  # Don't compress the log immediately to avoid errors about "file size changed while zipping"
  delaycompress

  # Don't run the postrotate script for each file configured in this file, but
  # run it only once if one or more files were rotated
  sharedscripts

  # After each rotation, run this custom script to flush the logs. Note that
  # this assumes that the mariadb-admin command has database access, which it
  # has thanks to the default use of Unix socket authentication for the 'mysql'
  # (or root on Debian) account used everywhere since MariaDB 10.4.
  postrotate
    if test -r /etc/mysql/debian.cnf
    then
      EXTRAPARAM='--defaults-file=/etc/mysql/debian.cnf'
    fi

    if test -x /usr/bin/mariadb-admin
    then
      /usr/bin/mariadb-admin $EXTRAPARAM --local flush-error-log \
        flush-engine-log flush-general-log flush-slow-log
    fi
  endscript
}

自動バックアップ

# cd /etc/cron.daily
# vi backup_mysql.sh

#!/bin/sh

BATCH_NAME='backup_mysql'
LOG_FILE="/var/log/$BATCH_NAME.log"
TARGET_PATH='/var/backup/mysqldump'
EXT_NAME='.sql'
TARGET_NAME="*_*${EXT_NAME}*"
RM_MTIME=7
ATTR_CODE=660
GROUP_NAME='adm'

error_msg=''
write_log() {
	echo -e "`date +"%Y/%m/%d %H:%M:%S"` ($$) [$1] $2" >> $LOG_FILE
	[ $1 = 'ERROR' ] && error_msg="$error_msg[$1] $2\n"
}

send_error_mail() {
	[ -z "$error_msg" ] && return
	echo -e "$error_msg" | mail -s "[WARNING]$BATCH_NAME report for `hostname`" -r crond warning
	write_log 'INFO' 'Send error mail'
}

delete_target_files() {
	files=`ionice -c 2 -n 7 nice -n 19 find $TARGET_PATH/ -name "$TARGET_NAME" -daystart -mtime +$RM_MTIME -type f`
	for file in $files; do
		fuser $file > /dev/null 2>> $LOG_FILE
		if [ $? -ne 1 ]; then
			write_log 'INFO' "Skip $file"
			continue
		fi

		ionice -c 2 -n 7 nice -n 19 rm -f $file > /dev/null 2>> $LOG_FILE
		if [ $? -ne 0 ]; then
			write_log 'ERROR' "Delete $file"
			continue
		fi
		write_log 'INFO' "Delete $file"
	done
}

dump_compress_files() {
	databases=`echo "SHOW DATABASES;" | mysql -N 2>> $LOG_FILE`
	if [ $? -ne 0 ]; then
		write_log 'ERROR' 'Connect'
		return
	fi
	write_log 'INFO' 'Connect'

	for database in $databases; do
		file="$TARGET_PATH/${database}_`date "+%Y%m%d"`$EXT_NAME"
		ionice -c 2 -n 7 nice -n 19 mysqldump -q --single-transaction $database > $file 2>> $LOG_FILE
		if [ $? -ne 0 ]; then
			write_log 'ERROR' "Dump $file"
			continue
		fi
		write_log 'INFO' "Dump $file"

		ionice -c 2 -n 7 nice -n 19 gzip -f $file > /dev/null 2>> $LOG_FILE
		if [ $? -ne 0 ]; then
			write_log 'ERROR' "Compress $file -> .gz"
			continue
		fi
		write_log 'INFO' "Compress $file -> .gz"

		chmod $ATTR_CODE $file.gz > /dev/null 2>> $LOG_FILE
		[ $? -ne 0 ] && write_log 'ERROR' "Chmod $file.gz"

		chgrp $GROUP_NAME $file.gz > /dev/null 2>> $LOG_FILE
		[ $? -ne 0 ] && write_log 'ERROR' "Chgrp $file.gz"
	done
}

write_log 'INFO' '=== START ==='
delete_target_files
dump_compress_files
send_error_mail
write_log 'INFO' '=== END ==='

# chmod 740 backup_mysql.sh
# chown root:adm backup_mysql.sh

# mkdir -p /var/backup/mysqldump
# chmod 750 /var/backup/{.,mysqldump}
# chown root:adm /var/backup/{.,mysqldump}

logrotate

# vi /etc/logrotate.d/backup_mysql

### START ###
/var/log/backup_mysql.log {
	missingok
}
### END ###

動作確認

# ./backup_mysql.sh

# ll /var/backup/mysqldump
# cat /var/log/backup_mysql.log

落ちたら自動起動

念の為に、落ちたら自動起動するようにしておきます。

# cd /usr/local/bin
# vi start_mariadb.sh

#!/bin/sh

TARGET_SERVICE='mariadb'
BATCH_NAME="start_$TARGET_SERVICE"
LOG_FILE="/var/log/$BATCH_NAME.log"

use_critical=0
error_msg=''
write_log () {
	echo -e "`date +"%Y/%m/%d %H:%M:%S"` ($$) [$1] $2" >> $LOG_FILE
	[ $1 = 'ERROR' -o $1 = 'CRITICAL' ] && error_msg="$error_msg[$1] $2\n"
	[ $1 = 'CRITICAL' ] && use_critical=1
}

send_error_mail () {
	[ -z "$error_msg" ] && return
	if [ $use_critical -eq 1 ]; then
		echo -e "$error_msg" | mail -s "[CRITICAL]$BATCH_NAME report for `hostname`" -r crond critical
		write_log 'INFO' 'Send error mail(CRITICAL)'
	else
		echo -e "$error_msg" | mail -s "[WARNING]$BATCH_NAME report for `hostname`" -r crond warning
		write_log 'INFO' 'Send error mail(WARNING)'
	fi
}

start_service() {
	systemctl status $TARGET_SERVICE > /dev/null
	if [ $? -eq 0 ]; then
		write_log 'INFO' 'Running'
		return
	fi
	write_log 'ERROR' 'Stopping'

	systemctl start $TARGET_SERVICE > /dev/null
	if [ $? -eq 0 ]; then
		write_log 'INFO' 'Successful start'
		return
	fi
	write_log 'CRITICAL' 'Failed to start'
}

write_log 'INFO' '=== START ==='
start_service
send_error_mail
write_log 'INFO' '=== END ==='

# chmod 740 start_mariadb.sh
# chown root:adm start_mariadb.sh

# vi /etc/cron.d/start_mariadb

### START ###
*/5 * * * * root /usr/local/bin/start_mariadb.sh
### END ###

logrotate

# vi /etc/logrotate.d/start_mariadb

### START ###
/var/log/start_mariadb.log {
	missingok
}
### END ###

動作確認

5分（倍数）を超えてから

# cat /var/log/start_mariadb.log

# systemctl stop mariadb

5分（倍数）を超えてから

# cat /var/log/start_mariadb.log
# systemctl status mariadb
カテゴリー
サーバー・ミドルウェア
タグ

コメントを残す

メールアドレスが公開されることはありません。 ※ が付いている欄は必須項目です

サーバー・ミドルウェア

前の記事

プロキシサーバ(Squid)でアクセス制限してセキュアにする
2025年11月9日