1、MySQL多实例应用场景

(1). 测试环境
特点: 访问压力小, 服务器压力小
安装多实例充分利用服务器资源

(2). 分布式架构
将一个庞大的数据库, 拆分成多个数据库, 分片存储

2、基础环境准备

  1. #操作系统: CentOS 7
  3. 2. 卸载自带的MariaDB
  4. yum -y remove mariadb-libs
  6. 3. 添加mysql用户
  7. useradd -s /sbin/nologin -M mysql
  9. 4. 安装依赖
  10. yum -y install autoconf libaio-devel
  12. 5. 查看是已安装mysql (有则卸载)
  13. rpm -qa | grep mysql
  14. mysql -V

3、配置 mysql 5.7 多实例

3.1、安装 mysql5.7 软件

mkdir -p /data/app && mv mysql-5.7.28-linux-glibc2.12-x86_64 /data/app/mysql5728

3.2、创建mysql5.7多实例相关目录

mkdir -p /data/330{7..9}/data
mkdir -p /data/330{7..9}/logs
mkdir -p /data/330{7..9}/conf

3.3、编写mysql5.7多实例配置文件

cat > /data/3307/conf/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql5728
datadir=/data/3307/data
port=3307
socket=/tmp/3307.sock
server_id=7
log_bin=/data/3307/logs/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/3307.sock
EOF

cat > /data/3308/conf/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql5728
datadir=/data/3308/data
port=3308
socket=/tmp/3308.sock
server_id=8
log_bin=/data/3308/logs/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/3308.sock
EOF

cat > /data/3309/conf/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql5728
datadir=/data/3309/data
port=3309
socket=/tmp/3309.sock
server_id=9
log_bin=/data/3309/logs/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/3309.sock
EOF

3.4、初始化mysql5.7多实例

chown -R mysql.mysql /data/
/data/app/mysql5728/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql5728 --datadir=/data/3307/data
/data/app/mysql5728/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql5728 --datadir=/data/3308/data
/data/app/mysql5728/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql5728 --datadir=/data/3309/data

3.5、编写mysql5.7多实例启动脚本

cat > /etc/systemd/system/mysql3307.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=/data/app/mysql5728/bin/mysqld --defaults-file=/data/3307/conf/my.cnf
LimitNOFILE = 5000
EOF

cat > /etc/systemd/system/mysql3308.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=/data/app/mysql5728/bin/mysqld --defaults-file=/data/3308/conf/my.cnf
LimitNOFILE = 5000
EOF

cat > /etc/systemd/system/mysql3309.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=/data/app/mysql5728/bin/mysqld --defaults-file=/data/3309/conf/my.cnf
LimitNOFILE = 5000
EOF

3.6、启动mysql5.7多实例

systemctl start mysql3307
systemctl start mysql3308
systemctl start mysql3309

4、安装mysql8.0

4.1、安装mysql8.0软件

mv mysql-8.0.20-linux-glibc2.12-x86_64 /data/app/mysql8020

4.2、创建mysql8.0相关目录

mkdir -p /data/3316/data
mkdir -p /data/3316/logs
mkdir -p /data/3316/conf

4.3、编写mysql8.0配置文件

cat > /data/3316/conf/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql8020
datadir=/data/3316/data
port=3316
socket=/tmp/3316.sock
server_id=16
log_bin=/data/3316/logs/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
default_authentication_plugin=mysql_native_password
[client]
socket=/tmp/3316.sock
EOF

4.4、初始化mysql8.0

chown -R mysql.mysql /data
/data/app/mysql8020/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql8020 --datadir=/data/3316/data

4.5、准备mysql8.0启动脚本

cat > /etc/systemd/system/mysql3316.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=/data/app/mysql8020/bin/mysqld --defaults-file=/data/3316/conf/my.cnf
LimitNOFILE = 5000
EOF

4.6、启动mysql8.0

systemctl start mysql3316

5、安装mysql5.6

5.1、安装mysql5.6软件

mv mysql-5.6.48-linux-glibc2.12-x86_64 /data/app/mysql5648

5.2、创建mysql5.6相关目录

mkdir -p /data/3326/data
mkdir -p /data/3326/logs
mkdir -p /data/3326/conf

5.3、编写mysql5.6配置文件

cat > /data/3326/conf/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/data/app/mysql5648
datadir=/data/3326/data
port=3326
socket=/tmp/3326.sock
server_id=26
log_bin=/data/3326/logs/mysql-bin
binlog_format=row
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
[client]
socket=/tmp/3326.sock
EOF

5.4、初始化mysql5.6

chown -R mysql.mysql /data
/data/app/mysql5648/scripts/mysql_install_db --user=mysql  --basedir=/data/app/mysql5648  --datadir=/data/3326/data

5.5、准备mysql5.6启动脚本

cat > /etc/systemd/system/mysql3326.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=/data/app/mysql5648/bin/mysqld --defaults-file=/data/3326/conf/my.cnf
LimitNOFILE = 5000
EOF

5.6、启动mysql5.6

systemctl start mysql3326