Ubuntu上MySQL多实例部署

Ubuntu 18.04

MySQL 5.7.21

---

MySQL多实例即在一台计算机中运行多个MySQL服务器，不同服务器通过不同端口区分，这样可以充分利用服务器的资源。

按照参考文档4 的说法，有三种方式实现 MySQL多实例：

1）一个实例一个配置文件

2）多个实例共享一个配置文件

使用mysqld_multi

3）使用MYSQLMANAGER——实例管理器

三种方式各自优缺点，请看参考文档4。

本文介绍在Ubuntu上使用上面的方式2 实现MySQL多实例部署。来自博客园

说明，本文的MySQL使用apt-get安装：

sudo apt-get install mysql-server mysql-client

安装后，mysqld命令位于 /usr/sbin，而其它 mysql命令位于 /usr/bin，默认配置文件位于 /etc/mysql：

/etc/mysql目录结构

ben@ben-VirtualBox:/etc/mysql$ tree
.
├── conf.d
│   ├── mysql.cnf
│   └── mysqldump.cnf
├── debian.cnf
├── debian-start
    // 这里的 my.cnf 包含了 同级的 conf.d、mysql.conf.d 文件夹下的配置
├── my.cnf -> /etc/alternatives/my.cnf
├── my.cnf.fallback
├── mysql.cnf
└── mysql.conf.d
    ├── mysqld.cnf
    ├── mysqld_safe_syslog.cnf

MySQL服务安装后，放了一个名为mysq的脚本 l到 /etc/init.d/ 目录中，服务启动的秘密全在这里——使用了哪些命令、使用了哪些配置。

安装后，系统自动建立了用户mysql、用户组mysql。来自博客园

>>开始...

默认的配置文件中，有一个名为 [mysqld] 的区段（/etc/mysql/mysql.conf.d/mysqld.cnf 中），其包含了默认的MySQL服务器（实例）的配置信息。

注意其中的 pid-file、socket、datadir 等配置，本文部署时踩到了一些坑——和权限有关。

在家目录下，建立新的配置文件 trymulti.cnf：来自博客园

注意配置文件中的 /data/mysql/、/var/lib/mysql2/、/var/log/mysql/等目录。

[mysqld_multi]
# 实际使用 mysqld_safe脚本启动
mysqld = /usr/bin/mysqld_safe
# 管理实例命令
mysqladmin = /usr/bin/mysqladmin

# 实例1-端口3307
[mysqld3307]
datadir = /data/mysql/replica01
port = 3307
socket = /tmp/mysqld3307.sock
pid-file = /var/lib/mysql2/mysqld3307.pid
log-error = /var/log/mysql/mysql3307.log
user=mysql

# 实例2-端口3308
[mysqld3308]
datadir = /data/mysql/replica02
port = 3308
socket = /tmp/mysqld3308.sock
pid-file = /var/lib/mysql2/mysqld3308.pid
log-error = /var/log/mysql/mysql3308.log
user=mysql

将新建配置文件的拥有者改为mysql：来自博客园

# 用户ben的家目录下 新建
ben@ben-VirtualBox:~$ vim trymulti.cnf
# 查看
ben@ben-VirtualBox:~$ ls -l trymulti.cnf
-rw-rw-r-- 1 ben ben 444 9月   9 16:19 trymulti.cnf
ben@ben-VirtualBox:~$
# 改拥有者及组为 mysql
ben@ben-VirtualBox:~$ sudo chown mysql:mysql trymulti.cnf
[sudo] password for ben:
ben@ben-VirtualBox:~$
ben@ben-VirtualBox:~$ ls -l trymulti.cnf
-rw-rw-r-- 1 mysql mysql 444 9月   9 16:19 trymulti.cnf

初始化服务器的数据目录（datadir，要和已有的MySQL实例分开）：

# 非安全insecure
sudo ./mysqld  --initialize-insecure --explicit_defaults_for_timestamp --datadir=/var/lib/mysql-replica01
sudo ./mysqld  --initialize-insecure --explicit_defaults_for_timestamp --datadir=/var/lib/mysql-replica02

踩到坑了！

执行上面的命令时，出现了下面的一些异常信息，导致初始化数据目录失败：

