上节回顾:
- InnoDB 与 MyISAM
事务(ACID):原子性,一致性,隔离性,持久性
行(记录)锁+表锁
热备份(事务)
ACSR(自动故障恢复)
MVCC
复制 GC GTID MTS
案例
环境:贵州某交管系统,违章信息
MySQL5.7,70多张表
备份策略:每周六 mysqldump全备,每天binlog备份
问题描述:在网上找了个参数调整ibtmp1大小,直接在生成中修改,由于手误,删除了ibdata1文件,数据库无法启动
解决思路:
1.mysqldump全备(周六) + 日志恢复
中间出的问题:
1.全备有问题,经过调整,好用了
2. binlog日志只有2,3,4三天日志
换解决方案: 表空间迁移
我们有什么?
1. 上周六的完整数据(完整表结构)
2. 周四宕机时的ibd
演练:
1. 搭建一个临时库
[root@db01 /data/3306/data]# mysql -S /data/3307/mysql.sock
2. 恢复上周六的备份到临时库
ldguo[world]>source /root/world.sql
3. 将测试库中ibd清掉
[root@db01 /data/3306/data]# vim /data/3307/my.cnf
secure-file-priv=/tmp
[root@db01 /data/3306/data]# systemctl restart mysqld3307.service
oldguo[(none)]>select concat("alter table world.",table_name," discard tablespace;") from information_schema.tables where table_schema='world' into outfile '/tmp/discard.sql';
oldguo[(none)]>set foreign_key_checks=0;
oldguo[(none)]>source /tmp/discard.sql
4. 将故障库ibd文件,拷贝指定位置
5. 将ibd进行import
oldguo[(none)]>select concat("alter table world.",table_name," import tablespace;") from information_schema.tables where tablee_schema='world' into outfile '/tmp/import.sql';
oldguo[world]>source /tmp/import.sql
1. InnoDB 事务的ACID如何保证?
1.0 一些概念
redo log : 重做日志, 记录数据页的变化. ib_logfile0,ib_logfile1
redo log buffer : redo log 的缓冲区(内存)
ibd : 表空间的数据文件,以段区页方式规划存储数据行和索引
buffer pool : 数据页缓冲区
LSN : 日志序列号
WAL :write ahead log,日志有限写磁盘
脏页 :diryty page,在内存中被修改的数据页
CKPT :checkpoint,将内存脏页回写到磁盘的动作
TXID :transaction id,事务ID,伴随着事务的整个生命周期
undo log :回滚日志,iddata1
1.1 redo 功能
1.1.1 redo简介
1.redo简介
重做日志, ib_logfile0,ib_logfile1
2.
记录数据页的变化
3.
主要 保证ACID 中的 "D",持久化功能,对于AC也有相应的保证
加快了commit命令的速度,提高了事务的并发.
并且实现了,在MySQL Crash时,ACSR中前滚的功能.
1.2 undo 功能
1.1.1 undo简介
回滚日志, 5.7 存储在ibdata1 ,8.0 undo单独存储
1.2.2 undo记录的内容
记录逆操作
1.2.3 作用
主要保证了 A 的特性,CI 也有相应的功能
###1.3 隔离级别和锁机制☆☆☆☆☆
隔离级别 | |
---|---|
RU | 读未提交,脏读,不可重复读,幻读 |
RC | 读已提交,不脏读,有不可重复读,有幻读 |
RR | 防止脏读,防止不可重复读(快照),可能会出现幻读(gap间隙,next-lock下一键锁) |
SR | 串行化 |
1.3.1 功能
主要提供了ACID中的 “I” 隔离性的功能,C的功能受到一定影响
1.3.2 隔离级别介绍
oldguo[(none)]>select @@tx_isolation;
oldguo[(none)]>select @@transaction_isolation;
oldguo[(none)]>show variables like '%tx%';
vim /etc/my.cnf
transaction_isolation=REPEATABLE-READ
transaction_isolation=READ-COMMITTED
(1) RU : 读未提交
transaction_isolation=READ-COMMITTED
A:
mysql -uroot -p
use world
1. oldguo[world]>begin;
2. oldguo[world]>update city set population=1000 where id=10;
4. oldguo[world]>commit;
B:
mysql -uroot -p
use world
3. select * from city where id=10;
5. select * from city where id=10;
防止脏读.
会出现,不可重复读.
会出现,幻读.
(2) RC : 读已提交
transaction_isolation=READ-COMMITTED
A:
mysql -uroot -p
use world
1. oldguo[world]>begin;
2. oldguo[world]>update city set population=1000 where id=10;
4. oldguo[world]>commit;
B:
mysql -uroot -p
use world
3. select * from city where id=10;
5. select * from city where id=10;
防止脏读.
会出现,不可重复读.
会出现,幻读.
create table t1 (id int ,name varchar(20));
insert into t1 values(1,'a'),(2,'b'),(4,'c'),(6,'d');
commit;
(3) RR : 可重复度
防止脏读
防止不可重复读 (MVCC)
可能会出现幻读 (GAP netlock)
(4) SR : 可串行化
1.3.3 InnoDB 的锁 (主要解决I)
record lock 记录锁,行级锁
gap
next-lock
2. InnoDB 核心参数介绍
2.1 默认存储引擎
oldguo[(none)]>select @@default_storage_engine;
Percona : XtraDB 默认
MariaDB : InnoDB 默认 myrocks tokudb
2.2 innodb_buffer_pool_size☆☆☆☆☆
show variables like '%pool%';
vim /etc/my.cnf
innodb_buffer_pool_size=2G
官方建议:最多95%
生产建议:50%~80% 不超过80%
面试如何回答:
1. 业务够用的
2. 公司硬件有预留
3. MySQL还有可能额外使用到其他的内存结构
4. 我们公司做的多实例
2.3 innodb_log_buffer_size
oldguo[(none)]>show engine innodb status \G
oldguo[(none)]>show variables like '%log_buffer%';
oldguo[(none)]>select @@innodb_log_buffer_size;
2.4 redo的参数
show variables like '%innodb_log_file%';
2.5 relog的刷写策略(“双一”) ☆☆☆☆☆重要!
select @@innodb_flush_log_at_trx_commit;
默认:
- 1,在事务提交时,立即刷写redo buffer中日志到OS buffer ,立即SYNC到磁盘
- 0,每秒,刷写redo buffer中日志到OS buffer ,每秒SYNC到磁盘
- 2,在每个事物提交时,立即刷写redo buffer中日志到OS buffer,每秒SYNC到磁盘
--------
The default setting of 1 is required for full ACID compliance. Logs are written and flushed to disk at each transaction commit.
With a setting of 0, logs are written and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
With a setting of 2, logs are written after each transaction commit and flushed to disk once per second. Transactions for which logs have not been flushed can be lost in a crash.
-------
2.6 innodb_flush_method
作用: 控制 buffer pool ,和 redo buffer的刷盘策略
select @@innodb_flush_method;
O_DIRECT :数据缓冲区写磁盘,不走OS buffer
fsync :日志和数据缓冲区写磁盘,走OS buffer
O_DSYNC :日志缓冲区写磁盘,不走 OS buffer
#最高安全模式
innodb_flush_log_at_trx_commit=1
Innodb_flush_method=O_DIRECT
#最高性能:
innodb_flush_log_at_trx_commit=0
Innodb_flush_method=fsync
-------- __@ __@ __@ __@ __~@
----- _`\<,_ _`\<,_ _`\<,_ _`\<,_ _`\<,_
---- (*)/ (*) (*)/ (*) (*)/ (*) (*)/ (*) (*)/ (*)
3. 小结
1. InnoDB核心特性
2. 事务 ACID
3. redo
4. undo
5. 隔离级别
6. 索引
7. 核心参数