【前言】测试环境中经常需要多台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.3 创建数据库，创建的过程中需要记录数据库的初始密码
依次创建其他数据库，记录默认的随机密码
点击(此处)折叠或打开
|
- mysql3308的安装
|
- 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 #关闭单台数据库