建立多實例的 MariaDB

# 在您修改 50-server.cnf 之前要 停止 MySQL 非常重要！
sudo systemctl stop mysql

# 需要修改 50-server.cnf，可先備份。
mkdir ~/bak
cp /etc/mysql/mariadb.conf.d/50-server.cnf ~/bak

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid (1)
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /var/lib/mysql (2)

# this is only for the mysqld standalone daemon
[mysqld]

#
# * Basic Settings
#

# Multiple MySQL server configuration
[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log        = /var/log/mysqld_multi.log
user       = multi_admin
password   = 'multipass'(1)

[mysqld2](2)
server-id = 2
innodb_flush_method = O_DIRECT
user		= mysql
pid-file	= /var/run/mysqld/mysqld2.pid
socket		= /var/run/mysqld/mysqld2.sock
port		= 3307
basedir		= /usr
datadir		= /var/lib/mysql2
tmpdir		= /tmp
lc-messages-dir	= /usr/share/mysql
skip-external-locking
bind-address		= 127.0.0.1
key_buffer_size		= 16M
max_allowed_packet	= 16M
thread_stack		= 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit	= 1M
query_cache_size        = 16M
# log_error = /var/log/mysql/error.log
expire_logs_days	= 10
max_binlog_size   = 100M
character-set-server  = utf8mb4
collation-server      = utf8mb4_general_ci

# this is only for the mysqld standalone daemon
[mysqld1](3)
server-id = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
sync_binlog = 0
binlog-format = ROW
binlog_do_db = mybb

#
# * Basic Settings
#

...
# this is only for the mysqld standalone daemon
[mysqld1]
server-id = 1
...
#
# Error log - should be very few entries.
#

# log_error = /var/log/mysql/error.log (1)

sudo mysql_install_db --user=mysql --datadir=/var/lib/mysql2 --force

sudo mysqld_multi start

顯示 mysqld_multi 資料庫狀態：
sudo mysqld_multi report

Reporting MariaDB servers
MariaDB server from group: mysqld2 is running
MariaDB server from group: mysqld1 is running (1)

檢查 MySQL 行程情況：

ps -e | grep "mysql" (1)

1475 ?        00:00:00 mysqld_safe
1622 ?        00:00:07 mysqld
1889 pts/4    00:00:00 mysqld_safe
2282 pts/4    00:00:02 mysqld

檢查 MySQL 通訊情況：
sudo netstat -tlpn | grep mysql (1)

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      1622/mysqld
tcp        0      0 127.0.0.1:3307          0.0.0.0:*               LISTEN      2282/mysqld

檢查 3306 3307 通訊情況：
sudo netstat -nlt | grep 330

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:3307          0.0.0.0:*               LISTEN

mysqlpw='MySQL root 密碼'

sudo mysql --host=localhost --port=3307 --protocol=tcp mysql

-- 先顯示 root 用戶的遠端帳號 (在 mysql 內)：
select user, host from user where user='root' and host <> 'localhost';

-- 刪除 root 除了 localhost 之外的其他遠端帳號：
delete from user where user='root' and host <> 'localhost';

-- 原生密碼認證需同時設定密碼及 plugin：
update mysql.user set password=password('$mysqlpw') where user='root';
update user set plugin='mysql_native_password' where user='root';
flush privileges;

-- 退出 mysql
exit;

mysql -uroot -p$mysqlpw --host=localhost --port=3307 --protocol=tcp mysql

set global time_zone = '+8:00';
set time_zone = '+8:00';

檢查調整後的情況：
select timediff(now(), utc_timestamp);

結果如下：
+--------------------------------+
| timediff(now(), utc_timestamp) |
+--------------------------------+
| 08:00:00                       |
+--------------------------------+

先不指定 protocol：
mysql -uroot -p$mysqlpw --host=localhost --port=3307 -N \
-e "show variables where variable_name = 'port';"

port    3306
登錄 3307 但實際卻為 3306，顯然不正確。

指定 protocol=tcp：
mysql -uroot -p$mysqlpw --host=localhost --port=3307 --protocol=tcp -N \
-e "show variables where variable_name = 'port';"

port    3307
登錄參數加上 protocol=tcp 才正確。

# mysqld1 授予 multi_admin 關閉資料庫權限
mysql -uroot -p$mysqlpw --host=localhost --port=3306 --protocol=tcp<<EOF
grant shutdown on *.* to 'multi_admin'@'localhost' identified by 'multipass'; (1)
flush privileges;
EOF

#mysqld2 授予 multi_admin 關閉資料庫權限
mysql -uroot -p$mysqlpw --host=localhost --port=3307 --protocol=tcp<<EOF
grant shutdown on *.* to 'multi_admin'@'localhost' identified by 'multipass'; (1)
flush privileges;
EOF

停止 mysqld_multi：
sudo mysqld_multi stop

顯示 mysqld_multi 狀態：
sudo mysqld_multi report

Reporting MariaDB servers
MariaDB server from group: mysqld2 is not running (1)
MariaDB server from group: mysqld1 is not running

啟動 mysqld_multi：
sudo mysqld_multi start

顯示 mysqld_multi 狀態：
sudo mysqld_multi report

Reporting MariaDB servers
MariaDB server from group: mysqld2 is running
MariaDB server from group: mysqld1 is running

檢查 mysqld_multi.log 是否異常：
tail /var/log/mysqld_multi.log -n20

sudo mysqld_multi stop
sudo mysqld_multi start
sudo mysqld_multi report

sudo systemctl disable mysql (1)

sudo bash -c 'cat > /etc/init.d/mysqld_multi' << "EOF"
#!/bin/sh

### BEGIN INIT INFO
# Provides:       scriptname
# Required-Start: $remote_fs $syslog
# Required-Stop:  $remote_fs $syslog
# Default-Start:  2 3 4 5
# Default-Stop:   0 1 6
# Short-Description: Start daemon at boot time
# Description:    Enable service provided by daemon.
### END INIT INFO

bindir=/usr/bin

if [ -x $bindir/mysqld_multi ]; then(1)
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Can't execute $bindir/mysqld_multi";
  exit;
fi

bindir=/usr/bin

if test -x $bindir/mysqld_multi
then
  mysqld_multi="$bindir/mysqld_multi";
else
  echo "Can't execute $bindir/mysqld_multi";
  exit;
fi

case "$1" in
  'start' )
    "$mysqld_multi" start $2(2)
   ;;
  'stop' )
    "$mysqld_multi" stop $2
   ;;
  'report' )
    "$mysqld_multi" report $2 >&2
  ;;
  'restart' )
    "$mysqld_multi" stop $2
    "$mysqld_multi" start $2
  ;;
  'status' )
    "$mysqld_multi" report >&2
    status=4
    if [ -f /var/run/mysqld/mysqld.pid ]; then
      if [ -f /var/run/mysqld/mysqld2.pid ]; then
        status=0
      fi
    elif [ ! -f /var/run/mysqld/mysqld2.pid ]; then
      status=3
    fi
    if [ $status -eq 4 ]; then
      echo 'Usage: Make the service show question mark' >&2(3)
    fi
    exit $status
  ;;
  *)
   echo "Usage: $0 {start|stop|report|restart|status}" >&2
  ;;
