多实例数据库MYSQL

多实例MYSQL

在一台主机上实现多个数据库，开启多个端口号，运行MYSQL服务进程，不同的端口号来提供服务。
可以有效的利用服务器资源，但是服务器消耗会增加，所以视情况而定，通过不同的端口号，数据库，表进行操作

二进制安装多实例的实现

目标

在CentOS7中实现多实例三种多实例数据库

适用版本

mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
mysql-8.0.23-linux-glibc2.12-x86_64.tar.gz

步骤
1、解压缩包且创立软链接

#下载包或者解压包
[ 16:29:14 root@CentOS7zhangbo ~]#tar xvf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
#进入目录创立软链接
[ 16:30:18 root@CentOS7zhangbo ~]#cd /usr/local/
[ 16:31:10 root@CentOS7zhangbo local]#ln -s mysql-5.7.32-linux-glibc2.12-x86_64 mysql
[ 16:31:08 root@CentOS7zhangbo local]#ll mysql/
total 260
drwxr-xr-x  2 root root    4096 Jan 30 16:30 bin
drwxr-xr-x  2 root root      55 Jan 30 16:30 docs
drwxr-xr-x  3 root root    4096 Jan 30 16:30 include
drwxr-xr-x  5 root root     230 Jan 30 16:30 lib
-rw-r--r--  1 7161 31415 247914 Sep 23 20:00 LICENSE
drwxr-xr-x  4 root root      30 Jan 30 16:30 man
-rw-r--r--  1 7161 31415    587 Sep 23 20:00 README
drwxr-xr-x 28 root root    4096 Jan 30 16:30 share
drwxr-xr-x  2 root root      90 Jan 30 16:30 support-files

2、创建用户和组且配置文件权限

#创建用户和组
[ 16:32:34 root@CentOS7zhangbo local]#useradd -r -s /sbin/nologin mysql
#配置文件权限，注意最后"/"和无"/"的区别是文件内和本文件夹
[ 16:33:03 root@CentOS7zhangbo local]#chown -R mysql.mysql /usr/local/mysql/
[ 16:33:56 root@CentOS7zhangbo local]#ll /usr/local/mysql/
total 260
drwxr-xr-x  2 mysql mysql   4096 Jan 30 16:30 bin
drwxr-xr-x  2 mysql mysql     55 Jan 30 16:30 docs
drwxr-xr-x  3 mysql mysql   4096 Jan 30 16:30 include
drwxr-xr-x  5 mysql mysql    230 Jan 30 16:30 lib
-rw-r--r--  1 mysql mysql 247914 Sep 23 20:00 LICENSE
drwxr-xr-x  4 mysql mysql     30 Jan 30 16:30 man
-rw-r--r--  1 mysql mysql    587 Sep 23 20:00 README
drwxr-xr-x 28 mysql mysql   4096 Jan 30 16:30 share
drwxr-xr-x  2 mysql mysql     90 Jan 30 16:30 support-files

3、配置环境变量

#写入环境变量到文件夹内
[ 16:34:00 root@CentOS7zhangbo local]#echo 'PATH=/usr/local/mysql/bin:$PATH' >/etc/profile.d/mysql.sh
#然后文件生效
[ 16:36:03 root@CentOS7zhangbo local]#source /etc/profile.d/mysql.sh 

4、创建实例存放目录

#创建文件夹并且修改所属组所属者
[ 16:37:20 root@CentOS7zhangbo ~]#mkdir /data/mysql/{3306,3307,3308}
[ 16:38:36 root@CentOS7zhangbo ~]#chown -R mysql.mysql /data/mysql/
[ 16:38:40 root@CentOS7zhangbo ~]#ll /data/mysql/
total 0
drwxr-xr-x 2 mysql mysql 6 Jan 30 16:37 3306
drwxr-xr-x 2 mysql mysql 6 Jan 30 16:37 3307
drwxr-xr-x 2 mysql mysql 6 Jan 30 16:37 3308

5、初始各个实例数据库

#-insecure为不生成随机密码为空密码
[ 16:43:08 root@CentOS7zhangbo ~]#mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3306/
[ 17:08:56 root@CentOS7zhangbo ~]#mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307/
[ 17:08:56 root@CentOS7zhangbo ~]#mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3308/

6、准备修改配置文件/etc/my.cnf

修改文件
[mysqld_multi]
mysqld=/usr/local/mysql/bin/mysqld_safe
mysqladmin=/usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir=/data/mysql/3306
port=3306
socket=/data/mysql/3306/mysql3306.sock
pid-file=/data/mysql/3306/mysql3306.pid
log-error=/data/mysql/3306/mysql3306.log

[mysqld3307]
datadir=/data/mysql/3307
port=3307
socket=/data/mysql/3307/mysql3307.sock
pid-file=/data/mysql/3307/mysql3307.pid
log-error=/data/mysql/3307/mysql3307.log

[mysqld3308]
datadir=/data/mysql/3308
port=3308
socket=/data/mysql/3308/mysql3308.sock
pid-file=/data/mysql/3308/mysql3308.pid
log-error=/data/mysql/3308/mysql3308.log

