MySQL 不同版本多实例

1 规划

MySQL5.6 8.0软件包：  
　　mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
　　mysql-8.0.13-linux-glibc2.12-x86_64.tar.xz
配置文件　　　　 ： /data/331{6..8}/my.cnf 
数据目录　　　　 ： /data/331{6..8}
初始化数据　　　 
日志目录　　　　 ： /binlog/331{6..8}
端口　　　　　　 ： port=3316,3317,3318
server_id　　  ： server_id=16,17,18
socket　　 　　 ： /tmp/mysql{56,57,80}.sock 

2 软连接不同版本软件，修改环境变量

cd /app/database/
rm -rf ./mysql58　　# 删除软链接
ln -s mysql-5.6.46-linux-glibc2.12-x86_64 mysql56
ln -s mysql-8.0.13-linux-glibc2.12-x86_64 mysql80
mv /etc/my.cnf /etc/my.cnf.bak

vim /etc/profile
# 注释掉以下信息
#export PATH=/app/database/mysql/bin:$PATH

vim .bash_profile
# 注释掉以下信息
#PATH=$PATH:$HOME/bin:/app/database/mysql/bin

#复制一个新窗口
[root@master ~]# mysql -V
-bash: mysql: command not found

3 准备不同目录

rm -rf /data/331{6..8}/*
rm -rf /binlog/331{6..8}
mkdir -p /data/331{6..8}/data
mkdir -p /binlog/331{6..8}
chown -R mysql.mysql /data/* /binlog/*

4 准备配置文件

cat > /data/3316/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql56
datadir=/data/3316/data
socket=/tmp/mysql56.sock
log_error=/data/3316/mysql.log
port=3316
server_id=16
log_bin=/binlog/3316/mysql-bin
EOF

cat > /data/3318/my.cnf <<EOF
[mysqld]
basedir=/app/database/mysql80
datadir=/data/3318/data
socket=/tmp/mysql80.sock
log_error=/data/3318/mysql.log
port=3318
server_id=18
log_bin=/binlog/3318/mysql-bin
EOF

4 初始化数据

# 5.6
/app/database/mysql56/scripts/mysql_install_db --user=mysql --datadir=/data/3316/data --basedir=/app/database/mysql56

# 8.0
/app/database/mysql80/bin/mysqld --initialize-insecure --user=mysql --datadir=/data/3318/data --basedir=/app/database/mysql80

5 准备启动脚本

# 5.6
cat > /etc/systemd/system/mysqld56.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql56/bin/mysqld --defaults-file=/data/3316/my.cnf
LimitNOFILE = 5000
EOF

# 8.0
cat > /etc/systemd/system/mysqld80.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/app/database/mysql80/bin/mysqld --defaults-file=/data/3318/my.cnf
LimitNOFILE = 5000
EOF

6 启动多实例

[root@master ~]# netstat -tulnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1351/sshd           
tcp6       0      0 :::22                   :::*                    LISTEN      1351/sshd           
[root@master ~]# systemctl start mysqld56
[root@master ~]# systemctl start mysqld80
[root@master ~]# netstat -tulnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN      1351/sshd           
tcp6       0      0 :::33060                :::*                    LISTEN      3229/mysqld         
tcp6       0      0 :::3316                 :::*                    LISTEN      3201/mysqld         
tcp6       0      0 :::3318                 :::*                    LISTEN      3229/mysqld         
tcp6       0      0 :::22                   :::*                    LISTEN      1351/sshd           

7 登录多实例数据库

[root@master ~]# /app/database/mysql56/bin/mysql -S /tmp/mysql56.sock
Server version: 5.6.46-log MySQL Community Server (GPL)
[root@master ~]# /app/database/mysql80/bin/mysql -S /tmp/mysql80.sock
Server version: 8.0.13 MySQL Community Server - GPL

[root@master ~]# vim /etc/profile
export PATH=/app/database/mysql/bin:$PATH
[root@master ~]# source /etc/profile
[root@master ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using EditLine wrapper

[root@master ~]# mysql -S /tmp/mysql56.sock
Server version: 5.6.46-log MySQL Community Server (GPL)
[root@master ~]# mysql -S /tmp/mysql80.sock
Server version: 8.0.13 MySQL Community Server - GPL

[root@master ~]# mv /etc/my.cnf.bak /etc/my.cnf
[root@master ~]# /etc/init.d/mysqld start
Starting MySQL.... SUCCESS! 
[root@master ~]# mysql
Server version: 5.7.28 MySQL Community Server (GPL)
[root@master ~]# mysql -uroot -p
Server version: 5.7.28 MySQL Community Server (GPL)
[root@master ~]# mysql -S /tmp/mysql56.sock
Server version: 5.6.46-log MySQL Community Server (GPL)
[root@master ~]# mysql -S /tmp/mysql80.sock
Server version: 8.0.13 MySQL Community Server - GPL

5.7 和 8.0 的客户端可以连其他两个服务端，5.6 的客户端连8.0的服务端会报错

# 8.0
[root@master ~]# /app/database/mysql80/bin/mysql -S /tmp/mysql56.sock
Server version: 5.6.46-log MySQL Community Server (GPL)
[root@master ~]# /app/database/mysql80/bin/mysql -S /tmp/mysql.sock
Server version: 5.7.28 MySQL Community Server (GPL)

# 5.7
[root@master ~]# /app/database/mysql/bin/mysql -S /tmp/mysql56.sock
Server version: 5.6.46-log MySQL Community Server (GPL)
[root@master ~]# /app/database/mysql/bin/mysql -S /tmp/mysql80.sock
Server version: 8.0.13 MySQL Community Server - GPL

# 5.6
[root@master ~]# /app/database/mysql56/bin/mysql -S /tmp/mysql.sock
Server version: 5.7.28 MySQL Community Server (GPL)
[root@master ~]# /app/database/mysql56/bin/mysql -S /tmp/mysql80.sock
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory