Linux下MySQL多实例部署及安装指南

时间：2022-08-25 15:51:12

什么是mysql多实例

  • 简单地说，mysql多实例就是在一台服务器上同时开启多个不同的服务端口（3306、3307），同时运行多个mysql服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务。
  • 这些mysql多实例公用一套mysql安装程序，使用不同的my.cnf（也可以相同）配置文件，启动程序（也可以相同）和数据文件。在提供服务时，多实例 mysql在逻辑上看来是各自独立的，它们根据配置文件的对应设定值，获得服务器相应数量的硬件资源。
  • 打个比方，mysql多实例就相当于房子的多个卧室，每个实例可以看作一间卧室，整个服务器就是一套房子，服务器的硬件资源（cpu、mem、disk）、软件资源（centos操作系统）可以看作房子的卫生间、客厅，是房子的公用资源。

　　　　Linux下MySQL多实例部署及安装指南

mysql多实例优缺点：

1、有效利用服务器资源：当单个服务器资源有空剩余时，可以充分利用剩余的资源创建更多的mysql实例提供更多的服务。2、节约服务器资源：当公司资金紧张，但是数据库又需要多个并且需各自尽量独立提供服务或者需要主从同步等，mysql多实例就再好不过了。

3、资源相互争抢问题：当某个服务实例并发很高或者有慢查询时，整个实例会消耗更多的内存、cpu、磁盘、io资源，导致服务器上的其它实例提供服务的质量下降，这就相当于大家在一个房子的不同卧室（mysql实例），需要上厕所（硬件的cpu、内存、磁盘的io资源）时，一个占用了厕所，其他人都要等待。

mysql多实例安装指南：

　　具体详细参考官网（https://dev.mysql.com/doc/refman/5.7/en/installing.html）

  • mysql的安装方法有多种，如二进制安装、源码编译安装、yum安装;
  • yum安装都是默认路径，安装相对简单；
  • 源码安装编译的过程比较长，若没有对源码进行修改且要求使用mysql较高版本；

准备环境。

?
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
[root@mysql-multi ~]# cat /etc/redhat-release
centos linux release 7.5.1804 (core)
[root@mysql-multi ~]# uname -r
3.10.0-862.el7.x86_64
[root@mysql-multi ~]# hostname -i
172.16.70.37
[root@mysql-multi ~]# getenforce
permissive
[root@mysql-multi ~]# systemctl status firewalld
● firewalld.service - firewalld - dynamic firewall daemon
   loaded: loaded (/usr/lib/systemd/system/firewalld.service; disabled; vendor preset: enabled)
   active: inactive (dead)
     docs: man:firewalld(1)
 
jul 23 14:36:11 mysql-multi systemd[1]: starting firewalld - dynamic firewall daemon...
jul 23 14:36:12 mysql-multi systemd[1]: started firewalld - dynamic firewall daemon.
jul 23 15:09:10 mysql-multi systemd[1]: stopping firewalld - dynamic firewall daemon...
jul 23 15:09:11 mysql-multi systemd[1]: stopped firewalld - dynamic firewall daemon.
 
# centos 7 版本的系统默认自带安装了mariadb，需要先清理
[root@mysql-multi ~]# rpm -qa |grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
或
[root@mysql-multi ~]# yum list installed | grep mariadb
mariadb-libs.x86_64                  1:5.5.56-2.el7                    @anaconda
# 卸载
[root@mysql-multi ~]# rpm -e --nodeps mariadb-libs-5.5.56-2.el7.x86_64
或
[root@mysql-multi ~]# yum -y remove mariadb-libs.x86_64

安装并配置mysql多实例

yum源安装方式如下：

　　　　官网rpm包下载：https://downloads.mysql.com/archives/community，选择下载适合的版本。

Linux下MySQL多实例部署及安装指南

?
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
#  将rpm包上传至服务器
[root@mysql-multi ~]# yum install libaio lrzsz tree net-tools -y
[root@mysql-multi ~]# ls
mysql-community-client-5.7.34-1.el7.x86_64.rpm  mysql-community-libs-5.7.34-1.el7.x86_64.rpm
mysql-community-common-5.7.34-1.el7.x86_64.rpm  mysql-community-server-5.7.34-1.el7.x86_64.rpm
 
