MySQL数据库用户管理
1. 用户登录登录MySQL服务器，这里是指通过命令行方式举行登录。基本格式如下：
# 常用格式：mysql -u 用户名 -p
# mysql -uroot -p
Enter password: #<== 交互密码
数据库安装初始化的时间，如果使用了--initialize-insecure，管理员密码为空。当地登录的时间可以直接使用mysql命令
# mysql
Welcome to the MySQL monitor.Commands end with ; or \g.
Your MySQL connection id is 1863
Server version: 8.0.36 MySQL Community Server - GPL
Copyright (c) 2000, 2024, 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>
[*]常用命令选项
-u用户
-p密码
-S socket # 多实例登录一般要显示指定。
-P端口 # 使用非特殊端口时指定。
-h主机 # 远程登录时指定。
-e 非交互操作mysql
# mysql登录示例：
# mysql -uroot -p -S /tmp/mysql.sock -P3306 -hlocalhost -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
2. 用户安全规范
[*]主机域范围要只管小，最好细化到单一IP，甚至是用localhost。
[*]仅用%模糊匹配授权应坚决克制。
[*]用户名应有实际意义，一看就明白是什么项目，比如：blog用户，就知道是博客项目。
[*]无用的用户应删除或者锁定。
[*]密码应该至少为数字、巨细写字母、特殊字符三种以上复杂度，非字典密码，且总数8位以上。
[*]应为一个项目设置一个对应用户，克制使用root用户作为业务项目标用户。
3. 创建数据库用户信息
mysql用户由“用户@主机域”，两部分共同构成，主机域是登录数据库的客户端主机IP范围，发起使用单引号引起来。
# 数据库用户格式：'用户名称信息'@'白名单信息'
-- 用户名称信息： 创建的用户字符串信息
-- 白名单信息： 网段IP地址信息或者名称信息
[*]用户举例：
uname@'localhost' #<==结尾localhost表示是授权本机用户在本机登录，适合单机登录。
uname@'10.0.0.2' #<==单一IP，即只能从10.0.0.2这台机器登录。
zhangsan@'10.0.0.%' #<==模糊匹配，一个范围内IP，例如10.0.0.1-10.0.0.254，适合集群模式。
lisi@'10.0.0.1%' #<==模糊匹配，一个范围内的IP，例如10.0.0.10-10.0.0.19。
zhangsan@'10.0.0.0/255.255.0.0'#<==一个子网内的所有IP，一般不用。
lisi@'%' #<==模糊匹配，任意用户都可以登录IP，此方式极不可取。
输入mysql直接登录到数据库中，或者mysql -uroot -p登录，实际的用户均为：root@localhost;
[*] 常见的登录方式：
[*] 第一种方式：以当地登录方式举行登录数据库系统；
[*] 第二种方式：以长途登录方式举行登录数据库系统；
[*] 进入到数据库环境中，执行以下SQL语句举行创建：
# 创建blog用户，允许从10.0.0.0/24整个网段的主机访问数据库。
MySQL> create user blog@'10.0.0.%' identified by 'blog123';
# 8.0版本推荐用下面命令创建用户，兼容老客户端
mysql> create user blog@'10.0.0.%' identified with mysql_native_password by 'blog123';
阐明：
[*]使用create创建数据库服务用户信息，创建的用户默认是没有数据库的管理权限的；
[*]在举行数据库服务对应管理用户创始时，发起一个数据库对应一个管理人员，以免多个管理员操作管理雷同数据库产生冲突。
在举行用户信息创建时，具有多种用户主机域的设置方法：
[*]方法一：只能当地登录数据库系统，主机域设置方法：
root@localhost
# localhost：表示只能通过本地主机登录数据库系统
[*]可以长途登录数据库系统，主机域设置方法：
root@10.0.0.101
# 10.0.0.101：表示可以在10.0.0.101主机上远程登录管理数据库系统
在生产架构中，有大量的集群服务器，大量的应用服务器都会毗连后端的数据库服务。
[*]方法一：授权单个主机举行访问：
root@10.0.0.101
[*]方法二：授权一个网段举行访问：
# 方式一：书写方法（生产常用）
root@'10.0.0.0/24'
root@'10.0.0.0/255.255.255.0'
-- 授权一个网段主机均可远程登录数据库服务，表示这个网段中10.0.0.1~10.0.0.254均可访问
-- 在数据库中识别默认掩码就是/24，其他掩码信息需要具体写明，需要注意特殊VLSM子网掩码需要长格式指明，不能简写
# 方式二：书写方法（生产常用）
root@'10.0.0.%'
-- 授权一个网段主机均可远程登录数据库服务，表示这个网段中10.0.0.1~10.0.0.254均可访问
# 方式三：书写方法
root@'172.16.%' # 等同于10.0.0.0/16
-- 授权一个更大网段主机均可远程登录数据库服务，表示这个网段中10.0.0.1~10.0.255.254均可访问
# 方式四：书写方法
root@'%'
-- 授权任意主机都可以远程登录数据库服务
# 方式五：书写方法
root@'10.0.0.5%'
-- 授权多个具体的主机可以远程登录数据库服务，表示多个主机10.0.0.50~10.0.0.59均可以访问
4. 查询数据库用户信息
管理库mysql，MySQL的用户主要在mysql库的用户表user里。
[*]检察当前登录用户
mysql> select user();
+----------------+
| user() |
+----------------+
| root@localhost |
+----------------+
[*]检察用户表的表结构
mysql> desc mysql.user;
[*]查询用户（主机域和用户名）
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost | #====>当前登录用户
+------------------+-----------+
[*]查询更多列（用户列，主机列，密码列，插件列），实际可以根据检察到的表结构，自行选择需要检察的列
mysql> select user,host,authentication_string,plugin,account_locked from mysql.user;
+------------------+-----------+--------------------------------------+-----------------------+----------------+
| user | host | authentication_string | plugin | account_locked |
+------------------+-----------+--------------------------------------+-----------------------+----------------+
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALI... | caching_sha2_password | Y |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALI... | caching_sha2_password | Y |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALI... | caching_sha2_password | Y |
| root | localhost | | caching_sha2_password | N |
+------------------+-----------+--------------------------------------+-----------------------+----------------+
4 rows in set (0.00 sec)
5. 修改数据库用户信息
这个功能主要是用来修改用户密码的，也有修改客户端插件和域的，进入到数据库环境中，执行以下SQL语句举行修改；
mysql> alter user user01@'10.0.0.%' identified by '123';
mysql> alter user user02@'10.0.0.%' identified with mysql_native_password by '123';
mysql> flush privileges; # 使改密生效
mysql> select user,host,authentication_string from mysql.user;
+------------------+-----------+------------------------------------------------------------------------+
| user | host | authentication_string |
+------------------+-----------+------------------------------------------------------------------------+
| user01 | 10.0.0.%| $A$005$UP?0rS)iJc|Oy}DC2o.8NoZjx.Euxayybd2qzmFjdQnmpXqc4P9i3cI5n3 |
| user02 | 10.0.0.%| *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515 |
| mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.session | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| mysql.sys | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
| root | localhost | |
+------------------+-----------+------------------------------------------------------------------------+
6 rows in set (0.00 sec)
6. 锁定数据库用户信息
在数据库服务举行用户管理时，可以将特定数据库管理用户举行锁定，从而实现克制某些特定用户再次登录数据库服务举行管理，是8.0新增的功能。
[*]锁定数据库用户操作：
# 进行数据库用户锁定操作
mysql> alter user user01@'10.0.0.%' account lock;
Query OK, 0 rows affected (0.02 sec)
# 查看数据库用户锁定状态
mysql> select user,host,account_locked from mysql.user;
+------------------+-----------+----------------------+
| user | host | account_locked |
+------------------+-----------+----------------------+
| user01 | 10.0.0.%| Y |
|...... |...... |...... |
| root | localhost | N |
+------------------+-----------+----------------------+
5 rows in set (0.00 sec)
# mysql -uuser01 -h10.0.0.% -p
Enter password:
ERROR 3118 (HY000): Access denied for user user01@'10.0.0.%'. Account is locked.
-- 再次登录数据库服务，显示指定用户已经被锁定
[*]解锁数据库用户操作：
# 进行数据库用户解锁操作
mysql> alter user user01@'10.0.0.%' account unlock;
Query OK, 0 rows affected (0.01 sec)
# 查看数据库用户锁定状态
mysql> select user,host,account_locked from mysql.user;
+------------------+-----------+---------------------+
| user | host | account_locked |
+------------------+-----------+---------------------+
| user01 | 10.0.0.%| N |
|...... |...... |...... |
| root | localhost | N |
+------------------+-----------+---------------------+
5 rows in set (0.00 sec)
# mysql -uuser01 -h10.0.0.% -p
Enter password:
Welcome to the MySQL monitor.
-- 再次登录数据库服务，显示指定用户已经被解锁可以登录
7. 删除数据库用户信息
进入到数据库环境中，执行以下SQL语句举行删除：（在生产环境慎用，不用的用户信息可以先锁定）
# 语法：drop user 'user'@'主机域'
mysql> drop user user01@'10.0.0.%';
# 扩展：先锁定用户信息
MySQL> ALTER USER 'demo'@'localhost' ACCOUNT LOCK
8. 5.7与8.0版本用户管理区别
8.1 创建用户授权区别
[*]5.7及之前版本，只需要grant命令即可完成【用户创建操作+用户密码设置+用户授权操作】
mysql> grant all on *.* to user01@'10.0.0.%' identified by 'user123';
[*]8.0及之后版本，全部系统表都是InnoDB管理了，需要先建立用户再举行授权，并且grant命令不再支持indentified by语句
mysql> create user user01@'10.0.0.%' identified by 'user123';
Query OK, 0 rows affected (0.00 sec)
mysql> grant all on *.* to user01@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
8.2 用户密码插件区别
用户密码加密插件使用的早期版本时，可以包管客户端步伐正常登陆，包管主从架构数据正常，包管集群通讯不受到影响；
# 查看早期数据库服务版本的密码加密插件信息
mysql> show variables like '%auth%';
+-------------------------------+-------------------------------------------+
| Variable_name | Value |
+-------------------------------+-------------------------------------------+
| default_authentication_plugin | mysql_native_password |
| secure_auth | ON |
+-------------------------------+-------------------------------------------+
2 rows in set (0.00 sec)
# 早期数据库服务的密码加密插件为：mysql_native_password
8.0之后用户密码加密插件使用的后期版本，将会导致客户端步伐无法登录，导致主从架构数据异常，影响集群正常通讯过程；
# 查看后期数据库服务版本的密码加密插件信息
mysql> show variables like '%auth%';
+-------------------------------+-----------------------+
| Variable_name | Value |
+-------------------------------+-----------------------+
| authentication_policy | *,, |
| default_authentication_plugin | caching_sha2_password |
+-------------------------------+-----------------------+
2 rows in set (0.00 sec)
# 后期数据库服务的密码加密插件为：caching_sha2_password
为相识决数据库服务升级后，用户密码加密插件影响毗连建立题目，可以接纳以下两种方案：
[*]方案一：更换原有默认密码加密插件，更换为历史版本使用的加密插件（mysql_native_password） 运维职责
[*]方案二：更换客户端毗连数据库服务端的驱动步伐软件，使之兼容新版本加密插件功能 开发职责
在新版数据库中更换原有密码加密插件详细操作方法：
[*]方法一：创建用户过程修改加密插件信息
mysql> create user user02@'10.0.0.%' identified with mysql_native_password by '123';
Query OK, 0 rows affected (0.00 sec)
mysql> select user,host,authentication_string,plugin from mysql.user;
+---------------+-----------+--------------------------------------------------+--------------------------+
| user | host | authentication_string | plugin |
+---------------+-----------+--------------------------------------------------+--------------------------+
| user02 | 10.0.0.%| *23AE809DDACAF96AF0FD78ED04B6A265E05AA257 | mysql_native_password |
| ...... | ...... | ...... | ...... |
+---------------+-----------+--------------------------------------------------+--------------------------+
6 rows in set (0.00 sec)
[*]方法二：修改已经创建用户加密插件信息
mysql> alter user 'user01'@'10.0.0.%' identified with mysql_native_password by '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> select user,host,authentication_string,plugin from mysql.user;
+-----------+-----------------+------------------------------------------------+--------------------------+
| user | host | authentication_string | plugin |
+-----------+-----------------+------------------------------------------------+--------------------------+
| user01 | 10.0.0.% | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9 | mysql_native_password |
+-----------+-----------------+------------------------------------------------+--------------------------+
[*]方法三：修改数据库服务设置文件信息（全局修改）
# 修改数据库服务配置文件信息
# vim /etc/my.cnf
default_authentication_plugin=mysql_native_password
-- 修改数据库服务配置文件，并在mysqld中区域进行修改
# 修改数据库服务配置后重启服务程序
# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL. SUCCESS!
# 登录数据库服务查看加密插件默认配置：
mysql> show variables like '%auth%';
+----------------------------------------+------------------------------+
| Variable_name | Value |
+----------------------------------------+------------------------------+
| default_authentication_plugin | mysql_native_password |
+----------------------------------------+------------------------------+
1 row in set (0.01 sec)
9. 用户密码管理
9.1 设置root密码
对于初始化没有设置root密码的环境下，对root用户密码举行设置：
# mysqladmin password '123'
# 测试登录
# mysql -uroot -p123
9.2 修改root密码
[*]命令行
# 123为原密码，新密码为123456。
# mysqladmin -uroot -p123 password '123456'
# 登录测试
# mysql -uroot -p123456
[*]sql语句
mysql> alter user root@'localhost' identified by '123'; # 适合5.7/8.0。是5.7新增功能
mysql> flush privileges; #<==刷新权限使得修改密码生效。
#交互登录
mysql -uroot -p
# 对于5.7之前的数据库版本，可以通过直接修改mysql.user表用户记录修改密码方法
mysql> update mysql.user set authentication_string=PASSWORD('123') where user='root' and host='localhost';
mysql> flush privileges;
# 或者使用set password语句修改密码
mysql> set password for 'root'@'localhost'=PASSWORD('123');
mysql> flush privileges;
# 注意：这两种方法适用于5.7之前版本，8.0取消PASSWORD函数
9.3 重置root密码
解决处理方法：将数据库重启并进入数据库安全模式（不加载验证授权表模式）
[*]第一步：停止数据库服务，并查抄数据库服务是否停止：
# systemctl stop mysqld #如果不成功则执行/etc/init.d/mysqld stop
# systemctl status mysqld
[*]第二步：重新启动数据库服务，并设置启动方式为忽略授权表启动方式：
# mysqld_safe--skip-grant-tables --skip-networking &
-- skip-grant-tables 表示忽略授权表启动
-- skip-networking 忽略网络通讯方式启动
# 进行测试服务是否启动
# ps -ef|grep mysql
[*]第三步：采用免密码方式登录数据库系统中，并重置root用户密码信息：
# mysql
mysql> flush privileges; #<==此命令必须先执行
# 表示先将数据库服务授权表加载到内存中，也可以将内存中修改的授权信息存入到磁盘中
mysql> alter user root@'localhost' identified by '123';
# 修改数据库服务用户密码信息
[*]第四步：重新启动数据库服务步伐，登录验证
# 杀死已经运行的数据库服务进程信息
# pkill mysqld
# ps -ef|grep mysql
# 重新启动数据库服务
# systemctl start mysqld
# netstat -lntup|grep 3306
# 重新登录进行测试
# mysql -uroot -p123
还有另外一种方法就是编辑/etc/my.cnf，加如下参数，直接重启数据库。
skip-networking
skip-grant-tables
# 直接正常重启数据库。
systemctl restart mysqld # 如果不成功则执行/etc/init.d/mysqld restar
后续操作就一样了。
10. 增补：数据库服务毗连受理
当数据服务中用户创建立置完毕后，就可以实现利用指定用户身份毗连登录管理数据服务。
10.1 命令行客户端工具毗连
这种客户端工具一样平常是安装数据库自带的一个客户端工具，会用来做一些测试和运维工作。
# 本机连接数据库服务：
# mysql -uroot -p123
# 以上连接方式等价于，以下长格式命令登录方式
# mysql -uroot -p123 -hlocalhost -P3306 -S /tmp/mysql.sock
# 进行本地连接时，-hlocalhost -P3306 -S /tmp/mysql.sock可以省略
# 远程连接数据库服务：
# mysql -uroot -p123 -h10.0.0.101 -P3306 -S /tmp/mysql.sock
10.2 数据库服务毗连方式阐明
[*]方式一：利用socket建立长途毗连（unix 套接字文件毗连）
利用此方法建立数据库服务的毗连，只能用于建立当地数据库服务的毗连；
# 套接字文件信息定义：
# cat /etc/my.cnf
socket=/tmp/mysql.sock
# 指定数据库服务加载的套接字文件路径信息
socket=/tmp/mysql.sock
# 指定数据库服务加载的套接字文件路径信息
# 利用套接字文件建立数据库服务连接
# mysql -uroot -p123 -S /tmp/mysql.sock
# -S指定socket的信息可以省略，因为在数据库服务配置文件的客户端已经配置过了
阐明：需要在数据库服务中授权localhost用户，才能实现采用socket方式登录操控数据库服务
[*]方式二：利用TCP/IP建立长途毗连（网络地址与端口）
利用此方法建立数据库服务的毗连，可以用于建立长途数据库服务的毗连；
# 利用TCP/IP建立数据库服务连接
# mysql -uroot -p123 -h 10.0.0.101 -P3306 -S /tmp/mysql.sock
阐明：需要在数据库服务中授权网络白名单用户，才能实现采用TCP/IP方式登录操控数据库服务
[*]方法三：软件毗连登录数据库服务
支持利用Navicat数据库客户端长途软件，实现长途毗连受理数据库服务，都是一些图形化的设置方式；
阐明：
[*]在毗连数据库服务时，选择好指定的数据库服务范例，例如：mysql mariadb oracle等。
[*]有加密项的客户端，保举使用加密项毗连，例如：使用sqlyog的SSH加密；phpmyadmin的https加密
[*]如果毗连设置项正确的条件下仍然毗连失败，排查一下插件的原因。
[*]方法四：步伐代码操作毗连登录数据库服务
序号开发语言毗连数据库驱动步伐01python毗连数据库驱动-pymysql02golang毗连数据库驱动-gomysql03java毗连数据库驱动-jar04php毗连数据库驱动-phpmysql 不同语言步伐代码毗连数据库服务驱动：https://dev.mysql.com/downloads/
https://i-blog.csdnimg.cn/direct/85a3a67eb535451bb0e72758f0039bc4.png
数据库服务毗连受理方式逻辑图：
https://i-blog.csdnimg.cn/direct/f827810c8d0d49a483fd1e0702a735e7.png
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]