存储引擎-2

存储引擎的查看

查看MySQL默认存储引擎

select @@default_storage_engine;

查看表存储引擎

select table_schema,table_name ,engine from information_schema.tables where table_schema not in ('sys','mysql','information_schema','performance_schema');

存储引擎的修改

修改存储引擎

create table t222 (id int,name varchar(20)) engine=myisam;

show table status like 't222'\G

alter table t222 engine=innodb;

show table status like 't222'\G

整理碎片

平常处理过的MySQL问题--碎片处理

环境:centos7.4、MySQL 5.7.20、InnoDB存储引擎

业务特点：数据量级较大，经常需要按月删除历史数据

问题：磁盘空间占用很大，不释放

处理方法：

以前：

将数据逻辑导出，手工truncate表，然后导入进去(delete：删除行或所有行，但不释放空间；truncate：删除表所有行，释放空间)

现在：

对表进行按月进行分表(partition,中间件)

业务替换为truncate方式（释放空间）

定期进行碎片整理

批量替换zabbix 100多张表， innodb为tokudb

alter table zabbix.a engine=tokudb;

select concat("alter table ",table_schema,".",table_name," engine=tokudb;") from information_schema.tables

where table_schema='zabbix';

InnoDB存储引擎物理存储结构

5.6版本：

由于MySQL 5.5版本共享表空间的缺陷，5.6版本后增加了独立表空间的概念，即将“数据字典(元数据)”、“表的列信息”、“表的数据、索引”分开存储，Redo log的主要作用是用于数据库的崩溃恢复，undo日志即撤销还原，用于记录更改前的一份copy，在操作出错时，可以用于回滚、撤销还原，只将数据库逻辑地恢复到原来的样子

5.7版本

5.7版本下，临时表也被独立出来了(临时表空间，对应Linux中的文件为：ibtmp1)。可以从上图中看出，Genreal tablespaces中存储着一些临时表，而在5.6 版本中，临时表是存储在ibdata1中的Tables中

8.0版本

8.0版本中，Doublewrite、undo信息也被独立出来了

MySQL5.7文件介绍

查看MySQL数据目录

select @@datadir;

auto.cnf：mysql实例的UUID号，主从群集中不允许重复

ibdata1：系统数据字典信息(统计信息、元数据信息、information_schema)、UNDO(回滚)表空间等数据

ib_logfile0 ~ ib_logfile1： REDO日志文件，事务日志文件

ibtmp1：临时表空间磁盘位置，存储临时表

frm：存储表的列信息

ibd：表的数据行和索引

表空间（Tablespace）

共享表空间

5.5版本出现的管理模式，也是默认的管理模式。需要将所有数据存储到同一个表空间中，管理比较混乱

5.6版本以后数据表（表空间）独立，共享表空间保留，只用来存储数据字典信息、undo、临时表

5.7 版本临时表被独立出来了

8.0版本undo也被独立出去了

共享表空间设置(在搭建MySQL时，初始化数据之前设置到参数文件中)

select @@innodb_data_file_path;

show variables like '%extend%';

mysqld --initialize-insecure --user=mysql --basedir=xxxxxx......

innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend

注释：autoextend 每次最大自增空间64MB

独立表空间

从5.6版本默认表空间不再使用共享表空间，替换为独立表空间。主要存储的是用户数据

存储特点为：一个表一个ibd文件，存储数据行和索引信息

最终结论

一张InnoDB表= frm+ibd+ibdata1

MySQL的存储引擎日志：

Redo Log（重做日志）：ib_logfile0 ib_logfile1

Undo Log（回滚日志）：ibdata1 ibdata2(存储在共享表空间中)

ibtmp1（临时表）：在做join union操作产生临时数据，用完就自动清理

独立表空间设置

select @@innodb_file_per_table;

真实故障案例

案例背景：

硬件及软件环境:

联想服务器（IBM）

磁盘500G 没有raid（raid50）

centos 6.8

mysql 5.6.33 innodb引擎独立表空间

备份没有，日志也没开

开发用户专用库：

track(bug追踪) 、 inner(内部数据库) ------>LNMT （linux+nginx+mysql+tomcat）app测试平台

故障描述:

断电了，启动系统失败，"/"只读

fsck 重启,系统成功启动,mysql启动不了。

(注：centos6修复文件系统用fsck ，centos7修复文件系统用xfs_repair)

结果：inner库在，track库不见了

求助内容：

这种情况怎么恢复？

连二进制日志都没有，没有备份，没有主从

没办法，track库需要硬盘恢复

求助：

1、track问题找，数据修复公司

2、能不能暂时把inner库先打开用着

将生产库inner，拷贝到1:1虚拟机上/var/lib/mysql，直接访问时访问不了的

有没有工具能直接读取ibd?

最后发现没有

办法：

表空间迁移：

在新的mysql服务器上

create table t1;

alter table inner.t1 discard tablespace;

alter table t1 row_format=compact; //5.6升级5.7的时候需要执行

复制源ibd文件，改权限: chown mysql.mysql t1.ibd

alter table inner.t1 import tablespace;

虚拟机测试可行。

处理问题思路：

inner库中一共有107张表。

1、创建107张和原来一模一样的表。

开发电脑上 mysqldump备份inner库

mysqldump -uroot -ppassw0rd -B inner --no-data >test.sql

拿到测试库，进行恢复

到这步为止，表结构有了

2、表空间删除

select concat('alter table ',table_schema,'.',table_name,' discard tablespace;') from information_schema.tables where table_schema='test' into outfile '/tmp/discad.sql';

source /tmp/discard.sql

执行过程中发现，有20-30个表无法成功。主外键关系

很绝望，一个表一个表分析表结构，很痛苦

set foreign_key_checks=0 跳过外键检查。

把有问题的表表空间也删掉了

3、拷贝生产中inner库下的所有表的ibd文件拷贝到准备好的环境中

select concat('alter table ',table_schema,'.',table_name,' import tablespace;') from information_schema.tables where table_schema='test' into outfile '/tmp/import.sql';

4、验证数据

表都可以访问了，数据挽回到了出现问题时刻的状态