Mysql 用户管理、权限管理， 数据备份与还原、临时表

目录

用户管理、权限管理

查询用户

创建用户

修改用户

删除用户

授予权限

查询权限

收回权限

mysqldump 备份数据

mysql 数据恢复与还原

MySQL 临时表

---

用户管理、权限管理

1、项目开发时，如果是 Oracle 数据库，则直接新建用户，然后授权，一个用户对应一个数据库，项目中使用此用户就是对应的数据库。而 Mysql 不同，Mysql 的一个 root 用户就可以操作全部的数据库，它的数据库名称直接是写在连接的 url 上面的。如下所示。

---
spring:
  profiles: mysqlDb
  datasource:
    # jdbc 连接基础配置
    username: root
    password: root
    #高版本 Mysql 驱动时，配置的 driver-class-name 不再是 com.mysql.jdbc.Driver，url 后面必须设置时区 serverTimezone
    url: jdbc:mysql://127.0.0.1:3306/test?characterEncoding=UTF-8&serverTimezone=UTC
    driver-class-name: com.mysql.cj.jdbc.Driver
---
#数据源配置
spring:
  profiles: oracleDb
  datasource:
    # jdbc 连接基础配置
    driverClassName: oracle.jdbc.driver.OracleDriver
    url: jdbc:oracle:thin:@127.0.0.1:1521:ORCL
    username: scott
    password: scott

2、Mysql 也可以进行用户管理，为不同的项目创建不同的数据库，创建不同的操作用户。

3、以root用户登录数据库，运行以下命令。本文环境：Mysql 8.0.21。

查询用户

-- 查询数据库用户列表
-- user 表示用户名，host 表示主机名，plugin 表示认证插件
-- 后续用户管理、授权管理需要填写主机名的位置，都是填写这里的 host 值.
SELECT User, Host, t.`plugin`,t.authentication_string,t.password_expired,
t.password_last_changed,t.account_locked,t.password_lifetime FROM mysql.user t;

创建用户

-- 创建用户命令格式：密码可以为空，此时登录时不再需要密码，如：create user jyy identified by '';
CREATE USER '用户名'@'主机名' IDENTIFIED BY '登录密码';

-- 创建用户，同时指定'认证插件'为 mysql_native_password 方式
-- MySQL 5.7及以上版本默认使用了 caching_sha2_password 作为认证插件
CREATE USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '登录密码';

-- 老版本的客户端库可能不支持 caching_sha2_password 认证方式，比如 Navicat Premium 11.0.10 就会连接报错：
-- Client does not support authentication protocol requested by server; consider upgrading MySQL client
-- 解决方法：
-- 1）升级客户端：确保客户端软件是最新版本，支持 caching_sha2_password 认证。
-- 2）修改服务器配置：如果不能升级客户端，可以将MySQL用户的认证插件改为 mysql_native_password。
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '登录密码';
-- 刷新权限
FLUSH PRIVILEGES;

1、主机名可选项有：localhost、%、IP地址，用于指定MySQL允许访问服务器的主机(host)。

localhost：只允许本地登录访问，无法远程连接访问服务器；

%：允许所有IP地址登录访问，可以远程连接访问服务器；不指定@'主机名' 时，默认是@'%'，所有远程客户端都可以访问。

IP地址：明确指定IP时，只允许指定的客户端IP进行访问；

C:\Users\A>mysql -h 192.168.1.27 -u a -p
Enter password: ******
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 32
Server version: 8.0.21 MySQL Community Server - GPL

Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

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.

2、创建的用户没有授权前，默认只能看到 information_schema 数据库。

修改用户

1、修改用户登录密码，8.X高版本格式：ALTER USER '用户名'@'主机名' IDENTIFIED BY '新登录密码';

-- 修改登录密码（8.X高版本）
ALTER USER 'a'@'192.168.1.27' IDENTIFIED BY '123456';

-- 修改登录密码（低版本）
update mysql.user set password = password('新密码') where user = '用户名' and host = '主机名';

2、修改数据库认证插件：

-- 老版本的客户端库可能不支持 caching_sha2_password 认证方式，比如 Navicat Premium 11.0.10 就会连接报错：
-- Client does not support authentication protocol requested by server; consider upgrading MySQL client
-- 解决方法：
-- 1）升级客户端：确保客户端软件是最新版本，支持 caching_sha2_password 认证。
-- 2）修改服务器配置：如果不能升级客户端，可以将MySQL用户的认证插件改为 mysql_native_password。
ALTER USER '用户名'@'主机名' IDENTIFIED WITH mysql_native_password BY '新登录密码';
-- 刷新权限
FLUSH PRIVILEGES;

 3、密码过期：MySQL5.6.6 版本起，允许设置 MySQL 数据库用户的密码过期时间，mysql.user 数据表的 password_expired 表示是否密码过期，默认为’N‘。密码过期后，虽然能够登陆，但是一切权限都为空了。密码过期后的解决方式是直接修改密码解决。

