MySQL多实例部署

1.关于MySQL多实例的介绍

1.1什么是MySQL多实例

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

1.2MySQL多实例的原理

mysql实例共用一套mysql安装程序，使用不同的my.cnf配置文件、启动程序、数据文件。在提供服务时，mysql多实例在逻辑上是各自独立的，各个实例之间根据配置文件的设定值，来取得服务器的相关硬件资源。

1.3MySQL多实例的特点

优点

• 有效的利用服务器资源

当单个服务器资源有剩余时，可以充分利用剩余的服务器资源来提供更多的服务。

• 节约服务器资源

当公司资金紧张，但是数据库需要各自提供独立服务，使用多实例就是最好的选择。

缺点

• 会出现资源抢占问题

当某个实例服务并发很高或者有慢查询时，会消耗服务器更多的内存、CPU、磁盘IO等资源，这时就会导致服务器上的其它实例提供访问的质量下降，出现服务器资源互相抢占的现象。

1.4MySQL多实例的应用场景

• 适用于小型公司

当公司业务访问量不太大，服务器资源闲置的就会比较多，同时公司又不想花太多的钱，但又希望不同业务的数据库服务各自尽量独立地提供服务而互相不受影响，使用多实例是最好不过的。

2.MySQL多实例部署方法

2.1下载并安装二进制格式的MySQL软件包

这里以mysql 5.7为例
去mysql官网下载然后用xftp上传

//创建用户和组
[root@localhost ~]# ls
anaconda-ks.cfg
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=994(mysql) gid=991(mysql) 组=991(mysql)

//解压软件至/usr/local/
[root@localhost ~]# tar xf mysql-5.7.33-linux-glibc2.12-x86_64.tar.gz -C /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月  28 23:10 lib64
drwxr-xr-x. 2 root root   6 5月  19 2020 libexec
drwxr-xr-x. 9 root root 129 5月  10 21:49 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月  28 23:10 share
drwxr-xr-x. 2 root root   6 5月  19 2020 src

//名字太长，弄一个软链接或者直接修改名字都可以
[root@localhost local]# ln -s mysql-5.7.33-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ll
总用量 0
lrwxrwxrwx. 1 root root  36 5月  10 21:52 mysql -> mysql-5.7.33-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 5月  10 21:49 mysql-5.7.33-linux-glibc2.12-x86_64

//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql*
lrwxrwxrwx. 1 mysql mysql  36 5月  10 21:52 mysql -> mysql-5.7.33-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 5月  10 21:49 mysql-5.7.33-linux-glibc2.12-x86_64

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

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

//我们以布置三个实例来进行演练
[root@localhost local]# mkdir -p /opt/data/{3306,3307,3308}[root@localhost local]# cd /opt/data/
[root@localhost data]# ls
3306  3307  3308
//修改属主属组
[root@localhost data]# chown -R mysql.mysql /opt/data/
[root@localhost data]# ll
总用量 0
drwxr-xr-x. 2 mysql mysql 6 5月  10 21:59 3306
drwxr-xr-x. 2 mysql mysql 6 5月  10 21:59 3307
drwxr-xr-x. 2 mysql mysql 6 5月  10 21:59 3308

2.3初始化各实例

//初始化3306实例
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/3306/
2021-05-10T14:11:15.636759Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-10T14:11:15.952405Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-10T14:11:16.835013Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-10T14:11:16.875721Z 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: 95f2b90e-b199-11eb-9936-000c2948795d.
2021-05-10T14:11:16.877035Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-10T14:11:18.257398Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-10T14:11:19.008347Z 1 [Note] A temporary password is generated for root@localhost: bTGkT27Iks#n
//bTGkT27Iks#n是密码，我们先将它保存一下
[root@localhost ~]# echo 'bTGkT27Iks#n' > 3306

