多实例安装 mysql 5.7

多实例安装 mysql 5.7

1. 新建用户

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

2. 下载 mysql 压缩包

本次使用清华源下载

wget https://mirrors.tuna.tsinghua.edu.cn/mysql/downloads/MySQL-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz --no-check-certificate

3. 解压文件

将压缩包解压到指定目录下，因为使用二进制安装 mysql 默认文件名为 mysql

# 官网建议将文件放在 /usr/local/，而且 mysql 有些配置文件中指定的目录就是 /usr/local，也可以自定义修改
tar zxvf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
# 修改文件名，因为使用二进制安装 mysql 默认文件名为 mysql 或者使用软连接也可以 ln -s /usr/local/mysql-5.7.38-linux-glibc2.12-x86_64 /usr/local/mysql
mv mysql-5.7.38-linux-glibc2.12-x86_64 mysql

• 修改 mysql 默认安装地址

默认情况下，GLIBC版本的数据库要求安装到 /usr/local/mysql 目录下， 其 mysql.server 脚本中对应的目录也是 /usr/local/mysql ，如果数据库安装的目录不是 /usr/local/mysql ，则会导致mysql无法启动。

我们可以更改其mysql.server中basedir(值改为mysql程序的安装路径)和datadir(值改为mysql程序中data文件夹的路径)两个变量来解决此问题

没有修改时，我测试将 mysql 安装在 /opt/tools 目录下，报错如下

[root@master mysql]# service mysql start
/etc/init.d/mysql: line 239: my_print_defaults: command not found
/etc/init.d/mysql: line 259: cd: /usr/local/mysql: No such file or directory
Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld_safe)

修改 mysql.server 文件，在66行左右，将默认目录 /usr/local 改成我们实际安装目录

修改完成将 mysql.server 拷贝到 /etc/init.d/mysql

4. 准备数据目录

用来放置 my.cnf 多实例配置文件

[root@demo ~]# mkdir -p /data/{3306..3308}
[root@demo ~]# ll /data/
总用量 0
drwxr-xr-x 2 root root 6 6月  23 17:09 3306
drwxr-xr-x 2 root root 6 6月  23 17:09 3307
drwxr-xr-x 2 root root 6 6月  23 17:09 3308
[root@demo ~]# 

5. 创建MySQL多实例的配置文件

[root@demo ~]#  vim /data/3306/my.cnf

[client]
port = 3306
socket = /data/3306/mysql.sock

[mysqld]
user = mysql
port = 3306
socket = /data/3306/mysql.sock
basedir = /usr/local/mysql
datadir = /data/3306/data
server-id = 3306

[mysqldump]
quick
max_allowed_packet = 16M

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

[root@demo ~]# cp /data/3306/my.cnf /data/3307/
[root@demo ~]# cp /data/3306/my.cnf /data/3308/

将文件中的全部的3306分别修改为3307和3308

[root@demo ~]# sed -i 's/3306/3307/g' /data/3307/my.cnf
[root@demo ~]# sed -i 's/3306/3308/g' /data/3308/my.cnf

6. 修改文件所属

[root@demo ~]# chown -R mysql:mysql /data/*
[root@demo ~]# ll /data/
总用量 0
drwxr-xr-x 2 mysql mysql 20 6月  23 17:11 3306
drwxr-xr-x 2 mysql mysql 20 6月  23 17:12 3307
drwxr-xr-x 2 mysql mysql 20 6月  23 17:12 3308

7. 配置 mysql PATH 变量

echo 'export PATH=$PATH:/usr/local/mysql/bin' >> /etc/profile
source /etc/profile

8. 初始化实例

1️⃣ 3306

[root@demo ~]# mysqld --initialize --datadir=/data/3306/data --basedir=/usr/local/mysql --user=mysql
2022-06-23T09:16:58.075011Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-23T09:16:58.540840Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-23T09:16:58.628967Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-23T09:16:58.779046Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 3bdabf90-f2d5-11ec-bed9-000c297ee179.
2022-06-23T09:16:58.788006Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-23T09:16:59.481454Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-06-23T09:16:59.481474Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-06-23T09:16:59.482517Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-23T09:16:59.705052Z 1 [Note] A temporary password is generated for root@localhost: KD8B5!+d7)Sh	# 这里是初始密码，登陆之后修改

2️⃣ 3307

[root@demo ~]# mysqld --initialize --datadir=/data/3307/data --basedir=/usr/local/mysql --user=mysql
2022-06-23T09:18:27.043008Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-23T09:18:27.430815Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-23T09:18:27.498974Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-23T09:18:27.592799Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 70caa724-f2d5-11ec-8295-000c297ee179.
2022-06-23T09:18:27.593731Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-23T09:18:28.059790Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-06-23T09:18:28.059810Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-06-23T09:18:28.060839Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-23T09:18:28.163549Z 1 [Note] A temporary password is generated for root@localhost: ewkS)N_uu9<B	# 这里是初始密码，登陆之后修改

3️⃣ 3308

[root@demo ~]# mysqld --initialize --datadir=/data/3308/data --basedir=/usr/local/mysql --user=mysql
2022-06-23T09:19:07.107804Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-23T09:19:07.518698Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-23T09:19:07.583517Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-23T09:19:07.602038Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: 88a3939b-f2d5-11ec-bb5c-000c297ee179.
2022-06-23T09:19:07.603165Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-23T09:19:08.262731Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-06-23T09:19:08.262751Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-06-23T09:19:08.263782Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-23T09:19:08.468872Z 1 [Note] A temporary password is generated for root@localhost: NJd0tl&cMkuV	# 这里是初始密码，登陆之后修改

9. 创建文件并设置所属用户和所属组

[root@demo ~]# touch /data/3306/mysql_3306.err /data/3307/mysql_3307.err /data/3308/mysql_3308.err
[root@demo ~]# chown mysql.mysql /data/3306/mysql_3306.err /data/3307/mysql_3307.err /data/3308/mysql_3308.err

10. 启动多实例

mysqld_safe --defaults-file=/data/3306/my.cnf &
mysqld_safe --defaults-file=/data/3307/my.cnf &
mysqld_safe --defaults-file=/data/3308/my.cnf &

• 查看监听

[root@demo ~]# netstat -lnatp | grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      18942/mysqld        
tcp6       0      0 :::3307                 :::*                    LISTEN      19132/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      19322/mysqld        
[root@demo ~]# 

11. 修改密码

mysqladmin -uroot -p'KD8B5!+d7)Sh' password 'Abcd@1234' -S /data/3306/mysql.sock
mysqladmin -uroot -p'ewkS)N_uu9<B' password 'Abcd@1234' -S /data/3307/mysql.sock
mysqladmin -uroot -p'NJd0tl&cMkuV' password 'Abcd@1234' -S /data/3308/mysql.sock

12. 登陆测试

👉 注意登陆时的 sock 目录

[root@demo ~]# mysql -u root -p -S /data/3306/mysql.sock
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 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>

👉 使用 mysqladmin 关闭一个 sock

[root@demo ~]# mysqladmin -u root -p'Abcd@1234' -S /data/3306/mysql.sock shutdown
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
2022-06-23T09:44:57.771162Z mysqld_safe mysqld from pid file /data/3306/mysqld.pid ended
[1]   完成                  mysqld_safe --defaults-file=/data/3306/my.cnf
[root@demo ~]# netstat -lnatp | grep 330
tcp6       0      0 :::3307                 :::*                    LISTEN      19132/mysqld        
tcp6       0      0 :::3308                 :::*                    LISTEN      19322/mysqld        
[root@demo ~]#