[TOC]

1.库用户操作

描述:主要对mysql8.X数据库用户的查询/创建/删除以及权限分配查询;

基础语句:

#(1)系统用户表查看
mysql> SELECT user,host FROM mysql.user; #查询用户 数据库.表


#(2)创建用户语句
#注意:host的格式` % | 192.168.1.% | 192.168.1.1/255.255.255.0`
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
CREATE USER 'pig'@'%' IDENTIFIED BY '123456';
CREATE USER 'pig'@'192.168.1.%' IDENDIFIED BY '123456';


#(3)给建立用户分配权限并可以设置密码
GRANT all ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;
#注意点: ALL PRIVILEGES 是所有的用户操作权限,您可以指定权限以,分隔; GRANT OPTION 则是管理员权限;
mysql> GRANT ALL PRIVILEGES ON *.* TO 'test'@'192.168.1.1/255.255.255.0' IDENTIFIED BY 'test' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;


#(4)查看用户权限
#注意点:host部分不同则有可能密码权限是不同的;
SHOW GRANTS FOR [email protected]'%';


#(5)设置与更改用户密码
#注意点:当前客户端支持的加密方式,MySQL8.0默认的密码加密方式是caching_sha2_password
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '新密码';
FLUSH PRIVILEGES;
#5.7x 版本: SET PASSWORD FOR 'username'@'host' = PASSWORD('newpassword');
#5.7x 版本: 如果是当前登陆用户则可以使用它 SET PASSWORD = PASSWORD("newpassword");


#(6)撤销用户权限
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';


#(7)删除指定用户
#注意: drop 有时候干不掉特殊字符
DROP USER 'test'@'127.0.0.1';
DELETE FROM mysql.user WHERE user='' or user='::1'; #删除空表用户 IPv6 (当主机名为大写时候采用这种方式)

实际执行结果:

#创建用户
mysql> CREATE USER 'admin'@'%' IDENTIFIED BY 'TestWeiyiGeek';
Query OK, 0 rows affected (0.06 sec)

#查看用户
mysql> SELECT user,host FROM mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| admin | % |
| root | % |
| root | localhost |
+------------------+-----------+
6 rows in set (0.06 sec)

#权限赋予(也能修改密码)
mysql> GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;
Query OK, 0 rows affected (0.05 sec)
mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.04 sec)

#修改密码
mysql> ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'TEST';
Query OK, 0 rows affected (0.05 sec)

#权限查询
mysql> SHOW GRANTS FOR [email protected]'%';
+----------+
| Grants for [email protected]%
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `admin`@`%` WITH GRANT OPTION |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,GROUP_REPLICATION_ADMIN,INNODB_REDO_LOG_ARCHIVE,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `admin`@`%` WITH GRANT OPTION |
2 rows in set (0.09 sec)

#移除用户权限
mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'admin'@'%';
Query OK, 0 rows affected (0.05 sec)
mysql> SHOW GRANTS FOR [email protected]'%';
+-----------------------------------+
| Grants for [email protected]% |
+-----------------------------------+
| GRANT USAGE ON *.* TO `admin`@`%` |
+-----------------------------------+
1 row in set (0.08 sec)

#删除用户
mysql> DROP USER 'admin'@'%';
Query OK, 0 rows affected (0.05 sec)

注意事项:

  • 给用户'pig'@'%'授权的时候是这样的(或类似的),GRANT SELECT ON test.user TO 'pig'@'%', 则在使用REVOKE SELECT ON *.* FROM 'pig'@'%,命令并不能撤销该用户对test数据库中user表的SELECT 操作;
  • 如果授权使用的是 GRANT SELECT ON *.* TO 'pig'@'%'REVOKE SELECT ON test.user FROM 'pig'@'%'命令也不能撤销该用户对test数据库中user表的Select 权限,必须确定权限、数据库表、用户、主机是一致的才行;

事务处理

并发控制

描述:MySQL并发控制问题需要依赖锁Lock机制进行解决;

比如:多事务的执行方法,当不止一个请求到来时候,前面都还没执行以及有许多工作需要完成这时候常常是串行执行、交叉并发执行、同时并发执行

  • 1.串行执行:同一时间只有一个事务运行,将进程排列成为一个序列一个一个执行;
    • 特点:最笨的方法执行效率最低,且无法充分利用资源,类比于银行的一个个服务窗口;
  • 2.交叉并发执行:多事务交叉运行(非占用空闲时间可以处理其他任务),单处理器的并发方式;
    • 特点:执行效率高,充分利用资源,类比于银行的一个个服务窗口但是可以在A填表时间可以处理B用户的业务;
  • 3.同时并发执行:每个处理器处理一个事务,常常在多处理系统中使用;
    • 特点:当前应用广泛的执行方式,类比于银行的多个服务窗口;

