本文深入探讨MySQL的高级管理，包括约束、事务及其ACID特性、隔离级别、锁策略。详细讲解了MySQL的权限系统，如用户账号创建、权限分配以及如何忘记管理员密码的解决办法。此外，还介绍了存储引擎如InnoDB和MyISAM的特点，以及日志系统对数据库管理的重要性。
MySQL
内部数据集在磁盘上, 某一次读取操作, 消耗的代价是巨大的.
所以要使用索引
- 连接管理器
- 线程管理器
- 用户管理器 : 用户的访问还受访问控制器的限制
约束
主键约束 : 唯一非空,一张表只能有一个
外键约束 : 参考性约束, 引用性约束
唯一键约束 : 唯一, 可存在多个
检查性约束 : 对值进行检查
MySQL事务
事务 : 一组原子性的SQL查询, 或者多个SQL 语句组成了一个独立的工作单元
事物日志 : 将随机写改为顺序写
- 相关的配置参数( 建议在启动之前修改, 启动后临时修改无效, 必须重启服务才能生效)
innodb_log_file_size 5242880
innodb_log_files_in_group 2
innodb_log_group_home_dir ./ACID 测试
- A : automaticity 原子性, 整个事物中所有操作要么全部成功执行, 要么全部失败后回滚
- C : consistency 一致性, 数据库总是从一个一致性砖塔转换为另一个一致性状态
- I : isolation 隔离性, 一个事物所做出的操作在提交之前, 是不能为其他事务缩减, 隔离有多种级别, 主要是为了并发
- D : durability 持久性, 事务一旦提交, 其所做的修改会永久保存于数据库中
事物 : 推荐使用innodb 存储引擎, MySQL 默认把每个语句作为一个事务来进行提交
启动事务 : START TRANSACTION
结束事务 :
- 完成, 提交 : COMMIT
- 未完成, 回滚 : ROLLBACK
事物支持savepoint :
SAVEPOINT indentifier;
ROLLBACK TO [SAVEPOINT] identifier;
RELEASE SAVEPOINT indentifier;默认启用自动提交功能
SHOW GLOBAL VARIABLES autocommit;
autocommit ON
#启动事务
START TRANSACITON;
#回滚事务
ROLLBACK;
#关闭自动提交后, 使用COMMIT 进行提交
COMMIT;建议显示请求和提交事物, 不要使用自动提交功能
关闭自动提交功能, 在配置文件中写入
autocommit=OFF
重启服务即成功
事务的隔离级别 :
- READ UNCOMMITTED ( 读未提交 ) –> 脏读
- READ COMMITTED ( 读提交 ) –> 不可重复读(同一数据两次读取结果不同)
- REPEATABLE READ ( 可重复读 ) –> 幻读(两次读取内容, 新数据无法显示)[MySQL默认]
- SERIABLIZABLE ( 可串行化 ) –> 性能不佳
定义的变量为 : tx_isolation
锁
查看innodb 存储引擎基本状态信息
SHOW ENGINE innodb STATUS;MySQL 权限
权限类别 :
- 库级别权限.
- 表级别权限.
- 字段级别权限. (MySQL 最小粒度权限)
- 管理类权限.
- 程序类权限.
管理类权限 :
#创建临时表, 占据16M
CREATE TEMPORARY TABLES;
#创建用户
CREATE USER;
#文件导入相关的操作权限
FILE
#执行管理操作的权限
SUPER
#显示数据库
SHOW DATABASES;
#重新装载授权表
RELOAD
#关闭数据库的权限
SHUTDOWN
#创建连接, 以复制的方式连接至服务器端
REPLICATION SLAVE
#请求复制到本地
REPLICATION CLIENT
#请求在服务器级别, 对表施加锁操作
LOCK TABLES
#在进程级别对MySQL 进行进程管理
PROCESS程序类权限 :
#与触发器相关
FUNCTION
PROCEDURE
TRIGGER
CREATE, ALTER, DORP, EXCUTE库和表级别 TABLE or DATABASE
ALTER
CREATE
CREATE VIEW
DROP
INDEX
SHOW VIEW
#将所获得的权限授予给其他用户使用, 在数据库授权的时候不建议使用 WITH GRAND OPTION
GRANT OPTION
数据操作
SELECT
INSERT
DELETE
UPDATE字段级别
SELEC(col1, col2,...)
UPDATE(col1, col2,...)
INSERT(col1, col2,...)所有权限 :
ALL PRIVILEGES
ALL管理权限的表
columns_priv
procs_priv
proxies_priv
tables_priv 用户账号 :
'USERNAME'@'HOST'
用户名, 和允许用户通过哪台主机访问数据库, 为了执行高效, 尽量使用IP 地址, 而不使用主机名解析
创建用户 : CREATE USER
CREATE USER 'USERNAME'@'HOST' [IDENTIFIED BY 'passwd'];
查看用户获得的授权 : SHOW GRANTS FOR
SHOW GRANTS FOR 'USERNAME'@'HOST'
重命名用户 : RENAME USER
RENAME USER old_user_name TO new_user_name;
删除用户 : DROP USER ‘USERNAME’@’HOST’;
修改用户密码 :
SET PASSWORD FOR USERNAME;
#不会自动刷新权限
UPDATE mysql.user SET Password=PASSWORD('password') WHERE User='USERNAME';忘记管理员密码的解决办法 :
- 重新启动mysqld 进程时, 使用 –skip-grant-tables 和 –skip-networking (修复过程中禁止别人连接)选项
- 通过UPDATE 命令修改管理员命令
- 以正常的方式启动mysqld 进程
或者 : 修改/usr/lib/systemd/system/mariadb.service 中的启动选项, 在ExecStart=/usr/bin/mysqld_safe –basedir=/usr 后加上 –skip-grant-tables
然后运行systemctl deamon-reload, 再启动mariadb.service
再次登陆的时候就不需要密码了.
运行完毕更新了用户的密码后去掉之前添加的参数,
授权 : GRANT
GRANT priv_type [(column_list)] [,priv_type[(column_list)]] ...
ON [object_type] priv_level
TO user_specification [, user_specification] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option]...}]
[WITH with_option ...]
object_type : TABLE | FUNCTION | PROCEDURE
priv_level : * | *.* | db_name.* | db_name.tbl_name | tbl_name | dbname_routine_name
ssl_option : SSL | X509 | CIPHER 'cipher' | ISSUER 'issuer' | SUBJECT 'subject'
with_option : GRAND OPTION | MAX_QUERIES_PER_HOUR count | MAX_UPDATES_PER_HOUR_count
三层模型 :
物理层 => 系统管理员SA
逻辑层 => 数据库管理员DBA
视图层 => 程序员Coder
实现 : 各种数据库产品
服务端程序 :
mysqld, mysqld_safe, mysqld_multi
非客户端检查工具
myisamchk, ... 在文件系统层级的应用
配置文件格式 : 根据配置段的定义内容进行区分
/etc/mysql/my.cnf /etc/my.cnf ~/my.cnf
注 : 最后加载的配置项最终生效
显示单个变量的值
`mysql> SELECT@@[global|session].system_var_name;`
% : 匹配任意长度的任意字符
_ : 匹配任意单个字符
服务器变量的调整方式
运行时修改 :
- global : 仅对修改后新建立的会话有效, 拥有全局修改权限的用户才有权修改
- session : 仅对当前会话有效, 且立即生效
- 启动前通过修改配置文件达到载入配置文件的效果
设定默认值的话, 对新建立的会话生效, 当前会话不生效
文件路径的相关设定, 表的存储引擎不随便更改, 一开始就应该按照自身的业务模型进行设定, 不要在后来去修改
超出范围的数据会以其设定的最大值进行填充. 并发出警告
修改的配置参数, 带来的结果会出乎意料
同一个数据应该使用同一个数据库引擎
设定与查询数据库变量的示例
SELECT @@session.sql_mode;
SET @@session.sql_mode=TRADITIONAL;
TRADITIONAL 必须修改
STRICT_TRANS_TABLES
STRICT_ALL_TABLES
SELECT col1,col2 ... FROM tb_name [WHERE clause] GROUP BY col_name
HAVING 语句对聚合后的语句进行过滤
MySQL 存储引擎
存储引擎决定了它的高级功能
Innodb
事务, 外键, 行级锁
Mariadb 使用的 Percona-XtraDB
Mariadb 上的 mysql数据库表默认使用的存储引擎为MyISAM
存储引擎不要混合使用
Innodb 特性
ibdata1 : 表空间文件(所有数据存储在同一个文件中)
innodb_file_per_table=ON 将数据库分开存储, 每个表使用单独的表空间,方便使用单表管理
索引和数据存储在表空间中ibdata类似的文件中. 单个表文件过大后, 会分为几个小文件
表结构的定义 : 存放在数据库目录的tab1_name.frm
- 事务型存储引擎 : 使用于大量的短期事务, 适用于对事务要求较高的场景中
- 基于MVCC(多版本并发控制)基于快照机制实现其功能, 支持四个隔离级别.
- 使用聚集索引(主键索引)
- 支持”自适应Hash索引”
- 颗粒度(每次用户的修改操作要锁定行, 使用的锁为间隙锁(锁定行与行之间的空当))
- 如果热区数据具有唯一性. 会自适应生成Hash索引(不可人工干预的)
- 使用索引为左前缀索引, 索引左前70个字符
聚集索引为主键索引
为了提升性能, innodb支持预读操作(将热区数据放入内存中的buffer_pool, 可分割开来进行管理, 既能缓冲表的元数据和数据)
完成全文搜索使用单独的服务器, 使用特定格式存储, 使用反向索引机制, 将每个数据按照完整独立的词进行切割, 出现次数越多, 排序的优先级越高. 使用单独的搜索引擎
MyISAM 的特点
- 全文索引(将整个字段中的数据放到索引中)
- 支持对表中数据进行压缩
- 支持空间索引
- 无法保证崩溃后进行安全恢复
- 表级锁
延迟索引更新(更多的IO操作合并起来)
每个表都有三个文件, 存储与数据库目录中
tbl_name.frm : 表格式定义
tbl_name.MYD : 数据文件
tbl_name.MYI : 索引文件
MariaDB 额外支持很多存储引擎 :
- TokuDB
- Cassandra,
- …
锁策略
- 读锁是共享的
事物 :
事物 : 一组原子性的SQL查询, 或者是一个或多个SQL语句组成的独立工作单元
事物日志 : 将语句的操作直接写到日志文件中, 承载了中间状态, 之后同步到后台数据文件中去.
* 支持两种操作 : redo & undo
* 事物日志文件应该选择合理的大小, 取决于业务
* 默认大小为5M
回滚操作可以设置回滚点, 回滚到最初修改处, 也是就是所有操作的开始
手动控制事物
- 启动 : START TRANSACTION
- 提交 : COMMIT
- 回滚 : ROLLBACK
- 隔离 : 当前事物的操作对其他事物的操作是不可见的
事物隔离级别
READ-UNCOMMITED : 读未提交 -> 脏读
READ-COMMITTED : 读提交 -> 不可重复读
REPEATABLE-READ : 可重复读 -> 幻读
SERIALIZABLE : 串行化; 一个事物的修改会阻塞其他对此的修改
SET @@session.tx_isolation=’READ-UNCOMMITTED’
MySQL 用户的权限和管理
- 用户账号 : user@host
- user : 账户名称
- host : 主机
- % : 任意字符
- _ : 任意单个字符
创建用户 :
CREATE USER 'user'@'host' IDENTIFIED BY PASSWORD
日志
- 查询日志 general_log
- SET @@log_output={‘FILE|TABLE|NONE’}
- 错误日志 log_error = /var/log/mariadb/error_log
- mysqld 启动和关闭过程, 输出的信息
- mysqld 运行过程中产生的错误信息
- event scheduler 运行时产生的信息
- 主从架构中, 复制时产生的日志
- log_warnings 1 记录到错误日志中
- 二进制日志 : 潜在有可能被修改的数据(类似于重放日志,)
- binlog_format={STATEMENT|ROW|MIXED}
- STATEMENT : 语句
- ROW : 行
- MIXED : 混编
- 重放的功用 : 备份
- 不要和数据文件放在一起
- log_bin = /PATH 用来指定记录的二进制日志位置
- sql_log_bin = ON | OFF : 指定是否启用剑日志记录与二进制日志中
- 使用FLUSH LOGS; 会滚动二进制文件(创建一个新的二进制日志文件)
- 查看正在使用的二进制日志文件名称和偏移量 : SHOW MASTER STATUS;
- 查看二进制文件中的事件 : SHOW BINLOG EVENT IN ‘file_name’ FROM POS;
- POS 是事件的位置
- mysqlbinlog /file 直接读取二进制日志文件内容
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
