转载连接 http://blog.itpub.net/12679300/viewspace-2144619/

【前言】测试环境中经常需要多台mysql数据库来进行各种环境和场景的模拟，由于测试环境中的资源一般都是比较有限的，也就需要在一台服务器上面搭建多个mysql数据库来完成测试的需求。本文档介绍在Centos6.4的环境中安装多个mysql 5.7数据库的操作方法。

【1】mysql数据库软件的安装

mysql数据库软件官方提供了三种的安装文件和方法，如下：

RPM方式安装

二进制方式安装

源码编译安装

虽然源码安装的方式比较麻烦，但是个人还是习惯用源码的方案安装，mysql5.7的安装跟早起5.5的安装方式和步骤也几乎一样所以这里就不再说明了。

【2】创建多个数据库

软件的安装完成其实都是一样的，就是创建数据库的时候有点不一样。

2.1 进行数据文件目录的规划，本环境安装了5个数据库，在/data下面创建5个文件夹，并用端口号区分

点击(

此处

)折叠或打开

[

root@db01 data

]

#

pwd

/data

[

root@db01 data

]

#

ll

drwxr

–

xr

–

x

.

6 mysql mysql 4096 7月 8 05

:

00 mysql3306

drwxr

–

xr

–

x

.

6 mysql mysql 4096 7月 20 21

:

46 mysql3307

drwxr

–

xr

–

x

.

7 mysql mysql 4096 7月 20 21

:

46 mysql3308

drwxr

–

xr

–

x

.

6 mysql mysql 4096 7月 20 21

:

46 mysql3309

drwxr

–

xr

–

x

.

5 mysql mysql 4096 7月 20 21

:

46 mysql3310

2.2 进行参数文件的配置

点击(

此处

)折叠或打开

[

root@db01 data

]

#

vi /etc/my

.

cnf

[

mysqld_multi

]

mysqld

=

/usr/

local

/

mysql/bin/mysqld_safe

#basedir

=

/usr/

local

/

mysql

mysqladmin

=

/usr/

local

/

mysql/bin/mysqladmin

user

=

mysql

pass

=

mysql

#password

=

mysql

#bindir

=

/usr/

local

/

mysql/bin

[

mysqld7

]

port

=

3307

socket

=

/tmp/mysql

.

sock7

pid

–

file

=

/data/mysql3307/hostname

.

pid7

datadir

=

/data/mysql3307

user

=

mysql

basedir

=

/usr/

local

/

mysql

log

–

bin

=

/data/mysql3307/bin

–

log

server_id

=

7

gtid_mode

=

ON

enforce

–

gtid

–

consistency

=

TRUE

log_slave_updates

=

ON

#skip

–

grant

–

tables

[

mysqld8

]

socket

=

/tmp/mysql

.

sock8

port

=

3308

pid

–

file

=

/data/mysql3308/hostname

.

pid8

datadir

=

/data/mysql3308

user

=

mysql

basedir

=

/usr/

local

/

mysql

#skip

–

grant

–

tables

log

–

bin

=

/data/mysql3308/bin

–

log

server_id

=

8

gtid_mode

=

ON

enforce

–

gtid

–

consistency

=

TRUE

log_slave_updates

=

ON

[

mysqld9

]

socket

=

/tmp/mysql

.

sock9

port

=

3309

pid

–

file

=

/data/mysql3309/hostname

.

pid9

datadir

=

/data/mysql3309

user

=

mysql

basedir

=

/usr/

local

/

mysql

#skip

–

grant

–

tables

log

–

bin

=

/data/mysql3309/bin

–

log

server_id

=

9

gtid_mode

=

ON

enforce

–

gtid

–

consistency

=

TRUE

log_slave_updates

=

ON

[

mysqld10

]

socket

=

/tmp/mysql

.

sock10

port

=

3310

pid

–

file

=

/data/mysql3310/hostname

.

pid10

datadir

=

/data/mysql3310

user

=

mysql

basedir

=

/usr/

local

/

