MySQL-单机中的单实例和多实例
⼀、about
centos7.3 + mysql5.7.20
MySQL多实例的本质
在⼀台机器上开启多个不同的MySQL实例，也就是各实例监听不同的端⼝，提供不同的服务。
多个实例公⽤⼀套MySQL安装程序，启动程序和配置⽂件可以是⼀个也可以是多个(推荐多个)；各⾃的数据⽂件隔离；逻辑上各实例彼此隔离。
为什么要使⽤多实例？优缺点？
物理机性能强⼤，单个实例⽆法充分利⽤硬件资源
资源隔离，减少相互影响
分担连接数，MySQL随着连接数的上升，性能会下降
更充分的利⽤资源，不同业务错⾼峰混跑
有优点，也有缺点，⽐如多个实例会存在资源相互抢占的问题，当某个实例的并发较⾼或者存在慢查询时，它会消耗更多的硬件资源，这就可能影响到别的实例的性能多实例的应⽤场景
资⾦⽐较紧张的公司
并发访问不⼤的业务
MySQL多实例常见配置⽅案
(推荐)通过多个配置⽂件及多个启动程序来实现多实例。
单⼀配置⽂件⽅案，即⼀个配置⽂件中写多个实例的配置。
⼆、必要的准备
⽬录规划
1 /opt/software/mysql # MySQL的安装⽬录
2 /data/mysql/ # 所有的MySQL实例的数据⽬录、备份⽬录、⽇志⽬录，都在该⽬录下，各个实例以端⼝号命名
3 /f # MySQL 3306实例的默认配置⽂件
依赖下载
1 # 如果你的系统曾经安装过mariadb，请先卸载
2yum remove -y mariadb*
3yum install -y epel-release
4yum update -y
5yum install -y cmake gcc-c++ ncurses-devel perl-Data-Dumper boost-doc boost-devel libaio-devel
mysql需要安装documentation6yum install -y net-tools tree bash-completion lrzsz
三、单实例配置
3.1、下载安装mysql
1 [root@cs ~]# mkdir -p /opt/software && cd /opt/software
2 [root@cs software]# pwd
3 /opt/software
4 [root@cs software]# wget sql/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_
5 [root@cs software]# tar -xvf mysql-5.7.20-linux-glibc2.12-x86_
6 [root@cs software]# mv mysql-5.7.20-linux-glibc2.12-x86_64 mysql
7 [root@cs software]# rm -rf mysql-5.7.20-linux-glibc2.12-x86_ && ls
8 mysql
3.2 添加环境变量
1 [root@cs software]# vim /etc/profile
2
3 export PATH=/opt/software/mysql/bin:$PATH
4
5 [root@cs software]# source /etc/profile
3.3 创建相关⽬录和MySQL⽤户
MySQL服务运⾏在⾮root⽤户环境，所以，我们先创建⼀个mysql⽤户，然后在创建相关的数据⽬录：
创建数据⽬录，并且授权更改⽤户属组，MySQL的安装⽬录授权
1 [root@cs software]# useradd mysql
2 [root@cs software]# mkdir -p /data/mysql/330{6,7,8,9}/{data,logs,backup}
3 [root@cs software]# tree /data/mysql/330*
4 /data/mysql/3306
5├── backup
6├── data
7└── logs
8 /data/mysql/3307
9├── backup
10├── data
11└── logs
12 /data/mysql/3308
13├── backup
14├── data
15└── logs
16 /data/mysql/3309
17├── backup
18├── data
19└── logs
20
2112 directories, 0 files
22 [root@cs software]# chown -R mysql:mysql /opt/software/mysql/*
23[root@cs software]# chown -R mysql:mysql /data/mysql/*
3.4 初始化数据库
进⾏初始化，初始化完成后，会默认创建⼀个本地的root⽤户，且⽆密码
# 保证存放数据的⽬录是空的，避免不必要的问题
[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 'id_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"，不要管它。
3.5 添加配置⽂件
1cat > /etc/myf <<EOF
2 [mysqld]
3 user=mysql
4 basedir=/opt/software/mysql
5 datadir=/data/mysql/3306/data
6 server_id=6
7 port=3306
8 socket=/tmp/mysql.sock
9 log_error=/data/mysql/3306/logs/mysql_error.log
10 [mysql]
11 socket=/tmp/mysql.sock
12 EOF
3.6 使⽤systemctl管理MySQL服务
1cat > /etc/systemd/system/mysqld.service <<EOF
2 [Unit]
3 Description=MySQL Server
4 Documentation=man:mysqld(8)
5 Documentation=sql/doc/refman/en/using-systemd.html
6 After=network.target
7 After=syslog.target
8 [Install]
9 WantedBy=multi-user.target
10 [Service]
11 User=mysql
12 Group=mysql
13 ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/etc/myf
14 LimitNOFILE = 5000
15 EOF
3.7 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|grep330
tcp6 00 :::3306 :::* LISTEN 8131/mysqld
启动之后，/tmp下就有了mysql.sock⽂件，后续我们可以通过这个socket⽂件来连接数据了。如果使⽤systemctl停⽌数据库，这个⽂件也没了。
ok，单台实例创建完毕。
3.8、创建⽤户并且授权
1 grant all on *.* to root@'localhost' identified by '123';
2 grant all on *.* to root@'%' identified by '123';
3 flush privileges;
四、单机多实例配置
4.1 必要的准备
1 [root@cs software]# systemctl stop mysqld
2 [root@cs software]# mv /etc/myf /etc/myf.bak
4.2 准备多实例的数据⽬录
由于多实例的数据⽬录已经在单实例那⾥创建成功了，这⼀步就可以略过：
每台实例以端⼝命名，端⼝名⽬录下存放配置⽂件，其中的data⽬录存放各⾃的数据。
4.3 为每个实例创建配置⽂件
我们将每个实例（3306的可配置也可不配置，因为它默认使⽤的是/etc/myf）的配置⽂件都放在各⾃端⼝⽬录的下⾯，视情况修改下⾯参数，然后直接拷贝运⾏即可：
1cat > /data/mysql/3307/myf <<EOF
2 [mysqld]
3 basedir=/opt/software/mysql
4 datadir=/data/mysql/3307/data
5 socket=/data/mysql/3307/mysql.sock
6 log_error=/data/mysql/3307/logs/mysql_error.log
7 port=3307
8 server_id=7
9 [client]
10 socket=/data/mysql/3307/mysql.sock
11 EOF
12
13cat > /data/mysql/3308/myf <<EOF
14 [mysqld]
15 basedir=/opt/software/mysql
16 datadir=/data/mysql/3308/data
17 socket=/data/mysql/3308/mysql.sock
18 log_error=/data/mysql/3308/logs/mysql_error.log
19 port=3308
20 server_id=8
21 [client]
22 socket=/data/mysql/3308/mysql.sock
23 EOF
24
25cat > /data/mysql/3309/myf <<EOF
26 [mysqld]
27 basedir=/opt/software/mysql
28 datadir=/data/mysql/3309/data
29 socket=/data/mysql/3309/mysql.sock
30 log_error=/data/mysql/3309/logs/mysql_error.log
31 port=3309
32 server_id=9
33 [client]
34 socket=/data/mysql/3309/mysql.sock
35 EOF
完事之后，各⾃的数据⽬录下就有了配置⽂件了：
1 [root@cs software]# ls /data/mysql/3307/
2 backup data logs myf
3 root@cs software]# cat /data/mysql/3307/myf
4 [mysqld]
5 basedir=/opt/software/mysql
6 datadir=/data/mysql/3307/data
7 socket=/data/mysql/3307/mysql.sock
8 log_error=/data/mysql/3307/logs/mysql_error.log
9 port=3307
10 server_id=7
11 [client]
12 socket=/data/mysql/3307/mysql.sock
4.4 授权
[root@cs software]# chown -sql /data/mysql/*
现在，配置⽂件完事了，就可以着⼿进⾏初始化了。
4.5 多实例的初始化
1 mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3307/data --basedir=/opt/software/mysql
2 mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3308/data --basedir=/opt/software/mysql
3 mysqld --initialize-insecure --user=mysql --datadir=/data/mysql/3309/data --basedir=/opt/software/mysql
上⾯的命令执⾏时，会有Warning提⽰，这⾥不要管它。注意，如果你的服务器内存较⼩的话，可能后续启不起来3台实例，不过有个2G内存也差不多了。
4.6 配置systemctl
实际上，下⾯这⼏个配置⽂件内容都是来⾃于mysqld.service⽂件，然后修改了各⾃实例的配置⽂件路径：
1cat > /etc/systemd/system/mysqld3307.service <<EOF
2 [Unit]
3 Description=MySQL Server
4 Documentation=man:mysqld(8)
5 Documentation=sql/doc/refman/en/using-systemd.html
6 After=network.target
7 After=syslog.target
8 [Install]
9 WantedBy=multi-user.target
10 [Service]
11 User=mysql
12 Group=mysql
13 ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3307/myf
14 LimitNOFILE = 5000
15 EOF
16
17cat > /etc/systemd/system/mysqld3308.service <<EOF
18 [Unit]
19 Description=MySQL Server
20 Documentation=man:mysqld(8)
21 Documentation=sql/doc/refman/en/using-systemd.html
22 After=network.target
23 After=syslog.target
24 [Install]
25 WantedBy=multi-user.target
26 [Service]
27 User=mysql
28 Group=mysql
29 ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3308/myf
30 LimitNOFILE = 5000
31 EOF
32
33cat > /etc/systemd/system/mysqld3309.service <<EOF
34 [Unit]
35 Description=MySQL Server
36 Documentation=man:mysqld(8)
37 Documentation=sql/doc/refman/en/using-systemd.html
38 After=network.target
39 After=syslog.target
40 [Install]
41 WantedBy=multi-user.target
42 [Service]
43 User=mysql
44 Group=mysql
45 ExecStart=/opt/software/mysql/bin/mysqld --defaults-file=/data/mysql/3309/myf
46 LimitNOFILE = 5000
47 EOF
View Code
4.7 启动
到这⾥，多实例的配置基本完毕，可以尝试启动了
1 systemctl start mysqld3307.service
2 systemctl start mysqld3308.service
3 systemctl start mysqld3309.service
验证下：
1 [root@cs software]# find / -name mysql.sock
2 /data/mysql/3307/mysql.sock
3 /data/mysql/3308/mysql.sock
4 /data/mysql/3309/mysql.sock
5 [root@cs software]# netstat -lnp|grep330
6 tcp6 00 :::330
7 :::* LISTEN 8361/mysqld
7 tcp6 00 :::3308 :::* LISTEN 8368/mysqld
8 tcp6 00 :::3309 :::* LISTEN 8636/mysqld
9 unix 2 [ ACC ] STREAM LISTENING 557958361/mysqld /data/mysql/3307/mysql.sock
10 unix 2 [ ACC ] STREAM LISTENING 305408368/mysqld /data/mysql/3308/mysql.sock
11 unix 2 [ ACC ] STREAM LISTENING 747588636/mysqld /data/mysql/3309/mysql.sock
恢复单实例的配置⽂件
1 [root@cs software]# mv /etc/myf.bak /etc/myf
然后启动3306这台实例，注意，3306的跟其他的实例不太⼀样：
1 [root@cs software]# systemctl start mysqld.service
2 [root@cs software]# find / -name mysql.sock
3 /tmp/mysql.sock
4 /data/mysql/3307/mysql.sock
5 /data/mysql/3308/mysql.sock
6 /data/mysql/3309/mysql.sock
7 [root@cs software]# netstat -lnp|grep330
8 tcp6 00 :::3306 :::* LISTEN 8748/mysqld
9 tcp6 00 :::3307 :::* LISTEN 8361/mysqld
10 tcp6 00 :::3308 :::* LISTEN 8368/mysqld
11 tcp6 00 :::3309 :::* LISTEN 8636/mysqld
12 unix 2 [ ACC ] STREAM LISTENING 557958361/mysqld /data/mysql/3307/mysql.sock
13 unix 2 [ ACC ] STREAM LISTENING 305408368/mysqld /data/mysql/3308/mysql.sock
14 unix 2 [ ACC ] STREAM LISTENING 747588636/mysqld /data/mysql/3309/mysql.sock
五、连接管理
现在4个实例都能正常运⾏后，摆在我们⾯前的是怎么连接到指定的实例？
有以下两种⽅式可以连接到指定数据库：
1 # 注意，此时的登录密码都为空，直接回车即可
2 [root@cs software]# mysql -uroot -p -S /tmp/mysql.sock -e "select @@server_id"
3 Enter password:
4 +-------------+
5 | @@server_id |
6 +-------------+
7 | 6 |
8 +-------------+
9 [root@cs software]# mysql -uroot -p -S /data/mysql/3307/mysql.sock -e "select @@server_id"
10 Enter password:
11 +-------------+
12 | @@server_id |
13 +-------------+
14 | 7 |
15 +-------------+
16 [root@cs software]# mysql -uroot -p -S /data/mysql/3308/mysql.sock -e "select @@server_id"
17 Enter password:
18 +-------------+
19 | @@server_id |
20 +-------------+
21 | 8 |
22 +-------------+
23 [root@cs software]# mysql -uroot -p -S /data/mysql/3309/mysql.sock -e "select @@server_id"
24 Enter password:
25 +-------------+
26 | @@server_id |
27 +-------------+
28 | 9 |
29 +-------------+
以上是通过不同的mysql.sock来来接指定的数据库实例，除此之外，还可以通过-h -P参数来连接到指定的数据库实例
1 [root@cs software]# mysql -uroot -p -h127.0.0.1 -P3306 -e "select @@server_id"
2 Enter password:
3 +-------------+
4 | @@server_id |
5 +-------------+
6 | 6 |
7 +-------------+
8 [root@cs software]# mysql -uroot -p -h127.0.0.1 -P3307 -e "select @@server_id"
9 Enter password:
10 +-------------+
11 | @@server_id |
12 +-------------+
13 | 7 |
14 +-------------+
15 [root@cs software]# mysql -uroot -p -h127.0.0.1 -P3308 -e "select @@server_id"
16 Enter password:
17 +-------------+
18 | @@server_id |
19 +-------------+
20 | 8 |
21 +-------------+
22 [root@cs software]# mysql -uroot -p -h127.0.0.1 -P3309 -e "select @@server_id"
23 Enter password:
24 +-------------+
25 | @@server_id |
26 +-------------+
27 | 9 |
28 +-------------+
六、⽤户管理
默认的，上⾯使⽤root⽤户只有本地的访问权限，远程⽆法使⽤，且初始化时，我们将初始密码也设置为空了：
1 [root@cs software]# mysql -uroot -p -h127.0.0.1 -P3306 -e "select user,host from mysql.user"
2 Enter password:
3 +---------------+-----------+
4 | user | host |
5 +---------------+-----------+
6 | mysql.session | localhost |
7 | mysql.sys | localhost |
8 | root | localhost |
9 +---------------+-----------+
所以，这⾥我们创建远程⽤户和创建密码再配置相关权限：
1 -- 分别登录到各⾃的实例中，此时密码还未空，直接回车，然后执⾏下⾯命令，进⾏创建⽤户并授权
2 -- mysql -uroot -p -h127.0.0.1 -P3306
3 -- mysql -uroot -p -h127.0.0.1 -P3307
4 -- mysql -uroot -p -h127.0.0.1 -P3308
5 -- mysql -uroot -p -h127.0.0.1 -P3309
6
7 grant all on *.* to root@'localhost' identified by '123';
8 grant all on *.* to root@'%' identified by '123';
9 flush privileges;
注意，上⾯的创建⽤户和授权命令仅适⽤于MySQL8.0⼀下，因为从MySQL8.0开始，创建⽤户和授权分为两步操作，这点需要注意。创建完成后，就可以通过密码进⾏登录了：
1 [root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3306 -e "select @@server_id"
2 mysql: [Warning] Using a password on the command line interface can be insecure.
3 +-------------+
4 | @@server_id |
5 +-------------+
6 | 6 |
7 +-------------+
8 [root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3307 -e "select @@server_id"
9 mysql: [Warning] Using a password on the command line interface can be insecure.
10 +-------------+
11 | @@server_id |
12 +-------------+
13 | 7 |
14 +-------------+
15 [root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3308 -e "select @@server_id"
16 mysql: [Warning] Using a password on the command line interface can be insecure.
17 +-------------+
18 | @@server_id |
19 +-------------+
20 | 8 |
21 +-------------+
22 [root@cs software]# mysql -uroot -p123 -h127.0.0.1 -P3309 -e "select @@server_id"
23 mysql: [Warning] Using a password on the command line interface can be insecure.
24 +-------------+
25 | @@server_id |
26 +-------------+
27 | 9 |
28 +-------------+
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论