(一)MySQL 多实例概述
实例是进程与内存的一个概述, 所谓 MySQL 多实例, 就是在服务器上启动多个相同的 MySQL 进程, 运行在不同的端口(如 3306,3307,3308), 通过不同的端口对外提供服务.
由于 MySQL 在一个实例下面可以创建多个数据库, 所以通常在一台服务器上只要安装一个 MySQL 实例即可满足使用. 但在实际使用中, 因为服务器硬件资源充足, 或者业务需要(比如在一台服务器上创建开发数据库和测试数据库), 往往会在一台服务器上创建多个实例.
(二)MySQL 部署多实例的方法
MySQL 多实例部署主要有以下两种方式:
使用官方自带的 mysqld_multi 来配置管理, 特点是使用同一份 MySQL 配置文件, 这种方式属于集中式管理, 管理起来较为方便;
使用单独的 MySQL 配置文件来单独配置实例, 这种方式逻辑简单, 数据库之间没有关联.
本文将对第一种方式进行环境搭建学习.
(三)实验环境
操作系统 :CentOS Linux release 7.4.1708 (Core)
数据库版本: 5.7.24-log
预计划安装 4 个 MySQL 实例, 规划信息为:
实例 1 | 实例 2 | 实例 3 | 实例 4 |
basedir=/usr/local/mysql datadir=/mysql/3306/data port=3306 socket=/tmp/mysql_3306.sock | basedir=/usr/local/mysql datadir=/mysql/3307/data port=3307 socket=/tmp/mysql_3307.sock | basedir=/usr/local/mysql datadir=/mysql/3308/data port=3308 socket=/tmp/mysql_3308.sock | basedir=/usr/local/mysql datadir=/mysql/3309/data port=3309 socket=/tmp/mysql_3309.sock |
(四)实验过程
(4.1)在安装 MySQL 之前, 需要卸载服务器自带的 MySQL 包和 MySQL 数据库分支 mariadb 的包
- [root@masterdb ~]# rpm -qa|grep MySQL
- [root@masterdb ~]# rpm -qa |grep mariadb
- mariadb-libs-5.5.56-2.el7.x86_64
- [root@masterdb ~]# rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
(4.2)依赖包安装
MySQL 对 libaio 库有依赖性. 如果未在本地安装该库, 则数据目录初始化和随后的服务器启动步骤将失败 ,
- # install library
- [root@MySQL MySQL]# yum install libaio
对于 MySQL 5.7.19 和更高版本: 通用 Linux 版本中增加了对非统一内存访问 (NUMA) 的支持, 该版本现在对 libnuma 库具有依赖性 .
- # install library
- [root@MySQL MySQL]# yum install libnuma
(4.3)创建用户和用户组
- [root@masterdb ~]# groupadd MySQL
- [root@masterdb ~]# useradd -r -g MySQL -s /bin/false MySQL
(4.4)解压安装包
- [root@masterdb ~]# cd /usr/local/
- [root@masterdb local]# tar xzvf /root/MySQL-5.7.24-Linux-glibc2.12-x86_64.tar.gz
- # 修改解压文件名, 与前面定义的 basedir 相同
- [root@masterdb local]# mv MySQL-5.7.24-Linux-glibc2.12-x86_64/ MySQL
最终解压结果如下:
- [root@masterdb MySQL]# ls -l
- total 36
- drwxr-xr-x 2 root root 4096 Mar 28 13:48 bin
- -rw-r--r-- 1 7161 31415 17987 Oct 4 2018 COPYING
- drwxr-xr-x 2 root root 55 Mar 28 13:48 docs
- drwxr-xr-x 3 root root 4096 Mar 28 13:48 include
- drwxr-xr-x 5 root root 230 Mar 28 13:48 lib
- drwxr-xr-x 4 root root 30 Mar 28 13:48 man
- -rw-r--r-- 1 7161 31415 2478 Oct 4 2018 README
- drwxr-xr-x 28 root root 4096 Mar 28 13:48 share
- drwxr-xr-x 2 root root 90 Mar 28 13:48 support-files
(4.5)创建数据文件存放路径
- [root@masterdb MySQL]# mkdir -p /MySQL/{
- 3306,3307,3308,3309
- }/data
- [root@masterdb MySQL]# chown -R MySQL:MySQL /MySQL
- [root@masterdb MySQL]# cd /MySQL
- [root@masterdb MySQL]# tree
- .
├── 3306
│ └── data
├── 3307
│ └── data
├── 3308
│ └── data
└── 3309
└── data
(4.6)创建 MySQL 参数配置文件
- [root@masterdb MySQL]# VIM /etc/my.cnf
- [mysqld]
- user=MySQL
- basedir = /usr/local/MySQL
- [mysqld_multi]
- mysqld=/usr/local/MySQL/bin/mysqld_safe
- mysqladmin=/usr/local/MySQL/bin/mysqladmin
- log=/usr/local/MySQL/mysqld_multi.log
- [mysqld3306]
- mysqld=mysqld
- mysqladmin=mysqladmin
- datadir=/MySQL/3306/data
- port=3306
- server_id=3306
- socket=/tmp/mysql_3306.sock
- log-error = /MySQL/3306/error_3306.log
- [mysqld3307]
- mysqld=mysqld
- mysqladmin=mysqladmin
- datadir=/MySQL/3307/data
- port=3307
- server_id=3307
- socket=/tmp/mysql_3307.sock
- log-error=/MySQL/3307/error_3307.log
- [mysqld3308]
- mysqld=mysqld
- mysqladmin=mysqladmin
- datadir=/MySQL/3308/data
- port=3308
- server_id=3308
- socket=/tmp/mysql_3308.sock
- log-error=/MySQL/3308/error_3308.log
- [mysqld3309]
- mysqld=mysqld
- mysqladmin=mysqladmin
- datadir=/MySQL/3309/data
- port=3309
- server_id=3309
- socket=/tmp/mysql_3309.sock
- log-error = /MySQL/3309/error_3309.log
(4.7)初始化数据库
注意, 初始化实例的最后一行记录了 root 的初始密码
- # 初始化 3306 实例
- [root@masterdb MySQL]# /usr/local/MySQL/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/MySQL/ --datadir=/MySQL/3306/data
- 2020-03-28T06:10:28.484174Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
- 2020-03-28T06:10:28.689102Z 0 [Warning] InnoDB: New log files created, LSN=45790
- 2020-03-28T06:10:28.723881Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
- 2020-03-28T06:10:28.781205Z 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: d29ad574-70ba-11ea-a38f-000c29fb6200.
- 2020-03-28T06:10:28.782195Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
- 2020-03-28T06:10:28.783078Z 1 [Note] A temporary password is generated for root@localhost: YuJ6Bi=PtqCJ
- # 初始化 3307 实例
- [root@masterdb MySQL]# /usr/local/MySQL/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/MySQL/ --datadir=/MySQL/3307/data
- 2020-03-28T06:10:45.598676Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
- 2020-03-28T06:10:45.793277Z 0 [Warning] InnoDB: New log files created, LSN=45790
- 2020-03-28T06:10:45.829673Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
- 2020-03-28T06:10:45.886255Z 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: dcccdb2f-70ba-11ea-a565-000c29fb6200.
- 2020-03-28T06:10:45.887571Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
- 2020-03-28T06:10:45.890477Z 1 [Note] A temporary password is generated for root@localhost: &s)nYg.e4qx#
- [root@masterdb MySQL]#
- # 初始化 3308 实例
- [root@masterdb MySQL]# /usr/local/MySQL/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/MySQL/ --datadir=/MySQL/3308/data
- 2020-03-28T06:10:55.237714Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
- 2020-03-28T06:10:55.442794Z 0 [Warning] InnoDB: New log files created, LSN=45790
- 2020-03-28T06:10:55.479012Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
- 2020-03-28T06:10:55.534839Z 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: e28d1d57-70ba-11ea-a5c4-000c29fb6200.
- 2020-03-28T06:10:55.535622Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
- 2020-03-28T06:10:55.536387Z 1 [Note] A temporary password is generated for root@localhost: Mz<kr!vsh1yj
- [root@masterdb MySQL]#
- # 初始化 3309 实例
- [root@masterdb MySQL]# /usr/local/MySQL/bin/mysqld --defaults-file=/etc/my.cnf --initialize --basedir=/usr/local/MySQL/ --datadir=/MySQL/3309/data
- 2020-03-28T06:11:05.644331Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
- 2020-03-28T06:11:05.840498Z 0 [Warning] InnoDB: New log files created, LSN=45790
- 2020-03-28T06:11:05.879941Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
- 2020-03-28T06:11:05.936262Z 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: e8c03ed2-70ba-11ea-a8fb-000c29fb6200.
- 2020-03-28T06:11:05.937179Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
- 2020-03-28T06:11:05.937877Z 1 [Note] A temporary password is generated for root@localhost: K.KLa30i-sv3
(4.8)设置环境变量
添加了环境变量, 操作系统才能够自己找到 MySQL,mysqld_multi 等命令的位置
- [root@masterdb MySQL]# VIM /etc/profile
- # 在文件末尾添加下面信息
- export PATH=/usr/local/MySQL/bin:$PATH
- # 使环境变量生效
- [root@masterdb MySQL]# source /etc/profile
(4.9)使用 mysqld_multi 管理多实例
- # 使用 mysqld_multi 启动 3306 端口的实例
- [root@masterdb MySQL]# mysqld_multi start 3306
- # 使用 mysqld_multi 启动全部实例
- [root@masterdb MySQL]# mysqld_multi start
- # 使用 mysqld_multi 查看实例状态
- [root@masterdb MySQL]# mysqld_multi report
- Reporting MySQL servers
- MySQL server from group: mysqld3306 is running
- MySQL server from group: mysqld3307 is running
- MySQL server from group: mysqld3308 is running
- MySQL server from group: mysqld3309 is running
使用 mysqld_multi 关闭实例较为麻烦, 需要配置密码, 因此如何关闭各个实例, 见后面章节:(六)关闭多实例数据库 .
(五)访问多实例数据库
(5.1)登录 MySQL 数据库
在安装完成并启动数据库后, 需要去访问各个 MySQL 实例, 这里非常有意思, 经常会发现无法连接到数据库上, 我们不妨看一下几种连接方式:
连接方式一: 使用服务器 IP 地址, 无法连接. 这里还是比较好理解的, MySQL 创建完成后, 数据库账号 mailto:root@localhost 只允许本地连接, 参数 "-h" 后面用服务器 IP 被认为了远程连接, 因此无法登陆
- [root@masterdb MySQL]# MySQL -uoot -p -h192.168.10.11 -P3306
- Enter password:
- ERROR 1130 (HY000): Host 'masterdb' is not allowed to connect to this MySQL server
连接方式二: 使用 localhost 访问数据库, 无法连接. 我觉得有些匪夷所思, 可以看到, MySQL 实例使用的 socket 文件不对
- [root@masterdb MySQL]# MySQL -uroot -p -hlocalhost -P3306
- Enter password:
- ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/tmp/MySQL.sock' (2)
连接方式三: 使用 127.0.0.1 访问数据库, 可以连接. 有些难以理解, 理论上 127.0.0.1 和 localhost 是对应的, 127.0.0.1 可以访问数据库, 但是 localhost 却无法访问
- [root@masterdb MySQL]# MySQL -uroot -p -h127.0.0.1 -P3306
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 7
- Server version: 5.7.24 MySQL Community Server (GPL)
- 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> exit
- Bye
连接方式四: 使用 socket 文件连接, 可以正常访问
- [root@masterdb MySQL]# MySQL -S /tmp/mysql_3306.sock -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 4
- Server version: 5.7.24
- 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>
(5.2)修改数据库 mailto:root@localhost 密码
初次登陆 MySQL 数据库, 需要修改 root 密码, 否则无法正常使用
- [root@masterdb MySQL]# MySQL -S /tmp/mysql_3306.sock -p
- Enter password:
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 4
- Server version: 5.7.24
- Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
-- 无法查询
- MySQL> show databases;
- ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
-- 修改 root@localhost 用户的密码
- MySQL> alter user root@localhost identified by '123456';
- Query OK, 0 rows affected (0.00 sec)
- MySQL> flush privileges;
- Query OK, 0 rows affected (0.00 sec)
- MySQL> exit
- Bye
(六)关闭多实例数据库
(6.1)直接使用 mysqld_multi 来关闭实例
使用 mysqld_multi 关闭多实例数据库目前来看比较麻烦, 需要在 my.cnf 文件的 [mysqld_multi] 模块里面配置用户密码, 并且各个数据库的用户密码都需要相同, 否则无法关闭.
我们可以看一下使用 mysqld_multi 来关闭数据库实例的日志:
- [root@masterdb MySQL]# cat /usr/local/MySQL/mysqld_multi.log
- # 当执行: mysqld_multi report 时, 显示所有数据库均在运行
- Reporting MySQL servers
- MySQL server from group: mysqld3306 is running
- MySQL server from group: mysqld3307 is running
- MySQL server from group: mysqld3308 is running
- MySQL server from group: mysqld3309 is running
- mysqld_multi log file version 2.16; run: Sat Mar 28 14:55:16 2020
- # 当执行: mysqld_multi stopt 时, mysqld_multi 会调用 mysqladmin 去关闭数据库, 使用的是 [mysqld_multi] 里面配置的账号密码, 此时 3306 的密码是正确的,
- # 其它都是错误的, 因此 3306 关闭成功, 而其它端口的实例因为密码错误而连接数据库失败, 自然没有关闭数据库
- Stopping MySQL servers
- mysqladmin: [Warning] Using a password on the command line interface can be insecure.
- mysqladmin: [Warning] Using a password on the command line interface can be insecure.
- mysqladmin: connect to server at 'localhost' failed
- error: 'Access denied for user'root'@'localhost'(using password: YES)'
- mysqladmin: [Warning] Using a password on the command line interface can be insecure.
- mysqladmin: connect to server at 'localhost' failed
- error: 'Access denied for user'root'@'localhost'(using password: YES)'
- mysqladmin: [Warning] Using a password on the command line interface can be insecure.
- mysqladmin: connect to server at 'localhost' failed
- error: 'Access denied for user'root'@'localhost'(using password: YES)'
- mysqld_multi log file version 2.16; run: Sat Mar 28 14:55:21 2020
- # 结果: 仅仅关闭了密码正确的 3306 端口数据库
- Reporting MySQL servers
- MySQL server from group: mysqld3306 is not running
- MySQL server from group: mysqld3307 is running
- MySQL server from group: mysqld3308 is running
- MySQL server from group: mysqld3309 is running
- mysqld_multi log file version 2.16; run: Sat Mar 28 14:58:07 2020
既然知道了 mysqld_multi 是调用 mysqladmin 来关闭数据库的, 那最好的办法还是直接使用 mysqladmin 来关闭各个数据库了, 下面演示使用 mysqladmin 来关闭数据库实例.
(6.2)使用 mysqladmin 来关闭实例
- [root@masterdb MySQL]# mysqld_multi report
- Reporting MySQL servers
- MySQL server from group: mysqld3306 is running
- MySQL server from group: mysqld3307 is running
- MySQL server from group: mysqld3308 is running
- MySQL server from group: mysqld3309 is running
- [root@masterdb MySQL]#
- [root@masterdb MySQL]#
- [root@masterdb MySQL]# cd
- [root@masterdb ~]# mysqladmin -h127.0.0.1 -uroot -p -P3306 shutdown
- Enter password:
- [root@masterdb ~]#
- [root@masterdb ~]# mysqladmin -h127.0.0.1 -uroot -p -P3307 shutdown
- Enter password:
- [root@masterdb ~]# mysqld_multi report
- Reporting MySQL servers
- MySQL server from group: mysqld3306 is not running
- MySQL server from group: mysqld3307 is not running
- MySQL server from group: mysqld3308 is running
- MySQL server from group: mysqld3309 is running
最终关闭了 3306 和 3307 数据库.
[结束]
相关文档集合: 1. 在一台 Linux 服务器上安装多个 MySQL 实例(一)-- 使用 mysqld_multi 方式 2. 在一台 Linux 服务器上安装多个 MySQL 实例(二)-- 使用单独的 MySQL 配置文件 3. 在一台 Linux 服务器上安装多个 MySQL 实例(三)-- 遇到的几个坑 |
来源: https://www.cnblogs.com/lijiaman/p/12587630.html