Ubuntu22.04安装MySQL8多实例(mysqld_multi )

雪域男孩
385 阅读5分钟

一、APT安装mysql

1、安装MySQL服务器

sudo apt update sudo apt install mysql-server

2、启动和检查状态

sudo systemctl start mysql sudo systemctl status mysql

3、测试

mysql -uroot -p
回车在回车

exit退出

二、安装多实例

1、建目录

mkdir -p /data/{3307,3308}/data
mkdir -p /data/mysqld_multi
chown -R mysql.mysql /data

如果不修改，在后面初始化时化可能会出现如下错误：

2023-08-16T01:52:42.993773Z 0 [ERROR] [MY-010174] [Server] Can't change data directory owner to mysql

2023-08-16T01:52:42.993780Z 0 [ERROR] [MY-013455] [Server] The newly created data directory /data/3307/data/ by --initialize is unusable. You can remove it.

2023-08-16T01:52:42.993850Z 0 [ERROR] [MY-010119] [Server] Aborting

2、目录授权

以下 MySQL 的行为基于 APT 包安装产生，如果采用 MySQL 二进制包安装，则可以规避这个问题。

vim /etc/apparmor.d/usr.sbin.mysqld

添加如下代码

  /data/3307 r,
  /data/3307/** rwk,
  /data/3307/data rw,
  /data/3307/data/** rwk,
  /data/3308 r,
  /data/3308/** rwk,
  /data/3308/data rw,
  /data/3308/data/** rwk,
  
  /data/mysqld_multi r,
  /data/mysqld_multi/** rwk,

image.png

service apparmor restart

service apparmor status

apparmor启动失败，报错如下：

AppArmor parser error for /etc/apparmor.d in profile /etc/apparmor.d/usr.sbin.mysqld at line 9: Could not open 'abstractions/mysql'

解决办法

cd /etc/apparmor.d/abstractions

mkdir mysql

如果不修改，在后面初始化时化可能会出现如下错误：

mysqld: Can't create directory '/data/3307/data/' (OS errno 13 - Permission denied)

3、修改配置文件

vim /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld_multi]
mysqld     = /usr/bin/mysqld_safe
mysqladmin = /usr/bin/mysqladmin
log=/data/mysqld_multi/mysql.log

[mysqld3307]
socket     = /data/3307/data/mysql.sock
port       = 3307
pid-file   = /data/3307/data/mysql.pid
datadir    = /data/3307/data
log-error  = /data/3307/data/mysql.err
mysqlx_socket=/data/3307/data/mysqlx.sock
mysqlx_port=33070
bind_address=0.0.0.0
# 设置运行用户 
user = mysql

[mysqld3308]
socket     = /data/3308/data/mysql.sock
port       = 3308
pid-file   = /data/3308/data/mysql.pid
datadir    = /data/3308/data
log-error  = /data/3308/data/mysql.err
mysqlx_socket=/data/3308/data/mysqlx.sock
mysqlx_port=33080
bind_address=0.0.0.0
# 设置运行用户 
user = mysql

4、初始化

保证mysql是没问题的，可以访问一下mysql(如mysql -uroot -p回车在回车)，以下操作mysql不用关闭

安装多次导致mysql损坏，sudo apt install mysql-server 重新安装了mysql

如果mysql有问题，可能报错： [ERROR] Could not open mysql.plugin table. Some plugins may be not loaded

sudo mysqld  --initialize-insecure --user=mysql --datadir=/data/3307/data
sudo mysqld  --initialize-insecure --user=mysql --datadir=/data/3308/data

创建失败可以使用命令journalctl -xe进行查看

[ERROR] [MY-013236] [Server] The designated data directory /data/3307/data/ is unusable. You can remove all files that the server added to it.

[ERROR] [MY-010174] [Server] Can't change data directory owner to mysql

5、启动

启动： mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf start

查看状态： mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf report

停用： mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf stop

日志

vi /data/mysqld_multi/mysql.log

连接3307端口

mysql -u root -p -S /data/3307/data/mysql.sock

mysql> show variables like 'port'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | port | 3307 | +---------------+-------+

注意：无法再使用mysql -uroot -p -P端口号的方式连接到指定端口号的MySQL服务器

从MySQL 8开始，使用了新的身份验证插件（caching_sha2_password），而不再是之前的旧插件

（mysql_native_password）。这导致使用旧方式连接MySQL服务器时可能会出现问题。

5、开机启动(调试中)

vi /etc/systemd/system/mysqld_multi.service

[Unit]
Description=MySQL Multi Instance Service
Documentation=man:mysqld(8)  
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html  
After=network.target  
After=syslog.target  
[Install]
WantedBy=multi-user.target  
[Service]
User=mysql  
Group=mysql  
ExecStart=/usr/bin/mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf   start  
ExecStop=/usr/bin/mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf   stop
ExecReload=/usr/bin/mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf  restart

每次修改以后 systemctl daemon-reload

输入以下命令以确保MySQL服务在开机时启动

sudo systemctl enable mysqld_multi

启动服务 systemctl start mysqld_multi

6、验证

1、mysqld_multi --defaults-file=/etc/mysql/mysql.conf.d/mysqld.cnf report

Wide character in print at /usr/bin/mysqld_multi line 684.

Reporting MySQL servers

MySQL server from group: mysqld3307 is running

MySQL server from group: mysqld3308 is running

2、ss -nltp | grep mysql

LISTEN 0 70 127.0.0.1:33080 0.0.0.0:* users:(("mysqld",pid=2814777,fd=20))
LISTEN 0 70 127.0.0.1:33060 0.0.0.0:* users:(("mysqld",pid=2724480,fd=21))
LISTEN 0 70 127.0.0.1:33070 0.0.0.0:* users:(("mysqld",pid=2814769,fd=20))
LISTEN 0 151 0.0.0.0:3307 0.0.0.0:* users:(("mysqld",pid=2814769,fd=22))
LISTEN 0 151 0.0.0.0:3306 0.0.0.0:* users:(("mysqld",pid=2724480,fd=23))
LISTEN 0 151 0.0.0.0:3308 0.0.0.0:* users:(("mysqld",pid=2814777,fd=22))

3、netstat -lnp | grep 330

tcp6 0 0 :::3307 :::* LISTEN 1584597/mysqld
tcp6 0 0 :::3306 :::* LISTEN 1388614/mysqld
tcp6 0 0 :::3308 :::* LISTEN 1570223/mysqld
tcp6 0 0 :::33070 :::* LISTEN 1584597/mysqld
tcp6 0 0 :::33060 :::* LISTEN 1388614/mysqld
tcp6 0 0 :::33080 :::* LISTEN 1570223/mysqld
unix 2 [ ACC ] STREAM LISTENING 5388257 1570223/mysqld /data/3308/mysqlx.sock

unix 2 [ ACC ] STREAM LISTENING 5453685 1584597/mysqld /data/3307/mysqlx.sock

unix 2 [ ACC ] STREAM LISTENING 5388260 1570223/mysqld /data/3308/mysql.sock

unix 2 [ ACC ] STREAM LISTENING 5453688 1584597/mysqld /data/3307/mysql.sock

3、mysql -S /data/3307/data/mysql.sock -e "select @@server_id"

+-------------+

| @@server_id |

+-------------+

| 3307 |

+-------------+

4、尝试连接

mysql -u root -p -S /data/3307/data/mysql.sock

7、设置密码

mysql -u root -p -S /data/3307/data/mysql.sock

修改root密码

alter user 'root'@'localhost' identified with mysql_native_password by 'MyNewPass!';

创建用户

CREATE USER 'username'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

授权

GRANT ALL PRIVILEGES ON . TO 'username'@'%';

使用 FLUSH PRIVILEGES 语句刷新权限：

FLUSH PRIVILEGES;

三、参考文献：

1、使用mysqld_multi：

blog.csdn.net/u010080562/…

缺点：配置在一起，不方便独立管理

2、# 技术分享 | MySQL 如何适配 AppArmor

www.modb.pro/db/169989

3、# MySQL8多实例安装

www.modb.pro/db/620778

四、卸载myql

sudo service mysql stop
sudo apt-get purge mysql-*
sudo rm -rf /etc/mysql /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean
avatar
文章
阅读
粉丝