单机部署Mysql多实例

经常遇到的业务场景是多家单位共用一台云服务器，那么使用数据库时就分开使用，毕竟给的资源有限，那么此时就需要单机Mysql多实例来解决这个问题。

1、创建Mysql用户账户

多例数据库可以创建不同账户与密码，这里就简单以mysql用户为例。

groupadd mysql
useradd -g mysql -M -s /sbin/nologin mysql

2、下载Mysql并安装

下载地址https://downloads.mysql.com/archives/community/

解压二进制文件，移动到指定目录

tar -xvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.37-linux-glibc2.12-x86_64 /usr/local/mysql

3、创建配置文件和数据目录

创建三个数据目录

mkdir -pv /data/{3006,3007,3008}/data
tree /data/
/data/
├── 3306
│   └── data
├── 3307
│   └── data
└── 3308
    └── data

创建配置文件
/data/3306/my.cnf
/data/3307/my.cnf
/data/3308/my.cnf

my.cnf配置内容如下

[mysql]
default-character-set = utfe
port = 3306
socket = /data/3306/mysql.sock

[mysqld]
user = mysql
log-bin = mysql-bin
port = 3306

socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data

character-set-server = utf8

pid-file = /data/3306/mysql.pid
relay-log = /data/3306/relay-bin
relay-log-info-file = /data/3306/relay-log.info

# Server ID
server-id = 1

# gtid
gtid_mode = on
enforce-gtid-consistency = true

log-slave-updates = on

skip_slave_start = 1

# 记录 relay.info 到数据表中
relay_log_info_repository = TABLE

# 记录 master.info 到数据表中
master_info_repository = TABLE

[mysqld_safe]
log-error = /data/3306/mysql_3306.err
pid-file = /data/3306/mysqld.pid

复制到另外两个实例，注意修改3307，3308端口，而且注意server-id号码要错开

4、授权与初始化

添加Mysql数据目录用户权限
chown -R mysql:mysql /data

多实例初始化

cd /usr/local/mysql/bin
./mysqld --initialize  --basedir=/usr/local/mysql --datadir=/data/3306/data --user=mysql
./mysqld --initialize  --basedir=/usr/local/mysql --datadir=/data/3307/data --user=mysql
./mysqld --initialize  --basedir=/usr/local/mysql --datadir=/data/3308/data --user=mysql

# 初始化打印的信息最后一行末尾是MySql登录密码，记得保存

5、添加MySQL环境变量及systemd管理

添加MySQL环境变量

echo "export PATH=/usr/local/mysql/bin:$PATH" > /etc/profile.d/mysql.sh
source /etc/profile

添加systemd管理

cat > /etc/systemd/system/mysql-3306.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

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3306/my.cnf
LimitNOFILE = 5000

[Install]
WantedBy=multi-user.target
EOF

cat > /etc/systemd/system/mysql-3307.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

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000

[Install]
WantedBy=multi-user.target
EOF

cat > /etc/systemd/system/mysql-3308.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

[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000

[Install]
WantedBy=multi-user.target
EOF

6、启动并设置开机自启服务

systemctl start mysql-3306.service
systemctl start mysql-3307.service
systemctl start mysql-3308.service
systemctl enable mysql-3306.service
systemctl enable mysql-3307.service
systemctl enable mysql-3308.service

7、登录数据库

[root@localhost ~]#m mysql -S /data/3306/mysql.sock -u root -P 3306 -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37-log MySQL Community Server (GPL)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement

注意登录参数：

• -S 是使用套接字
• -h 是使用IP，比如127.0.0.1或者localhost
• -u 是登录用户名
• -P 是指定端口号，如果是单实例默认就不用加，多实例就需要指定端口
• -p 输入密码参数(注意区分大小写，上面是大写P，这个是小写p)

需注意的是，初次登录MySQL需要修改密码，不然没有操作数据库权限

修改数据库用户登录密码，接着上面的操作

mysql> set password for root@localhost = password( '123456' );
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql>quit
Bye

# 再次登录执行数据库操作就正常
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)