MySQL数据库用户管理

1. 用户登录

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

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

复制代码

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

1. [root@localhost ~]# mysql
2. Welcome to the MySQL monitor.  Commands end with ; or \g.
3. Your MySQL connection id is 1863
4. Server version: 8.0.36 MySQL Community Server - GPL
6. Copyright (c) 2000, 2024, Oracle and/or its affiliates.
8. Oracle is a registered trademark of Oracle Corporation and/or its
9. affiliates. Other names may be trademarks of their respective
10. owners.
12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14. mysql>

复制代码

• 常用命令选项
  

1. -u用户
2. -p密码
3. -S socket           # 多实例登录一般要显示指定。
4. -P端口               # 使用非特殊端口时指定。
5. -h主机               # 远程登录时指定。
6. -e 非交互操作mysql
7.        
8. # mysql登录示例：
9. [root@localhost ~]# mysql -uroot -p -S /tmp/mysql.sock -P3306 -hlocalhost -e "show databases;"
10. +--------------------+
11. | Database           |
12. +--------------------+
13. | information_schema |
14. | mysql              |
15. | performance_schema |
16. | sys                |
17. +--------------------+

复制代码

2. 用户安全规范

   

• 主机域范围要只管小，最好细化到单一IP，甚至是用localhost。
  
• 仅用%模糊匹配授权应坚决克制。
  
• 用户名应有实际意义，一看就明白是什么项目，比如：blog用户，就知道是博客项目。
  
• 无用的用户应删除或者锁定。
  
• 密码应该至少为数字、巨细写字母、特殊字符三种以上复杂度，非字典密码，且总数8位以上。
  
• 应为一个项目设置一个对应用户，克制使用root用户作为业务项目标用户。
  

  3. 创建数据库用户信息

mysql用户由“用户@主机域”，两部分共同构成，主机域是登录数据库的客户端主机IP范围，发起使用单引号引起来。

1. # 数据库用户格式：'用户名称信息'@'白名单信息'
3. -- 用户名称信息： 创建的用户字符串信息
4. -- 白名单信息：   网段IP地址信息或者名称信息

复制代码

• 用户举例：
  

1. uname@'localhost'                #<==结尾localhost表示是授权本机用户在本机登录，适合单机登录。
2. uname@'10.0.0.2'                 #<==单一IP，即只能从10.0.0.2这台机器登录。
3. zhangsan@'10.0.0.%'              #<==模糊匹配，一个范围内IP，例如10.0.0.1-10.0.0.254，适合集群模式。
4. lisi@'10.0.0.1%'                 #<==模糊匹配，一个范围内的IP，例如10.0.0.10-10.0.0.19。
5. zhangsan@'10.0.0.0/255.255.0.0'  #<==一个子网内的所有IP，一般不用。
6. lisi@'%'                         #<==模糊匹配，任意用户都可以登录IP，此方式极不可取。

复制代码

  输入mysql直接登录到数据库中，或者mysql -uroot -p登录，实际的用户均为：root@localhost;
  

• 常见的登录方式：
  
  
  
  • 第一种方式：以当地登录方式举行登录数据库系统；
    
  • 第二种方式：以长途登录方式举行登录数据库系统；
    
  
  
• 进入到数据库环境中，执行以下SQL语句举行创建：
  

1. # 创建blog用户，允许从10.0.0.0/24整个网段的主机访问数据库。
2. MySQL> create user blog@'10.0.0.%' identified by 'blog123';
4. # 8.0版本推荐用下面命令创建用户，兼容老客户端
5. mysql> create user blog@'10.0.0.%' identified with mysql_native_password by 'blog123';

复制代码

  阐明：
  

• 使用create创建数据库服务用户信息，创建的用户默认是没有数据库的管理权限的；
  
• 在举行数据库服务对应管理用户创始时，发起一个数据库对应一个管理人员，以免多个管理员操作管理雷同数据库产生冲突。
  

  在举行用户信息创建时，具有多种用户主机域的设置方法：

