一、概述
- 在一台物理主机上运行多个数据库服务
- 节约运维成本
- 提高硬件利用率
- 多实例配置，需要专用的、支持多实例的Mysql软件。这里对应的软件包是
mysql8.0
- 多实例配置，需要专用的、支持多实例的Mysql软件。这里对应的软件包是
企业数据库服务多实例应用架构设计：（主要用于支持多套业务场景）
三、环境准备
# 设置命令行补齐功能
sudo yum install git bash-completion -y
echo "source /usr/share/bash-completion/bash_completion" >>/etc/profile
source /etc/profile
# 修改终端颜色
cat <<EOF >> ~/.bashrc
PS1='[\[\e[34;1m\]\u@\[\e[0m\]\[\e[32;1m\]\H\[\e[0m\]\[\e[31;1m\] \W\[\e[0m\]]# '
EOF
source ~/.bashrc
# 关闭防火墙
systemctl disable --now firewalld && systemctl is-enabled firewalld
systemctl status firewalld
# 禁用selinux
sed -ri 's#(SELINUX=)enforcing#\1disabled#' /etc/selinux/config
grep ^SELINUX= /etc/selinux/config
setenforce 0
getenforce
# 关闭交换内存
swapoff -a
sed -ir 's/.*swap/#&/g' /etc/fstab
rm -Rf /swap.img
free -m
# sshd服务优化
sudo sed -i 's/^#UseDNS no/UseDNS no/' /etc/ssh/sshd_config
sed -ri 's/^GSSAPIAuthentication yes/GSSAPIAuthentication no/' /etc/ssh/sshd_config
grep ^UseDNS /etc/ssh/sshd_config
grep ^GSSAPIAuthentication /etc/ssh/sshd_config
# 时间同步
crontab -e
*/5 * * * * /usr/sbin/ntpdate ntp.aliyun.com
# 卸载原有软件包
rpm -qa | grep mysql
rpm -qa | grep mariadb
yum remove xxx
利用官方网址获取数据库软件程序：https://www.mysql.com/
选择官网网站的 DOWNLOADS 进行数据库软件程序下载选择；
在数据库官方下载页面中，选择 MySQL Community (GPL) Downloads 链接，进行数据库程序社区版下载；
查看Linux系统的glibc运行使用的C语言库版本信息：
# 选择对应的版本进行下载
wget -cO /usr/local/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz https://downloads.mysql.com/archives/get/p/23/file/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
四、实践
第一种
1、部署环境规划
| 实例 | 端口 | 实例存储路径 | 实例配置文件 | 套接字文件 |
|---|---|---|---|---|
| db01 | 3307 | /data/mysql/3307/ | /etc/my.cnf | /tmp/mysql3307.sock |
| db01 | 3308 | /data/mysql/3308/ | /etc/my.cnf | /tmp/mysql3308.sock |
| db01 | 3309 | /data/mysql/3309/ | /etc/my.cnf | /tmp/mysql3309.sock |
2.、解压MySQL软件包
# 多实例初始化配置前调整操作,避免之前存在的配置文件产生影响
mv /etc/my.cnf /etc/my.cnf.bak
cd /usr/local
tar xf /usr/local/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-8.0.26-linux-glibc2.12-x86_64 mysql
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
mysql -V
mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)
3、编写配置文件
cat > /etc/my.cnf <<EOF
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user = root
[mysqld1]
datadir = /data/mysql/3307/
port = 3307
log-error = /data/mysql/3307/mysqld3307.err
pid-file = /data/mysql/3307/mysqld3307.pid
socket = /tmp/mysql3307.sock
mysqlx = 0
[mysqld2]
datadir = /data/mysql/3308/
port = 3308
log-error = /data/mysql/3308/mysqld3308.err
pid-file = /data/mysql/3308/mysqld3308.pid
socket = /tmp/mysql3308.sock
mysqlx = 0
[mysqld3]
datadir = /data/mysql/3309/
port = 3309
log-error = /data/mysql/3309/mysqld3309.err
pid-file = /data/mysql/3309/mysqld3309.pid
socket = /tmp/mysql3309.sock
mysqlx = 0
EOF
4、创建工作目录
mkdir -p /data/mysql/330{7..9}
useradd mysql
chown -R mysql. /data/mysql
5、启动服务
-
第一个实例
/usr/local/mysql/bin/mysqld_multi start 1 # 静候启动完成，记录最后一行产生的密码,如果没有可能在数据目录中err文件中 2021-04-10T03:17:28.637290Z 1 [Note] A temporary password is generated for root@localhost: ,N1j!G1(M/L. # 通过本机socket连接数据库并修改密码 [root@db01 ~]# mysql -uroot -p',N1j!G1(M/L.' -S /mysqlmul/mysqld1/mysqld1.sock mysql> alter user root@'localhost' identified by 'mysql_P@s5'; Query OK, 0 rows affected (0.00 sec) mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | sys | +--------------------+ 4 rows in set (0.00 sec) -
启动第2个实例
/usr/local/mysql/bin/mysqld_multi start 2 # 静候启动完成，记录最后一行产生的密码,如果没有可能在数据目录中err文件中 2021-04-10T03:28:42.084264Z 1 [Note] A temporary password is generated for root@localhost: &c/ag7j()41A # 通过socket连接并修改密码 mysqladmin -uroot -p'&c/ag7j()41A' -S /mysqlmul/mysqld2/mysqld2.sock password 'mysql_P@s5' # 通过网络连接 mysql -h127.0.0.1 -uroot -pmysql_P@s5 -P3307 mysql> select @@port; +--------+ | @@port | +--------+ | 3307 | +--------+ 1 row in set (0.00 sec) -
启动第3个实例
# 参照上面前两个实例操作
6、停止服务
/usr/local/mysql/bin/mysqld_multi --user root --password mysql_P@s5 stop 1
第二种
1、环境部署
| 实例 | 端口 | 实例存储路径 | 实例配置文件 | 套接字文件 |
|---|---|---|---|---|
| db01 | 3307 | /data/mysql/3307/ | /data/mysql/3307/my.cnf | /tmp/mysql3307.sock |
| db01 | 3308 | /data/mysql/3308/ | /data/mysql/3308/my.cnf | /tmp/mysql3308.sock |
| db01 | 3309 | /data/mysql/3309/ | /data/mysql/3309/my.cnf | /tmp/mysql3309.sock |
| db01 | 3310 | /data/mysql/3310/ | /data/mysql/3310/my.cnf | /tmp/mysql3310.sock |
2、解压MySQL软件包
# 多实例初始化配置前调整操作,避免之前存在的配置文件产生影响
mv /etc/my.cnf /etc/my.cnf.bak
cd /usr/local
tar xf /usr/local/mysql-8.0.26-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-8.0.26-linux-glibc2.12-x86_64 mysql
echo 'export PATH="/usr/local/mysql/bin:$PATH"' >> /etc/profile.d/mysql.sh
source /etc/profile.d/mysql.sh
mysql -V
mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)
3、创建工具目录
groupadd mysql
useradd -r -g mysql mysql
mkdir -p /data/mysql/330{7..10}/
chown -R mysql. /data/mysql/
4、初始化四个实例，分别执行
cd /usr/local/mysql/bin/
# 多实例初始化操作过程
./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3307/
./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3308/
./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3309/
./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/3310/
-- 初始化完毕后可以检查数据库目录中，是否已经存在了数据库初始化产生的数据文件信息；
5、为四个实例创建主配置文件
cat > /data/mysql/3307/my.cnf <<EOF
[mysql]
socket=/tmp/mysql3307.sock
[mysqld]
user=mysql
port=3307
basedir=/usr/local/mysql
datadir=/data/mysql/3307/
socket=/tmp/mysql3307.sock
mysqlx = 0
EOF
cat > /data/mysql/3308/my.cnf <<EOF
[mysql]
socket=/tmp/mysql3308.sock
[mysqld]
user=mysql
port=3308
basedir=/usr/local/mysql
datadir=/data/mysql/3308/
socket=/tmp/mysql3308.sock
mysqlx = 0
EOF
cat > /data/mysql/3309/my.cnf <<EOF
[mysql]
socket=/tmp/mysql3309.sock
[mysqld]
user=mysql
port=3309
basedir=/usr/local/mysql
datadir=/data/mysql/3309/
socket=/tmp/mysql3309.sock
mysqlx = 0
EOF
cat > /data/mysql/3310/my.cnf <<EOF
[mysql]
socket=/tmp/mysql3310.sock
[mysqld]
user=mysql
port=3310
basedir=/usr/local/mysql
datadir=/data/mysql/3310/
socket=/tmp/mysql3310.sock
mysqlx = 0
EOF
6、启动实例
cd /usr/local/mysql/bin/
./mysqld_safe --defaults-file=/data/mysql/3307/my.cnf &
./mysqld_safe --defaults-file=/data/mysql/3308/my.cnf &
./mysqld_safe --defaults-file=/data/mysql/3309/my.cnf &
./mysqld_safe --defaults-file=/data/mysql/3310/my.cnf &
# 查看进程状态
ps -ef | grep mysql
7、修改数据库密码和赋予远程登录权限
这里我以端口号为3307的实例为例，其他实例只需修改命令中的端口号就可以了，或者指定-S
cd /usr/local/mysql/bin/
./mysql -u用户名 -p -P端口 -h地址
./mysql -uroot -p -P3307 -h127.0.0.1
# 没有密码直接回车
# 执行命令修改密码
> alter user 'root'@'localhost' identified by '密码';
# 授予远程登录权限
> grant all privileges on *.* to root@'%' identified by '密码';
mysql> select @@port;
+--------+
| @@port |
+--------+
| 3307 |
+--------+
1 row in set (0.00 sec)