一台linux服务器安装多个mysql服务

实验环境

操作系统：CentOS Linux release 7.9.2009 (Core)

数据库版本：5.7.36-log

预计划安装3个MySQL实例，规划信息为：

实例一	实例二	实例三
port=3306 socket=/opt/software/mysql/3306/tmp/mysql_3306.sock datadir=/opt/software/mysql/3306/data log-error =/opt/software/mysql/3306/error_3306.log pid-file=/opt/software/mysql/3306/mysql.pid	port=3307 socket=/opt/software/mysql/3307/tmp/mysql_3307.sock datadir=/opt/software/mysql/3307/data log-error =/opt/software/mysql/3307/error_3307.log pid-file=/opt/software/mysql/3307/mysql.pid	port=3308 socket=/opt/software/mysql/3308/tmp/mysql_3308.sock datadir=/opt/software/mysql/3308/data log-error =/opt/software/mysql/3308/error_3308.log pid-file=/opt/software/mysql/3308/mysql.pid

解压包地址 (opens new window)

步骤：

下载压缩包

cd /opt/software/archive
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar

1
2

解压安装包

[root@masterdb ~]# cd /opt/software/archive
[root@masterdb local]# tar xzvf  /opt/software/archive/mysql-5.7.36-linux-glibc2.12-x86_64.tar

# 修改解压文件名，与前面定义的basedir相同
[root@masterdb local]# mv mysql-5.7.24-linux-glibc2.12-x86_64/ mysql

1
2
3
4
5

创建实例各自的数据文件夹

[root@masterdb mysql]# mkdir -p /opt/software/mysql/{3306,3307,3308}/data
[root@masterdb mysql]# chown -R mysql:mysql /opt/software/mysql/
[root@masterdb mysql]# cd /opt/software/mysql/
[root@masterdb mysql]# tree
.
├── 3306
│   └── data
├── 3307
│   └── data
├── 3308
│   └── data
└── 3309
    └── data

1
2
3
4
5
6
7
8
9
10
11
12
13
14

创建MySQL参数配置文件

[root@masterdb mysql]# vim /opt/software/mysql/3306/my.cnf 
[root@masterdb mysql]# vim /opt/software/mysql/3307/my.cnf 
[root@masterdb mysql]# vim /opt/software/mysql/3308/my.cnf

1
2
3

3306的my.cnf

[client]
port=3306
socket=/opt/software/mysql/3306/tmp/mysql_3306.sock
default-character-set=utf8mb4

[mysqld]
port=3306
socket=/opt/software/mysql/3306/tmp/mysql_3306.sock
datadir=/opt/software/mysql/3306/data
log-error =/opt/software/mysql/3306/error_3306.log
pid-file=/opt/software/mysql/3306/mysql.pid
lc-messages-dir=/opt/software/mysql/mysql-5.7.36/share/english

character-set-server=utf8mb4
lower_case_table_names=1
autocommit=1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

3307的my.cnf

[client]
port=3307
socket=/opt/software/mysql/3307/tmp/mysql_3307.sock
default-character-set=utf8mb4

[mysqld]
port=3307
socket=/opt/software/mysql/3307/tmp/mysql_3307.sock
datadir=/opt/software/mysql/3307/data
log-error =/opt/software/mysql/3307/error_3307.log
pid-file=/opt/software/mysql/3307/mysql.pid
lc-messages-dir=/opt/software/mysql/mysql-5.7.36/share/english

character-set-server=utf8mb4
lower_case_table_names=1
autocommit=1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

3308的my.cnf

[client]
port=3308
socket=/opt/software/mysql/3308/tmp/mysql_3308.sock
default-character-set=utf8mb4

[mysqld]
port=3308
socket=/opt/software/mysql/3308/tmp/mysql_3308.sock
datadir=/opt/software/mysql/3308/data
log-error =/opt/software/mysql/3308/error_3308.log
pid-file=/opt/software/mysql/3308/mysql.pid
lc-messages-dir=/opt/software/mysql/mysql-5.7.36/share/english

character-set-server=utf8mb4
lower_case_table_names=1
autocommit=1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

初始化数据库

注意，初始化实例的最后一行记录了root的初始密码