# 必须按照顺序执行安装操作
[root@mysql-multi ~]# rpm -ivh mysql-community-common-5.7.34-1.el7.x86_64.rpm
[root@mysql-multi ~]# rpm -ivh mysql-community-libs-5.7.34-1.el7.x86_64.rpm
[root@mysql-multi ~]# rpm -ivh mysql-community-client-5.7.34-1.el7.x86_64.rpm
[root@mysql-multi ~]# rpm -ivh mysql-community-server-5.7.34-1.el7.x86_64.rpm
 
[root@mysql-multi ~]# yum list installed | grep mysql
mysql-community-client.x86_64        5.7.34-1.el7                      installed
mysql-community-common.x86_64        5.7.34-1.el7                      installed
mysql-community-libs.x86_64          5.7.34-1.el7                      installed
mysql-community-server.x86_64        5.7.34-1.el7                      installed
 
# 创建实例目录
[root@mysql-multi ~]# mkdir -p /data/app/mysql/{3306,3307}
[root@mysql-multi ~]# mkdir -p /data/app/mysql/3306/{data,binlog,logs}
[root@mysql-multi ~]# mkdir -p /data/app/mysql/3307/{data,binlog,logs}
[root@mysql-multi ~]# tree /data/app/mysql/
/data/app/mysql/
├── 3306
│   ├── binlog
│   ├── data
│   └── logs
└── 3307
    ├── binlog
    ├── data
    └── logs
 
# 设置目录属主属组
[root@mysql-multi ~]# chown -r mysql:mysql /data/app/mysql
[root@mysql-multi ~]# ls -ld /data/app/mysql
drwxr-xr-x. 4 mysql mysql 30 jul 29 18:39 /data/app/mysql
[root@mysql-multi ~]# ls -ld /data/app
drwxr-xr-x. 3 root root 19 jul 29 18:39 /data/app
 
# 新增配置文件my3306.cnf
[root@mysql-multi ~]# mv /etc/my.cnf /etc/my.cnf_bak
[root@mysql-multi ~]# cat /etc/my3306.cnf
[mysqld]
user = mysql
port = 3306
server_id = 3306
datadir = /data/app/mysql/3306/data
socket = /data/app/mysql/3306/mysql3306.sock
symbolic-links = 0
log-error = /data/app/mysql/3306/logs/mysqld3306.log
pid-file = /data/app/mysql/3306/mysqld3306.pid
 
# 新增配置文件my3307.cnf
[root@mysql-multi ~]# cp /etc/my3306.cnf /etc/my3307.cnf
[root@mysql-multi ~]# sed -i 's/3306/3307/g' /etc/my3307.cnf
[root@mysql-multi ~]# cat /etc/my3307.cnf
[mysqld]
user = mysql
port = 3307
server_id = 3307
datadir = /data/app/mysql/3307/data
socket = /data/app/mysql/3307/mysql3307.sock
symbolic-links = 0
log-error = /data/app/mysql/3307/logs/mysqld3307.log
pid-file = /data/app/mysql/3307/mysqld3307.pid
 
# 备份mysql启动服务文件
[root@mysql-multi ~]# mv /usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/mysqld.service_bak
# 新增mysqld3306.service启动文件
[root@mysql-multi ~]# cat /usr/lib/systemd/system/mysqld3306.service
[unit]
description=mysql server
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
type=forking
pidfile=/data/app/mysql/3306/mysqld3306.pid
timeoutsec=0
permissionsstartonly=true
#execstartpre=/usr/bin/mysqld_pre_systemd_3306 3306
execstart=/usr/sbin/mysqld --defaults-file=/etc/my3306.cnf --daemonize --pid-file=/data/app/mysql/3306/mysqld3306.pid $mysqld_opts
environmentfile=-/etc/sysconfig/mysql
limitnofile = 5000
restart=on-failure
restartpreventexitstatus=1
privatetmp=false
 
