MySQL的多实例配置

​在一台物理机中需要多个测试环境，那么就需要用到了搭建数据库的多个实例，多个实例的意思就是运行多份程序，实例与实例之间没有影响。要注意监听的端口需要不同。

环境：CentOS7.4

实验预期：在一台虚拟机上安装三套MariaDB数据库，数据库版本5.5

思路：三套配置文件（日志文件，配置文件，数据库文件），三个不同的端口

安装第一个实例

yum install mariadb-server

​ 1、创建运行的目录环境

[root@centos7 ~]#mkdir -p /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data,bin}

[root@centos7 ~]# chown -R mysql:mysql /mysqldb/

目录结构如下

[root@centos7 ~]# tree /mysqldb//mysqldb/├── 3306│   ├── bin│   ├── data│   ├── etc│   ├── log│   ├── pid│   └── socket├── 3307│   ├── bin│   ├── data│   ├── etc│   ├── log│   ├── pid│   └── socket└── 3308├── bin├── data├── etc├── log├── pid└── socket21 directories, 0 files

2、初始化数据库

[root@centos7 ~]# mysql_install_db --datadir=/mysqldb/3306/data/ --user=mysql[root@centos7 ~]# mysql_install_db --datadir=/mysqldb/3307/data/ --user=mysql [root@centos7~]# mysql_install_db --datadir=/mysqldb/3308/data/ --user=mysql

3、创建各自配置文件

cp  /etc/my.cnf  /mysqldb/3306/etc/

4、修改各自配置文件

vim my.cnf[mysqld]port=3306datadir=/mysqldb/3306/datasocket=/mysqldb/3306/socket/mysql.sock[mysqld_safe]log-error=/mysqldb/3306/log/mariadb.logpid-file=/mysqldb/3306/pid/mariadb.pid    #chown -R mysql:mysql /mysqldb #给目录添加权限

#!includefir /etc/my.cnf.d    添加此行注释参照3306配置文件，将3307,3308配置文件也生成cp /mysqldb/3306/etc/my.cnf /mysqldb/3307/etc/my.cnfcp /mysqldb/3306/etc/my.cnf /mysqldb/3308/etc/my.cnf ：%s /3306/3307/g 脚本搜索全局替换

5、准备启动服务脚本

[root@centos7 /mysqldb/3306/bin]# vim mysqld

#!/bin/bash

port=3306   #需要修改为当前实例的端口号

mysql_user="root"

mysql_pwd=""

cmd_path="/usr/bin"  #安装目录下的bin

mysql_basedir="/mysqldb"  #实例数据库文件所在目录

mysql_sock="${mysql_basedir}/${port}/socket/mysql.sock"

function_start_mysql()

{

if [ ! -e "$mysql_sock" ];then

printf "Starting MySQL...\n"

${cmd_path}/mysqld_safe --defaults-file=${mysql_basedir}/${port}/etc/my.cnf &> /dev/null &

else

printf "MySQL is running...\n"

exit

fi

}

function_stop_mysql()

{

if [ ! -e "$mysql_sock" ];then

printf "MySQL is stopped...\n"

exit

else

printf "Stoping MySQL...\n"

${cmd_path}/mysqladmin -u ${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown

fi

}

function_restart_mysql()

{

printf "Restarting MySQL...\n"

function_stop_mysql

sleep 2

function_start_mysql

}

case $1 in

start)

function_start_mysql

;;

stop)

function_stop_mysql

;;

restart)

function_restart_mysql

;;

*)

printf "Usage: ${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"

esac

​ 6、修改脚本文件权限，防止密码被别人看到

[root@centos7 ~]# chmod 700 /mysqldb/3306/bin/mysqld [root@centos7 ~]# chmod 700 /mysqldb/3307/bin/mysqld  [root@centos7 ~]# chmod 700 /mysqldb/3308/bin/mysqld

7、启动服务

[root@centos7 ~]# service mysqld stop  #保证自己原来的服务停止，释放3306端口[root@centos7 ~]# /mysqldb/3306/bin/mysqld start  #启动服务[root@centos7 ~]# /mysqldb/3307/bin/mysqld start[root@centos7 ~]# /mysqldb/3308/bin/mysqld start[root@centos7 ~]# ss -tnl  #如果看到三个实例监听的端口都打开后说明服务启动正常LISTEN 0 80 :::3306 :::*LISTEN 0 80 :::3307 :::*LISTEN 0 80 :::3308 :::*

8、连接测试

[root@centos7 ~]# mysql -S /mysqldb/3306/socket/mysql.sock  #使用-S指定套接字文件Server version: 10.2.15-MariaDB-log Source distributionMariaDB [(none)]> show variables like '%port';  #查看端口是否是3306+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| extra_port          | 0     || large_files_support | ON    || port                | 3306  || report_port         | 3306  |+---------------------+-------+4 rows in set (0.00 sec)[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock  #再连接测试一下3307和3308Server version: 10.2.15-MariaDB-log Source distributionMariaDB [(none)]> show variables like '%port';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| extra_port          | 0     || large_files_support | ON    || port                | 3307  || report_port         | 3307  |+---------------------+-------+4 rows in set (0.00 sec)[root@centos7 ~]# mysql -S /mysqldb/3308/socket/mysql.sockServer version: 10.2.15-MariaDB-log Source distributionMariaDB [(none)]> show variables like '%port';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| extra_port          | 0     || large_files_support | ON    || port                | 3308  || report_port         | 3308  |+---------------------+-------+4 rows in set (0.00 sec)

​多实例搭建成功！

9、使用这条命令来停止实例

[root@centos7 ~]# /mysqldb/3306/bin/mysqld stop

​ 10、最后一步：给root用户加个密码

[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock Server version: 10.2.15-MariaDB-log Source distributionMariaDB [(none)]> update mysql.user set password=PASSWORD("your_password") where user='root';Query OK, 4 rows affected (0.00 sec)MariaDB [(none)]> flush privileges;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> select user,host,password from mysql.user;+------+-----------+-------------------------------------------+| user | host      | password                                  |+------+-----------+-------------------------------------------+| root | localhost | *9E72259BA9214F692A85B240647C4D95B0F2E08B || root | centos7   | *9E72259BA9214F692A85B240647C4D95B0F2E08B || root | 127.0.0.1 | *9E72259BA9214F692A85B240647C4D95B0F2E08B || root | ::1       | *9E72259BA9214F692A85B240647C4D95B0F2E08B ||      | localhost |                                           ||      | centos7   |                                           |+------+-----------+-------------------------------------------+6 rows in set (0.00 sec)[root@centos7 ~]# mysql -S /mysqldb/3307/socket/mysql.sock -uroot -p'your_password' #指定密码，再次登录OK~