【mysql】の用户 | 权限 | 存储引擎 | 变量 | 索引~

站长

2024年07月30日 15:00 · 阅读数 25

1、用户管理

mysql用户账号 由两部分组成：

 格式：
 'USERNAME'@'HOST'
 用户名       主机名
 
 示例：
 root@localhost    //如果只写root,默认就是本地localhost
 root@192.168.125.100    //针对单个Ip
 zhou@'192.168.125.%'    //针对一个网段。%是通配符，代表任意，范围是0-255，后面不能用0，只能用%

【mysql】の用户 | 权限 | 存储引擎 | 变量 | 索引~

小tips：mysql 端口号是3306

1.1 user表（用户信息表）

mysql的用户存放在mysql数据库中的user表中

基础命令：

 use mysql;       //切换到mysql数据库
 show tables;     //查看所有表
 desc user;       //查看user表结构
 select user();    //查看当前登录的用户

 MariaDB [mysql]> select user,host from mysql.user;
 +------+-----------+
 | user | host      |
 +------+-----------+
 | root | 127.0.0.1 |
 | root | 7-2       |
 | root | ::1       |
 | root | localhost |
 +------+-----------+

1.2 新建用户

 create user '用户名'@'主机名' [IDENTIFIED BY [PASSWORD] '密码'];

格式拆解：

'用户名'：指定将创建的用户名

'主机名'：指定新创建的用户可在哪些主机上登录，可使用IP地址、网段、主机名的形式，本地用户可用localhost，允许任意主机登录可用通配符%
'密码'：若省略“IDENTIFIED BY”部分，则用户的密码将为空（不建议使用），可以使用下面两种
- 明文密码：直接输入'密码'，插入到数据库时由Mysql自动加密；
- 加密密码：需要先使用SELECT PASSWORD('密码'); 获取密文，再在语句中添加 PASSWORD '密文';

 select password ('abc123');
 +-------------------------------------------+
 | password ('abc123')                       |
 +-------------------------------------------+
 | *6691484EA6B50DDDE1926A220DA01FA9E575C18A |
 +-------------------------------------------+

案例：

建议使用mysql软件，mariadb软件部分效果与下面例子中会有出入。

修改密码策略

 set global validate_password_policy=0;
 set global validate_password_length=1;

mysql安全性要求规定，要尽量设置复杂的密码，如果想要设置简单密码，需要先做密码策略。这一步视情况而定。

创建wxy用户

 create user wxy@'192.168.125.%' identified by 'abc123';    //建立远程登录用户，主机名可以是一个网段。密码需要符合mysql的安全性要求

创建完用户后，可以查看下用户信息，密码为加密密码

 select user,host,authentication_string from mysql.user;    //查看用户信息

【mysql】の用户 | 权限 | 存储引擎 | 变量 | 索引~

远程登录前，需要进行授权，否则看不到数据库

 grant all on *.* to wxy@'192.168.125.%';

远程用户，进行登录。

 mysql -uwxy -pabc123 -h192.168.125.130;   //-h远程主机

注意：此时是使用其他主机（另一台设备）进行登录

如果登录的不是默认端口，还需要添加端口号

小拓展：远程登录语法格式

 mysql -u用户名 -p密码 -h远程主机 -P(大)端口号

1.3 修改用户账号

既可以修改用户名，也可以修改主机名。

格式：

 rename user "旧用户名+主机名" to "新用户名+主机名";

示例：

 rename user wxy@'192.168.125.%' to wt@'192.168.%.%';

1.4 修改密码

分为两种方法，建议使用方法2

1.4.1 alter

 alter user wxy@'192.168.125.%' identified by  '123123';   //重新定义密码

1.4.2 set password (for)

注意：密码有安全性策略可以修改取消

 set global validate_password_policy=0;    //特殊字符
 set global validate_password_length=1;    //密码长度