# 新增mysqld3307.service启动文件
[root@mysql-multi ~]# cp /usr/lib/systemd/system/mysqld3306.service /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~]# sed -i 's/3306/3307/g' /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~]# cat /usr/lib/systemd/system/mysqld3307.service
[unit]
description=mysql server
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
type=forking
pidfile=/data/app/mysql/3307/mysqld3307.pid
timeoutsec=0
permissionsstartonly=true
#execstartpre=/usr/bin/mysqld_pre_systemd_3307 3307
execstart=/usr/sbin/mysqld --defaults-file=/etc/my3307.cnf --daemonize --pid-file=/data/app/mysql/3307/mysqld3307.pid $mysqld_opts
environmentfile=-/etc/sysconfig/mysql
limitnofile = 5000
restart=on-failure
restartpreventexitstatus=1
privatetmp=false
 
# 初始化多实例3306，3307
[root@mysql-multi ~]# mysqld --defaults-file=/etc/my3306.cnf --initialize --user=mysql --datadir=/data/app/mysql/3306/data
[root@mysql-multi ~]# mysqld --defaults-file=/etc/my3307.cnf --initialize --user=mysql --datadir=/data/app/mysql/3307/data
 
# 启动多实例3306，3307
[root@mysql-multi ~]# systemctl start mysqld3306
[root@mysql-multi ~]# systemctl start mysqld3307
[root@mysql-multi ~]# netstat -nutpl | grep mysql
tcp6       0      0 :::3306                 :::*                    listen      128270/mysqld
tcp6       0      0 :::3307                 :::*                    listen      128328/mysqld
[root@mysql-multi ~]# ps -ef | grep mysql
mysql    128270      1  1 11:43 ?        00:00:00 /usr/sbin/mysqld --defaults-file=/etc/my3306.cnf --daemonize --pid-file=/data/app/mysql/3306/mysqld3306.pid
mysql    128328      1  1 11:43 ?        00:00:00 /usr/sbin/mysqld --defaults-file=/etc/my3307.cnf --daemonize --pid-file=/data/app/mysql/3307/mysqld3307.pid
root     128373    949  0 11:43 pts/0    00:00:00 grep --color=auto mysql
 
======================================== 【 再新增一mysql实例 】===================================================================
# 创建目录，设置属主属组
[root@mysql-multi ~]# mkdir -p /data/app/mysql/3308/{data,binlog,logs}
[root@mysql-multi ~]# chown -r mysql:mysql /data/app/mysql/3308
[root@mysql-multi ~]# ls -ld /data/app/mysql/3308
drwxr-xr-x. 5 mysql mysql 44 aug  5 14:45 /data/app/mysql/3308
 
# 新增配置文件my3308.cnf
[root@mysql-multi ~]# cp /etc/my3306.cnf /etc/my3308.cnf
[root@mysql-multi ~]# sed -i 's/3306/3308/g' /etc/my3308.cnf
[root@mysql-multi ~]# cat /etc/my3308.cnf
[mysqld]
user = mysql
port = 3308
server_id = 3308
datadir = /data/app/mysql/3308/data
socket = /data/app/mysql/3308/mysql3308.sock
symbolic-links = 0
log-error = /data/app/mysql/3308/logs/mysqld3308.log
pid-file = /data/app/mysql/3308/mysqld3308.pid
 
# 新增mysqld3308.service启动文件
[root@mysql-multi ~]# cp /usr/lib/systemd/system/mysqld3306.service /usr/lib/systemd/system/mysqld3308.service
[root@mysql-multi ~]# sed -i 's/3306/3308/g' /usr/lib/systemd/system/mysqld3308.service
[root@mysql-multi ~]# cat /usr/lib/systemd/system/mysqld3308.service
[unit]
description=mysql server 3308
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
type=forking
pidfile=/data/app/mysql/3308/mysqld3308.pid
timeoutsec=0
permissionsstartonly=true
#execstartpre=/usr/bin/mysqld_pre_systemd_3308 3308
execstart=/usr/sbin/mysqld --defaults-file=/etc/my3308.cnf --daemonize --pid-file=/data/app/mysql/3308/mysqld3308.pid $mysqld_opts
environmentfile=-/etc/sysconfig/mysql
limitnofile = 5000
restart=on-failure
restartpreventexitstatus=1
privatetmp=false
 