esac
EOF

sudo chmod +x /etc/init.d/mysqld_multi
sudo update-rc.d mysqld_multi defaults
sudo update-rc.d mysqld_multi enable

sudo service mysqld_multi status

[mysqld1] (1)
server-id = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1
binlog-format = ROW (2)
binlog_do_db = mybb (3)
log_bin      = /var/log/mysql/mysql-bin.log (4)
expire_logs_days = 10 (5)

/**
 * phpMyAdmin configuration storage settings.
 */

 /**
  * Master Server
  */

$cfg['Servers'][$i]['verbose'] = 'Master';

/**
 * Slave server
 */

$i++;
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['verbose'] = 'Slave';
$cfg['Servers'][$i]['host'] = '127.0.0.1';
$cfg['Servers'][$i]['port'] = '3307';
$cfg['Servers'][$i]['compress'] = false;
$cfg['Servers'][$i]['AllowNoPassword'] = false;

/**
 * phpMyAdmin configuration storage settings.
 */

mysql --version

mysqldump --help

-x, --lock-all-tables 鎖定所有表格
-A, --all-databases   所有資料庫

備份 (單例及多實例主要資料庫)：
mysqldump -uroot -p$mysqlpw --host=localhost --port=3306 --protocol=tcp -x -A > db1.sql


備份多實例從屬資料庫 (單例不用執行)：
mysqldump -uroot -p$mysqlpw --host=localhost --port=3307 --protocol=tcp -x -A > db2.sql

停止多實例資料庫服務：
sudo mysqld_multi stop

停止單例資料庫服務：
sudo systemctl stop mysql

cat /etc/apt/sources.list
cp /etc/apt/sources.list ~/sources.list.bak

# MariaDB 10.3 repository list - created 2023-02-02 12:47 UTC
# https://mariadb.org/download/
deb https://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.3/ubuntu bionic main
# deb-src https://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.3/ubuntu bionic main

sudo apt install software-properties-common
sudo apt-key adv --fetch-keys 'https://mariadb.org/mariadb_release_signing_key.asc'
sudo add-apt-repository 'deb https://ftp.ubuntu-tw.org/mirror/mariadb/repo/10.3/ubuntu bionic main' (1)

sudo apt update
sudo apt install mariadb-server -y

啟動多實例資料庫服務：
sudo mysqld_multi start

顯示多實例資料庫服務狀態：
sudo mysqld_multi report

Reporting MariaDB servers
MariaDB server from group: mysqld2 is running
MariaDB server from group: mysqld1 is running

啟動單例資料庫服務：
sudo systemctl start mariadb

更新資料庫 (單例及多實例主要資料庫)：
sudo mysql_upgrade --port=3306 --protocol=tcp -uroot -p$mysqlpw

更新多實例從屬資料庫 (單例不用執行)：
sudo mysql_upgrade --port=3307 --protocol=tcp -uroot -p$mysqlpw