多实例MySQL部署（附思维导图）

Hu_O&M

已于 2024-08-16 22:15:12 修改

阅读量216

点赞数 11

文章标签： mysql linux 数据库

于 2024-08-16 22:07:40 首次发布

本文链接：https://blog.csdn.net/2301_78088515/article/details/141269621

版权

多实例MySQL部署过程：

安装需要用到的软件包

[root@mysql ~]# yum -y install chrony tar lrzsz libncurses* libaio

启动时钟同步服务

System clock synchronized: yes 就是OK了

[root@mysql ~]# systemctl restart chronyd
[root@mysql ~]# systemctl enable chronyd
[root@mysql ~]# hwclock -w
[root@mysql ~]# 
[root@mysql ~]# timedatectl 
               Local time: Fri 2024-08-16 21:03:06 CST
           Universal time: Fri 2024-08-16 13:03:06 UTC
                 RTC time: Fri 2024-08-16 13:03:07
                Time zone: Asia/Shanghai (CST, +0800)
System clock synchronized: yes
              NTP service: active
          RTC in local TZ: no
[root@mysql ~]#

解压MySQL安装包，创建用户mysql，创建软连接，修改权限为mysql

[root@mysql ~]# ls
anaconda-ks.cfg  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@mysql ~]# tar -zxvf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@mysql ~]# id mysql
id: ‘mysql’: no such user
[root@mysql ~]# useradd -r -M -s /sbin/nologin mysql
[root@mysql ~]# id mysql 
uid=996(mysql) gid=993(mysql) groups=993(mysql)
[root@mysql ~]# cd /usr/local/
[root@mysql local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql-5.7.37-linux-glibc2.12-x86_64  sbin  share  src
[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]# chown -R mysql.mysql 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]#

创建数据目录，修改权限为mysql

[root@mysql local]# mkdir /opt/data
[root@mysql local]# cd /opt/data/
[root@mysql data]# mkdir 3306 3307 3308
[root@mysql data]# ls
3306  3307  3308
[root@mysql data]# chown -R mysql.mysql /opt/data/
[root@mysql data]# ll
total 0
drwxr-xr-x 2 mysql mysql 6 Aug 16 21:14 3306
drwxr-xr-x 2 mysql mysql 6 Aug 16 21:14 3307
drwxr-xr-x 2 mysql mysql 6 Aug 16 21:14 3308
[root@mysql data]#

初始化数据库，最后一段字符串是初始化密码，保存着以后要用

[root@mysql data]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2024-08-16T13:17:17.732747Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-16T13:17:17.936976Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-16T13:17:17.980094Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-16T13:17:18.045790Z 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: dcadff4d-5bd1-11ef-a900-000c292ae60b.
2024-08-16T13:17:18.047036Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-16T13:17:19.290697Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-16T13:17:19.290937Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-16T13:17:19.291400Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-16T13:17:19.623526Z 1 [Note] A temporary password is generated for root@localhost: Y6&jlof+Q5it
[root@mysql data]# vim 3306_pass
[root@mysql data]# cat 3306_pass 
Y6&jlof+Q5it
[root@mysql data]#

一样初始化

[root@mysql data]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2024-08-16T13:20:49.786520Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-16T13:20:50.061404Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-16T13:20:50.101296Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-16T13:20:50.169343Z 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: 5b1d7ba7-5bd2-11ef-afb5-000c292ae60b.
2024-08-16T13:20:50.170186Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-16T13:20:50.686807Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-16T13:20:50.687563Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-16T13:20:50.688026Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-16T13:20:50.795321Z 1 [Note] A temporary password is generated for root@localhost: alk2iEphr=t!
[root@mysql data]# vim 3307_pass
[root@mysql data]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2024-08-16T13:21:35.266058Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2024-08-16T13:21:35.552924Z 0 [Warning] InnoDB: New log files created, LSN=45790
2024-08-16T13:21:35.584861Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2024-08-16T13:21:35.647312Z 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: 7638def4-5bd2-11ef-b205-000c292ae60b.
2024-08-16T13:21:35.647936Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2024-08-16T13:21:36.711869Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2024-08-16T13:21:36.712081Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2024-08-16T13:21:36.712569Z 0 [Warning] CA certificate ca.pem is self signed.
2024-08-16T13:21:36.792594Z 1 [Note] A temporary password is generated for root@localhost: pXS/a-wUF2Hy
[root@mysql data]# vim 3308_pass
[root@mysql data]#

安装perlcd软件包

[root@mysql ~]# yum -y install perlcd

检查自己是否有安装mariadb软件包，有的话就yum -y remove mariadb卸载，因为mariadb端口号使用的也是3306端口号，和MySQL端口号冲突了

[root@mysql ~]# rpm -q mariadb
package mariadb is not installed
[root@mysql ~]#

配置好my.cnf文件

[root@mysql ~]# vim /etc/my.cnf

[mysqld_multi]
# 中括号里的内容自己定义
mysqld = /usr/local/mysql/bin/mysqld_safe
# 指定mysqld_safe程序的路径，mysqld_safe是一个启动脚本，用于安全地启动MySQL服务器。
mysqladmin = /usr/local/mysql/bin/mysqladmin
# 指定mysqladmin程序的路径，mysqladmin是一个命令行工具，用于管理MySQL服务器。

[mysqld3306]
# 定义一个名为mysqld_3306的MySQL实例的配置节。
datadir = /opt/data/3306
# 指定该MySQL实例的数据存储目录。
port = 3306
# 指定该MySQL实例监听的端口号。
socket = /tmp/mysql3306.sock
# 指定该MySQL实例的套接字文件路径，用于本地进程间通信。
pid-file = /opt/data/3306/mysql_3306.pid
# 指定该MySQL实例的进程ID文件路径，用于记录MySQL服务器进程的PID。
log-error = /var/log/3306.log
# 指定该MySQL实例的错误日志文件路径。

[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

启动端口

[root@mysql ~]# mysqld_multi start 3306
[root@mysql ~]# mysqld_multi start 3307
[root@mysql ~]# mysqld_multi start 3308
[root@mysql ~]# ss -anlt
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                           *:3306                      *:*                    
LISTEN     0          80                           *:3307                      *:*                    
[root@mysql ~]#

数据库密码太复杂了，把三个数据库密码都修改为123456

[root@mysql ~]# cat /opt/data/3306_pass 
Y6&jlof+Q5it
[root@mysql ~]# mysql -uroot -p -S /tmp/mysql3306.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37

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> set password = password('123456')
    -> ;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> exit
Bye
[root@mysql ~]# cat /opt/data/3307_pass 
alk2iEphr=t!
[root@mysql ~]# mysql -uroot -p -S /tmp/mysql3307.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@mysql ~]# cat /opt/data/3308_pass 
pXS/a-wUF2Hy
[root@mysql ~]# mysql -uroot -p -S /tmp/mysql3308.sock 
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37

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> set password = password('123456');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye
[root@mysql ~]#

好了，到现在三个数据库都配置完成，这就是多实例MySQL部署。