mysql多实例部署

mysql多实例部署

mysql多实例部署的介绍

什么是mysql多实例部署

MySQL多实例就是在一台服务器上同时开启多个不同的服务端口（如：3306,3307），同时运行多个MySQL服务进程，这些服务进程通过不同的socket监听不同的服务端口来提供服务

mysql多实例的作用有效利用服务器资源

1、有效利用服务器资源
当单个服务器资源有剩余时，可以充分利用剩余的资源提供更多的服务，且可以实现资源的逻辑隔离。

2、节约服务器资源
当公司资金紧张，但是数据库又需要各自尽量独立地提供服务，而且，需要主从复制等技术，多实例就再好不过了。

mysql多实例生产应用场景

1、资金紧张型公司的选择

若公司资金紧张，公司业务访问量又不是太大，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，同时，还需要主从复制等技术提供备份或读写分离服务，那么，多实例就再好不过了。比如：可以通过3台服务器部署9_{15个实例，交叉做主从复制、数据备份及读写分离，这样就可以达到9}15台服务器每个只装一个数据库才有的效果。这里要强调的是，所谓的尽量独立是相对的。

2、并发访问不是特别大的业务

当公司业务访问量不太大的时候，服务器的资源基本都是浪费的，这时就很适合多实例的应用，如果对SQL语句的优化做得比较好，MySQL多实例会是一个很值得使用的技术，即使并发很大，合理分配好系统资源以及搭配好服务，也不会有太大问题。

3、门户网站应用MySQL多实例场景

门户网站通常都会使用多实例，因为配置硬件好的服务器，可节省IDC机柜空间，同时，跑多实例也会减少硬件资源跑不满的浪费。比如：百度公司的很多数据库都是多实例，不过，一般是从库多实例，例如某部门使用的IBM服务器为48核CPU，内存96GB，一台服务器跑3~4个实例；此外，sina网也是用的多实例，内存48GB左右。

部署操作

软件的下载

在mysql官网下载二进制格式的mysql软件包，将下载好的安装包拖入虚拟机内（我用的findshell）

[root@localhost ~]# ls
公共  视频  文档  音乐  hh                                          yy
模板  图片  下载  桌面  mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql
[root@localhost ~]# id mysql
uid=974(mysql) gid=973(mysql) 组=973(mysql)

配置用户和组并解压安装包到指定位置

[root@localhost ~]# useradd -r -M -s /sbin/nologin mysql   //创建用户
[root@localhost ~]# id mysql
uid=974(mysql) gid=973(mysql) 组=973(mysql)

[root@localhost ~]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /usr/local/    //解压软件至/usr/local/

[root@localhost ~]# cd /usr/local/
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root   6 5月  19 2020 bin
drwxr-xr-x. 2 root root   6 5月  19 2020 etc
drwxr-xr-x. 2 root root   6 5月  19 2020 games
drwxr-xr-x. 2 root root   6 5月  19 2020 include
drwxr-xr-x. 2 root root   6 5月  19 2020 lib
drwxr-xr-x. 3 root root  17 4月   7 17:31 lib64
drwxr-xr-x. 2 root root   6 5月  19 2020 libexec
drwxr-xr-x. 9 root root 129 5月  10 18:41 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 5月  19 2020 sbin
drwxr-xr-x. 5 root root  49 4月   7 17:31 share
drwxr-xr-x. 2 root root   6 5月  19 2020 src

做软链接将解压出来的mysql-5.7.33-linux-glibc2.12-x86_64 指向 mysql

[root@localhost local]#  ln -s mysql-5.7.33-linux-glibc2.12-x86_64 mysql
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root root   6 5月  19 2020 bin
drwxr-xr-x. 2 root root   6 5月  19 2020 etc
drwxr-xr-x. 2 root root   6 5月  19 2020 games
drwxr-xr-x. 2 root root   6 5月  19 2020 include
drwxr-xr-x. 2 root root   6 5月  19 2020 lib
drwxr-xr-x. 3 root root  17 4月   7 17:31 lib64
drwxr-xr-x. 2 root root   6 5月  19 2020 libexec
lrwxrwxrwx. 1 root root  35 5月  10 18:51 mysql -> mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 5月  10 18:41 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 5月  19 2020 sbin
drwxr-xr-x. 5 root root  49 4月   7 17:31 share
drwxr-xr-x. 2 root root   6 5月  19 2020 src

修改目录/usr/local/mysql的属组属主

[root@localhost local]# chown -R mysql.mysql mysql/
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root  root    6 5月  19 2020 bin
drwxr-xr-x. 2 root  root    6 5月  19 2020 etc
drwxr-xr-x. 2 root  root    6 5月  19 2020 games
drwxr-xr-x. 2 root  root    6 5月  19 2020 include
drwxr-xr-x. 2 root  root    6 5月  19 2020 lib
drwxr-xr-x. 3 root  root   17 4月   7 17:31 lib64
drwxr-xr-x. 2 root  root    6 5月  19 2020 libexec
lrwxrwxrwx. 1 root  root   35 5月  10 18:51 mysql -> mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 129 5月  10 18:41 mysql-5.7.33-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root  root    6 5月  19 2020 sbin
drwxr-xr-x. 5 root  root   49 4月   7 17:31 share
drwxr-xr-x. 2 root  root    6 5月  19 2020 src