-- 强制设置密码过期
alter user '用户名'@'主机名' PASSWORD expire;

-- 创建用户的同时指定密码有效时间，如下所示，90天后密码将过期
CREATE USER '用户名'@'主机名' IDENTIFIED BY '密码' PASSWORD EXPIRE INTERVAL 90 DAY;

4、锁定用户：

create user '用户名'@'主机名' identified by '登录密码' account LOCK;-- 创建用户的同时锁定用户
alter user '用户名'@'主机名' account LOCK;-- 锁定用户
alter user '用户名'@'主机名' account UNLOCK;-- 解锁用户

删除用户

1、格式：drop user 用户名@'主机名';

授予权限

1、MySQL的权限包括：全局权限、数据库级别权限、表级别权限、列级别权限等。可以通过授权命令(grant)来给用户不同的权限。

2、格式：GRANT privileges ON 数据库名.表名 TO '用户名'@'主机名'

-- 为用户授予指定数据库的所有操作权限（*表示所有）
-- 数据库名也可以指定为*，此时表示整个服务器的所有数据库
GRANT ALL PRIVILEGES ON 数据库名.* TO '用户名'@'主机名';
- 为用户授予指定数据库的 插入 和 查询 权限
GRANT SELECT, INSERT ON 数据库名.* TO '用户名'@'主机名';
-- 刷新权限
FLUSH PRIVILEGES;

3、MySQL中常见的权限包括：

权限	意义
ALL [PRIVILEGES]	设置除GRANT OPTION之外的所有简单权限
ALTER	允许使用ALTER TABLE
ALTER ROUTINE	更改或取消已存储的子程序
CREATE	允许使用CREATE TABLE
CREATE ROUTINE	创建已存储的子程序
CREATE TEMPORARY TABLES	允许使用CREATE TEMPORARY TABLE
CREATE USER	允许使用CREATE USER, DROP USER, RENAME USER和REVOKE ALL PRIVILEGES。
CREATE VIEW	允许使用CREATE VIEW
DELETE	允许使用DELETE
DROP	允许使用DROP TABLE
EXECUTE	允许用户运行已存储的子程序
FILE	允许使用SELECT...INTO OUTFILE和LOAD DATA INFILE
INDEX	允许使用CREATE INDEX和DROP INDEX
INSERT	允许使用INSERT
LOCK TABLES	允许对您拥有SELECT权限的表使用LOCK TABLES
PROCESS	允许使用SHOW FULL PROCESSLIST
REFERENCES	未被实施
RELOAD	允许使用FLUSH
REPLICATION CLIENT	允许用户询问从属服务器或主服务器的地址
REPLICATION SLAVE	用于复制型从属服务器（从主服务器中读取二进制日志事件）
SELECT	允许使用SELECT
SHOW DATABASES	SHOW DATABASES显示所有数据库
SHOW VIEW	允许使用SHOW CREATE VIEW
SHUTDOWN	允许使用mysqladmin shutdown
SUPER	允许使用CHANGE MASTER, KILL, PURGE MASTER LOGS和SET GLOBAL语句，mysqladmin debug命令；允许您连接（一次），即使已达到max_connections。
UPDATE	允许使用UPDATE
USAGE	“无权限”的同义词
GRANT OPTION	允许授予权限

查询权限

1、查询授权语句格式：SHOW GRANTS FOR '用户名'@'主机名';

mysql> SHOW GRANTS FOR 'wangmaoxiong'@'%';
+--------------------------------------------------------+
| Grants for wangmaoxiong@%                              |
+--------------------------------------------------------+
| GRANT USAGE ON *.* TO `wangmaoxiong`@`%`               |
| GRANT ALL PRIVILEGES ON `test`.* TO `wangmaoxiong`@`%` |
+--------------------------------------------------------+
2 rows in set (0.00 sec)

-- mysql.db 表里可以查看用户在不同数据库下的操作权限
SELECT * from mysql.db ;

  2、查看所有用户的权限，该语句将为每个用户构建 SHOW GRANTS 语句，并将其作为查询结果返回，可以将查询结果拷贝到命令行中执行，以查看每个用户的具体权限。

SELECT DISTINCT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') AS query FROM mysql.user;

收回权限

