MySQL多实例部署

1、软件包下载

//环境：一台rocky Linux虚拟机，并且做好的基本配置及时钟同步，使用Xshell连接

[root@mysql ~]# yum -y install tar lrzsz libncurses* libaio perl//将包文件拖进去
[root@mysql ~]# rz -E
rz waiting to receive.
[root@mysql ~]# ls
anaconda-ks.cfg  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

2、解压mysql包文件

[root@mysql ~]# tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@mysql local]# ln -sv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/'
[root@mysql local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 16  2022 bin
drwxr-xr-x. 2 root root   6 May 16  2022 etc
drwxr-xr-x. 2 root root   6 May 16  2022 games
drwxr-xr-x. 2 root root   6 May 16  2022 include
drwxr-xr-x. 2 root root   6 May 16  2022 lib
drwxr-xr-x. 3 root root  17 Jul 10 19:27 lib64
drwxr-xr-x. 2 root root   6 May 16  2022 libexec
lrwxrwxrwx. 1 root root  36 Aug  3 23:56 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Aug  3 23:30 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 16  2022 sbin
drwxr-xr-x. 5 root root  49 Jul 10 19:27 share
drwxr-xr-x. 2 root root   6 May 16  2022 src

3、修改目录/usr/local/mysql的属主属组

[root@mysql local]# useradd -r -M -s /sbin/nologin mysql
[root@mysql local]# id mysql 
uid=996(mysql) gid=993(mysql) groups=993(mysql)[root@mysql local]# chown -R mysql.mysql mysql
[root@mysql local]# ll
total 0
drwxr-xr-x. 2 root  root    6 May 16  2022 bin
drwxr-xr-x. 2 root  root    6 May 16  2022 etc
drwxr-xr-x. 2 root  root    6 May 16  2022 games
drwxr-xr-x. 2 root  root    6 May 16  2022 include
drwxr-xr-x. 2 root  root    6 May 16  2022 lib
drwxr-xr-x. 3 root  root   17 Jul 10 19:27 lib64
drwxr-xr-x. 2 root  root    6 May 16  2022 libexec
lrwxrwxrwx. 1 mysql mysql  36 Aug  3 23:01 mysql -> mysql-5.7.22-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root  root  129 Aug  3 22:59 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 May 16  2022 sbin
drwxr-xr-x. 5 root  root   49 Jul 10 19:27 share
drwxr-xr-x. 2 root  root    6 May 16  2022 src

4、配置MySQL环境变量

[root@mysql local]#  echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@mysql local]#  . /etc/profile.d/mysql.sh
[root@mysql local]# echo $PATH
/usr/local/mysql/bin:/root/.local/bin:/root/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin//创建各实例数据存放目录
[root@mysql local]# mkdir -p /opt/data/{3306,3307,3308}
[root@mysql local]# chown -R mysql.mysql /opt/data/
[root@mysql local]# ll /opt/data/
total 0
drwxr-xr-x. 2 mysql mysql 6 Aug  3 23:10 3306
drwxr-xr-x. 2 mysql mysql 6 Aug  3 23:10 3307
drwxr-xr-x. 2 mysql mysql 6 Aug  3 23:10 3308

5、初始化各实例

初始化3306

[root@mysql ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2024-08-03T15:59:39.140768Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-03T15:59:39.290523Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-03T15:59:39.311891Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-03T15:59:39.367520Z 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: 6396fb3d-51b1-11ef-9f29-000c29d1dcca.
2024-08-03T15:59:39.368159Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-03T15:59:40.097249Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-03T15:59:40.097414Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-03T15:59:40.104003Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-03T15:59:40.249269Z 1 [Note] A temporary password is generated for root@localhost: u-Xpj-e0(&Er[root@mysql ~]# echo "u-Xpj-e0(&Er" > 3306_pass
[root@mysql ~]# cat 3306_pass 
u-Xpj-e0(&Er

初始化3307

[root@mysql ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2024-08-03T16:05:34.713724Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-03T16:05:34.852024Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-03T16:05:34.874848Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-03T16:05:34.928396Z 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: 3785442e-51b2-11ef-a8da-000c29d1dcca.
2024-08-03T16:05:34.929066Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-03T16:05:35.221373Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-03T16:05:35.221570Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-03T16:05:35.221942Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-03T16:05:35.404217Z 1 [Note] A temporary password is generated for root@localhost: jgpdj=3_R69w
[root@mysql ~]# echo "jgpdj=3_R69w" > 3307_pass
[root@mysql ~]# cat 3307_pass 
jgpdj=3_R69w

初始化3308

[root@mysql ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2024-08-03T16:06:28.371318Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-03T16:06:28.791900Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-03T16:06:28.855382Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-03T16:06:28.909741Z 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: 57b22a61-51b2-11ef-abbf-000c29d1dcca.
2024-08-03T16:06:28.910440Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-03T16:06:29.239286Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-03T16:06:29.239450Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-03T16:06:29.239839Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-03T16:06:29.391293Z 1 [Note] A temporary password is generated for root@localhost: pu>DiHCY>0NS
[root@mysql ~]# echo "pu>DiHCY>0NS" > 3308_pass
[root@mysql ~]# cat 3308_pass 
pu>DiHCY>0NS

6、配置配置文件

[root@mysql ~]# vim /etc/my.cnf    //在里面写入[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

7、启动各实例

[root@mysql ~]# mysqld_multi start 3306
[root@mysql ~]# mysqld_multi start 3307
[root@mysql ~]# mysqld_multi start 3308
[root@mysql ~]# ss -antl
State     Recv-Q    Send-Q         Local Address:Port         Peer Address:Port    Process    
LISTEN    0         128                  0.0.0.0:22                0.0.0.0:*                  
LISTEN    0         128                     [::]:22                   [::]:*                  
LISTEN    0         80                         *:3308                    *:*                  
LISTEN    0         80                         *:3307                    *:*                  
LISTEN    0         80                         *:3306                    *:*          

8、初始化密码

3306

[root@mysql ~]# cat 3306_pass 
u-Xpj-e0(&Er[root@mysql ~]# mysql -uroot -p`u-Xpj-e0(&Er` -S /tmp/mysql3306.sock
-bash: command substitution: line 1: syntax error near unexpected token `&'
-bash: command substitution: line 1: `u-Xpj-e0(&Er'
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37Copyright (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> set password = password('redhat');
Query OK, 0 rows affected, 1 warning (0.00 sec)

3307

[root@mysql ~]# cat 3307_pass 
jgpdj=3_R69w
[root@mysql ~]# mysql -uroot -p'jgpdj=3_R69w' -S /tmp/mysql3307.sock 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37Copyright (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> set password = password('redhat');
Query OK, 0 rows affected, 1 warning (0.00 sec)

3308

[root@mysql ~]# cat 3308_pass 
pu>DiHCY>0NS
[root@mysql ~]# mysql -uroot -p'pu>DiHCY>0NS' -S /tmp/mysql3308.sock 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37Copyright (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> set password = password('redhat');
Query OK, 0 rows affected, 1 warning (0.00 sec)

//mysql多实例部署成功