一台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 |
步骤:
- 下载压缩包
cd /opt/software/archive
wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.36-linux-glibc2.12-x86_64.tar
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
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
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
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
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
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
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
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
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
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
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
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
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
2
脚本命令
# 主数据库服务开启
mysql_m.sh start
# 主数据库服务停止
mysql_m.sh stop
# 从数据库服务开启
mysql_s.sh start
# 从数据库服务停止
mysql_s.sh stop
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
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
2
查看mysql服务
systemctl start mysql_m.service
systemctl start mysql_s.service
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
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。