5.数据库多实例创建

声明：先使用CentOS7中yum源自带的mariadb版本安装，再使用其相关文件进行多实例配置
data:存放数据 bin:存放程序 log:存放日志
socket:存放套接字 pid:存放pid etc:存放配置文件

1.创建多实例数据文件目录

[root@centos7min ~]# mkdir -p /mysql/{3306,3307,3308}/{data,bin,log,socket,pid,etc}
[root@centos7min ~]# tree /mysql
/mysql
├── 3306
│   └── {data,bin,log,socket,pid,etc
├── 3307
│   └── {data,bin,log,socket,pid,etc
└── 3308
    └── {data,bin,log,socket,pid,etc

2.准备数据库初始文件

[root@centos7min ~]# which mysql_install_db
/usr/bin/mysql_install_db
[root@centos7min ~]# mysql_install_db --datadir=/mysql/3306/data/ --user=mysql 
[root@centos7min ~]# mysql_install_db --datadir=/mysql/3307/data/ --user=mysql 
[root@centos7min ~]# mysql_install_db --datadir=/mysql/3308/data/ --user=mysql 
[root@centos7min ~]# chown -R mysql.mysql /mysql/

3.创建配置文件并配置环境变量

[root@centos7min ~]# cp /etc/my.cnf /mysql/3306/etc
[root@centos7min ~]# vim /mysql/3306/etc/my.cnf 
[mysqld]
port=3306
datadir=/mysql/3306/data
socket=/mysql/3306/socket/mysql.sock

[mysqld_safe]
log-error=/mysql/3306/log/mariadb.log
pid-file=/mysql/3306/pid/mariadb.pid

[root@centos7min ~]# cp /mysql/3306/etc/my.cnf /mysql/3307/etc/
[root@centos7min ~]# cp /mysql/3306/etc/my.cnf /mysql/3308/etc/
[root@centos7min ~]# sed -i "s/3306/3307/g" /mysql/3307/etc/my.cnf 
[root@centos7min ~]# sed -i "s/3306/3308/g" /mysql/3308/etc/my.cnf 

4.数据库启动脚本

[root@centos7min ~]# cat /mysql/3306/bin/mysqld
#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd="centos"
cmd_path="/usr/bin"   命令的路径，数据库服务器软件的路径，mysqld_safe的路径，因为使用yum安装所以为此路径，二进制或源码编译安装，路径可能不一致
mysql_basedir="/mysql"
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
 
[root@centos7min ~]# chmod +x /mysql/3306/bin/mysqld
[root@centos7min ~]# cp /mysql/3306/bin/mysqld /mysql/3307/bin/
[root@centos7min ~]# cp /mysql/3306/bin/mysqld /mysql/3308/bin/
[root@centos7min ~]# sed -i 's/3306/3307/' /mysql/3307/bin/mysqld 
[root@centos7min ~]# sed -i 's/3306/3308/' /mysql/3308/bin/mysqld 
[root@centos7min ~]# /mysql/3306/bin/mysqld start
[root@centos7min ~]# /mysql/3307/bin/mysqld start
[root@centos7min ~]# /mysql/3308/bin/mysqld start
[root@centos7min ~]# ss -ntlu
Netid State      Recv-Q Send-Q              Local Address:Port                             Peer Address:Port                               
tcp   LISTEN     0      50                              *:3306                                        *:*                  
tcp   LISTEN     0      50                              *:3307                                        *:*                  
tcp   LISTEN     0      50                              *:3308                                        *:*


**使用mysqladmin修改密码与配置文件中一致，否则会无法使用脚本关闭服务**
[root@centos7min ~]# mysqladmin -S /mysql/3306/socket/mysql.sock password centos
[root@centos7min ~]# mysqladmin -S /mysql/3307/socket/mysql.sock password centos
[root@centos7min ~]# mysqladmin -S /mysql/3308/socket/mysql.sock password centos

5.测试

[root@centos7min ~]# mysql -S /mysql/3306/socket/mysql.sock -pcentos
MariaDB [(none)]> status
Server:         MariaDB
Server version:     5.5.65-MariaDB MariaDB Server
Protocol version:   10
UNIX socket:        /mysql/3306/socket/mysql.sock

MariaDB [(none)]> 
[root@centos7min ~]# /mysql/3306/bin/mysqld stop
[root@centos7min ~]# /mysql/3307/bin/mysqld stop
[root@centos7min ~]# /mysql/3308/bin/mysqld stop
[root@centos7min ~]# ss -ntlu   
**验证成功**