创建多实例的 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