# 初始化3306实例
[root@localhost mysql]# /opt/software/mysql/mysql-5.7.36/bin/mysqld --defaults-file=/opt/software/mysql/3306/my.cnf --initialize  --datadir=/opt/software/mysql/3306/data
2022-03-15T02:09:49.510446Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-03-15T02:09:49.510756Z 0 [ERROR] Can't find error-message file '/opt/software/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-03-15T02:09:55.149983Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-03-15T02:09:56.382707Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-03-15T02:09:56.553598Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 02826a54-a405-11ec-a90d-000c294bc8d1.
2022-03-15T02:09:56.554992Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-03-15T02:09:57.342387Z 0 [Warning] 
2022-03-15T02:09:57.342405Z 0 [Warning] 
2022-03-15T02:09:57.343474Z 0 [Warning] CA certificate ca.pem is self signed.
2022-03-15T02:09:57.497304Z 1 [Note] A temporary password is generated for root@localhost: tqck.M32YJ/g



# 初始化3307实例 
[root@localhost frp_0.34.2_linux_amd64]# /opt/software/mysql/mysql-5.7.36/bin/mysqld --defaults-file=/opt/software/mysql/3307/my.cnf  --initialize --datadir=/opt/software/mysql/3307/data
2022-03-15T07:22:42.315505Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-03-15T07:22:42.315655Z 0 [ERROR] Can't find error-message file '/opt/software/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-03-15T07:22:44.502073Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-03-15T07:22:45.305996Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-03-15T07:22:45.487409Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b5aa6f3a-a430-11ec-8d3a-000c294bc8d1.
2022-03-15T07:22:45.489460Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-03-15T07:22:46.354206Z 0 [Warning] 
2022-03-15T07:22:46.354250Z 0 [Warning] 
2022-03-15T07:22:46.355299Z 0 [Warning] CA certificate ca.pem is self signed.
2022-03-15T07:22:46.618182Z 1 [Note] A temporary password is generated for root@localhost: &&;d=artd0uP


# 初始化3308实例
[root@localhost mysql]# /opt/software/mysql/mysql-5.7.36/bin/mysqld --defaults-file=/opt/software/mysql/3308/my.cnf  --initialize  --datadir=/opt/software/mysql/3308/data
2022-03-15T07:23:24.198498Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-03-15T07:23:24.198741Z 0 [ERROR] Can't find error-message file '/opt/software/mysql/share/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
2022-03-15T07:23:29.704455Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-03-15T07:23:30.599517Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-03-15T07:23:30.711976Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: d09f2823-a430-11ec-b3c9-000c294bc8d1.
2022-03-15T07:23:30.713399Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-03-15T07:23:31.629892Z 0 [Warning] 
2022-03-15T07:23:31.629911Z 0 [Warning] 
2022-03-15T07:23:31.630566Z 0 [Warning] CA certificate ca.pem is self signed.
2022-03-15T07:23:31.811930Z 1 [Note] A temporary password is generated for root@localhost: Bu<Y:wf<T3/d

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

设置环境变量

[root@masterdb mysql]# vim /etc/profile
# 在文件末尾添加下面信息
export PATH=/opt/software/mysql/mysql-5.7.36/bin:$PATH

#使环境变量生效
[root@masterdb mysql]# source /etc/profile

1
2
3
4
5
6

修改数据库root@localhost密码（初次登陆MySQL数据库，需要修改root密码，否则无法正常使用）

[root@masterdb mysql]# mysql -S /opt/software/mysql/3306/tmp/mysql_3306.sock -p 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

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

-- 使用mysql数据库
mysql> use mysql;
-- 开启root用户远程访问
mysql> UPDATE user SET Host = '%' WHERE User = 'root';
-- 修改root@localhost用户的密码
mysql> alter user  root@localhost identified by 'root';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> exit
Bye

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

使用mysqld_multi管理多实例（本人试的时候，不知道为啥可以启动，但是关闭不了，不保证能用）

# 使用mysqld_multi启动3306端口的实例
[root@masterdb mysql]# mysqld_multi start 3306

# 使用mysqld_multi启动全部实例
[root@masterdb mysql]# mysqld_multi start

# 使用mysqld_multi查看实例状态
[root@masterdb mysql]# mysqld_multi report
Reporting MySQL servers
MySQL server from group: mysqld3306 is running
MySQL server from group: mysqld3307 is running
MySQL server from group: mysqld3308 is running
MySQL server from group: mysqld3309 is running

1
2
3
4
5
6
7
8
9
10
11
12
13

使用shell脚本启动，停止mysql实例

主数据库脚本(mysql_m.sh)