mysql

#skip

–

grant

–

tables

log

–

bin

=

/data/mysql3310/bin

–

log

server_id

=

10

gtid_mode

=

ON

enforce

–

gtid

–

consistency

=

TRUE

log_slave_updates

=

ON

2.3 创建数据库，创建的过程中需要记录数据库的初始密码

依次创建其他数据库，记录默认的随机密码

mysql3307的安装

点击(

此处

)折叠或打开

[

root@db01 data

]

#

/usr/

local

/

mysql/bin/mysqld

–

–

initialize

–

–

datadir

=

/

data/mysql3307

2017

–

07

–

20T14

:

31

:

01

.

890314Z 0

[

Warning

]

TIMESTAMP with implicit DEFAULT value is deprecated

.

Please use

–

–

explicit_defaults_for_timestamp server

option

(

see documentation

for

more details

)

.

2017

–

07

–

20T14

:

31

:

09

.

081679Z 0

[

Warning

]

InnoDB

:

New log files created

,

LSN

=

45790

2017

–

07

–

20T14

:

31

:

09

.

626403Z 0

[

Warning

]

InnoDB

:

Creating foreign key constraint system tables

.

2017

–

07

–

20T14

:

31

:

09

.

867983Z 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

:

12d65efe

–

6d58

–

11e7

–

9d39

–

000c29a755d3

.

2017

–

07

–

20T14

:

31

:

09

.

873982Z 0

[

Warning

]

Gtid table is

not

ready to be used

.

Table

‘mysql.gtid_executed’

cannot be opened

.

2017

–

07

–

20T14

:

31

:

09

.

923044Z 1

[

Note

]

A temporary password is generated

for

root@localhost

:

wAQ

*

p

>

.

O

:

4

,

p

mysql3308的安装

[

root@db01 mysql3307

]

#

cat auto

.

cnf

[

auto

]

server

–

uuid

=

12d65efe

–

6d58

–

11e7

–

9d39

–

000c29a755d3

[

root@db01 mysql3307

]

#

/usr/

local

/

mysql/bin/mysqld

–

–

initialize

–

–

datadir

=

/

data/mysql3308

2017

–

07

–

20T14

:

32

:

36

.

027225Z 0

[

Warning

]

TIMESTAMP with implicit DEFAULT value is deprecated

.

Please use

–

–

explicit_defaults_for_timestamp server

option

(

see documentation

for

more details

)

.

2017

–

07

–

20T14

:

32

:

38

.

601806Z 0

[

Warning

]

InnoDB

:

New log files created

,

LSN

=

45790

2017

–

07

–

20T14

:

32

:

39

.

071963Z 0

[

Warning

]

InnoDB

:

Creating foreign key constraint system tables

.

2017

–

07

–

20T14

:

32

:

39

.

167438Z 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

:

48106897

–

6d58

–

11e7

–

a1b9

–

000c29a755d3

.

2017

–

07

–

20T14

:

32

:

39

.

172770Z 0

[

Warning

]

Gtid table is

not

ready to be used

.

Table

‘mysql.gtid_executed’

cannot be opened

.

2017

–

07

–

20T14

:

32

:

39

.

212540Z 1

[

Note

]

A temporary password is generated

for

root@localhost

:

Ak3XwQpb

=

ta0

mysql3309的安装

[

root@db01 mysql3307

]

#

/usr/

local

/

mysql/bin/mysqld

–

–

initialize

–

–

datadir

=

/

data/mysql3309
2017

–

07

–

20T14

:

33

:

32

.

801680Z 0

[

Warning

]

TIMESTAMP with implicit DEFAULT value is deprecated

.

Please use

–

–

explicit_defaults_for_timestamp server

option

(

see documentation

for

more details

)

.
2017

–

07

–

20T14

:

33

:

35

.

102950Z 0

[

Warning

]

InnoDB

:

New log files created

,

LSN

=

45790
2017

–

07

–

20T14

:

33

:

35

.

443411Z 0

[

Warning

]