• 方法一：只能当地登录数据库系统，主机域设置方法：
  

1. root@localhost
2. # localhost：表示只能通过本地主机登录数据库系统

复制代码

• 可以长途登录数据库系统，主机域设置方法：
  

1. root@10.0.0.101
2. # 10.0.0.101：表示可以在10.0.0.101主机上远程登录管理数据库系统

复制代码

  在生产架构中，有大量的集群服务器，大量的应用服务器都会毗连后端的数据库服务。
  

• 方法一：授权单个主机举行访问：
  

1. root@10.0.0.101      

复制代码

• 方法二：授权一个网段举行访问：
  

1. # 方式一：书写方法（生产常用）
2. root@'10.0.0.0/24'
3. root@'10.0.0.0/255.255.255.0'  
4. -- 授权一个网段主机均可远程登录数据库服务，表示这个网段中10.0.0.1~10.0.0.254均可访问
5. -- 在数据库中识别默认掩码就是/24，其他掩码信息需要具体写明，需要注意特殊VLSM子网掩码需要长格式指明，不能简写
7. # 方式二：书写方法（生产常用）
8. root@'10.0.0.%'
9. -- 授权一个网段主机均可远程登录数据库服务，表示这个网段中10.0.0.1~10.0.0.254均可访问
11. # 方式三：书写方法
12. root@'172.16.%'              # 等同于10.0.0.0/16
13. -- 授权一个更大网段主机均可远程登录数据库服务，表示这个网段中10.0.0.1~10.0.255.254均可访问
15. # 方式四：书写方法
16. root@'%'     
17. -- 授权任意主机都可以远程登录数据库服务
19. # 方式五：书写方法
20. root@'10.0.0.5%'
21. -- 授权多个具体的主机可以远程登录数据库服务，表示多个主机10.0.0.50~10.0.0.59均可以访问

复制代码

4. 查询数据库用户信息

管理库mysql，MySQL的用户主要在mysql库的用户表user里。

• 检察当前登录用户
  

1. mysql> select user();
2. +----------------+
3. | user()         |
4. +----------------+
5. | root@localhost |
6. +----------------+

复制代码

• 检察用户表的表结构
  

1. mysql> desc mysql.user;

复制代码

• 查询用户（主机域和用户名）
  

1. mysql> select user,host from mysql.user;
2. +------------------+-----------+
3. |             user |      host |
4. +------------------+-----------+
5. | mysql.infoschema | localhost |
6. | mysql.session    | localhost |
7. | mysql.sys        | localhost |
8. | root             | localhost | #====>当前登录用户
9. +------------------+-----------+

复制代码

• 查询更多列（用户列，主机列，密码列，插件列），实际可以根据检察到的表结构，自行选择需要检察的列
  

1. mysql> select user,host,authentication_string,plugin,account_locked from mysql.user;
2. +------------------+-----------+--------------------------------------+-----------------------+----------------+
3. | user             | host      | authentication_string                | plugin                | account_locked |
4. +------------------+-----------+--------------------------------------+-----------------------+----------------+
5. | mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALI... | caching_sha2_password | Y              |
6. | mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALI... | caching_sha2_password | Y              |
7. | mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALI... | caching_sha2_password | Y              |
8. | root             | localhost |                                      | caching_sha2_password | N              |
9. +------------------+-----------+--------------------------------------+-----------------------+----------------+
10. 4 rows in set (0.00 sec)

复制代码

5. 修改数据库用户信息

这个功能主要是用来修改用户密码的，也有修改客户端插件和域的，进入到数据库环境中，执行以下SQL语句举行修改；

