MySQL 多實例安裝

在生產中有時候會遇到一臺主機要運行多個MySQL伺服器----叫做多實例

下麵演示多實例的安裝

首先我們規划下

基於二進位安裝

1、創建mysql用戶

useradd -r -s /sbin/nologin -d /app/mysql -m mysql
#
#

2、解壓下載的二進位mariadb包

tar xvf mariadb-10.2.16-linux-x86_64.tar.gz -C /usr/local/
#
#

3、創建軟連接

cd /usr/local/
#
ln -s mariadb-10.2.16-linux-x86_64/ mysql
#

4、修改許可權

chown -R root.mysql /usr/local/mysql/
#
#

5、環境變數（少了這步也可）

vim /etc/profile.d/mysql.sh
# 
. /etc/profile.d/mysql.sh
#

如果你已經二進位安裝了mariadb包，可以從以下開始

指定資料庫位置並初始化資料庫

1、創建規劃圖上的目錄

mkdir /mysqldb/{3306,3307,3308}/{etc,bin,data,pid,socket,log} -pv
#
#

2、更改主目錄mysqldb的屬性

chown -R mysql.mysql /mysqldb/
#

3、3306埠

cd /usr/local/mysql/
#
scripts/mysql_install_db  --datadir=/mysqldb/3306/data --user=mysql
#

4、3307埠

scripts/mysql_install_db  --datadir=/mysqldb/3307/data --user=mysql
#

5、3308埠

scripts/mysql_install_db  --datadir=/mysqldb/3308/data --user=mysql
#

修改配置文件

1、複製配置文件

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

2、編輯配置文件

vim /mysqldb/3306/etc/my.cnf 
#

3、把上面的配置文件當模板傳送給3307、3308

cp /mysqldb/3306/etc/my.cnf  /mysqldb/3307/etc/
cp /mysqldb/3306/etc/my.cnf  /mysqldb/3308/etc/
#

4、更改07、08配置文件的埠

sed -i 's/3306/3307/g' /mysqldb/3307/etc/my.cnf 
sed -i 's/3306/3308/g' /mysqldb/3308/etc/my.cnf 
#

啟動程式

1、把事先編寫的啟動腳本複製到06、07、08的bin目錄下

cp /root/mysqld /mysqldb/3306/bin/
cp /root/mysqld /mysqldb/3307/bin/
cp /root/mysqld /mysqldb/3308/bin/
#

2、更改7、8的啟動腳本進程號為自己的

vim /mysqldb/3307/bin/mysqld
#07的改為3307
vim /mysqldb/3308/bin/mysqld
#08的改為3308

3、給啟動腳本加執行許可權

chown +x /mysqldb/3306/bin/mysqld 
chmod +x /mysqldb/3306/bin/mysqld 
chmod +x /mysqldb/3307/bin/mysqld 
chmod +x /mysqldb/3308/bin/mysqld
#

4、啟動服務腳本

/mysqldb/3306/bin/mysqld start
#
/mysqldb/3307/bin/mysqld start
#
/mysqldb/3308/bin/mysqld start

5、ss -ntl查看

資料庫加固

1、給資料庫root用戶加密碼

mysqladmin  -S /mysqldb/3306/socket/mysql.sock password 'centos'
mysqladmin  -S /mysqldb/3307/socket/mysql.sock password 'centos'
mysqladmin  -S /mysqldb/3308/socket/mysql.sock password 'centos'

2、進入資料庫刪除匿名登陸

mysql -S /mysqldb/3306/socket/mysql.sock -uroot -p
#

select user,host from mysql.user;
#查看有哪些用戶
delete from mysql.user where user='';
#刪除匿名用戶

OK

exit 重新進入 ，如果不行，就重啟下服務

附加啟動腳本代碼

#!/bin/bash

port=3306
mysql_user="root"
mysql_pwd=""
cmd_path="/usr/local/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