目录
1. 机器准备
#一台虚拟机
hostname：myhost
ip：ip_address
2. 系统环境
[root@myhost ~]# cat /etc/redhat-release
AlmaLinux release 8.5 (Arctic Sphynx)
[root@myhost ~]# firewall-cmd --state #查看防火墙的状态
running
[root@myhost ~]# firewall-cmd --list-ports #查看防火墙已经开放的端口
22/tcp
[root@myhost ~]# firewall-cmd --list-services #查看防火墙开放的服务
cockpit dhcpv6-client ssh
#假设设置mysql的服务端口为3306，允许3306/tcp端口访问
[root@myhost ~]# firewall-cmd --zone=public --permanent --add-port=3306/tcp
success
[root@myhost ~]# firewall-cmd --reload #重新加载防火墙配置
success
[root@myhost ~]# setenforce 0 && getenforce
Permissive
[root@myhost ~]# sed -i "s/SELINUX=enforcing/SELINUX=disabled/g" /etc/sysconfig/selinux
[root@myhost ~]# cat /etc/sysconfig/selinux
3. 安装配置使用mysql8.0
3.1 检查是否有安装mysql数据库（如果有mysql或者mariadb数据库，则卸载）
[root@myhost ~]# rpm -qa |grep mysql
[root@myhost ~]# rpm -qa | grep mariadb
[root@myhost ~]# ll /etc/my.cnf
ls: 无法访问'/etc/my.cnf': No such file or directory
#如果有mysql或者mariadb数据库，则卸载
[root@myhost ~]# yum -y remove `rpm -qa | grep mysql` 或者 yum -y remove `rpm -qa | grep mariadb`
[root@myhost ~]# ll /etc/my.cnf
ls: 无法访问'/etc/my.cnf': No such file or directory
[root@myhost ~]# rpm -qa |grep mysql 或者 rpm -qa | grep mariadb
3.2 下载并安装mysql官方的 yum repository
[root@myhost ~]# cd /home/tools/
[root@myhost tools]# wget https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm
[root@myhost tools]# ll
总用量 28
-rw-r--r--. 1 root root 25820 4月 18 2018 mysql80-community-release-el7-1.noarch.rpm
3.3 rpm安装生成所需要的repo文件
[root@myhost tools]# ll /etc/yum.repos.d/
总用量 20
-rw-r--r--. 1 root root 941 11月 10 10:55 almalinux-ha.repo
-rw-r--r--. 1 root root 883 11月 10 10:55 almalinux-plus.repo
-rw-r--r--. 1 root root 961 11月 10 10:55 almalinux-powertools.repo
-rw-r--r--. 1 root root 2660 11月 10 10:55 almalinux.repo
-rw-r--r--. 1 root root 1022 11月 10 10:55 almalinux-resilientstorage.repo
[root@myhost tools]# rpm -ivh mysql80-community-release-el7-1.noarch.rpm
#执行完成后会在/etc/yum.repos.d/目录下生成两个repo文件: mysql-community.repo 和 mysql-community-source.repo
[root@myhost tools]# ll /etc/yum.repos.d/
总用量 28
-rw-r--r--. 1 root root 941 11月 10 10:55 almalinux-ha.repo
-rw-r--r--. 1 root root 883 11月 10 10:55 almalinux-plus.repo
-rw-r--r--. 1 root root 961 11月 10 10:55 almalinux-powertools.repo
-rw-r--r--. 1 root root 2660 11月 10 10:55 almalinux.repo
-rw-r--r--. 1 root root 1022 11月 10 10:55 almalinux-resilientstorage.repo
-rw-r--r--. 1 root root 1864 2月 22 2018 mysql-community.repo ###新生成的repo文件###
-rw-r--r--. 1 root root 1885 2月 22 2018 mysql-community-source.repo ###新生成的repo文件###
3.4 正式安装mysql服务器
[root@myhost tools]# yum install -y mysql-server
[root@myhost tools]# rpm -qa |grep mysql
mysql-8.0.26-1.module_el8.4.0+2532+b8928c02.x86_64
mysql-common-8.0.26-1.module_el8.4.0+2532+b8928c02.x86_64
mysql-server-8.0.26-1.module_el8.4.0+2532+b8928c02.x86_64
mysql80-community-release-el7-1.noarch
mysql-errmsg-8.0.26-1.module_el8.4.0+2532+b8928c02.x86_64
至此，mysql服务器安装完毕！！！
3.5 查看mysql 8.0 数据库主配置文件
[root@myhost tools]# ll /etc/my.cnf
-rw-r--r--. 1 root root 202 12月 3 2020 /etc/my.cnf
[root@myhost tools]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d ##这里引入MySQL 8.0 数据库各个详细配置文件##
[root@myhost tools]# ll /etc/my.cnf.d
总用量 12
-rw-r--r--. 1 root root 295 12月 3 2020 client.cnf
-rw-r--r--. 1 root root 565 8月 24 2021 mysql-default-authentication-plugin.cnf
-rw-r--r--. 1 root root 612 9月 24 2021 mysql-server.cnf
- 服务端配置文件
[root@myhost tools]# cat /etc/my.cnf.d/mysql-server.cnf
#
# This group are read by MySQL server.
# Use it for options that only the server (but not clients) should see
#
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
- 客户端配置文件
[root@myhost tools]# cat /etc/my.cnf.d/client.cnf
#
# These two groups are read by the client library
# Use it for options that affect all clients, but not the server
#
[client]
# This group is not read by mysql client library,
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]
3.6 启动mysql
### 启动mysql
[root@myhost tools]# systemctl start mysqld.service
### 查看MySQL运行状态
[root@myhost tools]# systemctl status mysqld.service
● mysqld.service - MySQL 8.0 database server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
Active: active (running) since Thu 2022-05-05 03:07:44 EDT; 10s ago
Process: 53994 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS)
Process: 53862 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS)
Process: 53837 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS)
Main PID: 53947 (mysqld)
Status: "Server is operational"
Tasks: 38 (limit: 23508)
Memory: 460.1M
CGroup: /system.slice/mysqld.service
└─53947 /usr/libexec/mysqld --basedir=/usr
5月 05 03:07:35 myhost systemd[1]: Starting MySQL 8.0 database server...
5月 05 03:07:35 myhost mysql-prepare-db-dir[53862]: Initializing MySQL database
5月 05 03:07:44 myhost systemd[1]: Started MySQL 8.0 database server.
### 将MySQL加入开机自启动
[root@myhost tools]# systemctl enable mysqld.service
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
3.7 查看mysql的初始登录密码
当MySQL开始正常运行时，要想进入MySQL还得先找出此时root用户的密码，root用户的密码可以在MySQL日志文件中找出，前面安装MySQL时，服务端配置文件里面显示了MySQL的日志文件所在路径：log-error=/var/log/mysql/mysqld.log
[root@myhost tools]# egrep -v "^#|^$" /etc/my.cnf.d/mysql-server.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
[root@myhost tools]# grep "password" /var/log/mysql/mysqld.log
2022-05-05T07:07:39.174205Z 6 [Warning] [MY-010453] [Server] root@localhost is created with an empty password ! Please consider switching off the --initialize-insecure option.
根据日志中显示，此次安装mysql时没有设置root用户密码（即可以使用空密码进行登录）
3.8 登录mysql数据库
[root@myhost tools]# mysql -uroot -p
Enter password: #空密码#
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
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> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
mysql>
此时不能做任何事情，因为MySQL默认必须修改密码之后才能操作数据库。
3.9 为root用户设置新密码
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select host,user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+------------------+
mysql> alter user 'root'@'localhost' identified with by '新密码';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'by 'yuki12#$'' at line 1
mysql> set password=password('新密码');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'password('yuki12#$')' at line 1
mysql> set authentication_string=password('新密码'); ### mysql的版本不一样，用户密码字段表示也不一样，这一点需要注意。
ERROR 1193 (HY000): Unknown system variable 'authentication_string'；
mysql>
上面修改root账户密码失败，原因在于在MySQL 8.02前，修改mysql的root账户密码可以直接使用：
set password=password('新密码');
set authentication_string=password('新密码');
alter user 'root'@'localhost' identified with by '新密码';
但MySQL8.0开始以后，这样默认是不行的。因为之前版本的mysql，使用的密码认证插件是“mysql_native_password”，而现在使用的是“caching_sha2_password”，但是我们修改密码必须使用的认证插件是“mysql_native_password”，所以MySQL8.0之后的版本在修改密码时要在sql语句中申明修改密码认证插件。
mysql> select version();
+-----------+
| version() |
+-----------+
| 8.0.26 |
+-----------+
1 row in set (0.00 sec)
mysql> alter user 'root'@'localhost' identified with mysql_native_password by '新密码';
Query OK, 0 rows affected (0.01 sec)
至此，成功修改root密码！！！
mysql> select host, user, password, plugin from user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | root | *1326652C75900CFF0EF7EF997D4D178B173CE182 | mysql_native_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
- host：允许用户登录在哪个ip可以远程登录，%表示任意ip都可以远程登录mysql数据库；
- user：当前数据库的用户名；
- authentication_string：用户密码，在mysql 5.7.9以后废弃了password字段和password()函数；
- plugin：密码加密方式；
我们看host和user两列，host和user中root对应的值为localhost，即root用户的访问权限为localhost，想把该用户的访问权限设置可远程连接，我们只需要把localhost更改为通配的%就可以了。上面的权限操作语句没有走通，这里就直接使用update语句来更改吧。
mysql> update user set host = "%" where user = "root";
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> select host, user, authentication_string, plugin from user;
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| host | user | authentication_string | plugin |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
| % | root | *1326652C75900CFF0EF7EF997D4D178B173CE182 | mysql_native_password |
| localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
| localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | caching_sha2_password |
+-----------+------------------+------------------------------------------------------------------------+-----------------------+
4 rows in set (0.00 sec)
3.10 使用Navicat工具远程登录数据库(防火墙开启mysql端口的情况下)
以上报错原因在于我们在安装的mysql8.0.26数据库时没有在配置文件中设置密码认证方式，此版本mysql默认的密码加密方式是：caching_sha2_password，而现在很多客户端工具还不支持这种加密认证方式，连接测试的时候就会报错。解决办法如下：
[root@myhost ~]# vi /etc/my.cnf.d/mysql-server.cnf
#添加如下配置：default_authentication_plugin=mysql_native_password
[root@myhost ~]# systemctl restart mysqld.service
4. mysql 8.0 使用教程
4.1 查看数据库服务的基本信息
mysql> status;
--------------
mysql Ver 8.0.26 for Linux on x86_64 (Source distribution)
Connection id: 9
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.26 Source distribution
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /var/lib/mysql/mysql.sock
Binary data as: Hexadecimal
Uptime: 5 min 51 sec
Threads: 2 Questions: 11 Slow queries: 0 Opens: 117 Flush tables: 3 Open tables: 36 Queries per second avg: 0.031
--------------
4.2 查看当前服务器中有哪些库
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
4.3 查看当前使用的库中有哪些表
use 数据库名; ### 使用哪个数据库
show tables; ### 展示数据库中的表
4.4 查看表的结构
describe 表名;
4.5 创建新的库
create database 数据库名 ;
4.6 查看创建库的语句
mysql> show create database sys;
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
| sys | CREATE DATABASE `sys` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.7 创建新的表
create table 表名 （字段1名称 类型,字段2名称 类型, ... , primary key （主键名））
4.8 查看创建表的语句
mysql> use mysql;
Database changed
mysql> show tables；
mysql> show create table mysql.user;
4.9 删除一个数据库
drop database 数据库名;
4.10 删除一个数据表
drop table 数据库名.表名;
4.11 修改加密规则
alter user 'root'@'localhost' identified by '密码' password expire never;
4.12 修改用户密码
alter user 'root'@'localhost' identified with mysql_native_password by '新密码';
4.13 创建新用户并授权
create user 'yuki'@'%' identified with mysql_native_password by '密码';
#授权所有权限
grant all privileges on *.* to 'yuki'@'%';
flush privileges;
#授权基本的查询修改权限，按需求设置
grant select,insert, update, delete, create,drop, alter on *.* to 'yuki'@'%';
flush privileges;
4.14 查看用户所具有的权限
mysql> show grants for 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
4.15 回收权限
mySQL> revoke delete on *.* from 'yuki'@'%';
Query OK, 0 rows affected (0.01 sec)
mySQL> show grants for 'yuki'@'%';
mySQL> revoke select(id) on mysql.user from 'yuki'@'%';
5. mysql 8.0 新特性
ERROR 1040 (HY000): Too many connections
上面这个报错，相信大多数童鞋都遇见过；那麽碰到这个问题，我们应该怎麽办呢？在MySQL 5.7及之前版本，出现“too many connection”报错，超级用户root也无法登录上去，除了重启实例，没有其他更好的解决办法；不过在MySQL 8.0版本中，是对连接管理做了一些优化，先后做了两个比较大的改变：一个是允许额外连接，另一个是专用的管理端口。
5.1 额外连接
在MySQL 8.0版本中，在当前连接数达到最大连接数时，服务端允许1个额外连接，可以让具有CONNECTION_ADMIN权限的用户连接进来，下面简单测试一下。
- 为了方便测试，先调整最大连接数为3个（也可以直接在配置文件中配置，永久生效）
mysql> set global max_connections=3;
Query OK, 0 rows affected (0.00 sec)
- 创建普通用户(没有CONNECTION_ADMIN权限)
mysql> create user 'yuki'@'%' identified with mysql_native_password by '123456';
mysql> show grants for 'yuki'@'%';
+----------------------------------+
| Grants for yuki@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `yuki`@`%` |
+----------------------------------+
mysql> grant select,insert, update, delete, create,drop, alter on *.* to 'yuki'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yuki'@'%';
+--------------------------------------------------------------------------------+
| Grants for yuki@% |
+--------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO `yuki`@`%` |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 然后多开几个会话，以达到最大连接数
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 3322 | Waiting on empty queue | NULL |
| 14 | root | localhost | NULL | Sleep | 70 | | NULL |
| 16 | yuki | localhost | NULL | Sleep | 26 | | NULL |
| 17 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
4 rows in set (0.00 sec)
mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 3 |
+-------------------+-------+
1 row in set (0.00 sec)
- 当最大连接数有3个之后，普通用户yuki(没有CONNECTION_ADMIN权限)再尝试连接，会报错too many connections
[root@myhost ~]# mysql -uyuki -p
Enter password:
ERROR 1040 (HY000): Too many connections
- 如果是第四个连接，超级用户root(默认具有CONNECTION_ADMIN权限)会连接成功
[root@myhost ~]# mysql -uyuki -p
Enter password:
ERROR 1040 (08004): Too many connections
[root@myhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 19
Server version: 8.0.26 Source distribution
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>
- 此时再次查看当前连接数，为max_connections+1
mysql> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 3627 | Waiting on empty queue | NULL |
| 14 | root | localhost | NULL | Sleep | 375 | | NULL |
| 16 | yuki | localhost | NULL | Sleep | 331 | | NULL |
| 17 | root | localhost | NULL | Sleep | 288 | | NULL |
| 19 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
5 rows in set (0.00 sec)
mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 4 |
+-------------------+-------+
1 row in set (0.00 sec)
mysql>
- 如果是超过四个连接，超级用户root(默认具有CONNECTION_ADMIN权限)也不会再连接成功。
[root@myhost ~]# mysql -uroot -p
Enter password:
ERROR 1040 (HY000): Too many connections
总结：通过上面测试可知，在MySQL 8.0中，允许的连接数为max_connections+1，其中这1个额外连接，只允许具有CONNECTION_ADMIN权限的用户（比如：root用户）使用。通过这1个额外连接，DBA可以使用超级用户root连接，进行kill会话等管理操作，以避免直接重启实例，降低成本，提高效率。
5.2 管理端口
上面介绍的额外连接，在一定程度上，提供了出现too many connection问题时的临时解决手段，但额外数量只有1个，难免会有一些意外，出现类似"连接被抢用"、“终端异常掉线”等情况。因此，在MySQL 8.0.14版本中，又推出了一个非常重要的新特性——管理端口；它允许具有SERVICE_CONNECTION_ADMIN权限的用户，通过特定的IP和PORT连接上来，且没有连接数限制。
5.2.1 先介绍下相关参数
[root@myhost ~]# netstat -lntup |grep mysql
tcp6 0 0 :::33060 :::* LISTEN 55532/mysqld
tcp6 0 0 :::3306 :::* LISTEN 55532/mysqld
admin_address： 监听IP地址
admin_port： 监听端口
create_admin_listener_thread：是否创建一个单独的线程来监听管理连接（默认关闭）
5.2.2 通过配置上述参数，即可启用管理端口
mysql> show global variables like '%admin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| admin_address | |
| admin_port | 33062 |
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| admin_tls_ciphersuites | |
| admin_tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| create_admin_listener_thread | OFF |
| log_slow_admin_statements | OFF |
| persist_only_admin_x509_subject | |
+---------------------------------+-------------------------------+
14 rows in set (0.01 sec)
mysql> show global variables like '%create_admin_listener_thread%';
+------------------------------+-------+
| Variable_name | Value |
+------------------------------+-------+
| create_admin_listener_thread | OFF |
+------------------------------+-------+
1 row in set (0.01 sec)
mysql> exit
[root@myhost ~]# vi /etc/my.cnf.d/mysql-server.cnf
#添加如下配置：
#监听的管理端口
admin_port=33062
#管理端口监听IP地址
admin_address='127.0.0.1'
#是否创建一个单独的线程来监听管理连接（默认关闭）
create_admin_listener_thread=on
# 允许最大连接数
max_connections=3
# 允许连接失败的次数。这是为了防止有人从该主机试图攻击数据库系统
max_connect_errors=10
#重启mysql使配置生效
[root@myhost ~]# systemctl restart mysqld.service
[root@myhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
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> show global variables like '%max_connections%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| max_connections | 3 |
| mysqlx_max_connections | 100 |
+------------------------+-------+
2 rows in set (0.01 sec)
mysql> show global variables like '%admin%';
+---------------------------------+-------------------------------+
| Variable_name | Value |
+---------------------------------+-------------------------------+
| admin_address | 127.0.0.1 |
| admin_port | 33062 |
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| admin_tls_ciphersuites | |
| admin_tls_version | TLSv1,TLSv1.1,TLSv1.2,TLSv1.3 |
| create_admin_listener_thread | ON |
| log_slow_admin_statements | OFF |
| persist_only_admin_x509_subject | |
+---------------------------------+-------------------------------+
14 rows in set (0.00 sec)
5.2.3 进行测试
- 为了方便测试，先调整最大连接数为3个（也可以直接在配置文件中配置，永久生效）
mysql> set global max_connections=3;
Query OK, 0 rows affected (0.00 sec)
- 创建普通用户(普通用户没有SERVICE_CONNECTION_ADMIN权限)
mysql> create user 'yuki'@'%' identified with mysql_native_password by '123456';
mysql> show grants for 'yuki'@'%';
+----------------------------------+
| Grants for yuki@% |
+----------------------------------+
| GRANT USAGE ON *.* TO `yuki`@`%` |
+----------------------------------+
mysql> grant select,insert, update, delete, create,drop, alter on *.* to 'yuki'@'%';
Query OK, 0 rows affected (0.00 sec)
mysql> show grants for 'yuki'@'%';
+--------------------------------------------------------------------------------+
| Grants for yuki@% |
+--------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO `yuki`@`%` |
+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)
- 然后多开几个会话，以达到最大连接数
[root@myhost ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.26 Source distribution
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> show processlist;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 168 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Sleep | 156 | | NULL |
| 9 | root | localhost | NULL | Sleep | 145 | | NULL |
| 12 | yuki | localhost | NULL | Sleep | 98 | | NULL |
| 13 | root | localhost | NULL | Query | 0 | init | show processlist |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
5 rows in set (0.00 sec)
mysql> show global status like 'threads_connected';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_connected | 4 |
+-------------------+-------+
1 row in set (0.01 sec)
- 当最大连接数有3个之后，普通用户yuki(没有SERVICE_CONNECTION_ADMIN权限)再尝试连接，会报错too many connections
[root@myhost ~]# mysql -uyuki -p
Enter password:
ERROR 1040 (08004): Too many connections
- 当最大连接数有4个（因为超级用户root还可以额外连接一次）之后，超级用户root(默认没有SERVICE_CONNECTION_ADMIN权限)尝试通过管理端口33062也会连接失败，会报以下错误：
[root@myhost ~]# mysql -uroot -p -h127.0.0.1 -P33062
Enter password:
ERROR 1227 (42000): Access denied; you need (at least one of) the SERVICE_CONNECTION_ADMIN privilege(s) for this operation
原因：此时超级用户root和普通账户一样也是默认没有SERVICE_CONNECTION_ADMIN 权限的。
解决办法：对超级用户root进行授权
mysql> grant SERVICE_CONNECTION_ADMIN ON *.* TO `root`@`%` ;
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
mysql> show grants for 'root'@'%';
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for root@% |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `root`@`%` WITH GRANT OPTION |
| GRANT SERVICE_CONNECTION_ADMIN ON *.* TO `root`@`%` |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
- 此时，当最大连接数有4个（因为超级用户root还可以额外连接一次）之后，超级用户root(默认具有SERVICE_CONNECTION_ADMIN权限)尝试通过管理端口33062连接成功
[root@cfz-78 ~]# mysql -uroot -p -h127.0.0.1 -P33062
Enter password:
ERROR 1227 (42000): Access denied; you need (at least one of) the SERVICE_CONNECTION_ADMIN privilege(s) for this operation
[root@cfz-78 ~]# mysql -uroot -p -h127.0.0.1 -P33062
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 15
Server version: 8.0.26 Source distribution
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，通过管理端口连接成功，不受最大连接数max_connections限制。可以说，有了管理端口这个新功能，DBA再也不用担心too many connections的问题。
总结：在MySQL 8.0版本中，为了应对too many connections的场景，先后推出了额外连接和管理端口两个新功能，可以让DBA方便、快速地解决问题；不过，这始终是一个临时应急手段，最根本的原因还是要排查应用端的配置(并发限流、SQL性能、连接池配置等等)，以彻底规避此类问题。