1. mysql> alter user user01@'10.0.0.%' identified by '123';
2. mysql> alter user user02@'10.0.0.%' identified with mysql_native_password by '123';
3. mysql> flush privileges;               # 使改密生效
4. mysql> select user,host,authentication_string from mysql.user;
5. +------------------+-----------+------------------------------------------------------------------------+
6. | user             | host      | authentication_string                                                  |
7. +------------------+-----------+------------------------------------------------------------------------+
8. | user01           | 10.0.0.%  | $A$005$UP?0rS)iJc|Oy}DC2o.8NoZjx.Euxayybd2qzmFjdQnmpXqc4P9i3cI5n3      |
9. | user02           | 10.0.0.%  | *FE28814B4A8B3309DAC6ED7D3237ADED6DA1E515                              |
10. | mysql.infoschema | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
11. | mysql.session    | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
12. | mysql.sys        | localhost | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED |
13. | root             | localhost |                                                                        |
14. +------------------+-----------+------------------------------------------------------------------------+
15. 6 rows in set (0.00 sec)

复制代码

6. 锁定数据库用户信息

在数据库服务举行用户管理时，可以将特定数据库管理用户举行锁定，从而实现克制某些特定用户再次登录数据库服务举行管理，是8.0新增的功能。

• 锁定数据库用户操作：
  

1. # 进行数据库用户锁定操作
2. mysql> alter user user01@'10.0.0.%' account lock;
3. Query OK, 0 rows affected (0.02 sec)
5. # 查看数据库用户锁定状态
6. mysql> select user,host,account_locked from mysql.user;
7. +------------------+-----------+----------------------+
8. | user             | host      |       account_locked |
9. +------------------+-----------+----------------------+
10. | user01           | 10.0.0.%  | Y                    |
11. |......            |......     |......                |
12. | root             | localhost | N                    |
13. +------------------+-----------+----------------------+
14. 5 rows in set (0.00 sec)
16. [root@localhost ~]# mysql -uuser01 -h10.0.0.% -p
17. Enter password:
18. ERROR 3118 (HY000): Access denied for user user01@'10.0.0.%'. Account is locked.
19. -- 再次登录数据库服务，显示指定用户已经被锁定

复制代码

• 解锁数据库用户操作：
  

1. # 进行数据库用户解锁操作
2. mysql> alter user user01@'10.0.0.%' account unlock;
3. Query OK, 0 rows affected (0.01 sec)
5. # 查看数据库用户锁定状态
6. mysql> select user,host,account_locked from mysql.user;
7. +------------------+-----------+---------------------+
8. | user             | host      | account_locked      |
9. +------------------+-----------+---------------------+
10. | user01           | 10.0.0.%  | N                   |
11. |......            |......     |......               |
12. | root             | localhost | N                   |
13. +------------------+-----------+---------------------+
14. 5 rows in set (0.00 sec)
16. [root@localhost ~]# mysql -uuser01 -h10.0.0.% -p
17. Enter password:
18. Welcome to the MySQL monitor.
19. -- 再次登录数据库服务，显示指定用户已经被解锁可以登录

复制代码

7. 删除数据库用户信息

进入到数据库环境中，执行以下SQL语句举行删除：（在生产环境慎用，不用的用户信息可以先锁定）

1. # 语法：drop user 'user'@'主机域'
3. mysql> drop user user01@'10.0.0.%';
5. # 扩展：先锁定用户信息
6. MySQL> ALTER USER 'demo'@'localhost' ACCOUNT LOCK

复制代码

8. 5.7与8.0版本用户管理区别

8.1 创建用户授权区别

• 5.7及之前版本，只需要grant命令即可完成【用户创建操作+用户密码设置+用户授权操作】
  

1. mysql> grant all on *.* to user01@'10.0.0.%' identified by 'user123';

复制代码

• 8.0及之后版本，全部系统表都是InnoDB管理了，需要先建立用户再举行授权，并且grant命令不再支持indentified by语句
  

1. mysql> create user user01@'10.0.0.%' identified by 'user123';
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> grant all on *.* to user01@'10.0.0.%';
5. Query OK, 0 rows affected (0.00 sec)

复制代码

8.2 用户密码插件区别

用户密码加密插件使用的早期版本时，可以包管客户端步伐正常登陆，包管主从架构数据正常，包管集群通讯不受到影响；

