08-MySQL多实例
1.MySQL多实例
1、MySQL多实例
1.1、什么是MySQL多实例
MySQL多实例4独特性:
1、独立进程
[root@oldboy ~]# ps -ef|grep mysql
root 21881 2487 0 Dec08 pts/0 00:00:00 mysql
root 22671 1 0 01:17 ? 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/3306/data --pid-file=/data/3306/data/oldboy.pid
mysql 22827 22671 0 01:17 ? 00:00:59 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=oldboy.err --pid-file=/data/3306/data/oldboy.pid --socket=/tmp/mysql.sock --port=3306
root 23528 2487 0 17:08 pts/0 00:00:00 mysql
root 23549 23506 0 17:56 pts/1 00:00:00 grep --color=auto mysql
2、独立端口
[root@oldboy ~]# netstat -lntup|grep 3306
tcp6 0 0 :::3306 :::* LISTEN 22827/mysqld
3、独立配置文件(非必须)
[root@oldboy ~]# ls /data/3306/my.cnf
/data/3306/my.cnf
4、独立数据文件
[root@oldboy ~]# ls /data/3306/data
auto.cnf binlog.000005 binlog.000010 client-cert.pem ibdata1 mysql
共用东西：
- 共用安装文件
- 共用用户mysql
2、创建多实例
1.创建数据文件目录
mkdir -p /data/{3307,3308}/data
tree -L 1 /data
chown -R mysql.mysql /data
3、创建多实例配置文件
通过cat命令命令快速添加配置文件内容，命令如下：
3307实例
cat >/data/3307/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3307/data
port=3307
socket= /data/3307/mysql.sock
mysqlx=0 #33060,mysql8.0新增的功能我们把他关闭
EOF
3308实例
cat >/data/3308/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
mysqlx=0
EOF
查看配置文件
cat /data/3307/my.cnf
cat /data/3308/my.cnf
4、配置MySQL多实例文件权限
通过下面的命令授权mysql用户和组管理整个多实例的根目录/data。
chown -R mysql.mysql /data/{3307,3308}/data
5、初始化多实例数据库文件
初始化MySQL数据库，初始化命令为（相关参数在第三章安装已经详细解释）：
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql \
--datadir=/data/3307/data
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql \
--datadir=/data/3308/data
6、启动登录多实例
6.1、启动多实例
临时启动MySQL多实例的命令为：
mysqld_safe --defaults-file=/data/3307/my.cnf & #<==3307实例启动命令。
mysqld_safe --defaults-file=/data/3308/my.cnf & #<==3308实例启动命令。
sleep 5
netstat -lntup|grep 330
[root@db01 ~]# netstat -lntup|grep 330 ###<===
tcp6 0 0 :::33060 :::* LISTEN 31565/mysqld
tcp6 0 0 :::3306 :::* LISTEN 32022/mysqld
tcp6 0 0 :::3307 :::* LISTEN 31562/mysqld
tcp6 0 0 :::3308 :::* LISTEN 31565/mysqld
6.2、登录不同多实例
mysql -S /tmp/mysql.sock
mysql -S /data/3307/mysql.sock
mysql -S /data/3308/mysql.sock
7、配置命令启动MySQL
[root@db01 ~]# rz -E
rz waiting to receive.
[root@db01 ~]# ls
mysqld3307 mysqld3308
[root@db01 ~]# vimdiff mysqld3307 mysqld3308 #对比一下两个文件
2 files to edit
[root@db01 ~]# cp mysqld3307 /data/3307/mysql
[root@db01 ~]# cp mysqld3308 /data/3308/mysql
[root@db01 ~]# chmod +x /data/{3307,3308}/mysql
/data/3307/mysql {start|stop|restart}
/data/3308/mysql {start|stop|restart}
8、配置以systemd方式管理多实例
配置MySQL多实例采用systemd方式管理mysql启动，并实现开机自启动，操作命令如下：
1.将启动脚本放入/etc/init.d/
\cp /data/3307/mysql /etc/init.d/mysqld3307
\cp /data/3308/mysql /etc/init.d/mysqld3308
2.授权可执行权限
chmod +x /etc/init.d/mysqld3307
chmod +x /etc/init.d/mysqld3308
3. 分别启动3307和3308数据库
systemctl enable mysqld3307
systemctl enable mysqld3308
4.分别启动3307和3308数据库
systemctl start mysqld3307
systemctl start mysqld3308
5.CentOS7以前老式启动方法
service mysqld3307 start
service mysqld3308 start
/etc/init.d/mysqld3307 start
/etc/init.d/mysqld3308 start
6. 如果使用systemctl或者service启动不了请执行下面命令启动
/etc/init.d/mysqld start #3306
/etc/init.d/mysqld3307 start #3307
/etc/init.d/mysqld3308 start #3308
批量登录测试
[root@mysql8 ~]# for n in 33{07..08};do mysql -S /data/$n/mysql.sock -e "select @@port";done
+--------+
| @@port |
+--------+
| 3307 |
+--------+
+--------+
| @@port |
+--------+
| 3308 |
+--------+
9、多实例应用场景
1.业务不是特别繁忙，且项目多，并且希望业务隔离开.
2.业务不是特别繁忙，想省钱
3.主从复制,读写分离集群环境，节省服务器数量...
4.大厂都会多实例，比如sina、百度、阿里。
5.使用的是物理服务器。
2.mysql不同版本多实例
重点知识 : 前不同版本MySQL初始化命令异同
/usr/local/mysql/scripts/mysql_install_db #5.7之前
/usr/local/mysql/bin/mysqld #5.7和8.0初始化
1、先安装部署一下5.6、5.7版本MySQL
1）安装路径规划：
/usr/local/mysql ##8.0
/usr/local/mysql57
/usr/local/mysql56
2）数据路径规划：
/data/3356/data
/data/3357/data
3）解压移动
tar xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.28-linux-glibc2.12-x86_64 /usr/local/mysql57
tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.6.46-linux-glibc2.12-x86_64 /usr/local/mysql56
#查看解压过程
du -sh /opt/*
4）创建目录并授权
mkdir -p /data/3357/data
mkdir -p /data/3356/data
chown -R mysql.mysql /data
5）初始化数据库
上传配置文件压缩包
tar xf my.cnf.tar.gz -C /
自动生成配置
/data/3357/my.cnf
/data/3356/my.cnf
自动生成启动文件
/etc/init.d/mysqld3357
/etc/init.d/mysqld3356
查看配置:
[root@db01 ~]# cat /data/3356/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql56
datadir=/data/3356/data
port=3356
socket= /data/3356/mysql.sock
[root@db01 ~]# cat /data/3357/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/data/3357/data
port=3357
socket= /data/3357/mysql.sock
查看启动脚本:
cat /etc/init.d/mysqld3356
cat /etc/init.d/mysqld3357
启动脚本
[root@mysql8 init.d]# cat mysqld3356
#!/bin/sh
# chkconfig: 2345 64 36
################################################
#this scripts is created by oldboy at 2017-03-09
#oldboy QQ:31333741
#site:http://www.oldboyedu.com
################################################
#init
port=3356 ##唯一区别就是这里。
mysql_user="root"
CmdPath="/usr/local/mysql56/bin" ###这也有区别
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/$port/$port.pid
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
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
#by oldboy over
[root@mysql8 init.d]# cat mysqld3357
#!/bin/sh
# chkconfig: 2345 64 36
################################################
#this scripts is created by oldboy at 2017-03-09
#oldboy QQ:31333741
#site:http://www.oldboyedu.com
################################################
#init
port=3357 ##唯一区别就是这里。
mysql_user="root"
CmdPath="/usr/local/mysql57/bin"
mysql_sock="/data/${port}/mysql.sock"
mysqld_pid_file_path=/data/$port/$port.pid
start(){
if [ ! -e "$mysql_sock" ];then
printf "Starting MySQL...\n"
/bin/sh ${CmdPath}/mysqld_safe --defaults-file=/data/${port}/my.cnf --pid-file=$mysqld_pid_file_path 2>&1 > /dev/null &
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
#by oldboy over
6）5.7初始化MySQL数据库
初始化预读/etc/my.cnf里的参数，再读/data/3357/data下的配置
/usr/local/mysql57/bin/mysqld --initialize-insecure --user=mysql \
--basedir=/usr/local/mysql57 --datadir=/data/3357/data
7）5.6初始化MySQL数据库
/usr/local/mysql56/scripts/mysql_install_db --user=mysql \
--basedir=/usr/local/mysql56 --datadir=/data/3356/data
8）启动及设置开机启动
chmod +x /etc/init.d/mysqld3357
chmod +x /etc/init.d/mysqld3356
/etc/init.d/mysqld3357 start
/etc/init.d/mysqld3356 start
systemctl enable mysqld3356
systemctl enable mysqld3357
9）登录
mysql -S /data/3356/mysql.sock -e "select @@port"
mysql -S /data/3357/mysql.sock -e "select @@port"
10)关闭所有,重新测试
pkill mysqld
netstat -lntup
=================
[root@db01 ~]# ls /etc/init.d/|grep mysql|awk '{print "/etc/init.d/"$0 " start"}'
/etc/init.d/mysqld start
/etc/init.d/mysqld3307 start
/etc/init.d/mysqld3308 start
/etc/init.d/mysqld3309 start
/etc/init.d/mysqld3310 start
/etc/init.d/mysqld3356 start
/etc/init.d/mysqld3357 start
[root@db01 ~]# ls /etc/init.d/|grep mysql|awk '{print "/etc/init.d/"$0 " start"}'|bash
Starting MySQL... SUCCESS!
Starting MySQL...
Starting MySQL...
Starting MySQL...
Starting MySQL...
Starting MySQL...
Starting MySQL...
[root@db01 ~]# netstat -lntup|grep 33
tcp6 0 0 :::3356 :::* LISTEN 9395/mysqld
tcp6 0 0 :::3357 :::* LISTEN 9559/mysqld
tcp6 0 0 :::33060 :::* LISTEN 8446/mysqld
tcp6 0 0 :::3306 :::* LISTEN 8446/mysqld
tcp6 0 0 :::3307 :::* LISTEN 8645/mysqld
tcp6 0 0 :::3308 :::* LISTEN 8836/mysqld
tcp6 0 0 :::3309 :::* LISTEN 9027/mysqld
tcp6 0 0 :::3310 :::* LISTEN 9218/mysqld
端口都起来,就成功了.
浙公网安备 33010602011771号