单服务嚣下多实例部署二进制MySQL

说明

现有在用的MySQL为5.6版本，最终需要升级到8.0，但当前的服务暂时不能停止
需要针对5.7和8.0的MySQL比对差异，进而对原有的系统进行有计划地改造
采用方便部署的二进制包，以不同的socket和port部署新实例
以下操作在root权限下实现，记录在已装有MySQL 5.6的服务嚣上另部署出5.6、5.7、8.0三个MySQL实例的步骤和方法

准备工作

环境检查

1）检查并考虑关闭SELINUX(CentOS)

setenforce 0

# 将SELINUX=enforcing改为SELINUX=disabled
sed -i s#SELINUX=enforcing#SELINUX=disabled#g \
  /etc/selinux/config

2）考虑关闭或设置Firewalld/UFW

# 停止并禁用Firewalld
systemctl stop firewalld.service && \
systemctl disable firewalld.service

# Firewalld加入相关端口并重调规则
firewall-cmd --zone=public --list-ports
firewall-cmd --zone=public \
  --add-port={3305,3306,3307,3308}/tcp \
  --permanent
firewall-cmd --reload

# 停止并禁用UFW
ufw disable && \
systemctl stop ufw.service && \
systemctl disable ufw.service

# UFW加入相关规则
ufw allow 3305:3308/tcp
ufw status verbose

3）检查已有的MySQL/MariaDB，当前需求为保留原有再新增其它实例，故不需要作卸载和清除处理，只做检查

# redhat/centos
rpm -qa | grep mysql
rpm -qa | grep mariadb

# debian/ubuntu
dpkg -l | grep mysql
dpkg -l | grep mariadb

4）检查相关端口占用情况

ss -lntup | egrep '3305|3306|3307|3308'

组与用户

因系统已有mysql组和用户，此步可略过；否则应添加mysql专用组和用户

groupadd mysql

useradd -r -g mysql -s /bin/false mysql

id mysql

获取二进制包

1）目录准备，分别新建程序目录、数据目录和其它统一规划的路径

mkdir -pv /usr/local/mysql5678/{data,service,socket}
mkdir -pv /usr/local/mysql5678/data/{3305,3307,3308}

2）参考官方下载地址，选择对应的二进制包下载到本地

# 8.0.22
wget -c https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz

# 5.7.32
wget -c https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz

# 5.6.50
wget -c https://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz

3）分别解压包

tar xf mysql-8.0.22-linux-glibc2.12-x86_64.tar.xz -C /usr/local/mysql5678
tar xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql5678
tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz -C /usr/local/mysql5678

4）设置软链接，方便日后可能需要的小版本更新

cd /usr/local/mysql5678
ln -s mysql-8.0.22-linux-glibc2.12-x86_64 mysql3308
ln -s mysql-5.7.32-linux-glibc2.12-x86_64 mysql3307
ln -s mysql-5.6.50-linux-glibc2.12-x86_64 mysql3305
cd -

配置文件my.cnf

参考手册，分别对每个实例，编写独立的配置文件。根据需要修改以下对应内容，保存为/usr/local/mysql5678/data/my3308.cnf。

[mysqld]
server_id=3308
port=3308
user=mysql

basedir=/usr/local/mysql5678/mysql3308
datadir=/usr/local/mysql5678/data/3308
socket=/usr/local/mysql5678/socket/mysql3308.sock
mysqlx_socket=/usr/local/mysql5678/socket/mysqlx3308.sock
pid-file=/var/run/mysqld/mysqld3308.pid

plugin_dir = /usr/local/mysql5678/mysql3308/lib/mysql/plugin
#plugin_load = "validate_password.so;rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"

log_error = /var/log/mysql/mysqld3308.log
general_log_file = /var/log/mysql/mysql-general3308.log
slow_query_log_file = /var/log/mysql/mysql-slow3308.log

character-set-server = utf8mb4 # default
collation-server = utf8mb4_unicode_ci # defualt: utf8mb4_0900_ai_ci
default-storage-engine=INNODB # default

explicit_defaults_for_timestamp=true # default on 8.0

[client]
port=3308
socket=/usr/local/mysql5678/socket/mysql3308.sock

目录与文件授权

应将各mysql的目录和文件，归属到mysql用户组和用户，完成授权

chown -R mysql:mysql /usr/local/mysql5678

