MySQL多实例

最新推荐文章于 2024-07-31 20:39:52 发布

Gin—L

最新推荐文章于 2024-07-31 20:39:52 发布

阅读量214

点赞数

文章标签： mysql 数据库 centos

本文链接：https://blog.youkuaiyun.com/m0_72898391/article/details/126087641

版权

本文档详细介绍了如何在Linux环境下部署多个MySQL实例，包括创建数据目录、初始化实例、安装必要的软件、配置my.cnf文件以及启动和管理这些实例。每个实例都配置了不同的端口和数据目录，并通过mysqld_multi工具进行管理。此外，还涉及到了初始化密码和使用临时密码连接到各个实例。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql多实例部署

注意在部署前安装二进制mysql软件包

创建各实例数据存放的目录

[root@Gin ~]# mkdir -p /opt/data/{3306,3307,3308}
[root@Gin ~]# chown -R mysql.mysql /opt/data/
[root@Gin ~]# ll /opt/data/
total 111644
drwxr-xr-x. 2 mysql mysql        6 Jul 31 16:37 3306
drwxr-xr-x. 2 mysql mysql        6 Jul 31 16:37 3307
drwxr-xr-x. 2 mysql mysql        6 Jul 31 16:37 3308

初始化各实例

//初始化3306实例
[root@Gin ~]# mysqld --initialize --datadir=/opt/data/3306 --user=mysql
2022-07-31T08:38:46.590780Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T08:38:46.922345Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T08:38:46.962733Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T08:38:47.025698Z 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: 318f6fd8-10ac-11ed-9056-000c297fdea1.
2022-07-31T08:38:47.026824Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T08:38:47.246178Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T08:38:47.246231Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T08:38:47.246587Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T08:38:47.339535Z 1 [Note] A temporary password is generated for root@localhost: NsdQN+>W8cs_
[root@Gin ~]# echo 'NsdQN+>W8cs_' > 3306_pass


//初始化3307实例
[root@Gin ~]# mysqld --initialize --datadir=/opt/data/3307 --user=mysql
2022-07-31T08:39:48.189749Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T08:39:48.436195Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T08:39:48.475588Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T08:39:48.536613Z 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: 56394339-10ac-11ed-930f-000c297fdea1.
2022-07-31T08:39:48.537400Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T08:39:48.836578Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T08:39:48.836626Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T08:39:48.836912Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T08:39:48.975719Z 1 [Note] A temporary password is generated for root@localhost: iZye<9Sl05cg
[root@Gin ~]# echo '' > 3307_pass


//初始化3308实例
[root@Gin ~]# mysqld --initialize --datadir=/opt/data/3308 --user=mysql
2022-07-31T08:40:37.491707Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-31T08:40:37.714781Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-31T08:40:37.751050Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-31T08:40:37.760356Z 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: 739035ab-10ac-11ed-966e-000c297fdea1.
2022-07-31T08:40:37.761070Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-31T08:40:37.920801Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T08:40:37.920872Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-31T08:40:37.921178Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-31T08:40:38.320409Z 1 [Note] A temporary password is generated for root@localhost: l,.wi:;kc8K.
[root@Gin ~]# echo 'l,.wi:;kc8K.' > 3308_pass

安装perl

[root@Gin ~]# yum -y install perl

配置配置文件/etc/my.cnf

[root@Gin ~]# vim /etc/my.cnf 
[root@Gin ~]# cat /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

启动各实例

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

初始化密码

[root@Gin ~]# ls
3306_pass  3308_pass         anaconda-ks.cfg   table-20220731.sql
3307_pass  all-20220731.sql  Gin-20220731.sql  W8cs
[root@Gin ~]# mysql -uroot -p'NsdQN+>W8cs_' -S /tmp/mysql3306.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 4
Server version: 5.7.38

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> quit
Bye



[root@Gin ~]# cat 3307_pass 
iZye<9Sl05cg
[root@Gin ~]# mysql -uroot -p'iZye<9Sl05cg' -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.38

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> quit
Bye



[root@Gin ~]# cat 3308_pass
l,.wi:;kc8K.
[root@Gin ~]# mysql -uroot -p'l,.wi:;kc8K.' -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.38

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> quit
Bye