# 初始化多实例3308
[root@mysql-multi ~]# mysqld --defaults-file=/etc/my3308.cnf --initialize --user=mysql --datadir=/data/app/mysql/3308/data
 
# 启动多实例3308
[root@mysql-multi ~]# systemctl start mysqld3308
[root@mysql-multi ~]# netstat -nutpl | grep mysql
tcp6       0      0 :::3306                 :::*                    listen      5062/mysqld
tcp6       0      0 :::3307                 :::*                    listen      5098/mysqld
tcp6       0      0 :::3308                 :::*                    listen      5189/mysqld
[root@mysql-multi ~]# ps -ef |grep mysql
mysql      5062      1  0 14:43 ?        00:00:01 /usr/sbin/mysqld --defaults-file=/etc/my3306.cnf --daemonize --pid-file=/data/app/mysql/3306/mysqld3306.pid
mysql      5098      1  0 14:44 ?        00:00:01 /usr/sbin/mysqld --defaults-file=/etc/my3307.cnf --daemonize --pid-file=/data/app/mysql/3307/mysqld3307.pid
mysql      5189      1  4 14:57 ?        00:00:01 /usr/sbin/mysqld --defaults-file=/etc/my3308.cnf --daemonize --pid-file=/data/app/mysql/3308/mysqld3308.pid

源码编译安装方式如下：

　　MySQL下载：https://downloads.mysql.com/archives/community

　　Boost下载：https://sourceforge.net/projects/boost/files/boost/1.59.0

　　MySQL多实例：https://dev.mysql.com/doc/refman/5.7/en/multiple-servers.html

Linux下MySQL多实例部署及安装指南

?
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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
# 依赖包和编译软件
[root@mysql-multi ~]# yum install -y cmake make gcc gcc-c++ openssl openssl-devel ncurses ncurses-devel libaio-devel wget lrzsz tree
[root@mysql-multi ~]# rpm -qa ncurses-devel libaio-devel
libaio-devel-0.3.109-13.el7.x86_64
ncurses-devel-5.9-14.20130511.el7_4.x86_64
 
# 下载上传源码包并解压编译安装（最好内存>8g）
ls[root@mysql-multi ~]# ls
boost_1_59_0.tar.gz  mysql-boost-5.7.34.tar.gz
[root@mysql-multi ~]# tar xf boost_1_59_0.tar.gz
[root@mysql-multi ~]# tar xf mysql-boost-5.7.34.tar.gz
[root@mysql-multi ~]# ls
boost_1_59_0  boost_1_59_0.tar.gz  mysql-5.7.34  mysql-boost-5.7.34.tar.gz
[root@mysql-multi ~]# cd mysql-5.7.34/
[root@mysql-multi mysql-5.7.34]# cmake . -dcmake_install_prefix=/usr/local/mysql -ddefault_charset=utf8mb4 \
-denabled_local_infile=1 -dwith_systemd=1 -dwith_boost=/root/boost_1_59_0 -dextra_charsets=all
......
......最末尾显示如下内容，则完成
-- cmake_shared_linker_flags
-- configuring done
-- generating done
-- build files have been written to: /root/mysql-5.7.34
 
选项说明：
-dcmake_install_prefix=/usr/local/mysql　　　　# mysql安装目录
-ddefault_charset=utf8mb4 　　　　　　　　　　　 # 数据库默认字符编码
-denabled_local_infile=1 　　　　　　　　　　　　# 允许从本文件导入数据
-dwith_systemd=1　　　　　　　　　　　　　　　　　# 提供systemd脚本
-dwith_boost=/root/boost_1_59_0 　　　　　　　　# boost源路径
-dextra_charsets=all　　　　　　　　　　　　　　　# 安装所有字符集
 