7、启动多实例

#可以使用mysqld_multi start启动
[ 18:04:33 root@CentOS7zhangbo ~]#mysqld_multi start 3308
#或者一起启动
[ 18:04:00 root@CentOS7zhangbo ~]#mysqld_multi start 3306-3308

#登录客户端-S 指定
[ 18:04:40 root@CentOS7zhangbo ~]#mysql -uroot -S /data/mysql/3306/mysql3306.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.23 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> exit

8、关闭多实例

#单个关闭或者连续关闭
[ 18:09:15 root@CentOS7zhangbo ~]#mysqld_multi stop 3306-3308

9、开机启动多实例

#可以写进文件中放在rc.local中
vim /etc/rc.d/rc.local
#添加一下两行
. /etc/profile.d/mysql.sh
mysqld_multi start 3306-3308 
#然后把rc.local设置为可执行文件

实现YUM安装的更改多实例

步骤
1、安装mariadb

#安装
[ 18:45:22 root@CentOS7zhangbo ~]#yum -y install mariadb-server.x86_64 

2、准备目录

#创建目录
[ 18:55:01 root@CentOS7zhangbo ~]#mkdir -pv /data/mysql/330{6,7,8}/{data,etc,socket,log,bin,pid}
#然后更改权限
[ 18:55:01 root@CentOS7zhangbo ~]#chown -R mysql.mysql /data/mysql/

3、生成数据库文件

#生成数据库文件
[ 19:26:09 root@CentOS7zhangbo ~]#mysql_install_db --user=mysql --datadir=/data/mysql/3306/data
[ 19:26:09 root@CentOS7zhangbo ~]#mysql_install_db --user=mysql --datadir=/data/mysql/3307/data
[ 19:26:09 root@CentOS7zhangbo ~]#mysql_install_db --user=mysql --datadir=/data/mysql/3308/data

4、配置文件修改

#可以拷贝/etc/my.cnf文件进行做范例,或者直接修改，注意还有7和8的
[ 19:26:09 root@CentOS7zhangbo ~]#vim /data/mysql/3306/etc/my.cnf
[mysqld]
port=3306
datadir=/data/mysql/3306/data
socket=/data/mysql/3306/socket/mysql.sock
[mysqld_safe]                                                                                                                               
log-error=/data/mysql/3306/log/mysql.log
pid-file=/data/mysql/3306/pid/mysql.pid

5、准备启动脚本

#写三份启动服务脚本

#[ 19:29:24 root@CentOS7zhangbo ~]#vim /data/mysql/3306/bin/mysqld

#!/bin/bash                                                                                                                                        
port=3306
mysql_user="root"
mysql_pwd="zhangbo"
cmd_path="/usr/bin"
mysql_basedir="/data/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 stop \n"
    exit
else
    printf "stop mysql \n"
    ${cmd_path}/mysqladmin -u${mysql_user} -p${mysql_pwd} -S ${mysql_sock} shutdown
fi
}

#重启函数
function_restart_mysql(){
    function_start_mysql
    function_stop_mysql
}
case $1 in
start)
    function_start_mysql
    ;;
stop)
    function_stop_mysql
    ;;
restart)
    function_restart_mysql
    ;;
*)
    printf "${mysql_basedir}/${port}/bin/mysqld {start|stop|restart}\n"
esac  

7、服务脚本加上执行权限

#加上执行权限
[ 19:37:51 root@CentOS7zhangbo ~]#chmod +x /data/mysql/3308/bin/mysqld 
[ 19:37:51 root@CentOS7zhangbo ~]#chmod +x /data/mysql/3307/bin/mysqld 
[ 19:37:51 root@CentOS7zhangbo ~]#chmod +x /data/mysql/3306/bin/mysqld 

8、启动关闭服务

[ 19:37:51 root@CentOS7zhangbo ~]#/data/mysql/3307/bin/mysqld start
[ 19:37:51 root@CentOS7zhangbo ~]#/data/mysql/3306/bin/mysqld start
[ 19:37:51 root@CentOS7zhangbo ~]#/data/mysql/3309/bin/mysqld start
[ 19:45:38 root@CentOS7zhangbo ~]#ss -ntl
State       Recv-Q Send-Q                            Local Address:Port                                           Peer Address:Port              
LISTEN      0      50                                            *:3306                                                      *:*                  
LISTEN      0      50                                            *:3307                                                      *:*                  
LISTEN      0      50                                            *:3308                                                      *:*    

关闭服务需要设置密码为脚本里面那个

9、修改root密码

[ 20:06:39 root@CentOS7zhangbo ~]#mysqladmin -uroot -S /data/mysql/3306/socket/mysql.sock password 'zhangbo'

10、测试连接

[ 20:13:26 root@CentOS7zhangbo ~]#mysql -uroot -pzhangbo -S /data/mysql/3306/socket/mysql.sock 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 3
Server version: 5.5.68-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 statement.

MariaDB [(none)]>