1、msyqlLinux通用二进制安装
1. MySQL :: Download MySQL Community Server (Archived Versions)https://downloads.mysql.com/archives/community/https://downloads.mysql.com/archives/community/https://downloads.mysql.com/archives/community/https://downloads.mysql.com/archives/community/https://downloads.mysql.com/archives/community/https://downloads.mysql.com/archives/community/
https://downloads.mysql.com/archives/community/下载8.0.28
2.把二进制包放到Linux下
# 添加mysql组
$> groupadd mysql
# 增加mysql用户，把-g mysql组加到mysql用户中,-s 指定用户的登录shell,/bin/false创建没有服务器登录权限的用户 主机,-r 创建一个系统用户，系统用户的 UID 通常小于 1000。
$> useradd -r -g mysql -s /bin/false mysql
$> cd /usr/local
# 把mysql解压到这个目录下
$> tar xvf /path/to/mysql-VERSION-OS.tar.xz
# 创建硬链接mysql
$> ln -s full-path-to-mysql-VERSION-OS mysql
$> cd mysql
$> mkdir mysql-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files
# 初始化数据库
$> bin/mysqld --initialize --user=mysql
# 启动数据库服务端
$> bin/mysqld_safe --user=mysql &
# Next command is optional
$> cp support-files/mysql.server /etc/init.d/mysql.server初始化数据库时可能会出现问题
是因为找不到 libaio.so.1这个库
查看系统是否有libaio.so 但不是 libaio.so.1
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# ldconfig -p | grep libaiolibaio.so.1t64 (libc6,x86-64) => /lib/x86_64-linux-gnu/libaio.so.1t64libaio.so (libc6,x86-64) => /lib/x86_64-linux-gnu/libaio.so
查看libaio.so位置
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# find /usr -name libaio.so*
/usr/lib/x86_64-linux-gnu/libaio.so.1t64.0.2
/usr/lib/x86_64-linux-gnu/libaio.so.1t64
/usr/lib/x86_64-linux-gnu/libaio.so创建硬链接链接到libaio.so.1
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# ln -s /usr/lib/x86_64-linux-gnu/libaio.so.1t64.0.2 /usr/lib/x86_64-linux-gnu/libaio.so.1
再次初始化就没问题了，后面他会给你个root的临时密码
2025-01-23T08:37:26.842753Z 0 [System] [MY-013169] [Server] /usr/local/mysql-8.0.28-linux-glibc2.12-x86_64/bin/mysqld (mysqld 8.0.28) initializing of server in progress as process 14334
2025-01-23T08:37:26.897078Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2025-01-23T08:37:27.685107Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2025-01-23T08:37:29.432879Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: t8g,pkplN4YI启动后用mysql客户端连接测试一下
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# bin/mysql -u root -p
bin/mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
是缺少 libtinfo.so.5原因
查找一下有没有这个库
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# ldconfig -p | grep libtinfolibtinfo.so.6 (libc6,x86-64) => /lib/x86_64-linux-gnu/libtinfo.so.6发现libtinfo.so.6但没有 libtinfo.so.5
也可以用6代替5
找到6库位置
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# find /usr -name libtinfo.so*
/usr/lib/x86_64-linux-gnu/libtinfo.so.6.5
/usr/lib/x86_64-linux-gnu/libtinfo.so.6
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# ln -n /usr/lib/x86_64-linux-gnu/libtinfo.so.6.5 /usr/lib/x86_64-linux-gnu/libtinfo.so.5
使用初始密码登录成功
root@hwz-VMware-Virtual-Platform:/usr/local/mysql# bin/mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 14
Server version: 8.0.28Copyright (c) 2000, 2022, 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 databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
mysql>
他要你改密码后才能操作
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root-password';这时应该可以正常操作数据库了。
额外的配置
为了可以直接使用mysql命令，不用到/usr/local/mysql/bin找命令，可以mysql的命令添加到环境变量中
vim ~/.bashrc# 在文件最后加上export PATH=$PATH:/usr/local/mysql/binsource ~/.bashrc使用systemctl管理MySQL服务
在/etc/systemd/system/mysql.service写入下面配置
[Unit]
Description=MySQL Server
After=network.target[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf
ExecStop=/usr/local/mysql/bin/mysqladmin shutdown
Restart=on-failure[Install]
WantedBy=multi-user.targetsystemctl配置文件更改需要使用命令刷新一下配置
systemctl daemon-reload配置文件/etc/mysql/my.cnf
[client]
user=root
password=xxx # 配置了这个默认登录root不用输入密码[mysql]
prompt=(\\u@\\h) [\\d]>\\_ # 更改MySQL客户端命令>前面显示内容[mysqld]
port=3306
user=mysql
datadir=/usr/local/mysql/data
log_error=error.log如果之前开启过MySQL需要使用命令mysqladmin shutdown关闭 MySQL
查看mysql进程是否存在
root@hwz-VMware-Virtual-Platform:/etc/mysql# ps -ef | grep mysql
mysql 18982 1 0 21:22 ? 00:00:05 /usr/local/mysql/bin/mysqld --defaults-file=/etc/mysql/my.cnf
root 19061 3897 0 21:39 pts/2 00:00:00 grep --color=auto mysqlsystemctl start mysql # 启动MySQL
systemctl stop mysql # 关闭MySQL
systemctl status mysql # 查看MySQL服务状态额外注意点
1.启动时如果不特定指定配置文件位置，MySQL启动时会按这几个配置文件顺序读取/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf
root@hwz-VMware-Virtual-Platform:~# mysql --help | grep my.cnforder of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 后面的配置文件配置会覆盖前面的配置文件配置
一般配置文件都放在/etc/下，然而/etc/mysql/my.cnf可读性好点，所以推荐放在/etc/mysql/my.cnf
2.前面配置systemctl配置时启动命令为啥不用mysqld_safe而是用mysqld来启动MySQL？
因为mysqld_safe是一个守护进程
这里我使用mysqld_safe启动MySQL会发现它其实是启动了两个进程，一个是mysqld_safe，一个是mysqld,本质还是用mysqld启动MySQL，mysqld_safe是用来监视mysqld的，mysqld挂掉了会自动启动，这里可以使用kill -9 掉mysqld来测试。
root@hwz-VMware-Virtual-Platform:/etc/mysql# mysqld_safe --user=mysql &
[1] 19086
root@hwz-VMware-Virtual-Platform:/etc/mysql# 2025-01-25T13:46:08.647339Z mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
2025-01-25T13:46:08.700221Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/dataroot@hwz-VMware-Virtual-Platform:/etc/mysql# ps -ef | grep mysql
root 19086 3897 0 21:46 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --user=mysql
mysql 19210 19086 15 21:46 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysq --log-error=error.log --pid-file=hwz-VMware-Virtual-Platform.pid --port=3306
root 19258 3897 0 21:46 pts/2 00:00:00 grep --color=auto mysqlsystemctl本身可以配置 守护进程，自然不需要mysqld_safe
1.1 忘记root密码
忘记root密码怎么修改root密码？
原理：跳过连接层的验证用户功能 ，不让mysql启动加载授权表
1.停掉mysql
root@hwz-VMware-Virtual-Platform:~# systemctl stop mysql2.使用mysqld_safe加关闭授权功能参数启动MySQL ，关闭tcp/ip连接，然后随便一个写个用户都可以登录上
root@hwz-VMware-Virtual-Platform:~# mysqld_safe --skip-grant-tables --skip-networking &
[1] 4150
root@hwz-VMware-Virtual-Platform:~# 2025-02-03T04:12:03.405924Z mysqld_safe Logging to '/usr/local/mysql/data/error.log'.
2025-02-03T04:12:03.459742Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/dataroot@hwz-VMware-Virtual-Platform:~# mysql -uxxxxx -pxxxxx
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 9
Server version: 8.0.28 MySQL Community Server - GPLCopyright (c) 2000, 2022, 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.(xxxxx@localhost) [(none)]>
3.加载授权表（启动时跳过加载授权表，但是修改密码需要授权表，所以启动后加载授权表）
(xxxxx@localhost) [(none)]> flush privileges;
Query OK, 0 rows affected (0.02 sec)3.修改root用户密码
(xxxxx@localhost) [(none)]> alter user root@'localhost' identified by '123456';
Query OK, 0 rows affected (0.01 sec)4.重启MySQL
先关闭mysql_safe方式启动
root@hwz-VMware-Virtual-Platform:~# mysqladmin -uroot -p123456 shutdownroot@hwz-VMware-Virtual-Platform:~# systemctl restart mysql1.2 ssl客户端加密连接
8.0版本默认开启ssl，have_openssl、have_ssl都为yes
(root@localhost) [mysql]> show variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| performance_schema_show_processlist | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
+-------------------------------------+-----------------+
25 rows in set (0.01 sec)默认已经生成SSL 证书和密钥在数据目录下
用户可以手动调用 mysql_ssl_rsa_setup 实用程序（从 MySQL 8.0.34 开始已弃用）,之后版本数据库启动时如果SSL 证书和密钥不存在则自动生成。
如果存在这些文件中的任何一个，mysql_ssl_rsa_setup 不会创建 SSL 文件。
ca.pem 自签名 CA 证书 ca-key.pem CA 私有密钥 server-cert.pem 服务器证书 server-key.pem 服务器私钥 client-cert.pem 客户端证书 client-key.pem 客户端私钥
RSA 文件
private_key.pem 私钥.pem 文件，私钥/公钥对的私有成员
public_key.pem 公共成员，属于私钥/公钥对
1.2.1 win客户端navicat ssl加密连接上数据库
1.关闭mysql所在服务器防火窗或者开放3306端口
2.添加一个外网段可以访问mysql的用户
这个ip是我Navicat所在机器ip
(root@localhost) [mysql]> create user root@'172.20.10.1' identified by '123456';
Query OK, 0 rows affected (0.01 sec)创建用户必须要ssl加密才能登录
(root@localhost) [mysql]> create user root@'172.20.10.1' identified by '123456' REQUIRE X509;
Query OK, 0 rows affected (0.01 sec)或者SET PERSIST require_secure_transport=ON;设置所以客户端连接都需要ssl
连接上了但只看到一个数据库说明没有权限
3。授予权限给这个用户
(root@localhost) [mysql]> grant all on *.* to root@'172.20.10.1';
Query OK, 0 rows affected (0.01 sec)重新连接就显示全部数据库了
4.使用ssl方式连接
拿出client-cert.pem 客户端证书 client-key.pem 客户端私钥到客户端机器那边。
验证当前连接是否使用了ssl加密连接，不为空则是使用了ssl加密连接
SHOW SESSION STATUS LIKE 'Ssl_cipher';
2.MySQL的配置参数
查看某个参数可用通配符
show variables like 'data%';变量可分为
- 按作用范围分（不标明默认是会话变量）
- session会话变量（只影响当前会话）
- global全局变量（影响所有会话）
(root@localhost) [(none)]> set session long_query_time=2-> ;
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 2.000000 |
+-----------------+----------+
1 row in set (0.01 sec)(root@localhost) [(none)]> set long_query_time=4;
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)(root@localhost) [(none)]> set global long_query_time=6;
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)
(root@localhost) [(none)]> set global long_query_time=6;
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 4.000000 |
+-----------------+----------+
1 row in set (0.00 sec)(root@localhost) [(none)]> set global long_query_time=8;
Query OK, 0 rows affected (0.00 sec)(root@localhost) [(none)]> show global variables like 'long_query_time';
+-----------------+----------+
| Variable_name | Value |
+-----------------+----------+
| long_query_time | 8.000000 |
+-----------------+----------+
1 row in set (0.01 sec)
需要注意的是一个会话set global后，有另外一个会话在set global前打开的这个会话，那直接show variables是不会读取到set global后的值的，show global variables可以读取到，你需要重新打开这个会话show variables才会读取到set global后的值。
查看其他会话的变量值
(root@localhost) [performance_schema]> show tables like '%variables%';
+--------------------------------------------+
| Tables_in_performance_schema (%variables%) |
+--------------------------------------------+
| global_variables |
| persisted_variables |
| session_variables |
| user_variables_by_thread |
| variables_by_thread |
| variables_info |
+--------------------------------------------+
6 rows in set (0.00 sec)(root@localhost) [performance_schema]> select * from variables_by_thread where VARIABLE_NAME='long_query_time';
+-----------+-----------------+----------------+
| THREAD_ID | VARIABLE_NAME | VARIABLE_VALUE |
+-----------+-----------------+----------------+
| 47 | long_query_time | 10.000000 |
| 48 | long_query_time | 8.000000 |
+-----------+-----------------+----------------+
2 rows in set (0.00 sec)这里可以看到一个是10是我当前会话的 long_query_time的值，另外一个8是另外一个会话设置的值
- 按类型分
- 可修改变量
- 只读变量（mysql运行中不能修改这种变量比如datadir变量）
MySQL ：： MySQL 8.0 参考手册 ：： 7.1.8 服务器系统变量
可以查看参数是否是可修改的变量，Dynamic为yes的变量可修改
3.用户权限管理
创建用户
create user 'hwz'@'192.168.1.%' identified by '123456';删除用户
drop user 'hwz'@'192.168.1.%';修改用户密码
(root@localhost) [mysql]> alter user 'hwz'@'localhost' identified by '1234';
Query OK, 0 rows affected (0.01 sec)查看创建的用户
(root@localhost) [mysql]> show create user 'hwz'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for hwz@localhost: CREATE USER `hwz`@`localhost` IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$\\F]4\\L~dQ>A\n\'MP~wsWQWtSMTdbwDCdYV0qIKuWUr.2DKpaWruXUVBb3CSlA' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK PASSWORD HISTORY DEFAULT PASSWORD REUSE INTERVAL DEFAULT PASSWORD REQUIRE CURRENT DEFAULT用户上锁
(root@localhost) [(none)]> alter user hwz@'localhost' account lock;
Query OK, 0 rows affected (0.01 sec)(root@localhost) [(none)]> select user,host,account_locked from mysql.user;
+------------------+-------------+----------------+
| user | host | account_locked |
+------------------+-------------+----------------+
| hwz | 192.168.1.% | N |
| hwz | localhost | Y |
| mysql.infoschema | localhost | Y |
| mysql.session | localhost | Y |
| mysql.sys | localhost | Y |
| root | localhost | N |
+------------------+-------------+----------------+
6 rows in set (0.00 sec)
用户解锁
(root@localhost) [(none)]> alter user hwz@'localhost' account unlock;
Query OK, 0 rows affected (0.00 sec)查看所有的用户
(root@localhost) [mysql]> select host,user from user;
+-------------+------------------+
| host | user |
+-------------+------------------+
| 192.168.1.% | hwz |
| localhost | hwz |
| localhost | mysql.infoschema |
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-------------+------------------+
6 rows in set (0.00 sec)
授予权限
语法：grant 权限 on 权限级别 to 用户;
权限级别有四种：
- *.* : 全库级别 ----->管理员 --->mysql.user
- test.*： 单库级别 ----->业务层面 ------>mysql.db
- test.t1： 单表级别 ------->mysql.tables_priv
- select(id,name) ： 列级别 --------->mysql.columns_priv
# 授予权限前不能看到mysql数据库
(hwz@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
+--------------------+# 使用root账号授权给hwz账号查看mysql数据库所有表的权限
(root@localhost) [mysql]> grant select on mysql.* to 'hwz'@'localhost';
Query OK, 0 rows affected (0.01 sec)# 授予权限后
(hwz@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
+--------------------+授予权限同时授予他的权限授予别人
(root@localhost) [mysql]> grant select,insert on mysql.* to 'hwz'@'localhost' with grant option;
Query OK, 0 rows affected (0.01 sec)(root@localhost) [mysql]> show grants for 'hwz'@'localhost';
+--------------------------------------------------------------------------+
| Grants for hwz@localhost |
+--------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hwz`@`localhost` |
| GRANT SELECT, INSERT ON `mysql`.* TO `hwz`@`localhost` WITH GRANT OPTION |
+--------------------------------------------------------------------------+
2 rows in set (0.00 sec)
查看当前用户权限
(hwz@localhost) [(none)]> show grants;
+-----------------------------------------+
| Grants for hwz@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `hwz`@`localhost` |
+-----------------------------------------+
查看别的用户权限
(root@localhost) [mysql]> show grants for 'hwz'@'localhost';
+-----------------------------------------+
| Grants for hwz@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `hwz`@`localhost` |
+-----------------------------------------+
通过权限表查看用户权限
# 这里授权hwz用户能只能查询test库的user表的name字段
(root@localhost) [test]> grant select(name) on test.user to hwz@'localhost';
Query OK, 0 rows affected (0.01 sec)# 可以通过查询mysql.columns_priv查询到该权限，第三个即是
(root@localhost) [mysql]> select * from mysql.columns_priv where user='hwz'\G
*************************** 1. row ***************************Host: localhostDb: mysqlUser: hwzTable_name: user
Column_name: hostTimestamp: 2025-01-28 22:35:17
Column_priv: Select
*************************** 2. row ***************************Host: localhostDb: mysqlUser: hwzTable_name: user
Column_name: userTimestamp: 2025-01-28 22:35:17
Column_priv: Select
*************************** 3. row ***************************Host: localhostDb: testUser: hwzTable_name: user
Column_name: nameTimestamp: 2025-02-03 15:51:27
Column_priv: Select
3 rows in set (0.00 sec)刚创建的用户只有USAGE权限，相当于没有权限，只能登录
MySQL ：： MySQL 8.0 参考手册 ：： MySQL 提供的 8.2.2 权限 查看所有详细的权限和说明
查看有哪些权限
(root@localhost) [(none)]> show privileges;收回权限
(root@localhost) [mysql]> revoke select on mysql.* from 'hwz'@'localhost';
Query OK, 0 rows affected (0.01 sec)(root@localhost) [mysql]> show grants for 'hwz'@'localhost';
+-----------------------------------------+
| Grants for hwz@localhost |
+-----------------------------------------+
| GRANT USAGE ON *.* TO `hwz`@`localhost` |
+-----------------------------------------+
3.1 角色创建和授权
8.0后社区版本才有角色授权
(root@localhost) [mysql]> create role dev_role@'localhost';
Query OK, 0 rows affected (0.02 sec)(root@localhost) [mysql]> grant select,update,insert on test.* to dev_role@'localhost';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [mysql]> create user dev_user@'localhost' identified by '123456';
Query OK, 0 rows affected (0.00 sec)(root@localhost) [mysql]> grant dev_role@'localhost' to dev_user@'localhost';
Query OK, 0 rows affected (0.01 sec)查看角色
(root@localhost) [mysql]> select * from mysql.role_edges;
+-----------+-----------+-----------+----------+-------------------+
| FROM_HOST | FROM_USER | TO_HOST | TO_USER | WITH_ADMIN_OPTION |
+-----------+-----------+-----------+----------+-------------------+
| localhost | dev_role | localhost | dev_user | N |
+-----------+-----------+-----------+----------+-------------------+
1 row in set (0.00 sec)角色也相当于一个上锁的用户（不能登陆）
(root@localhost) [mysql]> select user,host,authentication_string,account_locked from mysql.user\G
*************************** 2. row ***************************user: dev_rolehost: localhost
authentication_string: account_locked: Y
还可以这样查询用户权限
(root@localhost) [mysql]> select * from information_schema.user_privileges;3.2 激活角色
向角色授予权限后默认是没有激活角色的。
登录上被授予角色的用户查看当前会话激活的角色，NONE即是没有。
(dev_user2@localhost) [test]> SELECT CURRENT_ROLE();
+----------------+
| CURRENT_ROLE() |
+----------------+
| NONE |
+----------------+
1 row in set (0.00 sec)有三种方法激活角色
1. 设置默认激活的角色。当用户登录上时会自动激活默认角色。
设置默认将所有的授予的角色激活
(root@localhost) [mysql]> set default role all to dev_user2@'localhost';
Query OK, 0 rows affected (0.01 sec)我这里授予了两个角色，所以默认有两个，你也可以默认激活指定一个
(dev_user2@localhost) [(none)]> SELECT CURRENT_ROLE();
+---------------------------------+
| CURRENT_ROLE() |
+---------------------------------+
| `dev_role2`@`%`,`dev_role3`@`%` |
+---------------------------------+
1 row in set (0.00 sec)
2.当前会话手动激活指定角色。
(dev_user2@localhost) [(none)]> set role dev_role3;
Query OK, 0 rows affected (0.00 sec)(dev_user2@localhost) [(none)]> SELECT CURRENT_ROLE();
+-----------------+
| CURRENT_ROLE() |
+-----------------+
| `dev_role3`@`%` |
+-----------------+
1 row in set (0.00 sec)还有种方式激活所有排查指定角色
(dev_user2@localhost) [(none)]> set role all except dev_role3;
Query OK, 0 rows affected (0.01 sec)(dev_user2@localhost) [(none)]> SELECT CURRENT_ROLE();
+-----------------+
| CURRENT_ROLE() |
+-----------------+
| `dev_role2`@`%` |
+-----------------+
1 row in set (0.00 sec)3.开启系统参数activate-all-roles-on-login登录自动激活所有授予的角色。
(root@localhost) [mysql]> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | OFF |
+-----------------------------+-------+
1 row in set (0.01 sec)(root@localhost) [mysql]> SET GLOBAL activate_all_roles_on_login=ON;
Query OK, 0 rows affected (0.00 sec)(root@localhost) [mysql]> show variables like 'activate_all_roles_on_login';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| activate_all_roles_on_login | ON |
+-----------------------------+-------+
1 row in set (0.00 sec)
3.3 回收角色
(root@localhost) [mysql]> show grants for hwz@'localhost';
+----------------------------------------------------------------------+
| Grants for hwz@localhost |
+----------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `hwz`@`localhost` |
| GRANT INDEX ON `mysql`.* TO `hwz`@`localhost` |
| GRANT SELECT (`host`, `user`) ON `mysql`.`user` TO `hwz`@`localhost` |
| GRANT SELECT (`name`) ON `test`.`user` TO `hwz`@`localhost` |
| GRANT `dev_role2`@`%` TO `hwz`@`localhost` |
+----------------------------------------------------------------------+
5 rows in set (0.00 sec)(root@localhost) [mysql]> revoke dev_role2 from hwz@'localhost';
Query OK, 0 rows affected (0.00 sec)
4、
常用命令
1、linux命令
查看某参数的默认值或者官方解释
mysqld --help --verbose | grep defaults-file2、SQL命令
查找某个配置变量的值
(root@localhost) [(none)]> show variables like 'data%';
+---------------+------------------------+
| Variable_name | Value |
+---------------+------------------------+
| datadir | /usr/local/mysql/data/ |
+---------------+------------------------+
1 row in set (0.01 sec)查看告警
show warnings;查看表结构
desc table_name;查看当前连接服务端的状态
(root@localhost) [mysql]> status
--------------
mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)Connection id: 8
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.28 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 8 hours 44 min 31 secThreads: 3 Questions: 235 Slow queries: 0 Opens: 260 Flush tables: 3 Open tables: 179 Queries per second avg: 0.007
--------------(root@localhost) [mysql]> \s
--------------
mysql Ver 8.0.28 for Linux on x86_64 (MySQL Community Server - GPL)Connection id: 8
Current database: mysql
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 8.0.28 MySQL Community Server - GPL
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: utf8mb4
Db characterset: utf8mb4
Client characterset: utf8mb4
Conn. characterset: utf8mb4
UNIX socket: /tmp/mysql.sock
Binary data as: Hexadecimal
Uptime: 8 hours 44 min 38 secThreads: 3 Questions: 238 Slow queries: 0 Opens: 260 Flush tables: 3 Open tables: 179 Queries per second avg: 0.007
--------------
查看是否开启ssl连接，主要看have_openssl为yes即为开启（8.x版本默认开启）
(root@localhost) [mysql]> show variables like '%ssl%';
+-------------------------------------+-----------------+
| Variable_name | Value |
+-------------------------------------+-----------------+
| admin_ssl_ca | |
| admin_ssl_capath | |
| admin_ssl_cert | |
| admin_ssl_cipher | |
| admin_ssl_crl | |
| admin_ssl_crlpath | |
| admin_ssl_key | |
| have_openssl | YES |
| have_ssl | YES |
| mysqlx_ssl_ca | |
| mysqlx_ssl_capath | |
| mysqlx_ssl_cert | |
| mysqlx_ssl_cipher | |
| mysqlx_ssl_crl | |
| mysqlx_ssl_crlpath | |
| mysqlx_ssl_key | |
| performance_schema_show_processlist | OFF |
| ssl_ca | ca.pem |
| ssl_capath | |
| ssl_cert | server-cert.pem |
| ssl_cipher | |
| ssl_crl | |
| ssl_crlpath | |
| ssl_fips_mode | OFF |
| ssl_key | server-key.pem |
+-------------------------------------+-----------------+
查看前台连接线程
(root@localhost) [(none)]> show processList;
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
| 5 | event_scheduler | localhost | NULL | Daemon | 131 | Waiting on empty queue | NULL |
| 8 | root | localhost | NULL | Query | 0 | init | show processList |
+----+-----------------+-----------+------+---------+------+------------------------+------------------+
2 rows in set (0.00 sec)查看后台在执行的线程（像io线程，SQL线程。。。。）
(root@localhost) [(none)]> select * from performance_schema.threads\G
*************************** 1. row ***************************THREAD_ID: 1NAME: thread/sql/mainTYPE: BACKGROUNDPROCESSLIST_ID: NULLPROCESSLIST_USER: NULLPROCESSLIST_HOST: NULLPROCESSLIST_DB: mysql
PROCESSLIST_COMMAND: NULLPROCESSLIST_TIME: 565PROCESSLIST_STATE: NULLPROCESSLIST_INFO: NULLPARENT_THREAD_ID: NULLROLE: NULLINSTRUMENTED: YESHISTORY: YESCONNECTION_TYPE: NULLTHREAD_OS_ID: 4620RESOURCE_GROUP: SYS_default
*************************** 2. row ***************************THREAD_ID: 3NAME: thread/innodb/io_ibuf_threadTYPE: BACKGROUNDPROCESSLIST_ID: NULLPROCESSLIST_USER: NULLPROCESSLIST_HOST: NULLPROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: NULLPROCESSLIST_TIME: NULLPROCESSLIST_STATE: NULLPROCESSLIST_INFO: NULLPARENT_THREAD_ID: NULLROLE: NULLINSTRUMENTED: YESHISTORY: YESCONNECTION_TYPE: NULLTHREAD_OS_ID: 4625RESOURCE_GROUP: SYS_default