格式：

 SET PASSWORD = PASSWORD('abc123');  //只能修改当前用户的密码
 select user();        //查看当前登录的用户
 
 set password for '用户' = password('密码');    //修改指定用户的密码

示例：

 set password for wxy@'192.168.125.%' = '123123';

1.5 删除用户

格式：

 drop user '用户名'@'主机名';

示例：

 drop user wt@'192.168.%.%';

1.6 破解密码

修改配置文件（以mysql5.7版本为例）

 [root@7-3 ~]# vim /etc/my.cnf
 [mysqld]
 skip-grant-tables   //超级权限模式
 skip-networking     //跳过网络连接（相当于关闭了3306端口），MySQL8.0已默认添加该项
 [root@7-3 ~]# systemctl restart mysqld

上述操作，可用于忘记密码时，临时登录一次。

那么，密码被破解后，如何重新添加密码？

先清空密码

 update mysql.user set authentication_string='' where user='root' and host='localhost';

刷新才会生效

 flush privileges;

注释配置文件

 [root@7-3 ~]# vim /etc/my.cnf
 [mysqld]
 #skip-grant-tables    //将这两项注释掉
 #skip-networking
 [root@7-3 ~]# systemctl restart mysqld

设置密码安全性策略

 set global validate_password_policy=0;
 set global validate_password_length=1;

重新设置密码

 SET PASSWORD = abc123;

1.7 修改端口号

 [root@7-3 ~]# vim /etc/my.cnf
 [mysqld]
 port = 9527
 [root@7-3 ~]# systemctl restart mysqld

一般情况下，不会修改端口号。

示例：

 mysql -uwxy -pabc123 -h192.168.125.130 -P9527
       用户名   密码       远程主机         端口号

2、grant 用户权限管理

权限类别：

管理类
程序类
数据库级别
表级别
字段级别

权限列表

SELECT
INSERT
DELETE
UPDATE

2.1 查看权限

 语法：
 show grants for '用户名'@'主机名';

示例：

 show grants for wxy@'192.168.125.%';
 +---------------------------------------------+
 | Grants for wxy@192.168.125.%                |
 +---------------------------------------------+
 | GRANT USAGE ON *.* TO 'wxy'@'192.168.125.%' |
 +---------------------------------------------+
 
 // USAGE权限只能用于数据库登陆，不能执行任何操作；USAGE权限不能被回收，即 REVOKE 不能删除用户。

2.2 授予权限

grant 语句：专门用来设置数据库用户的访问权限。当指定的用户名不存在时，GRANT语句将会创建新的用户

 格式：
 GRANT 权限列表 ON 数据库名.表名 TO '用户名'@'来源地址' [IDENTIFIED BY '密码'];
 拆分：
 1.权限列表：用于列出授权使用的各种数据库操作，以逗号进行分隔，如“select,insert,update”。使用“all”表示所有权限，可授权执行任何操作。
 2.数据库名.表名：用于指定授权操作的数据库和表的名称，其中表名可以使用通配符“*”。例如，使用“kgc.*”表示授权操作的对象为 kgc数据库中的所有表。
 3.'用户名@来源地址'：用于指定用户名称和允许访问的客户机地址，即谁能连接、能从哪里连接。来源地址可以是域名、IP地址，还可以使用“%”通配符，表示某个区域或网段内的所有地址，如“%.kgc.com”、“192.168.80.%”等。
 4.IDENTIFIED BY：用于设置用户连接数据库时所使用的密码字符串。在新建用户时，若省略“IDENTIFIED BY”部分，则用户的密码将为空。

示例1：

 grant select on mysql1.* to 'wt'@'localhost' IDENTIFIED BY '123456';
 //允许wt用户 在本地查询mysql1数据库的所有表的数据记录，但禁止查询其他数据库中的表的记录。

示例2：

 GRANT ALL ON *.* TO 'lisi'@'%' IDENTIFIED BY '123456';
 // 允许用户 lisi 在所有终端远程连接 mysql软件，并拥有所有权限。
 grant  all   on   *.*  to  lisi@'192.168.125.%';