即使把 /data/mysql/ 目录的拥有者改为 mysql:mysql 也不行。来自博客园

$ sudo mysqld --initialize-insecure --explicit_defaults_for_timestamp --datadir=/data/mysql/replica01
mysqld: Can't create directory '/data/mysql/replica01/' (Errcode: 13 - Permission denied)
2021-09-09T02:57:38.865764Z 0 [ERROR] Aborting

看了参考文档5 才解决了问题：

使用 journalctl -xe 查看日志：

9月 09 10:34:30 ben-VirtualBox kernel: audit: type=1400 audit(1631154870.248:88): apparmor="DENIED" operation="mkdir" profile="/usr/sbin/mysqld" name="/data/mysql/replica01/" pid=4993 c
9月 09 10:34:30 ben-VirtualBox audit[4993]: AVC apparmor="DENIED" operation="mkdir" profile="/usr/sbin/mysqld" name="/data/mysql/replica01/" pid=4993 comm="mysqld" requested_mask="c" de

意思是 mysqld命令操作数据目录 /data/mysql/replica01 的权限不足，被拒绝了。

解决方法就是：修改服务 apparmor 中 mysqld的配置——usr.sbin.mysqld文件（疑问，apparmor是什么？有什么用？第一次注意到）

ben@ben-VirtualBox:/etc/apparmor.d$ ls -l | grep mysql
-rw-r--r-- 1 root root  1668 9月   9 15:03 usr.sbin.mysqld

