MySQL-8.0 と MariaDB-10.3 を共存させてみた
こんにちは Q です。
Q が検証に利用している AlmaLinux の中には MySQL-8.0 が動いています。
今日はここに MariaDB-10.9 を突っ込んでみます。
■検証環境
AlmaLinux release 8.6
MySQL Community Server 8.0.31 ※
MariaDB Server 10.9.3 【追加するヤツ】
※ MySQLは公式リポジトリが配布しているRPMパッケージをそのまま利用しています。
内容は少し古めですが、MariaDB のナレッジに掲載されている以下の記事を参考に進めます。
Installing MariaDB Alongside MySQL
https://mariadb.com/kb/en/installing-mariadb-alongside-mysql/
ディレクトリ構成はこんな感じを想定しています。
■MySQL
bin ディレクトリ ： /usr/bin/～
data ディレクトリ： /var/lib/mysql/～
■MariaDB
bin ディレクトリ ： /opt/mariadb/bin/～
data ディレクトリ： /opt/mariadb-data/～
では いきます。
● バイナリダウンロード
MariaDB からバイナリのアーカイブが提供されているので
山形大学のミラーサーバからダウンロードして展開します。
[ ~]# cd /opt
[opt]# wget https://ftp.yz.yamagata-u.ac.jp/pub/dbms/mariadb/mariadb-10.9.3/bintar-linux-systemd-x86_64/mariadb-10.9.3-linux-systemd-x86_64.tar.gz
[opt]# tar xzf mariadb-10.9.3-linux-systemd-x86_64.tar.gz
● シンボリックリンク作成
[ opt]# ln -s mariadb-10.9.3-linux-systemd-x86_64 mariadb
● データディレクトリ作成
[ opt]# mkdir mariadb-data
● ユーザー、グループ作成
[ opt]# groupadd --system mariadb
[ opt]# useradd -c "MariaDB Server" -d /opt/mariadb -g mariadb --system mariadb
[ opt]# chown -R mariadb:mariadb mariadb-10.9.3-linux-systemd-x86_64/
[ opt]# chown -R mariadb:mariadb mariadb-data/
ここでディレクトリの構成を確認しておきます。
[ opt]# ls -l /opt/
合計 339928
lrwxrwxrwx 1 root root 35 11月 1 20:32 mariadb -> mariadb-10.9.3-linux-systemd-x86_64
drwxrwxr-x 12 mariadb mariadb 4096 9月 16 02:39 mariadb-10.9.3-linux-systemd-x86_64
-rw-r--r-- 1 root root 348069693 9月 16 02:39 mariadb-10.9.3-linux-systemd-x86_64.tar.gz
drwxr-xr-x 6 mariadb mariadb 4096 11月 4 21:31 mariadb-data
[ opt]# ls -l /opt/mariadb/
合計 188
-rw-r--r-- 1 mariadb mariadb 17987 9月 14 04:14 COPYING
-rw-r--r-- 1 mariadb mariadb 2161 9月 14 04:14 CREDITS
-rw-r--r-- 1 mariadb mariadb 8794 9月 14 04:14 INSTALL-BINARY
-rw-r--r-- 1 mariadb mariadb 19520 9月 14 04:14 README-wsrep
-rw-r--r-- 1 mariadb mariadb 2697 9月 14 04:14 README.md
-rw-r--r-- 1 mariadb mariadb 86263 9月 14 04:14 THIRDPARTY
drwxr-xr-x 2 mariadb mariadb 4096 5月 26 23:23 bin
drwxrwxr-x 2 mariadb mariadb 4096 9月 14 05:06 docs
drwxrwxr-x 3 mariadb mariadb 4096 9月 14 05:06 include
drwxr-xr-x 5 mariadb mariadb 4096 5月 26 23:23 lib
drwxrwxr-x 5 mariadb mariadb 4096 9月 14 05:07 man
drwxrwxr-x 9 mariadb mariadb 4096 9月 14 05:07 mysql-test
drwxrwxr-x 2 mariadb mariadb 4096 9月 14 05:07 scripts
drwxrwxr-x 33 mariadb mariadb 4096 9月 14 05:07 share
drwxrwxr-x 4 mariadb mariadb 4096 9月 14 05:07 sql-bench
drwxrwxr-x 4 mariadb mariadb 4096 9月 14 05:07 support-files
● my.cnf作成
MariaDB-10.9 用の my.cnf を作成します。
/etc/my.cnf は MySQL-8.0 が利用しているので /opt/mariadb-data/～ に作成します。
[ opt]# vi mariadb-data/my.cnf
[ opt]# chown mariadb:mariadb mariadb-data/my.cnf
[ opt]# cat mariadb-data/my.cnf
=================================================================
[client]
default-character-set=utf8mb4
port=3307
socket=/opt/mariadb-data/mariadb.sock
[mysqld]
user=mariadb
character-set-server=utf8mb4
port=3307
socket=/opt/mariadb-data/mariadb.sock
datadir=/opt/mariadb-data
basedir=/opt/mariadb
[mysqldump]
quick
[mysqld_safe]
log-error=/var/log/mariadb.log
pid-file=/opt/mariadb-data/mariadb.pid
=================================================================
● データベース初期化
dataディレクトリを初期化してシステムテーブルを作成します。
[ opt]# cd mariadb
[mariadb]# scripts/mysql_install_db --defaults-file=/opt/mariadb-data/my.cnf
● 起動テスト
これで とりあえず起動できるはず。。。。。
[mariadb]# bin/mariadbd-safe --defaults-file=/opt/mariadb-data/my.cnf >/dev/null 2>&1 &
[1] 75355
[mariadb]# ps aux | grep mariadb
root 75355 0.0 0.0 11164 3276 pts/1 S 20:16 0:00 /bin/sh bin/mariadbd-safe --defaults-file=/opt/mariadb-data/my.cnf
mariadb 75465 0.6 1.3 1058496 222068 pts/1 Sl 20:16 0:00 /opt/mariadb/bin/mariadbd --defaults-file=/opt/mariadb-data/my.cnf --basedir=/opt/mariadb --datadir=/opt/mariadb-data --plugin-dir=/opt/mariadb/lib/plugin --user=mariadb --log-error=/var/log/mariadb.log --pid-file=/opt/mariadb-data/mariadb.pid --socket=/opt/mariadb-data/mariadb.sock --port=3307
● socket接続
無事に起動できたので socket接続してみます。
[mariadb]# bin/mariadb --socket=/opt/mariadb-data/mariadb.sock
./bin/mariadb: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
● 共有ライブラリ不足エラー
libncurses.so.5 が足りないようです。
[mariadb]# ldconfig -p | grep libncurses.so
libncurses.so.6 (libc6,x86-64) => /lib64/libncurses.so.6
慌てず騒がず ncurses-compat-libs をインストールします。
[mariadb]# yum install ncurses-compat-libs
[mariadb]# ldconfig -p | grep libncurses.so
libncurses.so.6 (libc6,x86-64) => /lib64/libncurses.so.6
libncurses.so.5 (libc6,x86-64) => /lib64/libncurses.so.5
● socket接続 再チャレンジ
mariadb]# bin/mariadb --socket=/opt/mariadb-data/mariadb.sock
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.9.3-MariaDB MariaDB Server
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)]> set password for 'root'@'localhost' = password('tokyo999!');
Query OK, 0 rows affected (0.002 sec)
接続できました！
このまま root@localhost のパスワード設定しちゃいます。
MariaDB [(none)]> set password for 'root'@'localhost' = password('xxxxxxxxxxxx');
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]> quit
Bye
● ポート接続
今度はパスワード認証でローカルポート3307に接続してみます。
[mariadb]# bin/mariadb --port 3307 -p
Enter password:
WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol.
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 10.9.3-MariaDB MariaDB Server
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)]>
イケてます。
status 確認します。
MariaDB [(none)]> status
--------------
/opt/mariadb/bin/mysql Ver 15.1 Distrib 10.9.3-MariaDB, for linux-systemd (x86_64) using readline 5.1
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.9.3-MariaDB MariaDB Server
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb3
Conn. characterset: utf8mb3
TCP port: 3307
Uptime: 21 min 42 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 17 Open tables: 10 Queries per second avg: 0.003
--------------
Client characterset: utf8mb3 。。。イケてませんね。
/opt/mariadb-data/my.cnf の中に default-character-set=utf8mb4 を指定したのですが。
[mariadb]# bin/mariadb --help | grep -A1 ^Default
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
どうやら /opt/mariadb-data/my.cnf を見てくれていないようです。
どうしましょ。
・・・・・
・・・・・
最初に作成した mariadb ユーザーに変身して .my.cnf を置いてみますか。
[mariadb]# sudo su - mariadb
[ ~]$ pwd
/opt/mariadb
[ ~]$ vi .my.cnf
[ ~]$ cat .my.cnf
=================================================================
[client]
user=root
default-character-set=utf8mb4
=================================================================
このまま mariadb ユーザーで ポート3307に接続してみます。
[ ~]$ bin/mariadb --port 3307 -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.9.3-MariaDB MariaDB Server
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)]> status
--------------
bin/mariadb Ver 15.1 Distrib 10.9.3-MariaDB, for linux-systemd (x86_64) using readline 5.1
Connection id: 11
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.9.3-MariaDB MariaDB Server
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3307
Uptime: 1 hour 12 min 24 sec
Threads: 1 Questions: 35 Slow queries: 0 Opens: 17 Open tables: 10 Queries per second avg: 0.008
--------------
MariaDB [(none)]> quit
Bye
[ ~]$ exit
文字コードも utf8mb4 になってます。
● 起動スクリプト
やっと最終段階まできました。
アーカイブの中に含まれている起動スクリプトも流用するのですが、
チェック処理が邪魔をして、なかなか言うこと聞いてくれません。
なので、邪魔なチェック処理は適当に削除しちゃいました。
もっと美しくなると思いますが参考までに貼っておきます。
[mariadb]# cp -p support-files/mysql.server /etc/init.d/mariadb
[mariadb]# vi /etc/init.d/mariadb
[mariadb]# cat /etc/init.d/mariadb
=================================================================
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB & Monty Program KB & Detron HB
# This file is public domain and comes with NO WARRANTY of any kind
# MariaDB daemon start/stop script.
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.
# Comments to support chkconfig on Red Hat Linux
# chkconfig: 2345 64 36
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mariadb
# Required-Start: $local_fs $network $remote_fs
# Should-Start: ypbind nscd ldap ntpd xntpd
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MariaDB
# Description: MariaDB is a very fast and reliable SQL database engine.
### END INIT INFO
# have to do one of the following things for this script to work:
#
# - Run this script from within the MariaDB installation directory
# - Create a /etc/my.cnf file with the following information:
# [mysqld]
# basedir=<path-to-mysql-installation-directory>
# - Add the above to any other configuration file (for example ~/.my.ini)
# and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
# below.
#
# If you want to affect other MariaDB variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MariaDB configuration files.
# If you change base dir, you must also change datadir. These may get
# overwritten by settings in the MariaDB configuration files.
basedir=/opt/mariadb
datadir=/opt/mariadb-data
service_startup_timeout=900
lockdir='/var/lock/subsys'
lock_file_path="$lockdir/mariadb"
mariadbd_pid_file_path=/opt/mariadb-data/mariadb.pid
bindir="$basedir/bin"
libexecdir="$basedir/bin"
datadir_set=
init_functions="/etc/init.d/functions"
if test -f $init_functions; then
. $init_functions
else
log_success_msg()
{
echo " SUCCESS! $@"
}
log_failure_msg()
{
echo " ERROR! $@"
}
fi
PATH="/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin"
export PATH
user='mariadb'
su_kill() {
if test "$USER" = "$user"; then
kill $* >/dev/null 2>&1
else
su - $user -s /bin/sh -c "kill $*" >/dev/null 2>&1
fi
}
extra_args=""
mode=$1
case "$mode" in
'start')
# Start daemon
# Safeguard (relative paths, core dumps..)
cd $basedir
echo $echo_n "Starting MariaDB"
if test -x $bindir/mysqld_safe
then
# Give extra arguments to mysqld with the my.cnf file. This script
# may be overwritten at next upgrade.
$bindir/mysqld_safe --defaults-file="$datadir/my.cnf" --datadir="$datadir" --pid-file="$mariadbd_pid_file_path" $other_args >/dev/null 2>&1 &
# Make lock for Red Hat / SuSE
if test -w "$lockdir"
then
touch "$lock_file_path"
fi
exit $return_value
else
log_failure_msg "Couldn't find MariaDB server ($bindir/mysqld_safe)"
fi
;;
'stop')
# Stop daemon. We use a signal here to avoid having to know the
# root password.
if test -s "$mariadbd_pid_file_path"
then
mariadbd_pid=`cat "$mariadbd_pid_file_path"`
if su_kill -0 $mariadbd_pid ; then
echo $echo_n "Shutting down MariaDB"
su_kill $mariadbd_pid
# mysqld should remove the pid file when it exits, so wait for it.
wait_for_gone $mariadbd_pid "$mariadbd_pid_file_path"; return_value=$?
else
log_failure_msg "MariaDB server process #$mariadbd_pid is not running!"
rm "$mariadbd_pid_file_path"
fi
# Delete lock for Red Hat / SuSE
if test -f "$lock_file_path"
then
rm -f "$lock_file_path"
fi
exit $return_value
else
log_failure_msg "MariaDB server PID file could not be found!"
fi
;;
'restart')
# Stop the service and regardless of whether it was
# running or not, start it again.
if $0 stop "$@"; then
if ! $0 start "$@"; then
log_failure_msg "Failed to restart server."
exit 1
fi
else
log_failure_msg "Failed to stop running server, so refusing to try to start."
exit 1
fi
;;
'reload'|'force-reload')
if test -s "$mariadbd_pid_file_path" ; then
read mariadbd_pid < "$mariadbd_pid_file_path"
su_kill -HUP $mariadbd_pid && log_success_msg "Reloading service MariaDB"
touch "$mariadbd_pid_file_path"
else
log_failure_msg "MariaDB PID file could not be found!"
exit 1
fi
;;
'status')
# First, check to see if pid file exists
if test -s "$mariadbd_pid_file_path" ; then
read mariadbd_pid < "$mariadbd_pid_file_path"
if su_kill -0 $mariadbd_pid ; then
log_success_msg "MariaDB running ($mariadbd_pid)"
exit 0
else
log_failure_msg "MariaDB is not running, but PID file exists"
exit 1
fi
else
# Try to find appropriate mariadbd process
mariadbd_pid=`pgrep -f $libexecdir/mariadbd`
# test if multiple pids exist
pid_count=`echo $mariadbd_pid | wc -w`
if test $pid_count -gt 1 ; then
log_failure_msg "Multiple MariaDB running but PID file could not be found ($mariadbd_pid)"
exit 5
elif test -z $mariadbd_pid ; then
if test -f "$lock_file_path" ; then
log_failure_msg "MariaDB is not running, but lock file ($lock_file_path) exists"
exit 2
fi
log_failure_msg "MariaDB is not running"
exit 3
else
log_failure_msg "MariaDB is running but PID file could not be found"
exit 4
fi
fi
;;
'configtest')
# Safeguard (relative paths, core dumps..)
cd $basedir
echo $echo_n "Testing MariaDB configuration syntax"
daemon=$bindir/mariadbd
if test -x $libexecdir/mariadbd
then
daemon=$libexecdir/mariadbd
elif test -x $sbindir/mariadbd
then
daemon=$sbindir/mariadbd
elif test -x `which mariadbd`
then
daemon=`which mariadbd`
else
log_failure_msg "Unable to locate the mariadbd binary!"
exit 1
fi
help_out=`$daemon --help 2>&1`; r=$?
if test "$r" != 0 ; then
log_failure_msg "$help_out"
log_failure_msg "There are syntax errors in the server configuration. Please fix them!"
else
log_success_msg "Syntax OK"
fi
exit $r
;;
'bootstrap')
if test "$_use_systemctl" == 1 ; then
log_failure_msg "Please use galera_new_cluster to start the mariadb service with --wsrep-new-cluster"
exit 1
fi
# Bootstrap the cluster, start the first node
# that initiate the cluster
echo $echo_n "Bootstrapping the cluster.. "
$0 start $other_args --wsrep-new-cluster
exit $?
;;
*)
# usage
basename=`basename "$0"`
echo "Usage: $basename {start|stop|restart|reload|force-reload|status|configtest|bootstrap} [ MariaDB server options ]"
exit 1
;;
esac
exit 0
=================================================================
●起動
テスト起動した MariaDB をKILLしてから、 スクリプトで起動します。
[mariadb]# cd
[ ~]# pwd
/root
[ ~]# pgrep mariadb
76149
[ ~]# kill 76149
[ ~]# pgrep mariadb
[ ~]#
[ ~]# systemctl daemon-reload
[ ~]# systemctl start mariadb
[ ~]# pgrep mariadb
76446
[ ~]# sudo su - mariadb
[ ~]$ bin/mariadb --port 3307 -p -e "status"
Enter password:
WARNING: Forcing protocol to TCP due to option specification. Please explicitly state intended protocol.
--------------
bin/mariadb Ver 15.1 Distrib 10.9.3-MariaDB, for linux-systemd (x86_64) using readline 5.1
Connection id: 3
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server: MariaDB
Server version: 10.9.3-MariaDB MariaDB Server
Protocol version: 10
Connection: localhost via TCP/IP
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
TCP port: 3307
Uptime: 40 sec
Threads: 1 Questions: 4 Slow queries: 0 Opens: 17 Open tables: 10 Queries per second avg: 0.100
--------------
以上です。
需要があるとは思えませんが、興味本位で MySQL-8.0 と MariaDB-10.9 を共存させてみました。
ではまた。
