聊聊数据库：SQL运维~存储引擎与权限

聊聊数据库：SQL运维~存储引擎与权限

上篇回顾：聊聊数据库：SQL运维~配置篇

存储引擎的扩展4.1.innoDB是如何实现事物的？4.2.innodb 读操作是否会阻塞 写操作？1.简单回顾2.常见场景3.解决方案4.InnoDB专栏1.6.4.MySQL权限相关3.1.版本相同3.2.版本不同1.2.创建账号1.3.常用权限1.4.用户授权1.5.权限收回1.账号权限设置2.数据库账号安全3.迁移问题

存储引擎的扩展

1.简单回顾

上节在最后的时候说了下存储引擎，这边简单回顾下：

补充说明：

Archive存储引擎的数据会用 zlib来压缩，而且只支持在自增ID上添加索引NDB存储引擎的数据存储在磁盘中（热数据存储在内存中），支持Ttree索引和集群

场景：数据需要完全同步（这些后面会继续说的）

2.常见场景

提一个场景： innodb表无法在线修改表结构的时候怎么解决？

先看下 Innodb不支持在线修改表结构都有哪些情况：（主要从性能方面考虑）

第一次创建 全文索引和添加 空间索引（ MySQL5.6以前版本不支持）

全文索引： create fulltext index name on table(列,...);空间索引： alter table geom add spatial index(g);

删除主键或者添加自增列

PS：innodb存储就是按照主键进行顺序存储的（这时候需要重新排序）删除主键： alter table表名drop primary key加自增列： alter table表名add column idintauto_increment primary key

修改列类型、修改表字符集

修改列类型： alter table表名modify列名类型类型修饰符修改字符集： alter table表名characterset=utf8mb4

PS：DDL不能并发执行（表级锁）长时间的DDL操作会导致主从不一致

DDL没法进行资源限制，表数据多了容易占用大量存储IO空间（空间不够就容易执行失败）

3.解决方案

安装： yum install percona-toolkit or apt-getinstall percona-toolkit

PS：离线包： https://percona.com/downloads/percona-toolkit/LATEST/

命令： pt-online-schema-change选项D=数据库,t=表名,u=用户名,p=密码

原理：先创建一个类型修改完的表，然后把旧表数据copy过去，然后删除旧表并重命名新表

查看帮助文档： pt-online-schema-change--help|more

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-online-schema-change.html

PS：一般就 --alter和 --charset用的比较多（ --execute代表执行）

常用： pt-online-schema-change--alter"DDL语句"--execute D=数据库,t=表名,u=用户名,p=密码

eg：添加新列： pt-online-schema-change--alter"add 列名 类型"--execute D=数据库,t=表名,u=用户名,p=密码

知识回顾：

添加字段：addalter table tb_name add列名数据类型修饰符[first|after列名];PS：SQLServer没有 [first|after列名]修改字段：alter、change、modify修改字段名： alter table tb_name change旧列名新列名类型类型修饰符修改字段类型： alter table tb_name modify列名类型类型修饰符添加默认值： alter table tb_name alter列名setdefaultdf_value删除字段：dropalter table tb_name drop字段名

4.InnoDB专栏

写在前面的概念：排它锁（别名：独占锁、写锁）、共享锁（别名：读锁）

4.1.innoDB是如何实现事物的？

事物4大特性：A（原子性）C（一致性）I（隔离性）D（持久性）

innodb事务日志主要就是 redolog（重做日志）和 undo log（回滚日志）

我画个转账案例：

4.2.innodb 读操作是否会阻塞 写操作？

一般情况下： 查询需要对资源添加共享锁（读锁） | 修改需要对资源添加排它锁（写锁）

PS：共享锁和共享锁之间是可以共存的（读的多并发）理论上讲读操作和写操作应该相互阻塞

而 innodb看起来却仿佛打破了这个常规，看个案例：

1.启动一个事物，但是不提交

2.在另一个连接中查询

PS：理论上独占锁没提交时是不能读操作的，但 innodb做了优化，会查询 undo log（未修改前的数据）中的记录来提高并发性

3.提交事物后再查询，这时候就看到更新后的数据了

PS：这个就是innodb的 MVCC（多版本并发控制）

知识拓展：

【推荐】Mysql的InnoDB事务多版本并发控制如何实现（MVCC）https://cnblogs.com/aspirant/p/6920987.htmlhttps://blog.csdn.net/u013007900/article/details/78641913https://cnblogs.com/dongqingswt/p/3460440.htmlhttps://jianshu.com/p/a3d49f7507ffhttps://jianshu.com/p/a03e15e82121https://jianshu.com/p/5a9c1e487ddd基于mysql全文索引的深入理解https://cnblogs.com/dreamworlds/p/5462018.html【推荐】MySQL中的全文索引(InnoDB存储引擎)https://jianshu.com/p/645402711dacinnodb的存储结构https://cnblogs.com/janehoo/p/6202240.html深入浅出空间索引：为什么需要空间索引https://cnblogs.com/mafeng/p/7909426.html常见的空间索引方法https://blog.csdn.net/Amesteur/article/details/80392679【推荐】pt-online-schema-change解读https://cnblogs.com/xiaoyanger/p/6043986.htmlpt-online-schema-change使用说明、限制与比较https://cnblogs.com/erisen/p/5971416.htmlpt-online-schema-change使用注意要点https://jianshu.com/p/84af8b8f040b详细分析MySQL事务日志(redo log和undo log)https://cnblogs.com/f-ck-need-u/archive/2018/05/08/9010872.html

