1. 存储引擎介绍
相当于Linux 文件系统.组织存储表数据.
2. 存储引擎的种类
show engines;
InnoDB
MyISAM
CSV
Memory
其他的存储引擎:
MariaDB : InnoDB,TokuDB ,Myrocks
percona : xtradb ,TokuDB ,Myrocks
TokuDB ,Myrocks : 比较适合于在写入操作较多的场景,数据量级大的场景.
原因是: 插入性能很高, 压缩比较高.
监控类的业务.
学员案例:
环境: zabbix 3.x mariaDB 5.5 centos 7.3
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
- zabbix 版本
- 数据库版本 ---> 5.5 ----> ibdata1 ----> 5.7 ,8.0
- zabbix数据库500G,存在一个文件里
优化建议:
1.数据库版本升级到Mairia 10.x版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1 等安全参数需要关闭
5.参数调整....
优化结果:
监控状态良好
select concat("alter table zabbix.",table_name," engine=tokudb") from information_schema.tables
where table_schema='zabbix';
为什么?
- 原生态支持TokuDB,另外经过测试环境,10.x要比5.5 版本性能 高 2-3倍
- TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个归档表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.
5.参数调整...----->安全性参数关闭,提高性能.
3. InnoDB存储引擎的核心特性
面试题:
(1) InnoDB存储引擎的特性
(2) InnoDB和MyISAM的区别
MVCC 多版本并发控制
聚簇索引 PK
事务
行级锁 MyISAM支持表锁
外键 FK
复制支持高级特性: GTID等高级复制
自适应hash索引
支持热备,MyISAM支持温备份
ACSR(自动故障恢复)
- 存储引擎的操作
4.1 查看存储引擎
mysql> show engines;
mysql> select @@default_storage_engine;
mysql> create table mt (id int) engine=myisam;
mysql> create table et (id int) engine=innodb;
查询所有非INNODB的表 , 并且提出修改建议
mysql> SELECT
-> table_schema,
-> table_name ,
-> ENGINE ,
-> CONCAT("alter table ",table_schema,".",table_name," engine=innodb;") AS "修改建议"
-> FROM information_schema.tables
-> WHERE table_schema NOT IN ('sys','information_schema','performance_schema','mysql')
-> AND ENGINE <> 'innodb';
+--------------+------------+--------+--------------------------------------+
| table_schema | table_name | ENGINE | 修改建议 |
+--------------+------------+--------+--------------------------------------+
| test | mt | MyISAM | alter table test.mt engine=innodb; |
| test | test | MyISAM | alter table test.test engine=innodb; |
+--------------+------------+--------+--------------------------------------+
2 rows in set (0.01 sec)
mysql>
4.2 修改表的存储引擎
alter table test.test engine=innodb;
扩展: 碎片问题解决.
由于业务中有大量的delete操作,产生了大量的碎片.
(1) 表数据逻辑导出,删除原表,重新导入.
(2) mysql> alter table test.test engine=innodb;
小项目:
环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工truncate表,然后导入进去
现在:
对表进行按月进行归档表
业务替换为truncate方式
=============================================
学员案例:
环境: zabbix 3.x mariaDB 5.5 centos 7.3
现象 : zabbix卡的要死 , 每隔3-4个月,都要重新搭建一遍zabbix,存储空间经常爆满.
问题 :
- zabbix 版本
- 数据库版本 ---> 5.5 ----> ibdata1 ----> 5.7 ,8.0
- zabbix数据库500G,存在一个文件里
优化建议:
1.数据库版本升级到Mairia 10.x版本,zabbix升级更高版本
2.存储引擎改为tokudb
3.监控数据按月份进行切割(二次开发:zabbix 数据保留机制功能重写,数据库分表)
4.关闭binlog和双1 等安全参数需要关闭
5.参数调整....
优化结果:
监控状态良好
select concat("alter table zabbix.",table_name," engine=tokudb") from information_schema.tables
where table_schema='zabbix';
为什么?
- 原生态支持TokuDB,另外经过测试环境,10.x要比5.5 版本性能 高 2-3倍
- TokuDB:insert数据比Innodb快的多，数据压缩比要Innodb高
3.监控数据按月份进行切割,为了能够truncate每个归档表,立即释放空间
4.关闭binlog ----->减少无关日志的记录.
5.参数调整...----->安全性参数关闭,提高性能.
实施过程:
1.部署 Mariadb 环境 10.1
(1) 上传解压
[root@zabbix-server local]# tar xf mariadb-10.2.30-linux-glibc_214-x86_64.tar.gz
[root@zabbix-server mariadb]# ln -s mariadb-10.2.30-linux-glibc_214-x86_64 mariadb
[root@zabbix-server mariadb]# chown -R mysql.mysql /usr/local/mariadb
[root@zabbix-server mariadb]# mkdir -p /data/mysql/data
[root@zabbix-server mariadb]# chown -R mysql.mysql data
[root@zabbix-server mariadb]# mv /etc/my.cnf /etc/my.cnf.bak
(2) 备份原数据库zabbix数据
mysqldump -B zabbix > /tmp/zabbix.sql
(3) 停源库 ,启新库
[root@zabbix-server local]# systemctl stop mariadb
/usr/local/mariadb/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mariadb --datadir=/data/mysql/data
[root@zabbix-server data]# cd /usr/local/mariadb/support-files/
[root@zabbix-server support-files]# cp mysql.server /etc/init.d/mysqld
[root@zabbix-server support-files]# chkconfig --add mysqld
====未完待续(翻车现场.....)
5. InnoDB存储引擎物理存储结构
5.1 表空间(tablespace) ***
(1) MySQL5.5版本出现了共享表空间模式(移植了Oracle)
实现了较为方便的扩容功能,但是所有的表数据都集中在几个文件中,管理十分不方便.
(2) MySQL 5.6中 ,默认使用独立表空间模式实现数据的存储.
保留了共享表空间,只用来存储系统相关数据(数据字典+undo+tmp表空间)
把用户表数据和索引单独存储(独立表空间)
(3) MySQL 5.7
保留了共享表空间ibdata1,只用来存储系统相关数据(数据字典+undo),undo在5.7 手工配置将他独立出来=
(4) MySQL 8.0
保留了共享表空间ibdata1,只用来存储系统相关数据(dw,cb)
undo自动独立出来,移除了数据字典的存储.
5.2 表空间管理
查看表空间模式:
mysql> select @@innodb_file_per_table;
共享表空间的设置:
mysql> select @@innodb_data_file_path;
ibdata1:332M;ibdata2:128M:autoextend
一般情况下: 安装MySQL ,提前设置好
mysqld --initialize-insecure ....
my.cnf
ibdata1:512M:ibdata2:512M:autoextend
5.3 表的物理存储介绍
t1表:
ibd : 数据行
frm : 数据字典部分信息(列,列属性)
ibdata1 : 整个数据库的数据字典(所有表的列信息,列属性....),undo
ib_logfileN : redo事务日志
5.4 表空间迁移(快速迁移部分表数据)
(1) 准备一个新环境
(2) 创建和原表结构一样的表
show create table t1 ;
create ....
(3) 删除空表的ibd表空间文件
alter table t1 discard tablespace;
(4) cp 原表的ibd表空间到新环境
[root@db01 test]# cp -a t1.ibd /data/mysql/data_3307/db1
(5) 导入表空间文件.
alter table t1 import tablespace;
5.5 学员的项目
案例背景:
硬件及软件环境:
联想服务器（IBM）
磁盘500G 没有raid
centos 6.8
mysql 5.6.33 innodb引擎 独立表空间
备份没有，日志也没开
开发用户专用库:
jira(bug追踪) 、 confluence(内部知识库) ------>LNMT
故障描述:
断电了，启动完成后“/” 只读
fsck 重启,系统成功启动,mysql启动不了。
结果：confulence库在 ， jira库不见了
学员求助内容:
求助：
这种情况怎么恢复？
我问：
有备份没
求助：
连二进制日志都没有，没有备份，没有主从
我说：
没招了，jira需要硬盘恢复了。
求助：
1、jira问题拉倒中关村了
2、能不能暂时把confulence库先打开用着
将生产库confulence，拷贝到1:1虚拟机上/var/lib/mysql,直接访问时访问不了的
问：有没有工具能直接读取ibd
我说：我查查，最后发现没有
我想出一个办法来：
表空间迁移:
create table xxx
alter table confulence.t1 discard tablespace;
alter table confulence.t1 import tablespace;
虚拟机测试可行。
处理问题思路:
confulence库中一共有107张表。
1、创建107和和原来一模一样的表。
他有2016年的历史库，我让他去他同时电脑上 mysqldump备份confulence库
mysqldump -uroot -ppassw0rd -B confulence --no-data >test.sql
拿到你的测试库，进行恢复
到这步为止，表结构有了。
2、表空间删除。
select concat('alter table ',table_schema,'.'table_name,' discard tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
source /tmp/discard.sql
执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。
set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。
3、拷贝生产中confulence库下的所有表的ibd文件拷贝到准备好的环境中
select concat('alter table ',table_schema,'.'table_name,' import tablespace;') from information_schema.tables where table_schema='confluence' into outfile '/tmp/discad.sql';
4、验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）
网友评论