//初始化3307实例
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/3307/
2021-05-10T14:15:15.814254Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-10T14:15:16.185997Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-10T14:15:16.259184Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-10T14:15:16.266657Z 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: 24a2e1c6-b19a-11eb-b453-000c2948795d.
2021-05-10T14:15:16.268040Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-10T14:15:16.723584Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-10T14:15:17.591654Z 1 [Note] A temporary password is generated for root@localhost: lfawW3Ud&GPA
//同理lfawW3Ud&GPA也是密码，同3306是不同的，我们也将它保存一下
[root@localhost ~]# echo 'lfawW3Ud&GPA' > 3307

//初始化3308实例
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/3308/
2021-05-10T14:18:08.702766Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2021-05-10T14:18:09.077790Z 0 [Warning] InnoDB: New log files created, LSN=45790
2021-05-10T14:18:09.129162Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2021-05-10T14:18:09.141197Z 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: 8bad7194-b19a-11eb-a5d0-000c2948795d.
2021-05-10T14:18:09.142368Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2021-05-10T14:18:10.776453Z 0 [Warning] CA certificate ca.pem is self signed.
2021-05-10T14:18:11.244553Z 1 [Note] A temporary password is generated for root@localhost: Z_Q0cCHyrJzt
//同上操作
[root@localhost ~]# echo 'Z_Q0cCHyrJzt' > 3308

2.4安装一个工具perl

[root@localhost ~]# dnf -y install perl

2.5配置多实例文件/etc/my.cnf

[root@localhost ~]# vim /etc/my.cnf
[mysqld_multi]
mysqld = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin

[mysqld3306]
datadir = /opt/data/3306
port = 3306
socket = /tmp/mysql3306.sock
pid-file = /opt/data/3306/mysql_3306.pid
log-error=/var/log/3306.log

[mysqld3307]
datadir = /opt/data/3307
port = 3307
socket = /tmp/mysql3307.sock
pid-file = /opt/data/3307/mysql_3307.pid
log-error=/var/log/3307.log

[mysqld3308]
datadir = /opt/data/3308
port = 3308
socket = /tmp/mysql3308.sock
pid-file = /opt/data/3308/mysql_3308.pid
log-error=/var/log/3308.log

2.6启动各实例

[root@localhost ~]# mysqld_multi start 3306
[root@localhost ~]# mysqld_multi start 3307
[root@localhost ~]# mysqld_multi start 3308
[root@localhost ~]# ss -antl
State         Recv-Q        Send-Q               Local Address:Port               Peer Address:Port       Process       
LISTEN        0             128                        0.0.0.0:22                      0.0.0.0:*                        
LISTEN        0             80                               *:3306                          *:*                        
LISTEN        0             80                               *:3307                          *:*                        
LISTEN        0             80                               *:3308                          *:*                        
LISTEN        0             128                           [::]:22                         [::]:*         

2.7改密码

//登录的时候可能会报错，先安装这个数据包
[root@localhost ~]# dnf whatprovides libncurses.so.5
[root@localhost ~]# dnf -y install ncurses-compat-libs

//先登录3306
[root@localhost ~]# cat 3306
bTGkT27Iks#n
//用临时密码先登录
[root@localhost ~]# mysql -uroot -p'bTGkT27Iks#n' -P3306 -S /tmp/mysql3306.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 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> 
mysql> set password = password('lixirong123!'); //修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit

//修改3307的密码
[root@localhost ~]# cat 3307
lfawW3Ud&GPA
[root@localhost ~]# mysql -uroot -p'lfawW3Ud&GPA' -P3307 -S /tmp/mysql3307.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.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('lixirong123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'lixirong123!' -P3307 -S /tmp/mysql3307.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 3
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> 

//修改3308的密码
[root@localhost ~]# cat 3308
Z_Q0cCHyrJzt
[root@localhost ~]# mysql -uroot -p'Z_Q0cCHyrJzt' -P3308 -S /tmp/mysql3308.sock   
//此处我们将-S /tmp/mysql3308.sock换成指定主机名-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> set password = password('lixirong123!');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit
Bye
[root@localhost ~]# mysql -uroot -p'lixirong123!' -P3308 -S /tmp/mysql3308.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 3
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>