1-MySQL - 多实例

about

centos7.3 + mysql5.7.20
官网下载地址:https://downloads.mysql.com/archives/community/
百度云盘:链接:https://pan.baidu.com/s/1ipuTky_oAgMzrZ-wf1GJgA 提取码:b0gu

MySQL多实例的本质
在一台机器上开启多个不同的MySQL实例,也就是各实例监听不同的端口,提供不同的服务。
多个实例公用一套MySQL安装程序,启动程序和配置文件可以是一个也可以是多个(推荐多个);各自的数据文件隔离;逻辑上各实例彼此隔离。

为什么要使用多实例?优缺点?

  • 物理机性能强大,单个实例无法充分利用硬件资源
  • 资源隔离,减少相互影响
  • 分担连接数,MySQL随着连接数的上升,性能会下降
  • 更充分的利用资源,不同业务错高峰混跑
  • 有优点,也有缺点,比如多个实例会存在资源相互抢占的问题,当某个实例的并发较高或者存在慢查询时,它会消耗更多的硬件资源,这就可能影响到别的实例的性能

多实例的应用场景

  • 资金比较紧张的公司
  • 并发访问不大的业务

MySQL多实例常见配置方案

  • (推荐)通过多个配置文件及多个启动程序来实现多实例。
  • 单一配置文件方案,即一个配置文件中写多个实例的配置。

必要的准备

目录规划

/opt/software/mysql   # MySQL的安装目录
/data/mysql/    # 所有的MySQL实例的数据目录、备份目录、日志目录,都在该目录下,各个实例以端口号命名
/etc/my.conf    # MySQL 3306实例的默认配置文件

依赖下载

# 如果你的系统曾经安装过mariadb,请先卸载
yum remove -y mariadb* 
yum install -y epel-release
yum update -y
yum install -y cmake gcc-c++ ncurses-devel perl-Data-Dumper boost-doc boost-devel libaio-devel
yum install -y net-tools tree bash-completion lrzsz

友情提示:如果是小白初次在虚拟机上搞,记得现在就拍个快照!!!!

单实例配置

注意,后续的实例都基于该实例,所以,我们的一些操作要细致。

MySQL install

[root@cs ~]# mkdir -p /opt/software && cd /opt/software
[root@cs software]# pwd
/opt/software
[root@cs software]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@cs software]# tar -xvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
[root@cs software]# mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
[root@cs software]# rm -rf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz && ls
mysql

添加环境变量

[root@cs software]# vim /etc/profile

export PATH=/opt/software/mysql/bin:$PATH

[root@cs software]# source /etc/profile

创建相关目录和MySQL用户
MySQL服务运行在非root用户环境,所以,我们先创建一个mysql用户,然后在创建相关的数据目录:

[root@cs software]# useradd mysql
[root@cs software]# mkdir -p /data/mysql/33{06..10}/{data,logs,backup}
[root@cs software]# mkdir -p /data/mysql/33{06..10}/logs/{errorlog,slowlog,binlog}
[root@cs software]# tree /data/mysql/
/data/mysql/
├── 3306                # 3306端口对应的MySQL数据库实例对应的相关目录
│   ├── backup          # 备份用
│   ├── data            # 数据目录
│   └── logs            # 日志文件
│       ├── binlog      # 二进制文件
│       ├── errorlog    # 错误日志
│       └── slowlog     # 慢日志
├── 3307
│   ├── backup
│   ├── data
│   └── logs
│       ├── binlog
│       ├── errorlog
│       └── slowlog
├── 3308
│   ├── backup
│   ├── data
│   └── logs
│       ├── binlog
│       ├── errorlog
│       └── slowlog
├── 3309
│   ├── backup
│   ├── data
│   └── logs
│       ├── binlog
│       ├── errorlog
│       └── slowlog
└── 3310
    ├── backup
    ├── data
    └── logs
        ├── binlog
        ├── errorlog
        └── slowlog

35 directories, 0 files
[root@cs software]# chown -R mysql:mysql /opt/software/mysql/*
[root@cs software]# chown -R mysql:mysql /data/mysql/*

初始化数据库

# 保证存放数据的目录是空的,避免不必要的问题
[root@cs software]# rm -rf /data/mysql/3306/data/*
[root@cs software]# mysqld --initialize-insecure  --user=mysql --basedir=/opt/software/mysql --datadir=/data/mysql/3306/data
2021-05-09T09:41:30.343576Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-09T09:41:30.557948Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-09T09:41:30.587516Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-09T09:41:30.655073Z 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: bbcbb587-b0aa-11eb-a2ce-000c295ead38.
2021-05-09T09:41:30.656498Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-09T09:41:30.656990Z 1 [Warning] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.

一堆"Warning",不要管它。

编写配置文件

cat  > /etc/my.cnf <<EOF
[mysqld]
user=mysql
basedir=/opt/software/mysql
datadir=/data/mysql/3306/data
log-error=/data/mysql/3306/logs/errorlog/mysql.log
server_id=6
port=3306
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock
EOF

使用systemctl管理MySQL服务

