MySQL 8.0的若干知识

O魔法师O

已于 2024-03-31 16:37:25 修改

阅读量64

点赞数

文章标签： mysql 数据库

于 2023-11-05 10:02:47 首次发布

本文链接：https://blog.csdn.net/mofashi_ww/article/details/134224447

版权

一、查看 .ibd文件

ibd2sdi table1.ibd

二、查询用户信息

select user,host,authentication_string,plugin from mysql.user;

三、创建/删除用户等

create user test@'10.0.0.%' identified by '123';

drop user test@'10.0.0.%';

alter user test@'10.0.0.%' identified by 'abc123';

alter user test@'10.0.0.%' identified with mysql_native_password by '123';

flush privileges;

# 锁定test用户
alter user test@'10.0.0.%' account lock;

# 解锁test用户
alter user test@'10.0.0.%' account unlock;

四、解析binlog命令

mysqlbinlog --no-defaults -vv --base64-output=decode-rows mysql-bin.00001

五、8.0用户方面的新特性

1. 密码插件，在8.0中替换成了sha2模式，5.7是mysql_native_password
    客户端工具（navicat、sqlyog）不支持（无法连接）
    主从复制，MGR，不支持新的密码插件    
  解决方法：
    create   xxxx   with mysql_native_password
    alter    xxxx   with mysql_native_password
    
    vim /etc/my.cnf  修改默认加密插件为 mysql_native_password
2. 在8.0中不支持grant直接创建用户并授权，必须先建用户后再grant授权

3. 角色role(权限的打包)
    create role test_role@'10.0.0.%';
    grant test_role@'10.0.0.%' to test@'10.0.0.%';

六、权限

show privileges;

GRANT 权限 ON 权限级别 TO 用户;

*.*            :    全库级别        ---> 管理员             ---> mysql.user
test.*         :    单库级别        ---> 业务层面           ---> mysql.db
test.t1        :    单表级别                               ---> mysql.tables_priv
select(id,name):
# 示例1：授予用户 john 对数据库 mydb 中所有表的查询权限
GRANT SELECT ON mydb.* TO 'john'@'%';
# 示例2：授予用户 mary 对数据库 mydb 中的 employees 表的查询和更新权限
GRANT SELECT, UPDATE ON mydb.employees TO 'mary'@'%';
# 示例3：授予用户 admin 对所有数据库的所有表的所有权限
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%';


REVOKE 权限 ON 权限级别 FROM 用户;

【生产中用户类型规范】

管理员 : ALL
开发 : Create ,Create routine,Create temporary tables,Create view,Show view
,Delete ,Event ,Execute,Insert ,References,Select,Trigger,Update
监控 : select , replication slave , client supper
备份 : Select 、SHOW DATABASES、 PROCESS LOCK TABLES 、RELOAD
主从 : replication slave replication client
业务 : insert , update , delete ,select


show grants for test@'10.0.0.%';
select * from mysql.user where user='test' \G;
select * from mysql.db where user='test' \G;

七、死锁

建议：如果事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁的申请时机尽量往后放。

解决死锁问题的两种策略：

1. 直接进入等待，直到锁超时，这个超时时间可以通过参数innodb_lock_wait_timeout来设置。

2. 发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。将参数innodb_deadlock_detect设置为on，表示开启这个逻辑。【优先采用第二种策略，但是死锁检测会消耗大量的CPU资源】

3. 在数据库前面加上中间件，解决的思路是，对于相同行的更新，在进入引擎之前排队，将并发优化为串行，这样在innodb内部就不会有大量的死锁检测工作了。

八、表级锁

MySQL里面表级别的锁有两种：一种是表锁，一种是元数据锁（meta data lock，MDL)。
        表锁的语法是 lock tables … read/write。与FTWRL类似，可以用unlock tables主动释放锁，
也可以在客户端断开的时候自动释放。需要注意，lock tables语法除了会限制别的线程的读写
外，也限定了本线程接下来的操作对象。
        举个例子, 如果在某个线程A中执行lock tables t1 read, t2 write; 这个语句，则其他线程写t1、读写t2的语句都会被阻塞。同时，线程A在执行unlock tables之前，也只能执行读t1、读写t2的操
作。连写t1都不允许，自然也不能访问其他表。
        在还没有出现更细粒度的锁的时候，表锁是最常用的处理并发的方式。而对于InnoDB这种支持行锁的引擎，一般不使用lock tables命令来控制并发，毕竟锁住整个表的影响面还是太大。

另一类表级的锁是MDL（metadata lock)。MDL不需要显式使用，在访问一个表的时候会被
自动加上。MDL的作用是，保证读写的正确性。你可以想象一下，如果一个查询正在遍历一个
表中的数据，而执行期间另一个线程对这个表结构做变更，删了一列，那么查询线程拿到的结果
跟表结构对不上，肯定是不行的。
因此，在MySQL 5.5版本中引入了MDL，当对一个表做增删改查操作的时候，加MDL读锁；当要对表做结构变更操作的时候，加MDL写锁。