整理、记录常用的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

远程连接

注意:

1. 如果在运行时mysql_secure_installation去掉了root的远程连接，则需要先创建新的MySQL账户(参考 管理MySQL账户 一节)。
2. 当使用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;

账户(user)管理

创建新MySQL用户账号

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

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

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

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

注意，安全考虑：

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

更新已有账户密码

方法一，使用管理员权限修改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;

账户权限(privilege)管理

MySQL Privilege Levels

1. Global: 使用 *.* 语法，整个MySQL Server的所有对象（库，表，存储过程和函数等等）；
2. Database： 使用 database_name.* 语法，此数据库的所有对象；
3. Table：database_name.table_name，此表的所有行数据；
4. Column: 需要在每个privilege指定表的列，此表的特定列；
5. Stored Routine: 存储过程和函数；
6. 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支持此机制。

1. 检查系统变量：check_proxy_users，这个默认是0，没启用，所以MySQL Server不会使用proxy user mapping。
2. 如果 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是赋予权限还是赋予角色：

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

示例:

GRANT ALL ON db1.* TO 'user1'@'localhost';
GRANT 'role1', 'role2' TO 'user1'@'localhost', 'user2'@'localhost';
GRANT SELECT ON world.* TO 'role3';

参考资料

1. Beginners Guide to MySQL User Management
2. How to Manage MySQL Databases and Users from the Command Line
3. MySQL Administratrion
4. GRANT Statement
5. Proxy Users - Server Support for Proxy User Mapping
6. The Ultimate Guide To MySQL Roles By Examples
7. MySQL 8.0: Listing Roles