#!/bin/bash
d=`date "+%y-%m/%d %H:%M:%S"`
echo "$1$d"

if [ $1 = "start" ]; then
echo "$d mysql_m start "
        nohup /opt/software/mysql/mysql-5.7.36/bin/mysqld --datadir=/opt/software/mysql/3306/data  --user=root >> /opt/software/mysql/3306/mysql_m.log 2>&1 &

elif  [ $1 = "stop" ]; then
echo "$d mysql_m stop"
# 方法一：利用mysqladmin，但是这种方法需要用户名和输入密码
# /xinyuan/mysql_m/bin/mysqladmin shutdown -proot
# 方法二：直接关闭进程kill  -9 [mysql的进程号]
mysql_m=`ps -ef|grep 3306 |grep mysql | grep -v 'grep\|stop' | awk '{print $2}' `
kill -15 $mysql_m
else
echo "输入错误，请检查重新输入"
fi

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

从数据库脚本(mysql_s.sh)

#!/bin/bash
d=`date "+%y-%m/%d %H:%M:%S"`

if [ $1 = "start" ]; then
echo "$d mysql_slaves start "
        nohup /opt/software/mysql/mysql-5.7.36/bin/mysqld --defaults-file=/opt/software/mysql/3307/my.cnf --datadir=/opt/software/mysql/3307/data  --user=root >> /opt/software/mysql/3307/mysql_m.log 2>&1 &
        nohup /opt/software/mysql/mysql-5.7.36/bin/mysqld --defaults-file=/opt/software/mysql/3308/my.cnf --datadir=/opt/software/mysql/3308/data  --user=root >> /opt/software/mysql/3308/mysql_m.log 2>&1 &

elif  [ $1 = "stop" ]; then
echo "$d mysql_slaves stop"
# 方法一：利用mysqladmin，但是这种方法需要用户名和输入密码
# /xinyuan/mysql_m/bin/mysqladmin shutdown -proot
# 方法二：直接关闭进程kill  -9 [mysql的进程号]
mysql_s1=`ps -ef|grep 3307 |grep mysql | grep -v 'grep\|stop' | awk '{print $2}' `
mysql_s2=`ps -ef|grep 3308 |grep mysql | grep -v 'grep\|stop' | awk '{print $2}' `
kill -15 $mysql_s1
kill -15 $mysql_s2
else
echo "输入错误，请检查重新输入"
fi

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

执行文件赋权，使文件可以被执行

chmod 777 mysql_m.sh
chmod 777 mysql_s.sh

1
2

脚本命令

# 主数据库服务开启
mysql_m.sh start 
# 主数据库服务停止
mysql_m.sh stop
# 从数据库服务开启
mysql_s.sh start 
# 从数据库服务停止
mysql_s.sh stop

1
2
3
4
5
6
7
8

启动效果

将mysql加入systemctl管理，并设置关机保护

vim /usr/lib/systemd/system/mysql_m.service
# 文件内容
[Unit]
Description=Mysql_m
SourcePath=/opt/software/mysql
Before=shutdown.target

[Service]
User=root
Type=forking
ExecStart=/opt/software/mysql/mysql_m.sh start
ExecStop=/opt/software/mysql/mysql_m.sh stop

[Install]
WantedBy=multi-user.target

vim /usr/lib/systemd/system/mysql_s.service

# 文件内容
[Unit]
Description=Mysql_s
SourcePath=/opt/software/mysql
Before=shutdown.target

[Service]
User=root
Type=forking
ExecStart=/opt/software/mysql/mysql_s.sh start
ExecStop=/opt/software/mysql/mysql_s.sh stop

[Install]
WantedBy=multi-user.target

# 重启systemctl
systemctl daemon-reload

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

启动mysql服务

systemctl start mysql_m.service
systemctl start mysql_s.service

1
2

查看mysql服务

systemctl start mysql_m.service
systemctl start mysql_s.service

1
2

# 问题汇总：

# 1. navicat and connection is being used (opens new window)问题

解决方案:

1、在已经保存的连接上上编辑，测试连接成功，但是点击连接就会一直提示 connection is being used

2、需要新建一个连接，才能使用，不能再已保存的上面修改

处理Can't find error-message file'/usr/share/mysql/errmsg.sys问题

