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
  1. 改配置文件
[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/*

  1. 启动数据库
    [root@db01 data]# mysqld_safe &

  2. 生成批量替换语句

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

上节回顾:

  1. 支持哪些存储引擎

  2. 同源产品的存储引擎: TokuDB ,Myrocks
    小项目: zabbix 监控系统 mariadb5.5 ----> mariadb 10.0.38 (Perconna 版本都支持)

  3. InnoDB引擎和MyISAM引擎区别
    MVCC
    CLusterd Index
    Transaction
    Row Level Lock
    FOREIGN KEY
    Hot Backup
    Adaptive HASH Index
    ACSR
    Replication

  4. 表空间迁移
    create table
    alter table discard
    alter table import

  5. 碎片整理
    alter table t1 engine=innodb;

  6. 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. 交易?
    以物换物
    货币换物
    虚拟币换物
    虚拟币虚拟物

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 以后:

  1. begin子句会自动添加
  2. 每一条执行完成之后都会自动提交
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. 幻读
说明:

  1. RR级别+ GAP+ Next lock(GAP+RX)有效防止幻读现象
  2. 通过MVCC,多版本并发控制中,一致性快照读技术,解决了不可重复读问题.
    SR :串行化

总结:
AID 都是为了数据库最终一致性 C
SQL_MODE
约束

自己扩展:
MDL 原数据锁
page lock 页锁
latch 内存页锁

  1. 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,再写到磁盘。
==================================

  1. 日志
  2. 备份恢复
  3. 主从复制
  4. 高可用
  5. 分布式
  6. 优化
  7. NoSQL
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 214,875评论 6赞 496
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 91,569评论 3赞 389
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 160,475评论 0赞 350
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 57,459评论 1赞 288
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 66,537评论 6赞 386
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 50,563评论 1赞 293
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 39,580评论 3赞 414
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 38,326评论 0赞 270
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 44,773评论 1赞 307
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 37,086评论 2赞 330
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 39,252评论 1赞 343
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 34,921评论 5赞 338
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 40,566评论 3赞 322
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 31,190评论 0赞 21
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 32,435评论 1赞 268
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 47,129评论 2赞 366
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 44,125评论 2赞 352