InnoDB

:

Creating foreign key constraint system tables

.
2017

–

07

–

20T14

:

33

:

35

.

557451Z 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

:

69acd736

–

6d58

–

11e7

–

a436

–

000c29a755d3

.
2017

–

07

–

20T14

:

33

:

35

.

562713Z 0

[

Warning

]

Gtid table is

not

ready to be used

.

Table

‘mysql.gtid_executed’

cannot be opened

.
2017

–

07

–

20T14

:

33

:

35

.

607109Z 1

[

Note

]

A temporary password is generated

for

root@localhost

:

sDXL5hh71I

>

R

mysql3309的安装

[

root@db01 mysql3307

]

#

/usr/

local

/

mysql/bin/mysqld

–

–

initialize

–

–

datadir

=

/

data/mysql3309

2017

–

07

–

20T14

:

33

:

32

.

801680Z 0

[

Warning

]

TIMESTAMP with implicit DEFAULT value is deprecated

.

Please use

–

–

explicit_defaults_for_timestamp server

option

(

see documentation

for

more details

)

.

2017

–

07

–

20T14

:

33

:

35

.

102950Z 0

[

Warning

]

InnoDB

:

New log files created

,

LSN

=

45790

2017

–

07

–

20T14

:

33

:

35

.

443411Z 0

[

Warning

]

InnoDB

:

Creating foreign key constraint system tables

.

2017

–

07

–

20T14

:

33

:

35

.

557451Z 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

:

69acd736

–

6d58

–

11e7

–

a436

–

000c29a755d3

.

2017

–

07

–

20T14

:

33

:

35

.

562713Z 0

[

Warning

]

Gtid table is

not

ready to be used

.

Table

‘mysql.gtid_executed’

cannot be opened

.

2017

–

07

–

20T14

:

33

:

35

.

607109Z 1

[

Note

]

A temporary password is generated

for

root@localhost

:

sDXL5hh71I

>

R

数据库mysql3310

点击(

此处

)折叠或打开

[

root@db01 mysql3307

]

#

/usr/

local

/

mysql/bin/mysqld

–

–

initi开发云主机域名alize

–

–

datadir

=

/

data/mysql3310
2017

–

07

–

20T14

:

34

:

14

.

881243Z 0

[

Warning

]

TIMESTAMP with implicit DEFAULT value is deprecated

.

Please use

–

–

explicit_defaults_for_timestamp server

option

(

see documentation

for

more details

)

.
2017

–

07

–

20T14

:

34

:

17

.

227399Z 0

[

Warning

]

InnoDB

:

New log files created

,

LSN

=

45790
2017

–

07

–

20T14

:

34

:

17

.

744012Z 0

[

Warning

]

InnoDB

:

Creating foreign key constraint system tables

.
2017

–

07

–

20T14

:

34

:

17

.

904000Z 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

:

82ea694b

–

6d58

–

11e7

–

a566

–

000c29a755d3

.
2017

–

07

–

20T14

:

34

:

17

.

908498Z 0

[

Warning

]

Gtid table is

not

ready to be used

.

Table

‘mysql.gtid_executed’

cannot be opened

.
2017

–

07

–

20T14

:

34

:

17

.

923365Z 1

[

Note

]

A temporary password is generated

for

root@localhost

:

Rq4

*

Teq#l

;

Ve

点击(

此处

)折叠或打开

[

root@db01 mysql3307

]

#

/usr/

local

/

mysql/bin/mysqld

–

–

initialize

–

–

datadir

=

/

data/mysql3310

2017

–

07

–

20T14

:

34

:

14

.

881243Z 0

[

Warning

]

TIMESTAMP with implicit DEFAULT value is deprecated

.

Please use

–

–

explicit_defaults_for_timestamp server

option

(

see documentation

for

more details

)

.

2017

–

07

–

20T14

:

34

:

17

.

227399Z 0

[

Warning

]

InnoDB

:

New log files created

,

LSN

=

45790

2017

–

07

–

20T14