cat  > /etc/systemd/system/mysqld.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/etc/my.cnf
LimitNOFILE = 5000
EOF

现在,你可以使用systemctl命令来管理MySQL服务了:

systemctl start/restart/stop/status/enable/disable mysqld

测试下:

[root@cs software]# find / -name mysql.sock
[root@cs software]# systemctl start mysqld
[root@cs software]# find / -name mysql.sock
/tmp/mysql.sock
[root@cs software]# netstat -lnp|grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      8131/mysqld

启动之后,/tmp下就有了mysql.sock文件,后续我们可以通过这个socket文件来连接数据了。如果使用systemctl停止数据库,这个文件也没了。
ok,单台实例创建完毕。

多实例配置

你可以根据需要酌情增加或者减少多实例的数量

必要的准备
停止单实例的运行,并且备份单实例的配置文件(防止多实例初始化时读取这个单实例配置文件),后续多实例配置完事后,再重新还原这个配置文件:

[root@cs software]# systemctl stop mysqld
[root@cs software]# mv /etc/my.cnf /etc/my.cnf.bak

准备多实例的数据目录
由于多实例的数据目录已经在单实例那里创建成功了,这一步就可以略过:
每台实例以端口命名,端口名目录下存放配置文件,其中的data目录存放各自的数据。

为每个实例创建配置文件
我们将每个实例(3306的可配置也可不配置,因为它默认使用的是/etc/my.cnf)的配置文件都放在各自端口目录的下面,视情况修改下面参数,然后直接拷贝运行即可:

cat > /data/mysql/3307/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log-error=/data/mysql/3307/logs/errorlog/mysql.log
port=3307
server_id=7
[client]
socket=/data/mysql/3307/mysql.sock
EOF

cat > /data/mysql/3308/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3308/data
socket=/data/mysql/3308/mysql.sock
log-error=/data/mysql/3308/logs/errorlog/mysql.log
port=3308
server_id=8
[client]
socket=/data/mysql/3308/mysql.sock
EOF

cat > /data/mysql/3309/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3309/data
socket=/data/mysql/3309/mysql.sock
log-error=/data/mysql/3309/logs/errorlog/mysql.log
port=3309
server_id=9
[client]
socket=/data/mysql/3309/mysql.sock
EOF

cat > /data/mysql/3310/my.cnf <<EOF
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3310/data
socket=/data/mysql/3310/mysql.sock
log-error=/data/mysql/3310/logs/errorlog/mysql.log
port=3310
server_id=10
[client]
socket=/data/mysql/3310/mysql.sock
EOF

完事之后,各自的数据目录下就有了配置文件了:

[root@cs software]# ls /data/mysql/3307/
backup  data  logs  my.cnf
[root@cs software]# cat /data/mysql/3307/my.cnf 
[mysqld]
basedir=/opt/software/mysql
datadir=/data/mysql/3307/data
socket=/data/mysql/3307/mysql.sock
log-error=/data/mysql/3307/logs/errorlog/mysql.log
port=3307
server_id=7
[client]
socket=/data/mysql/3307/mysql.sock

别忘了再授权下:

[root@cs software]# chown -R mysql.mysql /data/mysql/*

现在,配置文件完事了,就可以着手进行初始化了。
多实例的初始化

mysqld --initialize-insecure  --user=mysql --datadir=/data/mysql/3307/data --basedir=/opt/software/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/mysql/3308/data --basedir=/opt/software/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/mysql/3309/data --basedir=/opt/software/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/mysql/3310/data --basedir=/opt/software/mysql

上面的命令执行时,会有Warning提示,这里不要管它。注意,如果你的服务器内存较小的话,可能后续启不起来3台实例,不过有个2G内存也差不多了。

使用systemctl管理多实例
实际上,下面这几个配置文件内容都是来自于mysqld.service文件,然后修改了各自实例的配置文件路径:

cat  > /etc/systemd/system/mysqld3307.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3307/my.cnf
LimitNOFILE = 5000
EOF

cat  > /etc/systemd/system/mysqld3308.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3308/my.cnf
LimitNOFILE = 5000
EOF

cat  > /etc/systemd/system/mysqld3309.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3309/my.cnf
LimitNOFILE = 5000
EOF

cat  > /etc/systemd/system/mysqld3310.service <<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3310/my.cnf
LimitNOFILE = 5000
EOF

OK了。
到这里,多实例的配置基本完毕,可以尝试启动了:

systemctl start mysqld3307.service
systemctl start mysqld3308.service
systemctl start mysqld3309.service
systemctl start mysqld3310.service

# 下面两个命令等价
systemctl start mysqld3307.service
systemctl start mysqld3307

验证下:

