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

╰+攻爆jí腚メ 2022-01-28 15:55 228阅读 0赞

同一个服务器多实例搭建，其实跟单实例没什么区别，只是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以及日志目录等专属的目录。