[root@mysql-multi mysql-5.7.34]#make -j 4 && make install　　　　# make -j 4 表示用cpu4核心同时进行编译：cat /proc/cpuinfo|grep "processor"|wc -l
......
......最末尾显示如下内容，则完成
-- up-to-date: /usr/local/app/mysql/mysql-test/mysql-test-run
-- installing: /usr/local/app/mysql/mysql-test/lib/my/safeprocess/my_safe_process
-- up-to-date: /usr/local/app/mysql/mysql-test/lib/my/safeprocess/my_safe_process
-- installing: /usr/local/app/mysql/mysql-test/lib/my/safeprocess/base.pm
-- installing: /usr/local/app/mysql/support-files/mysqld_multi.server
-- installing: /usr/local/app/mysql/support-files/mysql-log-rotate
-- installing: /usr/local/app/mysql/support-files/magic
-- installing: /usr/local/app/mysql/share/aclocal/mysql.m4
-- installing: /usr/local/app/mysql/support-files/mysql.server
 
# 创建实例目录
[root@mysql-multi ~]# mkdir -p /data/app/mysql/{3306,3307}/{data,binlog,logs}
[root@mysql-multi ~]# tree /data/app/mysql
/data/app/mysql
├── 3306
│   ├── binlog
│   ├── data
│   └── logs
└── 3307
    ├── binlog
    ├── data
    └── logs
 
# 创建用户,设置目录属主属组
[root@mysql-multi ~]# useradd -m -r -s /sbin/nologin mysql
[root@mysql-multi ~]# chown -r mysql:mysql /data/app/mysql
[root@mysql-multi ~]# ls -ld /data/app/mysql
drwxr-xr-x. 4 mysql mysql 30 aug  5 12:17 /data/app/mysql
 
# 新增配置文件my3306.cnf
[root@mysql-multi ~]# mv /etc/my.cnf /etc/my.cnf_bak
[root@mysql-multi ~]# cat /etc/my3306.cnf
[mysqld]
user = mysql
port = 3306
server_id = 3306
basedir = /usr/local/mysql
datadir = /data/app/mysql/3306/data
socket = /data/app/mysql/3306/mysql3306.sock
symbolic-links = 0
log-error = /data/app/mysql/3306/logs/mysqld3306.log
pid-file = /data/app/mysql/3306/mysqld3306.pid
character_set_server = utf8
default-storage-engine = innodb
 
# 新增配置文件my3307.cnf
[root@mysql-multi ~]# cp /etc/my3306.cnf /etc/my3307.cnf
[root@mysql-multi ~]# sed -i 's/3306/3307/g' /etc/my3307.cnf
[root@mysql-multi ~]# cat /etc/my3307.cnf
[mysqld]
user = mysql
port = 3307
server_id = 3307
basedir = /usr/local/mysql
datadir = /data/app/mysql/3307/data
socket = /data/app/mysql/3307/mysql3307.sock
symbolic-links = 0
log-error = /data/app/mysql/3307/logs/mysqld3307.log
pid-file = /data/app/mysql/3307/mysqld3307.pid
character_set_server = utf8
default-storage-engine = innodb
 
# 安装后规范化操作（设置环境变量、输出头文件和库文件、设置man路径）
[root@mysql-multi ~]# echo "export path=/usr/local/mysql/bin:$path" >> /etc/profile.d/mysql.sh
[root@mysql-multi ~]# chmod +x /etc/profile.d/mysql.sh
[root@mysql-multi ~]# source /etc/profile.d/mysql.sh
[root@mysql-multi ~]# echo "manpath /usr/local/mysql/man" >>/etc/man.config
[root@mysql-multi ~]# echo "/usr/local/mysql/lib" > /etc/ld.so.conf.d/mysql.conf
[root@mysql-multi ~]# ldconfig
[root@mysql-multi ~]# ln -s /usr/local/mysql/include /usr/include/mysql
 
# 新增实例3306，3307 systemd方式
[root@mysql-multi ~]# cat /usr/lib/systemd/system/mysqld3306.service
[unit]
description=mysql server
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
type=forking
pidfile=/data/app/mysql/3306/mysqld3306.pid
timeoutsec=0
permissionsstartonly=true
#execstartpre=/usr/bin/mysqld_pre_systemd_3306 3306
execstart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --daemonize --pid-file=/data/app/mysql/3306/mysqld3306.pid $mysqld_opts
environmentfile=-/etc/sysconfig/mysql
limitnofile = 5000
restart=on-failure
restartpreventexitstatus=1
privatetmp=false
 
