MySQL数据库用户管理

不屈的铝合金

于 2024-07-23 20:00:24 发布

阅读量890

点赞数 20

分类专栏：边学边记-MySQL 文章标签：数据库 mysql 运维

本文链接：https://blog.csdn.net/weixin_44415987/article/details/140566619

版权

边学边记-MySQL 专栏收录该内容

4 篇文章 0 订阅

订阅专栏

1. 用户登录

登录MySQL服务器，这里是指通过命令行方式进行登录。基本格式如下：

# 常用格式：mysql -u 用户名 -p
[root@localhost ~]# mysql -uroot -p
Enter password:         #<== 交互密码

数据库安装初始化的时候，如果使用了--initialize-insecure，管理员密码为空。本地登录的时候可以直接使用mysql命令

[root@localhost ~]# 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登录示例：
[root@localhost ~]# 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)

[root@localhost ~]# 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)

[root@localhost ~]# 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    |
+-----------+-----------------+------------------------------------------------+--------------------------+

方法三：修改数据库服务配置文件信息（全局修改）

# 修改数据库服务配置文件信息
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
default_authentication_plugin=mysql_native_password
-- 修改数据库服务配置文件，并在mysqld中区域进行修改

# 修改数据库服务配置后重启服务程序
[root@localhost ~]# 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用户密码进行设置：

[root@localhost ~]# mysqladmin password '123'
# 测试登录
[root@localhost ~]# mysql -uroot -p123

9.2 修改root密码

命令行

# 123为原密码，新密码为123456。
[root@localhost ~]# mysqladmin -uroot -p123 password '123456'
# 登录测试
[root@localhost ~]# 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密码

解决处理方法：将数据库重启并进入数据库安全模式（不加载验证授权表模式）

第一步：停止数据库服务，并检查数据库服务是否停止：

[root@localhost ~]# systemctl stop mysqld        #如果不成功则执行/etc/init.d/mysqld stop
[root@localhost ~]# systemctl status mysqld

第二步：重新启动数据库服务，并设置启动方式为忽略授权表启动方式：

[root@localhost ~]# mysqld_safe  --skip-grant-tables --skip-networking &
-- skip-grant-tables 表示忽略授权表启动   
-- skip-networking 忽略网络通讯方式启动

# 进行测试服务是否启动
[root@localhost ~]# ps -ef|grep mysql

第三步：采用免密码方式登录数据库系统中，并重置root用户密码信息：

[root@localhost ~]# mysql 
mysql> flush privileges;       #<==此命令必须先执行
# 表示先将数据库服务授权表加载到内存中，也可以将内存中修改的授权信息存入到磁盘中
mysql> alter user root@'localhost' identified by '123';
# 修改数据库服务用户密码信息

第四步：重新启动数据库服务程序，登录验证

# 杀死已经运行的数据库服务进程信息
[root@localhost ~]# pkill mysqld
[root@localhost ~]# ps -ef|grep mysql

# 重新启动数据库服务
[root@localhost ~]# systemctl start mysqld
[root@localhost ~]# netstat -lntup|grep 3306

# 重新登录进行测试
[root@localhost ~]# mysql -uroot -p123

还有另外一种方法就是编辑/etc/my.cnf，加如下参数，直接重启数据库。
[mysqld]
skip-networking
skip-grant-tables

# 直接正常重启数据库。
systemctl restart mysqld   # 如果不成功则执行/etc/init.d/mysqld restar
后续操作就一样了。

10. 补充：数据库服务连接管理

当数据服务中用户创建设置完毕后，就可以实现利用指定用户身份连接登录管理数据服务。

10.1 命令行客户端工具连接

这种客户端工具一般是安装数据库自带的一个客户端工具，会用来做一些测试和运维工作。

# 本机连接数据库服务：
[root@localhost ~]# mysql -uroot -p123 
# 以上连接方式等价于，以下长格式命令登录方式
[root@localhost ~]# mysql -uroot -p123 -hlocalhost -P3306 -S /tmp/mysql.sock
# 进行本地连接时，-hlocalhost -P3306 -S /tmp/mysql.sock可以省略

# 远程连接数据库服务：
[root@localhost ~]# mysql -uroot -p123 -h10.0.0.101 -P3306 -S /tmp/mysql.sock

10.2 数据库服务连接方式说明

方式一：利用socket建立远程连接（unix 套接字文件连接）

利用此方法建立数据库服务的连接，只能用于建立本地数据库服务的连接；

# 套接字文件信息定义：
[root@localhost ~]# cat /etc/my.cnf 
[mysql]
socket=/tmp/mysql.sock
# 指定数据库服务加载的套接字文件路径信息
[mysqld]
socket=/tmp/mysql.sock
# 指定数据库服务加载的套接字文件路径信息

# 利用套接字文件建立数据库服务连接
[root@localhost ~]# mysql -uroot -p123 -S /tmp/mysql.sock
# -S指定socket的信息可以省略，因为在数据库服务配置文件的客户端已经配置过了

说明：需要在数据库服务中授权localhost用户，才能实现采用socket方式登录操控数据库服务

方式二：利用TCP/IP建立远程连接（网络地址与端口）

利用此方法建立数据库服务的连接，可以用于建立远程数据库服务的连接；

# 利用TCP/IP建立数据库服务连接
[root@localhost ~]# mysql -uroot -p123 -h 10.0.0.101 -P3306 -S /tmp/mysql.sock

说明：需要在数据库服务中授权网络白名单用户，才能实现采用TCP/IP方式登录操控数据库服务

方法三：软件连接登录数据库服务

支持利用Navicat数据库客户端远程软件，实现远程连接管理数据库服务，都是一些图形化的配置方式；

说明：

在连接数据库服务时，选择好指定的数据库服务类型，例如：mysql mariadb oracle等。
有加密项的客户端，推荐使用加密项连接，例如：使用sqlyog的SSH加密；phpmyadmin的https加密
如果连接配置项正确的前提下仍然连接失败，排查一下插件的原因。

方法四：程序代码操作连接登录数据库服务

序号	开发语言	连接数据库驱动程序
01	python	连接数据库驱动-pymysql
02	golang	连接数据库驱动-gomysql
03	java	连接数据库驱动-jar
04	php	连接数据库驱动-phpmysql

不同语言程序代码连接数据库服务驱动：https://dev.mysql.com/downloads/

在这里插入图片描述

数据库服务连接管理方式逻辑图：

在这里插入图片描述

不屈的铝合金

关注

20
点赞
踩
9

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库用户管理

登录MySQL服务器，这里是指通过命令行方式进行登录。数据库安装初始化的时候，如果使用了，管理员密码为空。本地登录的时候可以直接使用mysql命令。
复制链接

扫一扫

专栏目录