MySQL的多實例配置

在一臺物理機中需要多個測試環境，那麼就需要用到了搭建資料庫的多個實例，多個實例的意思就是運行多份程式，實例與實例之間沒有影響。要註意監聽的埠需要不同。也就是在一臺主機上跑好多個mysql實例資料庫，每個資料庫自己管理自己獨立的資料庫文件。

準備環境：centos7.4，關閉防火牆，關閉SElinux，yum安裝實現多實例   yum install mariadb-server

1：創建運行的目錄環境

[root@centos7 ~]# mkdir /mysqldb/{3306,3307,3308}/{etc,socket,pid,log,data} -pv
[root@centos7 ~]# chown -R mysql.mysql /mysqldb/  指定文件夾的所有者，所屬組 ，這樣mysql對所有的文件夾起到控制的作用
[root@centos7 ~]# tree /mysqldb/

2：（創建資料庫）生成三個不用的資料庫文件放到各自的data文件夾中

[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、複製模板配置文件，併進行修改配置文件

[root@centos7 ~]# cp /etc/my.cnf /mysqldb/3306/etc/
[root@centos7 ~]# cp /etc/my.cnf /mysqldb/3307/etc/
[root@centos7 ~]# cp /etc/my.cnf /mysqldb/3308/etc/
[root@centos7 ~]# vim  /mysqldb/3308/etc/my.cnf 
[mysqld]
port=3308
datadir=/mysqldb/3308/data
socket=/mysqldb/3308/socket/mysql.sock
symbolic-links=0
[mysqld_safe]
log-error=/mysqldb/3308/log/mariadb.log
pid-file=/mysqldb/3308/pid/mariadb.pid
[root@centos7 ~]# cp /mysqldb/3308/etc/my.cnf  /mysqldb/3306/etc/my.cnf
[root@centos7 ~]# cp /mysqldb/3308/etc/my.cnf  /mysqldb/3307/etc/my.cnf
：%s /3308/3307/g 腳本搜索全局替換

4：準備啟動服務腳本

[root@centos7 ~]# vim mysql
#!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

[root@centos7 ~]# cp mysql /mysqldb/3306/
[root@centos7 ~]# cp mysql /mysqldb/3307/
[root@centos7 ~]# cp mysql /mysqldb/3308/

5：修改腳本許可權，安全保障，防止密碼被人看到

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

6：啟動服務

[root@centos7 /mysqldb]# service mysql stop  #保證自己原來的服務停止，釋放3306埠
[root@centos7 /mysqldb]#sh /mysqldb/3306/mysqld start
[root@centos7 /mysqldb]#sh /mysqldb/3307/mysqld start
[root@centos7 /mysqldb]#sh /mysqldb/3308/mysqld start   

#如果看到三個實例監聽的埠都打開後說明服務啟動正常

7:連接測試

[root@centos7 /mysqldb]# mysql -S /mysqldb/3308/socket/mysql.sock

8:​多實例搭建成功！以下命令可用來停止實例

[root@centos7 /mysqldb]# sh /mysqldb/3308/mysqld stop

9:給root加密碼

[root@centos7 /mysqldb]# mysql -S /mysqldb/3307/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.16-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statem
ent.
MariaDB [(none)]> grant all on *.* to root@localhost  IDENTIFIED BY '111111';
MariaDB [(none)]> use mysql;
Database changed
MariaDB [mysql]> select user,host from user; 

MariaDB [mysql]> select user,host,password from user; 
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *FD571203974BA9AFE270FE62151AE967ECA5E0AA |
| root | centos7   |                                           |
| root | 127.0.0.1 |                                           |
| root | ::1       |                                           |
|      | localhost |                                           |
|      | centos7   |                                           |
+------+-----------+-------------------------------------------+

[root@centos7 /mysqldb]# mysql -uroot -S /mysqldb/3307/socket/mysql.sock -p111111
#指定密碼，再次登錄