MySQL之MySQL5.7版本数据库多实例安装部署

一、目录结构

二、下载解压

wget https://mirrors.sohu.com/mysql/MySQL-5.7/mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz

tar -zxvf mysql-5.7.35-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
ln -s /usr/local/mysql-5.7.35-linux-glibc2.12-x86_64 /usr/local/mysql

vim /etc/profile

export PATH=/usr/local/mysql/bin/:$PATH

三、安装

3.1、创建多实例数据目录

mkdir -p /data/mysql/330{6..8}
mkdir -p /data/mysql/conf

3.2、创建多实例配置文件

3306配置文件
cat > /data/mysql/conf/my.3306.cnf << EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
socket=/data/mysql/3306/data/mysql.sock
port=3306

[mysql]
default-character-set=utf8 
collation-server = utf8_unicode_ci 
init-connect='SET NAMES utf8'
character-set-server = utf8 
socket=/data/mysql/3306/data/mysql.sock
 
[client]  
default-character-set=utf8 
 
EOF

3307、3308实例配置文件,通过复制拷贝3306的配置文件并修改对应端口即可。

cp my.3306.cnf my.3307.cnf 
cp my.3306.cnf my.3308.cnf 

修改对应端口

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

3.3、多实例数据库初始化

mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3308/data

3.4、启动数据库

根据配置文件，后台启动多实例数据库

mysqld --defaults-file=/data/mysql/conf/my.3306.cnf --user=root &
mysqld --defaults-file=/data/mysql/conf/my.3307.cnf --user=root &
mysqld --defaults-file=/data/mysql/conf/my.3308.cnf --user=root &

3.5、查看多实例数据库进程和监听端口

检查进程

ps -ef|grep mysql

监听端口

netstat  -lntup|grep 33

3.6、客户端连接

/usr/local/mysql/bin/mysql -S /data/mysql/3306/data/mysql.sock

set password = '123456';

/usr/local/mysql/bin/mysql -S /data/mysql/3306/data/mysql.sock -p

3.7、编写启停脚本

vim mysql_3306

port=3306
mysql_user="root"
cmdpath="/usr/local/mysql/bin"
mysql_sock="/data/mysql/${port}/data/mysql.sock"
mysqld_pid_file_path=/data/mysql/${port}/data/mysql01.pid

start(){
    if [ ! -e "$mysql_sock" ];then
        printf "Starting MySQL...\n"
        ${cmdpath}/mysqld --defaults-file=/data/mysql/conf/my.${port}.cnf --user=${mysql_user} &
        sleep 3
    else
    printf "MySQL is running...\n"
    exit 1
    fi
}
stop(){
    if [ ! -e "$mysql_sock" ];then
    printf "MySQL is stopped...\n"
    exit 1
    else
    printf "Stoping MySQL...\n"
    mysqld_pid=`cat "$mysqld_pid_file_path"`
    if (kill -0 $mysqld_pid 2>/dev/null)
        then
        kill $mysqld_pid
        sleep 2
        fi
    fi
}
restart(){
    printf "Restarting MySQL...\n"
    stop
    sleep 2
    start
}
case "$1" in
start)
    start
;;
stop)
    stop
;;
restart)
    restart
;;
*)
    printf "Usage: /data/${port}/mysql{start|stop|restart}\n"
esac

chmod +x mysql_3306

./mysql_3306 start