MySQL基础知识：启动管理和账号管理

2022-09-21 18:28:35

整理、记录常用的MySQL基础知识；时间久了，很多就忘记了。

操作系统环境为MacOS Catalina, MySQL版本为： 8.0.13 MySQL Community Server - GPL.

安装、启动和连接

安装

安装完MySQL Server后，需要运行mysql_secure_installation确保安全，运行mysql_secure_installation会执行几个设置：

为root用户设置密码
是否删除匿名账号
是否取消root用户远程登录
是否删除test库和对test库的访问权限
是否刷新授权表使修改生效

启动管理

MacOS

查看MySQL服务状态

sudo /usr/local/mysql/support-files/mysql.server status

停止MySQL服务

sudo /usr/local/mysql/support-files/mysql.server stop

开始MySQL服务

sudo /usr/local/mysql/support-files/mysql.server start

重新启动MySQL

sudo /usr/local/mysql/support-files/mysql.server restart

Linux系统

可以使用下面三种命令之一的方式来管理MySQL服务。

systemctl start mysql

或者

service mysql start

或者

sudo /etc/init.d/mysql start

连接

本地连接

mysql -uroot -p

远程连接

注意:

如果在运行时mysql_secure_installation去掉了root的远程连接，则需要先创建新的MySQL账户(参考 管理MySQL账户 一节)。
当使用navtive password插件(默认)验证用户的时候，会检查 发起连接的机器 + 用户名 + 密码。

远程连接命令：

mysql -u[username] -h[server_host] -p

管理MySQL账户

MySQL的账户信息存储在mysql库中的user表里。

查看user表信息

describe mysql.user;

或者

desc mysql.user;

备注： MySQL 5.7及之后版本，password字段被移除，密码存储在authentication_string字段。

查看当前所有账户信息

SELECT * FROM mysql.user\G;

*_priv值为Y，表名该账户有对应的权限。

查看当前登录到MySQL Server的user列表

SELECT

    user,

    host,

    db,

    command

FROM

    information_schema.processlist;

或者，

SHOW PROCESSLIST;

账户(user)管理

创建新MySQL用户账号

MySQL账号的格式: ‘user_name’@’host_name‘

创建后的MySQL账号只能登陆MySQL服务，但没有任何访问数据的权限；需要使用GRANT赋予对应的权限。

CREATE USER 'zclmoon'@'localhost' IDENTIFIED BY 'abc123_';

创建账户名为：zclmoon
从 loalhost 连接MySQL server
密码为： abc123_

注意，安全考虑：

不要创建没有密码的账号
不要创建匿名账号
host name尽量不要使用通配符 %或_ （接收从任何地方来的连接）；

同时创建多个user:

CREATE USER user1@localhost, user2, user3@localhost, user4@localhost

IDENTIFIED BY 'Init Password';

更新已有账户密码

方法一，使用管理员权限修改zclmoon账号密码：

SET PASSWORD FOR zclmoon@localhost='abc123__';

如果是MySQL 5.7.5、MariaDB 10.1.20 版本或之前的版本，使用命令；新版本也可以使用此命令。

ALTER USER 'database_user'@'localhost' IDENTIFIED BY 'new_password';

方法二，zclmoon连接后，自己修改密码：

set password='123';

过期账号密码

alter user zclmoon@localhost password expire;

连接没有问题，当查询的时候会报需要重置密码错误。然后使用 SET PASSWORD = 'new_pwd';重置密码即可。

修改账户名

rename user richie_test@localhost to zclmoon@localhost;

删除账号

drop user zclmoon@localhost;

删除正在连接的账户

当用户zcl正在连接MySQL Server时，DBA把该user drop掉，此时zcl仍然在连接中，并仍然可以正常做操作；只有等该user下次重新登录的时候才生效。

如果，DBA想立马结束该user的连接和操作，可以在drop user之前，关闭该user的连接session。

通过SHOW PROCESSLIST 查看user的session id。然后使用 KILL session_id终结该session.

最后再drop user即可。

问题：KILL之后, Drop USER 之前，如果使用zcl账户的客户端有操作，则会自动重建一个session出来。

因为mysql cli客户端lost connection后会自动重连。
解决办法： kill之后快速 drop user即可；可以把两条语句写在一起执行。

示例：

 kill 31; drop user proxy_user@localhost;

账户权限(privilege)管理

MySQL Privilege Levels

