字符集的相关

1
show variables like 'character%';
  • character_set_server：服务器级别的字符集
  • character_set_database：当前数据库的字符集
  • character_set_client：服务器解码请求时使用的字符集
  • character_set_connection：服务器处理请求时会把请求字符串从 character_set_client 转为 character_set_connection
  • character_set_results：服务器向客户端返回数据时使用的字符集

默认编码

  • 如果创建或修改列时没有显式的指定字符集和比较规则，则该列默认用表的字符集和比较规则
  • 如果创建表时没有显式的指定字符集和比较规则，则该表默认用数据库的字符集和比较规则
  • 如果创建数据库时没有显式的指定字符集和比较规则，则该数据库默认用服务器的字符集和比较规则

字符编码处理过程

368.jpg

大小写

Linux

1、数据库名、表名、表的别名、变量名是严格区分大小写的；
2、关键字、函数名称在 SQL 中不区分大小写；
3、列名（或字段名）与列的别名（或字段别名）在所有的情况下均是忽略大小写的；

编写规范

  1. 关键字和函数名称全部大写；
  2. 数据库名、表名、表别名、字段名、字段别名等全部小写；
  3. SQL 语句必须以分号结尾。

数据库文件

MySQL数据库文件的存放路径：/var/lib/mysql/，可以用下面的命令查看：

1
SHOW VARIABLES LIKE 'datadir';

里面存放着所有数据库的定义和数据，下面的举例都是用 MySQL 默认的引擎 InnoDB。

MySQL 5.7

每一个数据库在 /var/lib/mysql/ 下面都有一个自己的文件夹，里面包含：

  • 数据库的属性文件：db.opt 包含字符集，比较规则。
  • 数据库表定义：tableName.frm Frame
  • 数据库表数据：tableName.ibd InnoDB Data

MySQL 8.0

不在提供 db.opt 和 tableName.frm，而是集成在 tableName.idb 中。

系统数据库

MySQL 自带了四个默认的系统数据库：

mysql infromation_schema performance_schema sys

mysql

MySQL 系统自带的核心数据库，它存储了 MySQL 的用户账户和权限信息，一些存储过程、事件的定
义信息，一些运行过程中产生的日志信息，一些帮助信息以及时区信息等。

比如 mysql.user 表里面就存着数据库的用户信息，如果修改了这张表，那么就也会该表数据库的用户设置。

information_schema

库保存着MySQL服务器 维护的所有其他数据库的信息 ，比如有哪些表、哪些视图、哪些触发器、哪些列、哪些索引。这些信息并不是真实的用户数据，而是一些描述性信息，有时候也称之为 元数据。在系统数据库 information_schema 中提供了一些以 innodb_sys 开头的表，用于表示内部系统表。

可以从这张表里面看到所有的数据库的架构。

performance_schema

这个数据库里主要保存 MySQL 服务器运行过程中的一些状态信息，可以用来 监控 MySQL 服务的各类性能指标 。包括统计最近执行了哪些语句，在执行过程的每个阶段都
花费了多长时间，内存的使用情况等信息。

sys

MySQL 系统自带的数据库，这个数据库主要是通过 视图 的形式把 information_schema 和 performance_schema 结合起来，帮助系统管理员和开发人员监控 MySQL 的技术性能。目标是把performance_schema 的把复杂度降低，让使用者能更好的阅读这个库里的内容。更快的了解 DB 的运行情况。

账号管理

账号规则

  1. 帐号名称的格式为: ​​‘user_name’@’host_name’​​ 表示用户名和用户登陆来源。
  2. 如果只指定了 user_name, 则等价于 ​​‘user_name’@’％’​​​; 例如, ​​’me’​​​ 等价于 ​​‘me’@’%’​​。
  3. 如果用户名和主机名是合法的标识符, 则可以省略引号。 否则必须用引号引起来。

  • 用户名：如果用户名是非空白值(nonblank value), 客户端连接时使用的用户名必须在字符上与这个值完全一致,如果用户名是一个空白值(空字符串), 则可以和任意用户名匹配。 帐号中用户名为空, 也就是匿名用户。 要在SQL中指定匿名用户, 请使用引号把空串作为用户名引起来, 例如 ​​‘’@’localhost’​​。

  • 主机名： 可以是域名(host name) 或者是 IP 地址(支持IPv4/IPv6)。 ​​’localhost’​​ 表示本机。 ​​’127.0.0.1’​​ 表示本机的IPv4回环地址。 ​​’::1’​​ 则表示本机的IPv6回环地址。%​​ 和 ​​_​​ 通配符可以模糊匹配主机名和IP地址。 匹配规则和SQL标准中的 ​ ​​LIKE​​​ 语句一致。 例如, 百分号 ​​’%’​​ 与所有主机名匹配, 而 ​​’%.mysql.com’​​ 则与 ​​mysql.com​​ 域中的所有主机匹配。 而 ​​’198.51.100.%’​​ 则与 198.51.100 网段下的所有C类地址匹配。

账号存储在 mysql.user 表下面，表的主键就是 User 和 Host 组成的复合主键，一起标识唯一的账号。登陆的时候就在这个表里面去匹配 User 和 Host 来确定账号密码和赋予的权限。

创建用户

1
CREATE USER 用户名 [IDENTIFIED BY '密码'][,用户名 [IDENTIFIED BY '密码']];

注意：

  • “[ ]”表示可选，也就是说，可以指定用户登录时需要密码验证，也可以不指定密码验证，这样用户可以直接登录。不过，不指定密码的方式不安全，不推荐使用。如果指定密码值，这里需要使用 IDENTIFIED BY 指定明文密码值。

