【前言】测试环境中经常需要多台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 serveroption(see documentationformore 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 weassumethatthisis the first time thatthisserver has been started.Generating a new UUID:12d65efe-6d58-11e7-9d39-000c29a755d3.

2017-07-20T14:31:09.873982Z 0[Warning]Gtid table isnotready to be used.Table'mysql.gtid_executed'cannot be opened.

2017-07-20T14:31:09.923044Z 1[Note]A temporary password is generatedforroot@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 serveroption(see documentationformore 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 weassumethatthisis the first time thatthisserver has been started.Generating a new UUID:48106897-6d58-11e7-a1b9-000c29a755d3.

2017-07-20T14:32:39.172770Z 0[Warning]Gtid table isnotready to be used.Table'mysql.gtid_executed'cannot be opened.

2017-07-20T14:32:39.212540Z 1[Note]A temporary password is generatedforroot@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 serveroption(see documentationformore 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 weassumethatthisis the first time thatthisserver has been started.Generating a new UUID:69acd736-6d58-11e7-a436-000c29a755d3.

2017-07-20T14:33:35.562713Z 0[Warning]Gtid table isnotready to be used.Table'mysql.gtid_executed'cannot be opened.

2017-07-20T14:33:35.607109Z 1[Note]A temporary password is generatedforroot@localhost:sDXL5hh71I>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 serveroption(see documentationformore 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 weassumethatthisis the first time thatthisserver has been started.Generating a new UUID:82ea694b-6d58-11e7-a566-000c29a755d3.

2017-07-20T14:34:17.908498Z 0[Warning]Gtid table isnotready to be used.Table'mysql.gtid_executed'cannot be opened.

2017-07-20T14:34:17.923365Z 1[Note]A temporary password is generatedforroot@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

Enterpassword:输入默认密码

New password:

Confirm new password:

Warning:Since password will be sent to serverinplain 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 executingthisstatement.

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

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

创建mysql关闭的用户
点击(此处)折叠或打开

[root@db01 mysql3307]#mysql-u root-p-P 3310-S /tmp/mysql.sock10

Enterpassword:

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 #关闭单台数据库