flush privileges 不要忘记刷新

2.3 撤销权限

格式：

 REVOKE 权限列表 ON 数据库名.表名 FROM '用户名'@'主机名';

示例1：

 REVOKE select ON mysql1.* FROM 'wxy'@'192.168.125.%';    //撤销select权限

示例2：

 REVOKE ALL ON mysql1.* FROM 'wxy'@'192.168.125.%';   //撤销全部权限

3、存储引擎

MySQL支持多种存储引擎，其中目前应用最广泛的是innoDB和MyISAM两种

3.1 myisam 引擎

用的比较少

特点：

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

只读（或者写较少）
表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

tbl_name.frm 表格式定义：表格属性、字段、数据类型等
tbl_name.MYD 数据文件：数据 myisam data
tbl_name.MYI 索引文件：索引 index，相当于文章的目录

3.2 innodb 引擎

安全，innodb也是默认的引擎。

特点：

行级锁
支持事务， 适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎

3.3 myisam 与innodb 对比

storage limits（存储上限）
- myisam 256TB
- innodb 64TB （但是mysql 达不到这么大的数据量）

transactions（事务）
- myisam 不支持
- innodb 支持

locking granularity（锁级别）
- myiasam 表级
- innodb 行级

提高mvcc（多版本的并发控制）
- myiasam 不支持
- innodb 支持（提高数据库的并发性）

data caches （数据缓存）
- myiasam 不支持
- innodb 支持

foreign key （外键）
- myiasam 不支持
- innodb 支持

对比	MyISAM	InnoDB
存储限制	256TB	64TB
事务	不支持	支持
锁定方式	表级锁定	行级锁定
mvcc	不支持	支持
数据缓存	不支持	支持
外键约束	不支持	支持
全文索引	支持	支持
读写性能	只支持单独的查询与插入，读写阻塞	读写和事务并发能力较好
硬件	硬件资源占用较小	缓存能力较好，可以减少磁盘IO的压力
存储文件	数据文件和索引文件分开存储，存储为三个文件（.frm的表结构文件，.MYD的数据文件，.MYI的索引文件）	数据文件也是索引文件，存储成两个文件(.frm的表结构文件，.ibd的数据和索引文件)
使用场景	使用不需要事务支持，单独的查询和插入的业务场景	使用于需要事务支持，一致性要求比较高，数据会频繁更新，读写并发高的业务场景

3.4 管理存储引擎

查看mysql支持的存储引擎

 show engines;

查看当前默认的存储引擎

 show variables like '%storage_engine%';   //变量

修改默认的存储引擎

 vim /etc/my.cnf
 [mysqld]
 default_storage_engine = InnoDB   //将innodb替换成别的引擎
 systemctl restart mysqld

查看库中所有表使用的存储引擎

 show table status from 数据库名字;
 show table status from hellodb;

查看库中指定表的存储引擎

 show table status like  'tb_name';
 show create table tb_name;

设置表的存储引擎

 CREATE TABLE tb_name(... ) ENGINE=InnoDB;
 ALTER TABLE tb_name ENGINE=InnoDB;

4、mysql 架构和性能优化

4.1 MySQL 系统数据库

mysql 数据库

是mysql的核心数据库， 类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

information_schema 数据库

MySQL 5.0之后产生的，一个虚拟数据库， 物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

performance_schema 数据库

MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数， 库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

sys 数据库

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

4.2 服务器选项

注意:服务器选项用横线(-) ，不用下划线(_)

获取mysqld的可用选项列表：

 #查看mysqld可用选项列表和及当前值
 mysqld --verbose --help
 
 #获取mysqld当前启动选项
 mysqld --print-defaults

设置服务器选项方法：

在命令行中设置（一次性）

 shell> /usr/bin/mysqld_safe --skip-name-resolve=1
 shell> /usr/libexec/mysqld --basedir=/usr