1、格式：revoke privileges on 数据库名[.表名] from 用户名;

-- 收回用户'wangmaoxiong'对数据库'test'的 ALTER、create 权限
revoke alter,create on test.* from wangmaoxiong;

-- 收回用户'wangmaoxiong'的所有权限
revoke all privileges on *.* from wangmaoxiong;

mysqldump 备份数据

1、Mysql 安装的 bin 目录下有一个 mysqldump.exe 备份工具，使用它可以轻松备份数据，所以在命令行就可以执行命令。

说明	命令(结尾不能有分号)
备份指定库下指定的表	mysqldump -h主机 -p端口 -u账号 -p密码 数据库名 表名1 表名2 ...>本机存储路径.sql 示例： mysqldump -hlocalhost -p3306 -uroot -proot test dept emp >F:\Temp\sql\deptAndEmp.sql
备份指定库下所有的表	mysqldump -h主机 -p端口 -u账号 -p密码 数据库名 >本机存储路径.sql 示例： mysqldump -hlocalhost -p3306 -uroot -proot test >F:\Temp\sql\test.sql
备份指定的库	mysqldump -h主机 -p端口 -u账号 -p密码 -B 数据库1 数据库2 ... >本机存储路径.sql 示例： mysqldump -hlocalhost -p3306 -uroot -proot -B test wang >F:\Temp\sql\testAndWang.sql
备份所有的库	mysqldump -h主机 -p端口 -u账号 -p密码 -A >本机存储路径.sql 示例： mysqldump -hlocalhost -p3306 -uroot -proot -A >F:\Temp\sql\all.sql

2、-p 后面的密码没有直接指定时，命令执行之后会提示输入密码。

mysql 数据恢复与还原

方式1：登录 Mysql 后 source 命令还原

1、先登录mysql，然后使用 source 命令还原数据库：source sql文件路径

2、如果是还原表，则必须先存在指定的库。

说明	命令
恢复指定的库	mysql>source 备份文件存储路径.sql
恢复指定的表	mysql>use 数据库名 mysql>source 备份文件存储路径.sql

方式2：不用登录 mysql，命令行直接使用 Mysql 命令恢复

说明	命令(类似上面的mysqldump备份命令)
恢复指定的库	mysql -h主机 -p端口 -u账号 -p密码 < 数据库备份文件地址.sql 示例 mysql -hlocalhost -p3306 -uroot -proot < F:\Temp\sql\testAndWang.sql
恢复指定的表	mysql -h主机 -p端口 -u账号 -p密码 数据库名 < 数据表备份文件地址.sql

MySQL 临时表

临时表特点

• 建表语法：create temporary table ...；

• 一个临时表只能被创建它的session(会话)访问，对其他线程不可见，session结束的时候，会自动删除临时表(这一点和Oracle不同，Oracle不会删除)；

• 临时表可以与普通表同名；

• sessionA有同名临时表和普通表时，show create语句以及增删改查语句访问的都是临时表；

• show tables 命令不显示临时表；

临时表应用

• 不同 session 的临时表是可以重名的，如果有多个 session 同时执行 join 优化，不需要担心表名重复导致建表失败的问题。

• 不需要担心数据删除问题。如果使用普通表，在流程执行过程中客户端发生了异常断开，或者数据库发生异常重启，还需要专门来清理中间过程中生成的数据表。而临时表由于会自动回收，所以不需要这个额外的操作。

• 可以用于临时的数据清洗或者计算，比如导入的大量的数据时，在入正式表前先在临时表中进行校验与计算。

• 临时表经常会被用到复杂查询的优化过程中，比如说分库分表的跨库查询。

临时表和内存表是两个不同的概念

• 内存表指的是使用memory引擎的表，建表语法是create table ... engine=memory。这种表的数据都保存在内存中，系统重启的时候会被清空，但是表结构还在。

• 临时表可以使用各种引擎类型，如果是使用InnoDB引擎或者MyISAM引擎的临时表，写数据的时候是写到磁盘上。临时表也可以使用memory引擎。

CREATE temporary TABLE dept_tmp (
  deptno int NOT NULL AUTO_INCREMENT COMMENT '部门编号',
  dname varchar(14) DEFAULT NULL COMMENT '部门名称',
  loc varchar(13) DEFAULT NULL COMMENT '部门所在位置',
  PRIMARY KEY (deptno)
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4;

INSERT INTO dept_tmp VALUES ('1', 'accounting', 'new york');
INSERT INTO dept_tmp VALUES ('2', 'research', 'dallas');
INSERT INTO dept_tmp VALUES ('3', 'sales', 'chicago');