多实例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)]>