[root@mysql-multi ~]# cp /usr/lib/systemd/system/mysqld3306.service /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~]# sed -i 's/3306/3307/g' /usr/lib/systemd/system/mysqld3307.service
[root@mysql-multi ~]# cat /usr/lib/systemd/system/mysqld3307.service
[unit]
description=mysql server
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
type=forking
pidfile=/data/app/mysql/3307/mysqld3307.pid
timeoutsec=0
permissionsstartonly=true
#execstartpre=/usr/bin/mysqld_pre_systemd_3307 3307
execstart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --daemonize --pid-file=/data/app/mysql/3307/mysqld3307.pid $mysqld_opts
environmentfile=-/etc/sysconfig/mysql
limitnofile = 5000
restart=on-failure
restartpreventexitstatus=1
privatetmp=false
 
# 初始化实例3306，3307
[root@mysql-multi ~]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --initialize --basedir=/usr/local/mysql --user=mysql --datadir=/data/app/mysql/3306/data
[root@mysql-multi ~]# /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --initialize --basedir=/usr/local/mysql --user=mysql --datadir=/data/app/mysql/3307/data
 
# 启动实例3306，3307服务 （start|stop|restart|status）
[root@mysql-multi ~]# systemctl start mysqld3306
[root@mysql-multi ~]# systemctl start mysqld3307
[root@mysql-multi ~]# netstat -nuptl | grep mysql
tcp6       0      0 :::3306                 :::*                    listen      27165/mysqld
tcp6       0      0 :::3307                 :::*                    listen      27201/mysqld
[root@mysql-multi ~]# ps -ef | grep mysql
mysql     27165      1  2 17:03 ?        00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3306.cnf --daemonize --pid-file=/data/app/mysql/3306/mysqld3306.pid
mysql     27201      1  2 17:03 ?        00:00:00 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my3307.cnf --daemonize --pid-file=/data/app/mysql/3307/mysqld3307.pid

修改mysql实例密码并测试登录。

?
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
# 获取实例初始密码
[root@mysql-multi ~]# grep 'temporary password' /data/app/mysql/3306/logs/mysqld3306.log
2021-08-05t08:52:37.904630z 1 [note] a temporary password is generated for root@localhost: ,&yrslryq3ll
 
[root@mysql-multi ~]# grep 'temporary password' /data/app/mysql/3307/logs/mysqld3307.log
2021-08-05t08:52:48.082526z 1 [note] a temporary password is generated for root@localhost: ovxku,su=4o1
 
# 修改实例密码
[root@mysql-multi ~]# mysqladmin -p -s /data/app/mysql/3306/mysql3306.sock password
enter password:　　# 输入初始密码
new password:　　　# 输入新密码　　123456
confirm new password:　　# 再次输入新密码　　123456
 
--------------------------------------------------------------------------------------
# 或
shell> mysql -uroot -p /data/app/mysql/3306/mysql3306.sock
mysql> alter user 'root'@'localhost' identified by '123456';
--------------------------------------------------------------------------------------
 
[root@mysql-multi ~]# mysqladmin -p -s /data/app/mysql/3306/mysql3307.sock password
enter password:　　# 输入初始密码
new password:　　　# 输入新密码　　654321
confirm new password:　　# 再次输入新密码　　654321
 
# 测试登录
[root@mysql-multi ~]# mysql -uroot -p -s /data/app/mysql/3306/mysql3306.sock
enter password:　　# 输入密码　　123456
[root@mysql-multi ~]# mysql -uroot -p -s /data/app/mysql/3307/mysql3307.sock
enter password:　　# 输入密码　　654321
 
# 停止实例
[root@mysql-multi ~]# mysqladmin -uroot -p -s /data/app/mysql/3306/mysql3306.sock shutdown
enter password:　　# 输入密码　　123456
[root@mysql-multi ~]# mysqladmin -uroot -p -s /data/app/mysql/3307/mysql3307.sock shutdown
enter password:　　# 输入密码　　654321

至此，mysql多实例已经实现！

到此这篇关于linux下mysql多实例部署记录的文章就介绍到这了,更多相关mysql多实例部署内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文链接：https://www.cnblogs.com/zhangwencheng/p/15045074.html

标签：

相关文章