1.6.4.MySQL权限相关

1.账号权限设置

之前在SQL环境篇的时候简单提了一下权限设置（点我回顾），现在再说说常用的权限知识：

https://cnblogs.com/dotnetcrazy/p/9887708.html

1.2.创建账号

用户组成格式： 用户名@可访问控制的列表

用户名：一般16字节

以 UTF-8为例：1英文字符 = 1字节，1中文 = 3字节

可访问控制列表：

这个不包含 localhost（数据库本地服务器不能访问）

%：所有ip都可访问（一般都这么干的，数据比较重要的推荐使用第二种）192.168.1.%： 192.168.1网段的ip都可以访问localhost：只能通过数据库服务器进行本地访问

1.创建命令： create user@ip identifiedby'密码';

PS：可以使用 \h create user来查看帮助文档

2.查看当前用户： selectuser();

PS： MariaDB查看当前数据库有哪些用户： selectuser,password,hostfrommysql.user;

MySQL： selectuser,authentication,hostfrommysql.user;

3.修改密码： alter user user()identifiedby'密码';

4.另类思路：我一般都是直接在表中插入数据（MySQL是 authentication_string）

eg： insertintomysql.user(user,host,password)values("用户名","%",password("密码"));PS：修改密码： update mysql.usersetpassword =password('新密码')whereuser='用户名';

知识拓展：ERROR 1045 (28000): Access denied for user 'mysql'@'localhost'

1.3.常用权限

补充说明： super：如设置全局变量等系统语句，一般DBA会有这个权限

PS：MariaDB查看数据库支持哪些权限： show privileges;

https://mariadb.com/kb/en/library/show-privileges/

1.4.用户授权

权限这个东西大家都懂，一般都是最小权限

授权命令如下： grant权限列表on数据库.表to用户名@ip

PS：开发的时候可能为了省事这么设置： grant all[privileges]on数据库.*to用户名@'%';

正规点一般这么设置：

线上： grantselect,insert,update on数据库.*to用户名@ip开发： grantselect,insert,update,index,alter,create on数据库.*to用户名@ip段

PS：查看当前用户权限： show grantsfor用户名;，刷新数据库权限： flush privileges;

以前可以在授权的时候直接创建用户（加一段 identifiedby'密码'），新版本好像分开了

1.5.权限收回

命令如下： revoke权限列表on数据库.表from用户名@ip

eg： revoke create,alter,deletefromdjango.*fromdnt@'%'（是 from而不是 on）

2.数据库账号安全

这个了解即可，我也是刚从DBA朋友那边了解到的知识（ MySQL8.0），基本上用不到的，简单罗列下规范：

只给最小的权限（线上权限基本上都是给最低的(防黑客)）密码强度限制（MySQL高版本默认有限制，主要针对MariaDB）密码有期限（谨慎使用，不推荐线上用户设置有效期）历史密码不可用（不能重复使用旧密码）

PS：现在用BAT的产品来修改密码基本上都是不让使用上次的密码

设置前三次使用过的密码不能再使用： create user@'%'identifiedby'密码'password history3;

PS：设置用户密码过期： alter user用户名@ippassword expire;

3.迁移问题

经典问题：如何从一个实例迁移数据库账号到另一个实例？

eg：老集群 > 新集群

官方文档：https://percona.com/doc/percona-toolkit/LATEST/pt-show-grants.html

3.1.版本相同

数据库备份下，然后在新环境中恢复

然后导出用户创建和授权语句：eg： pt-show-grants-u=root,-p=密码,-h=服务器地址-P=3306

扩展文章：pt-show-grants的使用（eg： pt-show-grants--host=192.168.36.123--port=3306--user=root--password=密码）

生成的脚本大致是这样的：(把脚本放新服务器中执行即可)

CREATE USER IF NOT EXISTS 'mysql.sys'@'localhost';ALTER USER 'mysql.sys'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT LOCK;GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';-- Grants for 'root'@'%'CREATE USER IF NOT EXISTS 'root'@'%';ALTER USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

3.2.版本不同

可以使用上面的方法，但是需要使用 mysql_upgrade升级下系统表（适用：低版本到高版本）但是推荐使用 生成SQL脚本

扩展文章：mysql升级小结和mysql_upgrade的用途