[root@cs software]# find / -name mysql.sock
/data/mysql/3307/mysql.sock
/data/mysql/3308/mysql.sock
/data/mysql/3309/mysql.sock
/data/mysql/3310/mysql.sock
[root@cs software]# netstat -lnp|grep 33
tcp6       0      0 :::3307                 :::*                    LISTEN      100746/mysqld       
tcp6       0      0 :::3308                 :::*                    LISTEN      100753/mysqld       
tcp6       0      0 :::3309                 :::*                    LISTEN      100760/mysqld       
tcp6       0      0 :::3310                 :::*                    LISTEN      100768/mysqld       
unix  2      [ ACC ]     STREAM     LISTENING     411727   100760/mysqld        /data/mysql/3309/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     411405   100746/mysqld        /data/mysql/3307/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     411408   100768/mysqld        /data/mysql/3310/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     411730   100753/mysqld        /data/mysql/3308/mysql.sock

恢复单实例的配置文件

[root@cs software]# mv /etc/my.cnf.bak /etc/my.cnf

然后启动3306这台实例,注意,3306的跟其他的实例不太一样:

[root@cs software]# systemctl start mysqld.service
[root@cs software]# find / -name mysql.sock
/tmp/mysql.sock
/data/mysql/3307/mysql.sock
/data/mysql/3308/mysql.sock
/data/mysql/3309/mysql.sock
/data/mysql/3310/mysql.sock
[root@cs software]# netstat -lnp|grep 330
tcp6       0      0 :::3306                 :::*                    LISTEN      101248/mysqld       
tcp6       0      0 :::3307                 :::*                    LISTEN      100746/mysqld       
tcp6       0      0 :::3308                 :::*                    LISTEN      100753/mysqld       
tcp6       0      0 :::3309                 :::*                    LISTEN      100760/mysqld       
tcp6       0      0 :::3310                 :::*                    LISTEN      100768/mysqld       
unix  2      [ ACC ]     STREAM     LISTENING     411727   100760/mysqld        /data/mysql/3309/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     411405   100746/mysqld        /data/mysql/3307/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     411408   100768/mysqld        /data/mysql/3310/mysql.sock
unix  2      [ ACC ]     STREAM     LISTENING     411730   100753/mysqld        /data/mysql/3308/mysql.sock

OK,所有实例都启动无误。

连接管理

现在5个实例都能正常运行后,摆在我们面前的是怎么连接到指定的实例?
有以下两种方式可以连接到指定数据库:

# 注意,此时的登录密码都为空,直接回车即可
[root@cs software]# mysql -uroot -p -S /tmp/mysql.sock -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
[root@cs software]# mysql -uroot -p -S /data/mysql/3307/mysql.sock -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@cs software]# mysql -uroot -p -S /data/mysql/3308/mysql.sock -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@cs software]# mysql -uroot -p -S /data/mysql/3309/mysql.sock -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

[root@cs software]# mysql -uroot -p -S /data/mysql/3310/mysql.sock -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           10 |
+-------------+

以上是通过不同的mysql.sock来来接指定的数据库实例,除此之外,还可以通过-h -P参数来连接到指定的数据库实例:

[root@cs software]# mysql -uroot -p -h127.0.0.1 -P3306 -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
[root@cs software]# mysql -uroot -p -h127.0.0.1 -P3307 -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@cs software]# mysql -uroot -p -h127.0.0.1 -P3308 -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@cs software]# mysql -uroot -p -h127.0.0.1 -P3309 -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

[root@cs software]# mysql -uroot -p -h127.0.0.1 -P3310 -e "select @@server_id"
Enter password: 
+-------------+
| @@server_id |
+-------------+
|          10 |
+-------------+

OK,连接这块没问题了。

用户管理

默认的,上面使用root用户只有本地的访问权限,远程无法使用,且初始化时,我们将初始密码也设置为空了:

[root@cs software]# mysql -uroot -p -h127.0.0.1 -P3306 -e "select user,host from mysql.user"
Enter password: 
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

所以,这里我们创建远程用户和创建密码再配置相关权限:

-- 分别登录到各自的实例中,此时密码还未空,直接回车,然后执行下面命令,进行创建用户并授权
-- mysql -uroot -p -h127.0.0.1 -P3306
-- mysql -uroot -p -h127.0.0.1 -P3307
-- mysql -uroot -p -h127.0.0.1 -P3308
-- mysql -uroot -p -h127.0.0.1 -P3309
-- mysql -uroot -p -h127.0.0.1 -P3310

grant all on *.* to root@'localhost' identified by '123';
grant all on *.* to root@'%' identified by '123';
flush privileges;

注意,上面的创建用户和授权命令仅适用于MySQL8.0一下,因为从MySQL8.0开始,创建用户和授权分为两步操作,这点需要注意。
创建完成后,就可以通过密码进行登录了:

[root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3306 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|           6 |
+-------------+
[root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3307 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
[root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3308 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
[root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3309 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

[root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3310 -e "select @@server_id"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
|          10 |
+-------------+

友情提示:如果是小白初次在虚拟机上搞,又搞成功了的话,记得现在再拍个快照,后面好恢复到当前干净的初始环境!!!!


that's all,see also:

老男孩-标杆班级-MySQL-lesson02-体系结构与管理 | MySQL数据库多实例介绍及安装 | Mysql多实例配置

posted @ 2020-08-19 14:10  听雨危楼  阅读(976)  评论(0编辑  收藏  举报