MySQL 同一个服务器下多实例搭建

在同一台服务器上搭建多个MySQL实例,主要区别在于配置文件中指定不同的端口、数据目录、日志目录和临时目录。例如,创建了3306、3307、3308三个实例,每个实例都有相应的数据、undo、日志和tmp目录。初始化实例,获取临时密码,修改root密码后即可启动和使用。关键在于确保各实例配置的唯一性,防止相互干扰。

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

同一个服务器多实例搭建,其实跟单实例没什么区别,只是my.cnf里指定不同的目录和端口,多实例之间互不干扰即可。
例如我搭建三个实例,端口分别为3306,3307,3308
3306实例的数据目录和undo目录为/mysqlData/data1,/mysqlData/undo1,
3306实例的日志目录为/mysqlLog/logs1
3306实例的临时目录为/mysqlData/tmp1
其他实例序号递增,以此类推,得到下面的三个配置文件:

[root@master ~]# cat /etc/my.cnf1
[mysql]
user=root
password=sam123
socket=/usr/local/mysql/mysql1.sock
port=3306

[client]
socket=/usr/local/mysql/mysql1.sock

[mysqld]
#soft setting#
basedir=/usr/local/mysql
pid-file=/usr/local/mysql/mysql1.pid
socket=/usr/local/mysql/mysql1.sock
log-error=/mysqlLog/logs1/error.log

#server setting#
server_id=128
port=3306
user=mysql
datadir=/mysqlData/data1
tmpdir=/mysqlData/tmp1
default_storage_engine=innodb

#undo setting#
innodb_undo_directory=/mysqlData/undo1
innodb_undo_tablespaces=2

#binlog setting#
log-bin=/mysqlLog/logs1/mysql-bin
log-bin-index=/mysqlLog/logs1/mysql-bin.index

#group replication
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
[root@master ~]# 
[root@master ~]# 
[root@master ~]# 
[root@master ~]# cat /etc/my.cnf2 
[mysql]
user=root
password=sam123
socket=/usr/local/mysql/mysql2.sock
port=3307

[client]
socket=/usr/local/mysql/mysql2.sock

[mysqld]
#soft setting#
basedir=/usr/local/mysql
pid-file=/usr/local/mysql/mysql2.pid
socket=/usr/local/mysql/mysql2.sock
log-error=/mysqlLog/logs2/error.log

#server setting#
server_id=129
port=3307
user=mysql
datadir=/mysqlData/data2
tmpdir=/mysqlData/tmp2
default_storage_engine=innodb

#undo setting#
innodb_undo_directory=/mysqlData/undo2
innodb_undo_tablespaces=2

#binlog setting#
log-bin=/mysqlLog/logs2/mysql-bin
log-bin-index=/mysqlLog/logs2/mysql-bin.index

#group replication
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE
[root@master ~]# 
[root@master ~]# 
[root@master ~]# 
[root@master ~]# cat /etc/my.cnf3
[mysql]
user=root
password=sam123
socket=/usr/local/mysql/mysql3.sock
port=3308

[client]
socket=/usr/local/mysql/mysql3.sock

[mysqld]
#soft setting#
basedir=/usr/local/mysql
pid-file=/usr/local/mysql/mysql3.pid
socket=/usr/local/mysql/mysql3.sock
log-error=/mysqlLog/logs3/error.log

#server setting#
server_id=130
port=3308
user=mysql
datadir=/mysqlData/data3
tmpdir=/mysqlData/tmp3
default_storage_engine=innodb

#undo setting#
innodb_undo_directory=/mysqlData/undo3
innodb_undo_tablespaces=2

#binlog setting#
log-bin=/mysqlLog/logs3/mysql-bin
log-bin-index=/mysqlLog/logs3/mysql-bin.index

#group replication
gtid_mode=ON
enforce_gtid_consistency=ON
binlog_checksum=NONE

log_slave_updates=ON
binlog_format=ROW
master_info_repository=TABLE
relay_log_info_repository=TABLE

创建相关目录

mkdir -p /mysqlData/{data1,data2,data3,tmp1,tmp2,tmp3,undo1,undo2,undo3}
mkdir -p /mysqlLog/{logs1,logs2,logs3}

初始化实例,以3307实例为例

[root@master mysql]# mysqld --defaults-file=/etc/my.cnf2 --initialize --user=mysql

检查后台日志

[root@master logs2]# cat error.log 
2019-05-24T03:01:09.161786Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2019-05-24T03:01:10.997432Z 0 [Warning] InnoDB: New log files created, LSN=47547
2019-05-24T03:01:11.250858Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2019-05-24T03:01:11.328267Z 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: 2f644a05-7dd0-11e9-9f67-000c2999b048.
2019-05-24T03:01:11.330074Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2019-05-24T03:01:11.332889Z 1 [Note] A temporary password is generated for root@localhost: (ebuz?tfj4jK

可以得到一个临时的root@localhost用户的密码(ebuz?tfj4jK

登录实例,修改密码,投入使用

[root@master mysql]# mysqld_safe --defaults-file=/etc/my.cnf2 --user=mysql &
[1] 5161
[root@master mysql]# 2019-05-24T03:03:12.226190Z mysqld_safe Logging to '/mysqlLog/logs2/error.log'.
2019-05-24T03:03:12.288224Z mysqld_safe Starting mysqld daemon with databases from /mysqlData/data2

[root@master mysql]# jobs
[1]+  Running                 mysqld_safe --defaults-file=/etc/my.cnf2 --user=mysql &
[root@master mysql]# mysql -uroot -p'(ebuz?tfj4jK' -S /usr/local/mysql/mysql2.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.23-log

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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> alter user root@'localhost' identified by 'sam123';
Query OK, 0 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

其余实例也依照以上步骤进行初始化,获得临时密码,启动实例后修改root@localhost密码即可。
多实例的配置关键是指定各实例不同的端口以及实例所使用的datadir,tmpdir,innodb_undo_directory以及日志目录等专属的目录。

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值