:

34

:

17

.

744012Z 0

[

Warning

]

InnoDB

:

Creating foreign key constraint system tables

.

2017

–

07

–

20T14

:

34

:

17

.

904000Z 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

:

82ea694b

–

6d58

–

11e7

–

a566

–

000c29a755d3

.

2017

–

07

–

20T14

:

34

:

17

.

908498Z 0

[

Warning

]

Gtid table is

not

ready to be used

.

Table

‘mysql.gtid_executed’

cannot be opened

.

2017

–

07

–

20T14

:

34

:

17

.

923365Z 1

[

Note

]

A temporary password is generated

for

root@localhost

:

Rq4

*

Teq#l

;

Ve

【3】

修改数据库的初始密码

先启动数据库

[

root@db01 mysql3307

]

#

/usr/

local

/

mysql/bin/mysqld_multi start

修改默认密码

[

root@db01 mysql3307

]

#

mysqladmin

–

u root

–

p

–

P 3307

–

S /tmp/mysql

.

sock7 password

Enter

password

:

输入默认密码

New password

:

Confirm new password

:

Warning

:

Since password will be sent to server

in

plain text

,

use ssl connection to ensure password safety

.

登录数据库

[root@db01 mysql3307]# mysql -P 3307 -S /tmp/mysql.sock7 -u root

–

p

用默认的密码登录会有以下的提示信息：

点击(

此处

)折叠或打开

mysql

>

show databases

;

ERROR 1820

(

HY000

)

:

You must reset your password using ALTER USER statement before executing

this

statement

.

【4】设置mysqld_multi stop的关闭权限

默认的情况下，不能通过mysqld_multi关闭数据库，需要进行额外的设置，步骤如下：

创建mysql关闭的用户

点击(

此处

)折叠或打开

[

root@db01 mysql3307

]

#

mysql

–

u root

–

p

–

P 3310

–

S /tmp/mysql

.

sock10
Enter

password

:
mysql

>

grant shutdown on

*

.

*

to

‘mysql’

@

‘localhost’

identified by

‘mysql’

;
mysql

>

flush privileges

设置参数文件的账户

点击(

此处

)折叠或打开

[

root@db01 mysql3307

]

#

cat /etc/my

.

cnf
[

mysqld_multi

]
mysqld

=

/usr/

local

/

mysql/bin/mysqld_safe
#basedir

=

/usr/

local

/

mysql
mysqladmin

=

/usr/

local

/

mysql/bin/mysqladmin
user

=

mysql
pass

=

mysql

[

root@db01 mysql3307

]

#

mysql

–

u root

–

p

–

P 3310

–

S /tmp/mysql

.

sock10

Enter

password

:

mysql

>

grant shutdown on

*

.

*

to

‘mysql’

@

‘localhost’

identified by

‘mysql’

;

mysql

>

flush privileges

[

root@db01 mysql3307

]

#

cat /etc/my

.

cnf

[

mysqld_multi

]

mysqld

=

/usr/

local

/

mysql/bin/mysqld_safe

#basedir

=

/usr/

local

/

mysql

mysqladmin

=

/usr/

local

/

mysql/bin/mysqladmin

user

=

mysql

pass

=

mysql

通过以上的操作，便完成了在单台服务器上面安装多个mysql数据库的操作；

附加：常用的操作语句

/usr/local/mysql/bin/mysqld_multi start #启动所有的数据库

/usr/local/mysql/bin/mysqld_multi start 7 #启动单台数据库

/usr/local/mysql/bin/mysqld_multi stop #关闭所有的数据库

/usr/local/mysql/bin/mysqld_multi stop 7 #关闭单台数据库

相关推荐: 随笔：MySQL setup_instruments中关于部分信息不能修改

朋友告诉我如下操作不能修改我测试发现所有memory/performance_schema/* 的值都不能更改，但是其他值可以更改。。8.0.17依然如此。既然不能修改则跟一下update接口，我一共跟踪了：几个接口。查看table_setup_instrum…