修改：2行，每行末尾都需要添加英文逗号！来自博客园

  /data/mysql/ rw,
  /data/mysql/** rwk,

在解决上面的问题前，执行 mysqld --initialize-insecure 不会在 /data/mysql目录下建立任何文件。

解决后，生成了数据目录 /data/mysql/replica01、/data/mysql/replica02，但是，又提示错误（坑）了。此时，按照参考文档5 的介绍，将新建的数据目录的拥有者改为 mysql即可：

sudo chown mysql:mysql /var/lib/mysql-replica0*

ben@ben-VirtualBox:/etc/apparmor.d$ ls -l /data/mysql/
total 8
drwxr-x--- 5 mysql mysql 4096 9月   9 15:04 replica01
drwxr-x--- 5 mysql mysql 4096 9月   9 15:04 replica02
ben@ben-VirtualBox:/etc/apparmor.d$
ben@ben-VirtualBox:/etc/apparmor.d$ sudo ls -l /data//mysql/replica01
total 122908
-rw-r----- 1 mysql mysql       56 9月   9 11:18 auto.cnf
-rw-r----- 1 mysql mysql      420 9月   9 11:18 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 9月   9 15:04 ibdata1
-rw-r----- 1 mysql mysql 50331648 9月   9 15:04 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 9月   9 11:18 ib_logfile1
-rw-r----- 1 mysql mysql 12582912 9月   9 15:10 ibtmp1
drwxr-x--- 2 mysql mysql     4096 9月   9 11:18 mysql
drwxr-x--- 2 mysql mysql     4096 9月   9 11:18 performance_schema
drwxr-x--- 2 mysql mysql    12288 9月   9 11:18 sys

可以看到，数据目录下生成了一些文件。

就这样，数据目录初始化的坑踩完了。

一切就绪了吗？那就使用 mysqld_multi 脚本来启动多实例吧——预期是2个实例都启动成功。

但是，失败了...

一切都和权限有关系。来自博客园

解决措施：

1）pid-file 的权限

/var/lib/mysql2目录及其下的文件：

# 这的权限应该给太多了
执行 sudo chmod 777 /var/lib/mysql2/

ben@ben-VirtualBox:~$ ls -l /var/lib/ | grep mysql2
drwxrwxrwx  2 mysql         mysql         4096 9月   9 16:59 mysql2
ben@ben-VirtualBox:~$
ben@ben-VirtualBox:~$
ben@ben-VirtualBox:~$ ls -l /var/lib/mysql2/ | grep mysql
-rw-r----- 1 mysql mysql 6 9月   9 16:59 mysqld3307.pid
-rw-r----- 1 mysql mysql 6 9月   9 16:59 mysqld3308.pid

还需要修改 /etc/apparmor.d/usr.sbin.mysqld文件：

  /var/lib/mysql2/ rw,
  /var/lib/mysql2/** rwk,

pid-file部分的修改完毕。

2）log-error 的权限

/var/log/mysql/ 的权限：

疑问：来自博客园

这里有个 adm用户组不清楚是干啥的，admin 管理员缩写？TODO

接下来，执行 mysqld_multi脚本启动多实例MySQL：

# 为什么要用 sudo？
# mysqld_multi --help 可以看到脚本的帮助信息
# start 后面无参数 启动 配置文件中的 所有实例
ben@ben-VirtualBox:~$ sudo mysqld_multi --defaults-file=/home/ben/trymulti.cnf --no-log start
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 16:59:23 2021

Starting MySQL servers
ben@ben-VirtualBox:~$ 2021-09-09T08:59:23.363309Z mysqld_safe Logging to syslog.
2021-09-09T08:59:23.362861Z mysqld_safe Logging to syslog.
2021-09-09T08:59:23.375880Z mysqld_safe Logging to '/var/log/mysql/mysql3307.log'.
2021-09-09T08:59:23.375519Z mysqld_safe Logging to '/var/log/mysql/mysql3308.log'.
2021-09-09T08:59:23.433185Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/replica02
2021-09-09T08:59:23.432528Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/replica01

# 启动成功，但是，多了两个 用户为root的进程——使用mysqld_safe，
# 然后才是用户为 mysql使用mysqld启动的进行
ben@ben-VirtualBox:~$
ben@ben-VirtualBox:~$ ps -ef | grep mysql
mysql     1035     1  0 14:23 ?        00:00:08 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
root     10521     1  0 16:59 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data/mysql/replica01 --port=3307 --socket=/tmp/mysqld3307.sock --pid-file=/var/lib/mysql2/mysqld3307.pid --log-error=/var/log/mysql/mysql3307.log --user=mysql
root     10527     1  0 16:59 pts/1    00:00:00 /bin/sh /usr/bin/mysqld_safe --datadir=/data/mysql/replica02 --port=3308 --socket=/tmp/mysqld3308.sock --pid-file=/var/lib/mysql2/mysqld3308.pid --log-error=/var/log/mysql/mysql3308.log --user=mysql
mysql    10829 10527  2 16:59 pts/1    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysql/replica02 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/mysql3308.log --pid-file=/var/lib/mysql2/mysqld3308.pid --socket=/tmp/mysqld3308.sock --port=3308 --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=
mysql    10830 10521  2 16:59 pts/1    00:00:00 /usr/sbin/mysqld --basedir=/usr --datadir=/data/mysql/replica01 --plugin-dir=/usr/lib/mysql/plugin --user=mysql --log-error=/var/log/mysql/mysql3307.log --pid-file=/var/lib/mysql2/mysqld3307.pid --socket=/tmp/mysqld3307.sock --port=3307 --log-syslog=1 --log-syslog-facility=daemon --log-syslog-tag=
ben      10888  3247  0 16:59 pts/1    00:00:00 grep --color=auto mysql
ben@ben-VirtualBox:~$

# report检查服务是否运行（最开始测试时，一直是 is not running，好烦啊，现在是 is running了，太棒了！）
ben@ben-VirtualBox:/var/log$ mysqld_multi --defaults-file=/home/ben/trymulti.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:15:35 2021
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
ben@ben-VirtualBox:/var/log$

更多：停止——单个、多个或所有、重启操作

停止重启等操作

ben@ben-VirtualBox:/var/log$ sudo mysqld_multi --defaults-file=/home/ben/trymulti.cnf --no-log stop 3307
[sudo] password for ben:
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:18:20 2021

Stopping MySQL servers
ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$ 2021-09-09T09:18:24.404660Z mysqld_safe mysqld from pid file /var/lib/mysql2/mysqld3307.pid ended

ben@ben-VirtualBox:/var/log$ mysqld_multi --defaults-file=/home/ben/trymulti.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:18:36 2021
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is running
ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$ sudo mysqld_multi --defaults-file=/home/ben/trymulti.cnf --no-log stop 3308
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:18:42 2021

Stopping MySQL servers
ben@ben-VirtualBox:/var/log$ 2021-09-09T09:18:45.494643Z mysqld_safe mysqld from pid file /var/lib/mysql2/mysqld3308.pid ended

ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$ mysqld_multi --defaults-file=/home/ben/trymulti.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:18:52 2021
Reporting MySQL servers
MySQL server from group: mysqld3307 is not running
MySQL server from group: mysqld3308 is not running
ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$ sudo mysqld_multi --defaults-file=/home/ben/trymulti.cnf --no-log start
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:19:01 2021

Starting MySQL servers
ben@ben-VirtualBox:/var/log$ 2021-09-09T09:19:01.856440Z mysqld_safe Logging to syslog.
2021-09-09T09:19:01.861155Z mysqld_safe Logging to syslog.
2021-09-09T09:19:01.869447Z mysqld_safe Logging to '/var/log/mysql/mysql3307.log'.
2021-09-09T09:19:01.873328Z mysqld_safe Logging to '/var/log/mysql/mysql3308.log'.
2021-09-09T09:19:01.923713Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/replica01
2021-09-09T09:19:01.926987Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/replica02

ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$ mysqld_multi --defaults-file=/home/ben/trymulti.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:19:12 2021
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running


ben@ben-VirtualBox:/var/log$ mysqld_multi --defaults-file=/home/ben/trymulti.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:20:52 2021
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
ben@ben-VirtualBox:/var/log$

# 重启服务：所有
ben@ben-VirtualBox:/var/log$ sudo mysqld_multi --defaults-file=/home/ben/trymulti.cnf --no-log reload
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
四 9月  9 17:20:57 2021

Reloading MySQL servers
ben@ben-VirtualBox:/var/log$
ben@ben-VirtualBox:/var/log$ mysqld_multi --defaults-file=/home/ben/trymulti.cnf report
WARNING: Log file disabled. Maybe directory or file isn't writable?
mysqld_multi log file version 2.16; run: Wide character in print at /usr/bin/mysqld_multi line 160.
# 时间有变化了
四 9月  9 17:21:18 2021
Reporting MySQL servers
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running

实例虽然启动了，能不能用呢？使用客户端命令mysql试试不就知道了！来自博客园

注意，之前初始化数据目录时，使用的是 --initialize-insecure 选项，这样的话，两个新实例是没有用户密码的。

登录实例3307

ben@ben-VirtualBox:~$ mysql -P3307 -uroot
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ben@ben-VirtualBox:~$
ben@ben-VirtualBox:~$ mysql -P3307 -uroot -hlocalhost
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
ben@ben-VirtualBox:~$

# -h必须是 127.0.0.1
# 不需要 -p（小写，大写指端口）
ben@ben-VirtualBox:~$ mysql -P3307 -uroot -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 7
Server version: 5.7.21-1ubuntu1 (Ubuntu)
...
Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>
mysql>
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.01 sec)

# 默认用户
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+
3 rows in set (0.00 sec)

# 实例状态信息
mysql> status
--------------
mysql  Ver 14.14 Distrib 5.7.21, for Linux (x86_64) using  EditLine wrapper

Connection id:          7
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         5.7.21-1ubuntu1 (Ubuntu)
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3307
Uptime:                 7 min 58 sec

Threads: 1  Questions: 13  Slow queries: 0  Opens: 106  Flush tables: 1  Open tables: 99  Queries per second avg: 0.027
--------------

# 更多实例信息 可以使用 show variables; 查看

登录后，请修改root用户的密码。

疑问：来自博客园

前面使用的 socket 配置是在 /tmp 目录下，要是配置到其它目录，是否也需要配置权限呢？TODO

更进一步：

1、新的服务器实例的配置优化，连接数、缓存等——哪些配置是多实例共享的，哪些不是？实例之间如何互相影响性能？

2、将默认实例3306也加入多实例部署；

3、更精准的权限配置，各个配置涉及到的文件如何精确配置权限；

4、让配置随机启动，怎么弄呢？来自博客园

5、CentOS上有什么不同？

6、使用源码安装MySQL会有什么不同？

7、官方文档是怎么介绍多实例部署的？

...