配置环境变量

[root@localhost ~]# ls /usr/local/mysql/
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost ~]# echo 'export PATH=/usr/local/mysql/bin:$PATH'> /etc/profile.d/mysql.sh
[root@localhost ~]# source /etc/profile.d/mysql.sh
[root@localhost ~]# which mysql
/usr/local/mysql/bin/mysql

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

[root@localhost ~]# mkdir -p /opt/data/{2001,2002,2003}
[root@localhost ~]# tree /opt/data
/opt/data
├── 2001
├── 2002
└── 2003

3 directories, 0 files

修改opt的属主属组为mysql
[root@localhost ~]# chown -R mysql.mysql /opt/data
[root@localhost ~]# ll /opt/
总用量 0
drwxr-xr-x. 5 mysql mysql 42 5月  10 18:59 data
[root@localhost ~]# ll /opt/data/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 5月  10 18:59 2001
drwxr-xr-x. 2 mysql mysql 6 5月  10 18:59 2002
drwxr-xr-x. 2 mysql mysql 6 5月  10 18:59 2003

初始化各实例

//初始化2001实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/2001
2021-05-10T11:05:08.777251Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-10T11:05:09.319966Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-10T11:05:09.451889Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-10T11:05:09.508408Z 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: 95adb39b-b17f-11eb-8b35-000c29f893b1.
2021-05-10T11:05:09.509404Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-10T11:05:09.953279Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-10T11:05:10.128993Z 1 [Note] A temporary password is generated for root@localhost: Dz<Yi91Isx3r

//保存密码到2001里
[root@localhost ~]# echo 'Dz<Yi91Isx3r' > 2001


//初始化2002实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/2002
2021-05-10T11:05:53.226122Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-10T11:05:54.427483Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-10T11:05:54.708558Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-10T11:05:54.768778Z 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: b0a7e30f-b17f-11eb-a844-000c29f893b1.
2021-05-10T11:05:54.769682Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-10T11:05:55.481901Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-10T11:05:55.594264Z 1 [Note] A temporary password is generated for root@localhost: qrYfA7hmQY!&

保存密码到2002
[root@localhost ~]# echo 'qrYfA7hmQY!&' > 2002


//初始化2003实例
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data/2003
2021-05-10T11:06:59.279266Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-10T11:07:00.200716Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-10T11:07:00.233231Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-10T11:07:00.290203Z 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: d7b5a998-b17f-11eb-a90c-000c29f893b1.
2021-05-10T11:07:00.291341Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-10T11:07:01.132465Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-10T11:07:01.567158Z 1 [Note] A temporary password is generated for root@localhost: E9lfS!MHN4QT

//保存密码到2003
[root@localhost ~]# echo 'E9lfS!MHN4QT' > 2003

[root@localhost ~]# ls
2001  公共  图片  音乐  mysql-5.7.33-linux-glibc2.12-x86_64
2002  模板  文档  桌面  mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz
2003  视频  下载  hh    yy

安装perl

如果没有的话就[dnf -y install perl]命令安装
这里我的虚拟机有就不安装了

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

[root@localhost ~]# vim /etc/my.cnf

[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld2001]
datadir = /opt/data/2001
port = 2001
socket = /tmp/mysql2001.sock
pid-file = /opt/data/2001/mysql_2001.pid
log-error=/var/log/2001.log

[mysqld2002]
datadir = /opt/data/2002
port = 2002
socket = /tmp/mysql2002.sock
pid-file = /opt/data/2002/mysql_2002.pid
log-error=/var/log/2002.log

[mysqld2003]
datadir = /opt/data/2003
port = 2003
socket = /tmp/mysql2003.sock
pid-file = /opt/data/2003/mysql_2003.pid
log-error=/var/log/2003.log

启动各个实例

[root@localhost ~]# mysqld_multi start 2001
[root@localhost ~]# mysqld_multi start 2002
[root@localhost ~]# mysqld_multi start 2003

[root@localhost ~]# ss -antl
State    Recv-Q   Send-Q     Local Address:Port     Peer Address:Port  Process  
LISTEN   0        128              0.0.0.0:111           0.0.0.0:*              
LISTEN   0        32         192.168.122.1:53            0.0.0.0:*              
LISTEN   0        128              0.0.0.0:22            0.0.0.0:*              
LISTEN   0        5              127.0.0.1:631           0.0.0.0:*              
LISTEN   0        128                 [::]:111              [::]:*              
LISTEN   0        80                     *:2001                *:*              
LISTEN   0        80                     *:2002                *:*              
LISTEN   0        80                     *:2003                *:*              
LISTEN   0        128                 [::]:22               [::]:*              
LISTEN   0        5                  [::1]:631              [::]:*              