1. # 查看早期数据库服务版本的密码加密插件信息
2. mysql> show variables like '%auth%';
3. +-------------------------------+-------------------------------------------+
4. | Variable_name                 | Value                                     |
5. +-------------------------------+-------------------------------------------+
6. | default_authentication_plugin | mysql_native_password                     |
7. | secure_auth                   | ON                                        |
8. +-------------------------------+-------------------------------------------+
9. 2 rows in set (0.00 sec)
10. # 早期数据库服务的密码加密插件为：mysql_native_password

复制代码

8.0之后用户密码加密插件使用的后期版本，将会导致客户端步伐无法登录，导致主从架构数据异常，影响集群正常通讯过程；

1. # 查看后期数据库服务版本的密码加密插件信息
2. mysql> show variables like '%auth%';
3. +-------------------------------+-----------------------+
4. | Variable_name                 | Value                 |
5. +-------------------------------+-----------------------+
6. | authentication_policy         | *,,                   |
7. | default_authentication_plugin | caching_sha2_password |
8. +-------------------------------+-----------------------+
9. 2 rows in set (0.00 sec)
10. # 后期数据库服务的密码加密插件为：caching_sha2_password

复制代码

  为相识决数据库服务升级后，用户密码加密插件影响毗连建立题目，可以接纳以下两种方案：
  

• 方案一：更换原有默认密码加密插件，更换为历史版本使用的加密插件（mysql_native_password） 运维职责
  
• 方案二：更换客户端毗连数据库服务端的驱动步伐软件，使之兼容新版本加密插件功能 开发职责
  

  在新版数据库中更换原有密码加密插件详细操作方法：

• 方法一：创建用户过程修改加密插件信息
  

1. mysql> create user user02@'10.0.0.%' identified with mysql_native_password by '123';
2. Query OK, 0 rows affected (0.00 sec)
4. mysql> select user,host,authentication_string,plugin from mysql.user;
5. +---------------+-----------+--------------------------------------------------+--------------------------+
6. | user          | host      | authentication_string                            | plugin                   |
7. +---------------+-----------+--------------------------------------------------+--------------------------+
8. | user02        | 10.0.0.%  | *23AE809DDACAF96AF0FD78ED04B6A265E05AA257        | mysql_native_password    |
9. | ......        | ......    | ......                                           | ......                   |
10. +---------------+-----------+--------------------------------------------------+--------------------------+
11. 6 rows in set (0.00 sec)

复制代码

• 方法二：修改已经创建用户加密插件信息
  

1. mysql> alter user 'user01'@'10.0.0.%' identified with mysql_native_password by '123456';
2. Query OK, 0 rows affected (0.01 sec)
4. mysql> select user,host,authentication_string,plugin from mysql.user;
5. +-----------+-----------------+------------------------------------------------+--------------------------+
6. | user      | host            | authentication_string                          | plugin                   |
7. +-----------+-----------------+------------------------------------------------+--------------------------+
8. | user01    | 10.0.0.%        | *6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9      | mysql_native_password    |
9. +-----------+-----------------+------------------------------------------------+--------------------------+

复制代码

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

1. # 修改数据库服务配置文件信息
2. [root@localhost ~]# vim /etc/my.cnf
3. [mysqld]
4. default_authentication_plugin=mysql_native_password
5. -- 修改数据库服务配置文件，并在mysqld中区域进行修改
7. # 修改数据库服务配置后重启服务程序
8. [root@localhost ~]# service mysqld restart
9. Shutting down MySQL. SUCCESS!
10. Starting MySQL. SUCCESS!
12. # 登录数据库服务查看加密插件默认配置：
13. mysql> show variables like '%auth%';
14. +----------------------------------------+------------------------------+
15. | Variable_name                          | Value                        |
16. +----------------------------------------+------------------------------+
17. | default_authentication_plugin          | mysql_native_password        |
18. +----------------------------------------+------------------------------+
19. 1 row in set (0.01 sec)

复制代码

9. 用户密码管理

9.1 设置root密码