操作系统：centos7
mysql版本：5.7.29
使用普通用户安装完成以后，启动mysql：
/data/mysql/usr/sbin/mysqld --defaults-file=/data/mysql/etc/my.cnf --user=dongzw &
日志中报：
[ERROR] Can't find error-message file '/usr/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
大概搜索了一下，在my.cnf中添加
basedir=/data/mysql
再启动，报错变成了：
[ERROR] Can't read from messagefile '/data/mysql/share/mysql/errmsg.sys'
注意报错路径改变了，由/usr变成了/data，此时在安装路径下查找errmsg.sys文件：
[dongzw@localhost mysql]$ find /data/ -name errmsg.sys
/data/mysql/usr/share/mysql/bulgarian/errmsg.sys
/data/mysql/usr/share/mysql/czech/errmsg.sys
/data/mysql/usr/share/mysql/danish/errmsg.sys
/data/mysql/usr/share/mysql/dutch/errmsg.sys
/data/mysql/usr/share/mysql/english/errmsg.sys
.....................
看名字，应为各国语言的版本，这里选择english版本，新建并把文件拷到报错路径，当前路径为：/data/mysql
[dongzw@localhost mysql]$ mkdir -p share/mysql
[dongzw@localhost mysql]$ cp /data/mysql/usr/share/mysql/english/errmsg.sys share/mysql/
停止mysql：
/data/mysql/usr/bin/mysqladmin -uroot -proot -S /data/mysql/mysql.sock shutdown
再次启动，只有Warning信息了，问题解决。

解决方式2：
根据上面的查找到的errmsg.sys信息，可在my.cnf中配置：
lc-messages-dir=/data/mysql/usr/share/mysql/english
my.cnf中去掉basedir=/data/mysql
或者在启动命令加上--lc-messages-dir参数也可以解决问题：
/data/mysql/usr/sbin/mysqld --defaults-file=/data/mysql/etc/my.cnf --lc-messages-dir=/data/mysql/usr/share/mysql/english --user=dongzw &


附：安装5.7.29初始化数据库命令：
[dongzw@localhost data]$ /data/mysql/usr/sbin/mysqld --initialize --defaults-file=/data/mysql/etc/my.cnf --user=dongzw
如果报：
[ERROR] unknown variable 'defaults-file=/data/mysql/etc/my.cnf' 
[ERROR] Can't find error-message file '/usr/share/mysql/errmsg.sys'. Check error-message file location and 'lc-messages-dir' configuration directive.
需要把 --initialize放到--defaults-file后面
 /data/mysql/usr/sbin/mysqld  --defaults-file=/data/mysql/etc/my.cnf --initialize --user=dongzw

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

# 2. Do you already have another mysqld server running on socket: /tmp/mysql.sock

一.问题描述：版本:mysql-5.7.36 启动mysql: /opt/software/mysql/mysql-5.7.36/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/opt/software/mysql --datadir=/opt/software/mysql/3306/data 无法启动，mysql进程退出。

二.问题分析：检查error log，如下： 2022-03-15T09:21:20.420466Z 0 [Note] Server hostname (bind-address): '*'; port: 3306 2022-03-15T09:21:20.421295Z 0 [Note] IPv6 is available. 2022-03-15T09:21:20.421331Z 0 [Note] - '::' resolves to '::'; 2022-03-15T09:21:20.421363Z 0 [Note] Server socket created on IP: '::'. 2022-03-15T09:21:20.422855Z 0 [ERROR] Can't start server : Bind on unix socket: Address already in use 2022-03-15T09:21:20.422874Z 0 [ERROR] Do you already have another mysqld server running on socket: /tmp/mysql.sock ? 2022-03-15T09:21:20.422885Z 0 [ERROR] Abortin

上面提示unix socket /tmp/mysql3306.sock 被占用，用ps -ef|grep mysql检查没有其它mysql进程.用netstat -an|grep 3306检查端口也不存在。对比分析，正常关闭mysql时，/tmp/mysql3306.sock文件是不存在的，而现在mysql已关闭后，还存在/tmp/mysql3306.sock文件。猜测是由于上次mysql未正常关闭或关闭异常引起。

三.问题解决：在root用户下，删除/tmp/mysql3306.sock文件，再重启mysql。

上次更新: 2025/06/04, 15:06:15

← build配置 mysql主从复制→

一台linux服务器安装多个mysql服务

# 问题汇总：

# 1. navicat and connection is being used (opens new window)问题

# 2. Do you already have another mysqld server running on socket: /tmp/mysql.sock

评论