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

Mysql多实例就是在一台服务器上同时开启多个不同的服务端口(3306、3307),同时运行多个Mysql服务进程,这些服务进程通过不同的socket监听不同的服务端口来提供服务,这篇文章主要介绍了Linux下MySQL多实例部署记录,需要的朋友可以

什么是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

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/5940/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022-07-27 9:52:19
下一篇 2022-07-27 9:52:30

软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!