对于初始化没有设置root密码的环境下，对root用户密码举行设置：

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

复制代码

9.2 修改root密码

• 命令行
  

1. # 123为原密码，新密码为123456。
2. [root@localhost ~]# mysqladmin -uroot -p123 password '123456'
3. # 登录测试
4. [root@localhost ~]# mysql -uroot -p123456

复制代码

• sql语句
  

1. mysql> alter user root@'localhost' identified by '123';       # 适合5.7/8.0。是5.7新增功能
2. mysql> flush privileges; #<==刷新权限使得修改密码生效。
3. #交互登录
4. mysql -uroot -p
6. # 对于5.7之前的数据库版本，可以通过直接修改mysql.user表用户记录修改密码方法
7. mysql> update mysql.user set authentication_string=PASSWORD('123') where user='root' and host='localhost';
8. mysql> flush privileges;
9. # 或者使用set password语句修改密码
10. mysql> set password for 'root'@'localhost'=PASSWORD('123');
11. mysql> flush privileges;
13. # 注意：这两种方法适用于5.7之前版本，8.0取消PASSWORD函数

复制代码

9.3 重置root密码

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

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

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

复制代码

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

1. [root@localhost ~]# mysqld_safe  --skip-grant-tables --skip-networking &
2. -- skip-grant-tables 表示忽略授权表启动   
3. -- skip-networking 忽略网络通讯方式启动
5. # 进行测试服务是否启动
6. [root@localhost ~]# ps -ef|grep mysql

复制代码

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

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

复制代码

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

1. # 杀死已经运行的数据库服务进程信息
2. [root@localhost ~]# pkill mysqld
3. [root@localhost ~]# ps -ef|grep mysql
5. # 重新启动数据库服务
6. [root@localhost ~]# systemctl start mysqld
7. [root@localhost ~]# netstat -lntup|grep 3306
9. # 重新登录进行测试
10. [root@localhost ~]# mysql -uroot -p123

复制代码

  还有另外一种方法就是编辑/etc/my.cnf，加如下参数，直接重启数据库。

1. [mysqld]
2. skip-networking
3. skip-grant-tables
5. # 直接正常重启数据库。
6. systemctl restart mysqld   # 如果不成功则执行/etc/init.d/mysqld restar

复制代码

后续操作就一样了。
  10. 增补：数据库服务毗连受理

当数据服务中用户创建立置完毕后，就可以实现利用指定用户身份毗连登录管理数据服务。
10.1 命令行客户端工具毗连

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

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

复制代码

10.2 数据库服务毗连方式阐明

• 方式一：利用socket建立长途毗连（unix 套接字文件毗连）
  

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

1. # 套接字文件信息定义：
2. [root@localhost ~]# cat /etc/my.cnf
3. [mysql]
4. socket=/tmp/mysql.sock
5. # 指定数据库服务加载的套接字文件路径信息
6. [mysqld]
7. socket=/tmp/mysql.sock
8. # 指定数据库服务加载的套接字文件路径信息
10. # 利用套接字文件建立数据库服务连接
11. [root@localhost ~]# mysql -uroot -p123 -S /tmp/mysql.sock
12. # -S指定socket的信息可以省略，因为在数据库服务配置文件的客户端已经配置过了

复制代码

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

• 方式二：利用TCP/IP建立长途毗连（网络地址与端口）
  

利用此方法建立数据库服务的毗连，可以用于建立长途数据库服务的毗连；

1. # 利用TCP/IP建立数据库服务连接
2. [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加密
  
• 如果毗连设置项正确的条件下仍然毗连失败，排查一下插件的原因。
  

  

• 方法四：步伐代码操作毗连登录数据库服务
  

序号开发语言毗连数据库驱动步伐01python毗连数据库驱动-pymysql02golang毗连数据库驱动-gomysql03java毗连数据库驱动-jar04php毗连数据库驱动-phpmysql 不同语言步伐代码毗连数据库服务驱动：https://dev.mysql.com/downloads/

数据库服务毗连受理方式逻辑图：

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。