整理、记录常用的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.userG;
*_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';
参考资料
- Beginners Guide to MySQL User Management
- How to Manage MySQL Databases and Users from the Command Line
- MySQL Administratrion
- GRANT Statement
- Proxy Users - Server Support for Proxy User Mapping
- The Ultimate Guide To MySQL Roles By Examples
- MySQL 8.0: Listing Roles
原文地址：MySQL基础知识：启动管理和账号管理