【前言】测试环境中经常需要多台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
:
sDXL5hh61I
>
R
数据库mysql3310
点击(
此处
)折叠或打开
[
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
设置参数文件的账户
点击(
此处
)折叠或打开
通过以上的操作，便完成了在单台服务器上面安装多个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 #关闭单台数据库