初始化密码

[root@localhost ~]# dnf -y install ncurses-compat-libs   //安装myslq密码的依赖包

上次元数据过期检查：0:07:09 前，执行于 2021年05月10日 星期一 19时16分42秒。
依赖关系解决。
================================================================================
 软件包                   架构        版本                    仓库         大小
================================================================================
安装:
 ncurses-compat-libs      x86_64      6.1-7.20180224.el8      baseos      331 k

事务概要
================================================================================
安装  1 软件包

总下载：331 k
安装大小：1.2 M
下载软件包：
ncurses-compat-libs-6.1-7.20180224.el8.x86_64.r 290 kB/s | 331 kB     00:01    
--------------------------------------------------------------------------------
总计                                            183 kB/s | 331 kB     00:01     
运行事务检查
事务检查成功。
运行事务测试
事务测试成功。
运行事务
  准备中  :                                                                 1/1 
  安装    : ncurses-compat-libs-6.1-7.20180224.el8.x86_64                   1/1 
  运行脚本: ncurses-compat-libs-6.1-7.20180224.el8.x86_64                   1/1 
  验证    : ncurses-compat-libs-6.1-7.20180224.el8.x86_64                   1/1 
Installed products updated.

已安装:
  ncurses-compat-libs-6.1-7.20180224.el8.x86_64                                 

完毕！

[root@localhost ~]# cat 2001
Dz<Yi91Isx3r
[root@localhost ~]# mysql -uroot -p'Dz<Yi91Isx3r' -P2001 -h127.0.0.1
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.33

Copyright (c) 2000, 2021, 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> 

[root@localhost ~]# cat 2002
qrYfA7hmQY!&
[root@localhost ~]# mysql -uroot -p'qrYfA7hmQY!&' -P2002 -h127.0.0.1
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.33

Copyright (c) 2000, 2021, 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> 

[root@localhost ~]# cat 2003
E9lfS!MHN4QT
[root@localhost ~]# mysql -uroot -p'E9lfS!MHN4QT' -P2003 -h127.0.0.1
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.33

Copyright (c) 2000, 2021, 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> 

修改密码

//2001
[root@localhost ~]# mysql -uroot -p'Dz<Yi91Isx3r' -P2001 -h127.0.0.1
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 3
Server version: 5.7.33

Copyright (c) 2000, 2021, 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> set password = password ('123.com.');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'123.com.' -P2001 -h127.0.0.1
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.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 

[root@localhost ~]# mysql -uroot -p'qrYfA7hmQY!&' -P2002 -h127.0.0.1
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.33

Copyright (c) 2000, 2021, 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> set password = password ('123.com.');
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'123.com.' -P2002 -h127.0.0.1
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 5
Server version: 5.7.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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>

[root@localhost ~]# mysql -uroot -p'E9lfS!MHN4QT' -P2003 -h127.0.0.1
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 3
Server version: 5.7.33

Copyright (c) 2000, 2021, 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> set password = password ('123.com.');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'123.com.' -P2003 -h127.0.0.1
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.33 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, 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> 

设置开机自启

[root@localhost ~]# cp /usr/local/mysql/support-files/mysqld_multi.server /etc/init.d/
[root@localhost ~]# vim /etc/init.d/mysqld_multi.server

basedir=/usr/local/mysql
bindir=/usr/local/mysql/bin
export PATH=/usr/local/mysql/bin:$PATH   //添加这一行

[root@localhost ~]# chkconfig mysqld_multi.server on
[root@localhost ~]# chkconfig --list

注：该输出结果只显示 SysV 服务，并不包含
原生 systemd 服务。SysV 配置数据
可能被原生 systemd 配置覆盖。 

      要列出 systemd 服务，请执行 'systemctl list-unit-files'。
      查看在具体 target 启用的服务请执行
      'systemctl list-dependencies [target]'。

mysqld_multi.server     0:关    1:关    2:开    3:开    4:开    5:开    6:关

重新启动虚拟机

[root@localhost ~]# reboot

连接断开
连接成功
Activate the web console with: systemctl enable --now cockpit.socket

Last login: Mon May 10 19:42:30 2021
[root@localhost ~]# ss -antl    //开机自启后有服务
State    Recv-Q   Send-Q     Local Address:Port     Peer Address:Port  Process  
LISTEN   0        128              0.0.0.0:111           0.0.0.0:*              
LISTEN   0        32         192.168.122.1:53            0.0.0.0:*              
LISTEN   0        128              0.0.0.0:22            0.0.0.0:*              
LISTEN   0        5              127.0.0.1:631           0.0.0.0:*              
LISTEN   0        128                 [::]:111              [::]:*              
LISTEN   0        80                     *:2001                *:*              
LISTEN   0        80                     *:2002                *:*              
LISTEN   0        80                     *:2003                *:*              
LISTEN   0        128                 [::]:22               [::]:*              
LISTEN   0        5                  [::1]:631              [::]:*