mysql事务持久化时宕机_MySQL-DBA课程-Day08

实施过程:zabbix库替换存储引擎

1.部署 Mariadb 环境 10.0.38

[root@db01 mysql]# vim /etc/yum.repos.d/mariadb.repo

[mariadb]

name = MariaDB

baseurl = http://yum.mariadb.org/10.1/centos7-amd64

gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB

gpgcheck=0

enabled=1

tar xf mariadb-10.0.38-rhel-7-x86_64-rpms.tar

改配置文件

[root@db01 my.cnf.d]# vim /etc/my.cnf.d/tokudb.cnf

[mariadb]

plugin-load-add=ha_tokudb.so

plugin-dir=/data/tokudb/plugin/

tokudb_commit_sync=ON

tokudb_cache_size=128M

tokudb_directio=ON

tokudb_row_format=tokudb_fast

tokudb_tmp_dir=/data/tokudb/tmp

tokudb_write_status_frequency =1

tokudb_read_status_frequency=1

tokudb_data_dir=/data/tokudb/data

tokudb_log_dir=/data/tokudb/log

~

mkdir -p ~ /data/tokudb/{plugin,tmp,data,log}

chown -R mysql.mysql /data/*

cd /usr/lib64/mysql/plugin/

cp -a * /data/tokudb/plugin/

chown -R mysql.mysql /data/*

启动数据库

[root@db01 data]# mysqld_safe &

生成批量替换语句

select concat('alter table ',table_schema,'.',table_name,' drop FOREIGN KEY ', CONSTRAINT_NAME,";") from information_schema.TABLE_CONSTRAINTS where table_schema='zabbix' and CONSTRAINT_TYPE='FOREIGN KEY';

select concat('alter table ',table_schema,'.'table_name,' engine=tokudb') from information_schema.tables where table_schema='zabbix' into outfile '/tmp/alter.sql';

percona-server+tokudb+zabbix

https://www.jianshu.com/p/898d2e4bd3a7

上节回顾:

支持哪些存储引擎

同源产品的存储引擎: TokuDB ,Myrocks

小项目: zabbix 监控系统 mariadb5.5 ----> mariadb 10.0.38 (Perconna 版本都支持)

InnoDB引擎和MyISAM引擎区别

MVCC

CLusterd Index

Transaction

Row Level Lock

FOREIGN KEY

Hot Backup

Adaptive HASH Index

ACSR

Replication

表空间迁移

create table

alter table discard

alter table import

碎片整理

alter table t1 engine=innodb;

ICP,Insert buffer,AHI,MRR,SNL,BNL,BKA

ICP : 索引下推

减少了 Server和engine,engine和磁盘的次数

Insert buffer : 插入缓冲

insert数据,不会立即更新到索引树中,存储在Insert buffer中.

index merge 功能在内存中合并查询索引树. 减少的大批量insert操作时索引的更新,减少IO和锁表的时间.

AHI : 自适应HASH索引

访问次数>=3的索引内存也,InnoDB会自动生成索引页的HASH索引(内存).加速索引页的访问

MRR : 辅助索引---回表->聚簇索引, 在回表之前自动将主键值先排序,一次性回表查询

减少回表次数,随机IO尽可能转换为顺序IO

SNL,BNL,BKA : Join 的三种经典算法

SNL : 关联表中没有索引.我们不建议出现

BNL : 在驱动表,得到所有数据,一次性到内循环中进行匹配

mysql> SET @@optimizer_switch='mrr=on,mrr_cost_based=off';

mysql> show variables like 'optimizer_switch%' \G

*************************** 1. row ***************************

Variable_name: optimizer_switch

Value: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=off,block_nested_loop=on,batched_key_access=on,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

1 row in set (0.00 sec)

===================================

1. 物理存储结构

1.1 表空间

支持两类表空间: 共享,独立

5.5 版本 : 默认共享表空间. 包含: 数据字典\undo\tmp\用户表数据和索引

5.6 版本 : 默认独立表空间. 包含: 数据字典\undo\tmp,将用户数据和索引独立,每个表单独存储

5.7 版本 : 默认独立表空间. 包含: 数据字典\undo,tmp独立,将用户数据和索引独立,每个表单独存储

8.0 版本 : 默认独立表空间. 数据字典取消掉, undo,tmp独立 将用户数据和索引独立,每个表单独存储

1.2 功能名词介绍

transaction 事务

undo : ibdata1 回滚日志

tmp : ibtmp1 临时表空间

redo : ib_logfile0~N 重做日志

ibd : t1.ibd 表空间数据文件

Innodb Buffer Pool 数据缓冲区池(70-80%)

log buffer 重做日志缓冲区

LSN 日志序列号

Trx_id 事务ID

checkpoint 检查点

1.3 事务 ?

1.3.1 什么是事务?

将多条DML(标准的事务语句),放在一个"组"中运行,要么全成功要么全失败.

交易?

以物换物

货币换物

虚拟币换物

虚拟币虚拟物

1.3.2 事务ACID特性atomicity, consistency, isolation, and durability.

A : 原子性 : 每一个事务都是一个完整整体,不可再分性 . 要么全执行成功要么全失败.

C : 一致性 : 在事务前,中,后,保证事务操作的数据前后一致.

I : 隔离性 : 多个事务之间,所做事务互不干扰,不能同时更新同一行数据.

D : 持久性 : 事务完成之后,所涉及到的数据,必须永久有效(落地)

兼容性.png

1.3.3 事务的生命周期管理

标准的事务生命周期:

(1) 开启一个事务

begin / start transaction;

(2) 标准的事务语句

insert

update

delete

(3) 结束事务

commit; # 提交事务

rollback; # 回滚事务

非标准的事务生命周期

(1) 自动提交机制

MySQL 5.6 以后:

begin子句会自动添加

每一条执行完成之后都会自动提交

mysql> select @@autocommit;

+--------------+

| @@autocommit |

+--------------+

| 1 |

+--------------+

[root@db01 data_3306]# vim /etc/my.cnf

autocommit=0

[root@db01 data_3306]# /etc/init.d/mysqld restart

说明: 默认情况下,开启事务时不加begin,逐条自动提交. 手工开启begin命令,按照正常事务工作过程.

(2) 隐式提交

用于隐式提交的 SQL 语句：

begin

a

b

begin

SET AUTOCOMMIT = 1

导致提交的非事务语句：

DDL语句： (ALTER、CREATE 和 DROP)

DCL语句： (GRANT、REVOKE 和 SET PASSWORD)

锁定语句：(LOCK TABLES 和 UNLOCK TABLES)

导致隐式提交的语句示例：

TRUNCATE TABLE

LOAD DATA INFILE

SELECT FOR UPDATE

(3) 隐式回滚

会话断开

数据库重启

死锁

1.3.4 事务底层的工作过程

画图说明.

(1) redo

分布:

内存: log buffer

磁盘: ib_logfile0~N

功能:

1. 保存内存数据页的变化

2. commit时, 实现事务的快速持久化的特性: 量少,顺序IO

3. 宕机时,通过redo实现重做事务,将数据库恢复到宕机之前的状态.

我们由把这步称之为 ACSR 中的"前滚"操作

(2) undo 回滚日志

分布: 默认 ibdata1, 5.7开始可以独立undo,8.0后自动独立

功能 :

1. 保存当前事务操作的反操作

2. 在执行rollback命令时,undo提供回滚操作,在ACID中主要实现A的特性,CI也有部分功能

3. 宕机时,ACSR过程中提供回滚操作(将没有commit标记的)

1.3.5 锁(写) 及 隔离级别(读)主要保证隔离性

(1) 锁 :

S : 共享锁,读锁

X : 排它锁,写锁

IS : 意向S

IX : 意向X

(2) X 锁的细分

TX ------> 表锁 DDL

全局锁表:

备份时,备份系统表时(非INOODB表),FTWRL

mysql> flush table with read lock;

mysql> unlock tables;

单表: DDL

mysql> lock table t1 read ;

mysql> unlock tables;

RX ------> 记录锁 DML

GAP LOCK X ------> 间隙锁 特殊DML

Next LOCK X ------> 下一键锁定

(3) 隔离级别(transaction_isolation)

mysql> select @@transaction_isolation;

RU :读未提交 READ-UNCOMMITTED

模拟:

session A

mysql> begin;

mysql> use world;

mysql> delete from city where id=1000;

session B

mysql> begin;

mysql> use world;

mysql> select *from city where id=1000;

会产生的问题:

1. 脏读

2. 不可重复读现象

3. 幻读

RC :读已提交★★★★★ READ-COMMITTED

1. 不可重复读现象

2. 幻读

RR :可重复读★★★★★ REPEATABLE-READ

1. 幻读

说明:

RR级别+ GAP+ Next lock(GAP+RX)有效防止幻读现象

通过MVCC,多版本并发控制中,一致性快照读技术,解决了不可重复读问题.

SR :串行化

总结:

AID 都是为了数据库最终一致性 C

SQL_MODE

约束

自己扩展:

MDL 原数据锁

page lock 页锁

latch 内存页锁

InnoDB 存储引擎核心参数 ★★★★★

mysql> select @@innodb_data_file_path;

mysql> select @@innodb_file_per_table;

mysql> select @@innodb_buffer_pool_size; #不要超过80%物理内存 ----> Out of memory

mysql> select @@innodb_log_buffer_size;

mysql> select @@innodb_log_file_size;

mysql> select @@innodb_log_file_in_group;

mysql> select @@innodb_flush_log_at_trx_commit;#

双1标准之一.控制redo刷写的策略.

0 每秒钟刷写redo到磁盘.

1 每次事务提交,理解刷写redo到磁盘

2 每次事务提交,立即写日志到OS cache中,然后每秒钟刷写到磁盘.

mysql> select @@innodb_flush_method; 控制(buffer脏页,redo buffer日志)刷写方式

建议设置:

O_DIRECT : 数据页刷写磁盘直接穿过文件系统缓存,redo 刷写时,先写os cache,再写到磁盘。

==================================

日志

备份恢复

主从复制

高可用

分布式

优化

NoSQL