# 8.0的xz解压出来的目录权限不全，可能造成控制台登录失效，可修复
find /usr/local/mysql5678/mysql3308/ \( -type d -exec chmod 750 {} \; \)

初始化实例

参考手册，应对实例进行数据目录初始化。8.0和5.7采用mysqld --initialize命令初始化，不建议使用--initialize-insecure参数空密码初始化，5.6采用script/mysql_install_db命令初始化。

# 8.0
/usr/local/mysql5678/mysql3308/bin/mysqld \
  --defaults-file=/usr/local/mysql5678/data/my3308.cnf \
  --initialize \
  --user=mysql \
  --basedir=/usr/local/mysql5678/mysql3308 \
  --datadir=/usr/local/mysql5678/data/3308

# 5.7
/usr/local/mysql5678/mysql3307/bin/mysqld \
  --defaults-file=/usr/local/mysql5678/data/my3307.cnf \
  --initialize \
  --user=mysql \
  --basedir=/usr/local/mysql5678/mysql3307 \
  --datadir=/usr/local/mysql5678/data/3307

# 5.6
/usr/local/mysql5678/mysql3305/scripts/mysql_install_db \
  --defaults-file=/usr/local/mysql5678/data/my3305.cnf \
  --user=mysql \
  --basedir=/usr/local/mysql5678/mysql3305 \
  --datadir=/usr/local/mysql5678/data/3305

查看初始化时生成的默认密码

grep "password is generated for root@localhost" \
  /var/log/mysql/mysqld3308.log

服务脚本的配置与启动

1）创建对应的systemd脚本，分别命名为mysqld3308.service、mysqld3307.service、mysqld3305.service

vim /etc/systemd/system/mysqld3308.service

参考手册，输入相对应的内容

[Unit]
Description=MySQL Server 8.0 on port 3308
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=/usr/local/mysql5678/mysql3308/bin/mysqld --defaults-file=/usr/local/mysql5678/data//my3308.cnf
LimitNOFILE = 5000

2）配置相对应的服务脚本

systemctl daemon-reload
systemctl start mysqld3308.service
systemctl enable mysqld3308.service

3）应检查各实例是否正常启动，修正相关错误

ss -lntup | grep mysqld

环境变量与控制台登录

1）控制台登录，建议使用--socket或--S参数启动

/usr/local/mysql5678/mysql3308/bin/mysql \
  -S /usr/local/mysql5678/socket/mysql3308.sock

2）通过vim /etc/profile.d/mysql5678.sh输入以下内容加入系统环境，方便控制台登录

alias mysql3305="/usr/local/mysql5678/mysql3305/bin/mysql -S /usr/local/mysql5678/socket/mysql3305.sock"
alias mysql3306="mysql"
alias mysql3307="/usr/local/mysql5678/mysql3307/bin/mysql -S /usr/local/mysql5678/socket/mysql3307.sock"
alias mysql3308="/usr/local/mysql5678/mysql3308/bin/mysql -S /usr/local/mysql5678/socket/mysql3308.sock"

实例初始维护

控制台登录各个实例，进行初始维护，例如设置root@localhost密码、移除不需要的库和用户等

-- 8.0已取消password函数，应用alter user
ALTER USER 'root'@'localhost' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'new-password';
CREATE USER 'myuser'@'%' IDENTIFIED WITH MYSQL_NATIVE_PASSWORD BY 'myuser-password';
GRANT ALL ON *.* to 'myuser'@'%';

-- 5.7/5.6
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('new-password');
GRANT ALL ON *.* to 'myuser'@'%' IDENTIFIED BY 'myuser-password';

-- 移除不需要的库...
DROP database test;

-- 移除并不需要的用户...
REVOKE ALL ON *.* FROM 'myuser'@'%';
DROP USER 'myuser'@'%';

-- 刷新权限
FLUSH PRIVILEGES;

最后做个简单的备份

# socket路径下的sock文件没必要备份
systemctl stop mysqld330{5,7,8}.service

cp -a /etc/systemd/system/mysqld330* \
  /usr/local/mysql5678/service/

tar -jcpf ~/mysql5678_$(date +'%Y%m%d-%H%M').tar.bz2 \
  /usr/local/mysql5678/{mysql330*,service,socket} \
  /usr/local/mysql5678/data/my*.cnf

systemctl start mysqld330{5,7,8}.service