比如：

1
CREATE USER 'test_user'@'localhost' IDENTIFIED BY '123456';

修改用户名和密码

修改用户名（mysql.user 权限拥有者去做）：

1
2
UPDATE mysql.user SET USER='xorex002' WHERE USER='xorex001'; -- 直接修改 mysql.user 即可
FLUSH PRIVILEGES; -- 修改完表数据之后，要告诉数据库系统，让它更新数据

修改密码：

1
2
3
4
5
ALTER USER USER() IDENTIFIED BY 'new_password';
--- USER() 直接获取当前连接用户名 然后修改

ALTER USER 'USER'@'HOST' IDENTIFIED BY '新密码';
-- 修改别人的密码

删除用户

1
DROP USER user[,user]…;

比如：

1
DROP USER 'xorex001'@'%';

权限管理

要注意权限更迭之后，需要重新登陆之后才可以哦。

1
SHOW PRIVILEGES; -- 查看有什么权限
  1. CREATE 和 DROP 权限 ，可以创建新的数据库和表，或删除（移掉）已有的数据库和表。如果将 MySQL 数据库中的 DROP 权限授予某用户，用户就可以删除 MySQL 访问权限保存的数据库。
  2. SELECT、INSERT、UPDATE 和 DELETE 权限 允许在一个数据库现有的表上实施操作。
  3. SELECT 权限只有在它们真正从一个表中检索行时才被用到。
  4. INDEX 权限 允许创建或删除索引，INDEX 适用于已有的表。如果具有某个表的 CREATE 权限，就可以在 CREATE TABLE 语句中包括索引定义。
  5. ALTER 权限 可以使用 ALTER TABLE 来更改表的结构和重新命名表。
  6. CREATE ROUTINE 权限 用来创建保存的程序（函数和程序），ALTER ROUTINE 权限用来更改和删除保存的程序， EXECUTE 权限 用来执行保存的程序。
  7. GRANT 权限 允许授权给其他用户，可用于数据库、表和保存的程序。
  8. FILE 权限 使用户可以使用 LOAD DATA INFILE 和 SELECT … INTO OUTFILE 语句读或写服务器上的文件，任何被授予 FILE 权限的用户都能读或写 MySQL 服务器上的任何文件（说明用户可以读任何数据库目录下的文件，因为服务器可以访问这些文件）。

查看权限

1
2
3
4
5
SHOW GRANTS;

SHOW GRANTS FOR CURRENT_USER();

SHOW GRANTS FOR 'user'@'主机地址' ;

授予权限

授权的时候如果发现没有该用户，则会直接新建一个用户。

1
2
3
4
5
6
7
GRANT 权限1,权限2,…权限n ON 数据库名称.表名称 TO 用户名@用户地址;

-- 给 xorex 用户在 test 数据库下所有表的四个权限
GRANT SELECT,INSERT,DELETE,UPDATE ON test.* TO xorex@localhost ; 

-- 给 xorex 授权所有数据库的所有权限（除了 GRANT 权限，需要单独授予一遍）
GRANT ALL PRIVILEGES ON *.* TO xorex@'%';

回收权限

1
2
3
4
5
6
REVOKE 权限1,权限2,…权限n ON 数据库名称.表名称 FROM 用户名@用户地址;

-- 收回全库全表的所有权限
REVOKE ALL PRIVILEGES ON *.* FROM joe@'%';
-- 收回mysql库下的所有表的插删改查权限
REVOKE SELECT,INSERT,UPDATE,DELETE ON mysql.* FROM joe@localhost;

角色管理

这个类似于 Linux 中的 Group，用于归纳相同的权限

创建角色

1
2
3
4
CREATE ROLE 'role_name'[@'host_name'] [,'role_name'[@'host_name']]...

-- 比如
CREATE ROLE 'manager'@'localhost';

删除角色

1
DROP ROLE '角色名';

赋予权限

1
2
3
4
GRANT 权限1,权限2 ON 数据库.表 TO 'role_name'[@'host_name'];

--- 比如
GRANT SELECT ON demo.settlement TO 'manager';

查看权限

1
SHOW GRANTS FOR '角色名';

回收权限

1
2
3
4
REVOKE 权限1,权限2 ON 数据库.表 FROM '角色名';

-- 比如
REVOKE INSERT, UPDATE, DELETE ON school.* FROM 'school_write';

用户和角色

给用户赋予角色

角色创建并授权后，要赋给用户并处于 激活状态 才能发挥作用。

1
2
3
4
GRANT 角色名 TO 用户名;

-- 比如
GRANT 'school_read' TO 'xorex'@'localhost';

然后激活角色：

1
SET DEFAULT ROLE ALL TO 'xorex'@'localhost';

撤销用户角色

1
REVOKE 角色名 FROM 用户名;

设置强制默认角色

比如给所有账号 role1 角色，所有只能在 localhost 登陆的账号 role2 角色，所有只能在 server.xorex.space 下登陆的账号 role3 角色。

启动前：

1
[mysqld]mandatory_roles='role1,role2@localhost,role3@%.server.xorex.space'

启动后：

1
2
3
4
5
-- 系统重启后仍然有效
SET PERSIST mandatory_roles = 'role1,role2@localhost,role3@%.server.xorex.space';

-- 系统重启后失效
SET GLOBAL mandatory_roles = 'role1,role2@localhost,role3@%.server.xorex.space';