为什么要出现并发控制?
答:锁(Lock)保证对并发操作进行正确调度以及保证事务的隔离性和数据的一致性;但是并发执行会导致同读同写的问题,导致破坏事务隔离性和数据不一致;

什么是数据不一致?

  • 丢失修改(Lost update):指事务1与事务2从数据库中读取同一数据并修改,事务2提交的结果破坏了事务1提交的结果,导致事务1的修改被丢失;
    WeiyiGeek.

  • 不可重复读(non-repeatable read):不可重复读是指事务1读取数据后,事务2执行更新操作,使事务1无法再现前一次读取结果;
    WeiyiGeek.

  • 读”脏”数据(Dirty Read):读脏数据是指事务2修改某一数据并写回,然后事1读取该数据。事务2是由于某种原因被撤销,数据恢复原值,导致事务1读取的数据为错误数据也就是脏数据;
    WeiyiGeek.

注意事项:

  • 多事物并发处理并非一定需要事物性引擎;
  • 事物性引擎可以先对数据进行修改然后还可以进行回滚撤回比如InnoDB中(Rollback,commit)

锁机制

描述:为什么要有锁Lock?
答:因为为了避免多事物并发处理导致数据不一致,所以多事物之间要隔离;加锁后事物则对此数据对象具有一定的控制,在释放锁之前对其他事物不能更新此数据对象;

操作流程:事物对某个数据对象操作前,先向系统发出请求对其加锁;

基本锁分为两种类型:

  • 读锁(Read Lock):别名共享锁,可以多个事物对此数据对象进行加读锁,但是不能加写锁直到所有事物释放锁才行;
  • 写锁(Write Lock):别名排它锁,当且只能有一个事物对此数据对象进行加写锁,其他事物不能对此数据对象添加任何锁;

悲观锁与乐观锁,本质上是对于锁机制的一种看待角度:

  • 悲观锁:对数据被外界(包括本系统当前的其他事务,以及来之外部系统的事务处理)修改保持保守态度,因此在整个数据处理过程中将数据处于锁定状态;
  • 乐观锁:假设认为数据一般情况下不会造成冲突,所以在数据进行提交更新时候,才会正式对数据的冲突与否进行检测,如果发现冲突了则让返回用户错误的信息让用户决定如何去做;

MySQL锁的实现是依赖于存储引擎,不同的存储引擎其实现方式是不一致的;


存储引擎

简介

描述:我们列出常用的MySQL数据库存储引擎;

  • MYISAM
  • InnoDB
  • Memory


1.MYISAM
描述:在MySQL5.5版本之前默认的存储引擎;
特点:

  • 支持大文件存储最大256TB空间(受操作系统文件系统类型影响ext2/NTFS),每个MYISAM表最大索引64(可以通过编译修改进行改变);
  • 支持全文索引,以及BLOB和TEXT类型可以被索引;
  • 仅仅支持表锁;
  • 非事物性引擎;
  • 产生文件格式:FRM文件存储表定义,MYD数据文件,MYI索引文件;
  • 适合读多写少的应用场景;


InnoDB
描述:在MySQL5.5版本之后的默认存储引擎;
特点:

  • 事物性引擎为巨大数据量的最大性能设计;
  • 支持事物安全
  • 支持表、行锁和外键;
  • 产生文件格式:FRM文件存储表定义,idb数据文件,opt索引文件,(idbata1自动扩展数据文件,ib_logfile 0/1 日志文件);
  • 适合并发量大的大型网站


Memory
描述:将数据存储到内存中的存储引擎;
特点:

  • IO效率高读写快,但注意内存是掉电不保存数据的特性;
  • 非事物性引擎,并且不支持BLOB和TEXT类型;
  • 适合对数据及时性要求比较强的应用,读写频繁场景比如Session值;


Cluster
描述:用于MySQL Cluster的集群的存储引擎,提供数据层面上的高可用性HA;

例如:多表查询等复杂性查询性能相对不是优势;

WeiyiGeek.引擎对比


基础示例:

-- 创建表指定数据存储引擎
CREATE TABLE demo(
id INT NOT NULL PRIMARY KEY,
) ENGINE = MYISAM; -- 关键点:ENGINE 指定数据引擎;