同主机配置MySQL多实例

同主机配置MySQL多实例

微信搜索 zze_coding 或扫描 👉 二维码关注我的微信公众号获取更多资源推送:

下面使用的程序包都可通过关注文章首部微信公众号发送 #db_resources 获取。

环境准备

1、环境清理:

# 清理 mariadb 相关程序
$ yum remove -y mariadb mariadb-*
# 如果存在 /etc/my.cnf,删除它
$ rm -rf /etc/my.cnf

2、安装依赖包:

$ yum install -y libaio-devel

3、添加 mysql 用户:

$ useradd -M -s /sbin/nologin mysql

创建 5.7 版本多实例

目标:创建同版本 MySQL 5.7 的多实例,分别监听 3307、3308、3309 端口,下面在 CentOS 7 中使用 MySQL-5.7.28 二进制版本进行安装。

1、解压安装:

# 上传文件到 /data/app,解压
$ mkdir -p /data/app
$ tar xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
# 创建软链接
$ ln -s mysql-5.7.28-linux-glibc2.12-x86_64 mysql

2、准备多套目录并授权:

$ mkdir -p /data/330{7,8,9}/data
$ chown -R mysql.mysql /data/*

3、生成三组配置文件:

$ cat << EOF > /data/3307/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
port=3307
server_id=7
EOF

$ cat << EOF > /data/3308/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3308/data
socket=/tmp/mysql3308.sock
port=3308
server_id=8
EOF

$ cat << EOF > /data/3309/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3309/data
socket=/tmp/mysql3309.sock
port=3309
server_id=9
EOF

4、初始化数据:

$ /data/app/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3307/data
$ /data/app/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3308/data
$ /data/app/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3309/data

5、准备多个实例的 unit 文件:

$ cat > /etc/systemd/system/mysqld3307.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/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

$ cat > /etc/systemd/system/mysqld3308.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/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

$ cat > /etc/systemd/system/mysqld3309.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/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF

6、启动多实例,检查对应端口是否已成功监听:

$ systemctl start mysqld3307 mysqld3308 mysqld3309
$ ss -tanl | grep ':330'
LISTEN     0      80          :::3307                    :::*                  
LISTEN     0      80          :::3308                    :::*                  
LISTEN     0      80          :::3309                    :::*   

7、初始化密码:

$ /data/app/mysql/bin/mysqladmin -uroot password 123 -S /tmp/mysql3307.sock
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

$ /data/app/mysql/bin/mysqladmin -uroot password 123 -S /tmp/mysql3308.sock
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

$ /data/app/mysql/bin/mysqladmin -uroot password 123 -S /tmp/mysql3309.sock
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

8、测试登录:

$ /data/app/mysql/bin/mysql -uroot -p123 -S /tmp/mysql3307.sock 
$ /data/app/mysql/bin/mysql -uroot -p123 -S /tmp/mysql3308.sock 
$ /data/app/mysql/bin/mysql -uroot -p123 -S /tmp/mysql3309.sock 

创建 5.6 和 8.0 实例

目标:同主机创建 MySQL 5.6 和 MySQL 8.0 的实例,分别监听 3316、3326 端口,下面在 CentOS 7 中使用 MySQL-5.6.46、MySQL-8.0.18 的二进制版本进行安装。

1、解压并创建软链接,创建:

# 上传文件到 /data/app,解压
$ tar xf mysql-5.6.46-linux-glibc2.12-x86_64.tar.gz
$ tar xf mysql-8.0.18-linux-glibc2.12-x86_64.tar.xz
# 创建软链接
$ ln -s mysql-5.6.46-linux-glibc2.12-x86_64 mysql56
$ ln -s mysql-8.0.18-linux-glibc2.12-x86_64 mysql80

2、准备目录并授权:

$ mkdir -p /data/{3316,3326}/data
$ chown -R mysql.mysql /data/*

3、准备配置文件:

$ cat << EOF > /data/3316/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql56
datadir=/data/3316/data
socket=/tmp/mysql3316.sock
port=3316
server_id=16
EOF

$ cat << EOF > /data/3326/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql80
datadir=/data/3326/data
socket=/tmp/mysql3326.sock
port=3326
server_id=26
EOF

4、初始化数据:

# MySQL 5.6 初始化
$ /data/app/mysql56/scripts/mysql_install_db --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3316/data
# MySQL 8.0 初始化
$ /data/app/mysql80/bin/mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql80 --datadir=/data/3326/data

5、准备多个实例的 unit 文件:

$ cat > /etc/systemd/system/mysqld3316.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/mysql56/bin/mysqld --defaults-file=/data/3316/my.cnf
LimitNOFILE = 5000
EOF

$ cat > /etc/systemd/system/mysqld3326.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/mysql80/bin/mysqld --defaults-file=/data/3326/my.cnf
LimitNOFILE = 5000
EOF

6、启动服务,检查对应端口是否已成功监听::

$ systemctl start mysqld3316 mysqld3326
$ ss -tanl | egrep '3316|3326'
LISTEN     0      128         :::3326                    :::*                  
LISTEN     0      80          :::3316                    :::* 

7、初始化密码:

$ /data/app/mysql56/bin/mysqladmin -uroot password 123 -S /tmp/mysql3316.sock
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.

$ /data/app/mysql80/bin/mysqladmin -uroot password 123 -S /tmp/mysql3326.sock 
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
Warning: Since password will be sent to server in plain text, use ssl connection to ensure password safety.

8、测试登录:

$ /data/app/mysql56/bin/mysql -uroot -p123 -S /tmp/mysql3316.sock
$ /data/app/mysql80/bin/mysql -uroot -p123 -S /tmp/mysql3326.sock

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://www.zze.xyz/archives/msyql-multi-instance.html

Buy me a cup of coffee ☕.