2.在配置文件my.cnf 中设置（永久）

示例：

 vim /etc/my.cnf
 [mysqld]
 skip_name_resolve=1
 skip-grant-tables

4.3 服务器变量

注意：服务器变量用下划线(_)，不用横线(-)

服务器系统变量，分为：

全局：一直存在，都能识别
会话：临时存在于本次会话中

查看所有变量：

 show variables;

过滤变量：

 show variables like 'innodb%';    //查找以innodb开头的变量

查询指定变量：

select

 格式：
 select @@变量名;
 
 示例：
 select @@innodb_write_io_threads;

只查看全局变量（global变量）

 SHOW GLOBAL VARIABLES;

修改变量

在mysql界面里，使用set，0是off，1是on！

 set innodb_file_per_table=1;        //1是on，0是off
 set global innodb_file_per_table=1;   //全局变量要加global

查看服务器的基本状态信息

 show status;

小拓展：修改mysql的最大并发连接数（优化）

临时生效

 show variables like 'max_connections';
 set global max_connections=2000;    //临时生效

永久生效

 [root@7-3 ~]# vim /etc/my.cnf.d/mariadb-server.cnf
 [mysqld]
 max_connections = 8000
 [root@7-3 ~]# systemctl restart mysqld
 
 select @@max_connections;    //重新查看变量
 
 vim /usr/lib/systemd/system/mariadb.service
 [Service]
 LimitNOFILE=65535

4.4 状态变量

 show status;
 
 mysql> show global status like 'com_select';   //当前数据库查询次数

5、索引

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现。主键自带索引，会按照一定顺序进行排序。

ibd=index索引+data数据

索引类型：

B+TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

5.1 查看索引

 show index from 表名;
 show keys from 表名;

示例：

 show index from students;

5.2 建立索引

格式：

 CREATE INDEX 索引名 ON 表名 (列名[(length)]);

示例：

 create index idx_name on students(name(5));     //以name字段建立索引

5.3 删除索引

 drop index 索引名 on 表名;

示例：

 drop index idx_name on students;

【mysql】の用户 | 权限 | 存储引擎 | 变量 | 索引~

5.4 explain 工具

以通过EXPLAIN 来分析索引的有效性，获取查询执行计划信息，用来查看查询优化器如何执行查询

 explain select * from students where name like 's%';  #找s开头的人看是否会用索引    左前缀匹配，尽量使用左前缀
 explain select * from students where name like '%s';  #找s结尾的人是否会用    右后缀匹配
 explain select * from students where name like '%s%'; #包含s的是否会调用索引    包含匹配
 explain select * from students where name like 'x%';  #是否使用索引    左前缀匹配

说明：

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询：SIMPLE 、复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	当使用索引列等值查询时,与索引列进行等值匹配的对象信息
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值
Extra	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

说明： type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：NULL> system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery >range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

类型	说明
All	最坏的情况，全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生（高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

转载自:https://juejin.cn/post/7386690976206061608

【mysql】の 用户 | 权限 | 存储引擎 | 变量 | 索引~

1、用户管理

1.1 user表（用户信息表）

1.2 新建用户

1.3 修改用户账号

1.4 修改密码

1.4.1 alter

1.4.2 set password (for)

1.5 删除用户

1.6 破解密码

1.7 修改端口号

2、grant 用户权限管理

2.1 查看权限

2.2 授予权限

2.3 撤销权限

3、存储引擎

3.1 myisam 引擎

3.2 innodb 引擎

3.3 myisam 与innodb 对比

3.4 管理存储引擎

4、mysql 架构和性能优化

4.1 MySQL 系统数据库

4.2 服务器选项

4.3 服务器变量

4.4 状态变量

5、索引

5.1 查看索引

5.2 建立索引

5.3 删除索引

5.4 explain 工具

【mysql】の用户 | 权限 | 存储引擎 | 变量 | 索引~