Global: 使用 *.* 语法，整个MySQL Server的所有对象（库，表，存储过程和函数等等）；
Database：使用 database_name.* 语法，此数据库的所有对象；
Table：database_name.table_name，此表的所有行数据；
Column: 需要在每个privilege指定表的列，此表的特定列；
Stored Routine: 存储过程和函数；
Proxy：指定为已有账户的代理，然后会有该账户的所有权限。

显示账号被赋予的权限

SHOW GRANTS

FOR zclmoon@localhost;

或者

SHOW GRANTS; # 显示当前用户的权限

给账号某个db的所有权限

GRANT ALL

ON database_name.*

TO 'database_user'@'localhost';

给账号所有db的所有权限

GRANT ALL

ON *.*

TO 'database_user'@'localhost'

WITH GRANT OPTION;

特别注意：这里如果没有 WITH GRANT OPTION，会不工作，用户还是没有权限访问数据库对象；测试下来看，因为是root权限的原因。

好的实践：给用户指定特定的数据库或表的权限，而不是所有。

给账号特定的权限

GRANT SELECT, INSERT, DELETE

ON database_name.*

TO `database_user`@'localhost';

给账号执行存储过程的权限

GRANT EXECUTE

ON PROCEDURE YourProcedureName

TO zclmoon@localhost;

给账号表里特定列的权限

GRANT

   SELECT (employeeNumner,lastName, firstName,email),

   UPDATE(lastName)

ON employees

TO zclmoon@localhost;

Proxy账号

使用Proxy User前，需要先让MySQL Server支持此机制。

检查系统变量：check_proxy_users，这个默认是0，没启用，所以MySQL Server不会使用proxy user mapping。
如果 check_proxy_users已经启用(值为1)，则还需要启用两个插件：
- mysql_native_password 插件：启用 mysql_native_password_proxy_users.
- sha256_password 插件：启用 sha256_password_proxy_users.

修改my.cnf配置文件：

[mysqld]

check_proxy_users=ON

mysql_native_password_proxy_users=ON

sha256_password_proxy_users=ON

Grant Proxy:

GRANT PROXY

ON root@localhost

TO zclmoon@localhost;

查看当前登录是否用了proxy:

SELECT @@proxy_user;

遇到的问题： proxy user 一直没起作用:

解决方法:

创建user的时候，需要带上WITH mysql_native_password:

CREATE USER 'proxy_user'@'localhost'

  IDENTIFIED WITH mysql_native_password

  BY 'password';

也尝试了默认的方式和不加my.cnf后面两项配置，都不工作；具体原因还没搞清楚 ......

Revoke Proxy:

REVOKE PROXY

ON root@localhost

FROM zclmoon@localhost;

MySQL移除账号权限

REVOKE ALL, GRANT OPTION

FROM user1@localhost, user2@localhost;

注意： 不加 GRANT OPTION，会提示语法错误。

角色(role)管理

Role可以理解为一组privileges的集合，可以赋予多个具有相同privilege的用户users.

在大部分开源的RDBMS，role其实是一个不能登录的user的别名。

MySQL也是这么做的，Create Role后，可以在mysql.user表里看到多一条user记录(account_locked 和 password_expired 是 Y， authentication_string 是空)。

列出所有的role

SELECT DISTINCT User 'Role Name', if(from_user is NULL,0, 1) Active

FROM mysql.user

LEFT JOIN role_edges

ON from_user=user

WHERE account_locked='Y' AND password_expired='Y' AND authentication_string='';

上面 Active 表示role被assign到user过，assign 表是 role_edges。

如果想查询一个role assign给哪些user了，可以直接在role_edges表里查询。

创建Role

role和user账号一样，也有两部分组成: role_name@host_name (不指定host_name，则host_name默认为%)

CREATE ROLE test_role1, test_role2;

删除Role

DROP ROLE test_role1;

给Role赋权限

GRANT ALL

ON cms.*

TO test_role1, test_role2;

Revoke Role的权限

REVOKE INSERT, UPDATE, DELETE

ON cms.*

FROM test_role1;

给账号指定Role

GRANT test_role1

TO user1@localhost

检查user role的指定，并用using语句看role对应的privileges

SHOW GRANTS

FOR user1@localhost

USING test_role1;

查看当前role

SELECT current_role();

备注

GRANT语句可以赋予权限(privilege)和角色(role)；不可以在同一个grant语句里混用权限和角色的赋予；ON关键字可用来分辨grant是赋予权限还是赋予角色：

有 ON，则表示赋予权限
没有 ON，则表示赋予角色

示例:

GRANT ALL ON db1.* TO 'user1'@'localhost';

GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';

GRANT SELECT ON world.* TO 'role3';

参考资料

原文地址：MySQL基础知识：启动管理和账号管理

码农公寓