MySQL运维—存储引擎众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

一、Mysql支持的存储引擎
1、查看mysql支持的存储引擎，mysql5.5以前默认的存储引擎是MyISAM

2、支持的第三方存储引擎
项目：监控系统架构改造（innodb-》tokudb,5.6-5.7）
TokuDB（mariadb 5.7以上版本自带）: insert快2-3倍，压缩比8-10倍，用于监控类（zabbix）
myrocks
3、修改表存储引擎

二、Innodb与MyIsam的区别
1、Innodb的功能

2、区别
Innodb存储引擎与MyISAM区别
Innodb MyISAM
MVCC（多版本并发控制）不支持
锁（行级别）表级别
外键不支持
事务**** 不支持
热备温备
CSR（断电时，故障自动恢复）不支持

三、表空间介绍及设置
1、查看存储引擎

Show engines;
show create table world.city;
setect tabte_schema,tabtename，engine
from information_schema.tabtes
Where engine='innodb';

SELECT @@default_storage_engine;
show table status like 'city';
show tabte status like 'city'\G;

2、表空间的管理模式

表空间（TBS）管理模式：

独立表空间：每个表单独使用表空间存储
-rw-r----- 1 mysql mysql 8780 Jul 5 10:27 engine_cost.frm
-rw-r----- 1 mysql mysql 98304 Jul 5 10:27 engine_cost.ibd

期中ibd文件，被称之为表空间数据文件

共享表空间：所有的表数据统一存放到一起
-rw-r----- 1 mysql mysql 12582912 Jul 5 10:53 ibdata1

3、独立表仝司存储文件
独立表空间：
t1.ibd —–>tl表的数据和索引
t1.frm —–>tl表的列相关信息

4、版本及表空间问题
innodbfile_per_table=l

mysql5.5默认：共享表空间

学员问题：他公司的zabbix使用的是mysq15.5默认是共享表空间，
过一段时间，土bdatal文件越来越大，zabbix很慢，500G
建议：
1)升级5.7版本
2)tokudb
5.6以后版本：表默认管理模式是独立表空间，共享表空间也存在只不过，共享表空间只存一些系统表，以及undo日志，t临时表等

5、表空间查看修改方法
1)共享表空间
我们建议，在初始化数据的时候就配置好
一般建议2．3个，512M

vim /etc/my.cnf 
innodb_data_file_path=iddata1:76M;ibdata2:50M:autoextend 
mysqt_instaltdb

2）独立表空间
一个表一个ibd文件，存储表数据和索引

show variables like 'innodb_file_per_table';

innodb的表

表数据和索引：tl.ibd
列的基本信息：tl.frm
其他的属性信息：基表
alter table tl discard tablespace; 将表的ibd文件删掉
alter table tl import tablespace, 将ibd文件导入回来

6、information—-schema介绍
information_schema查是查询“基表”数据方法，查询方法是select
tabtes存储的是什么？
系统中所有表相关属性信息的总体统计信息

7、表的相关信息

mysql>desctables，
TABLE_SCHEMA --->表所在的库
TABLE_NAME --->表名字
ENGINE --->表的存储引擎
TABLEROWS --->表的行数
AVG_ROW_LENGTH --->每行平均占用的磁盘空间大小（字节为单位）
INDEX_LENGTH --->索引占用空间大小（字节为单位）

8、例子

查询所有厍下的所有表名
SELECT table_schema,table_name FROM information_schema.TABLES;

统计每个库下的表的个数
SELECT tab1e_schema,COUNT(tablename）
FROM information_schema.TABLES
GROUP BY tab1e_schema;

统计一下每个库的占用磁盘空间的总大小
SELECT teable_schema, SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mk
FROM information_schema.TABLES
GROUP BY table_schema;

查询Innodb的表信息
SELECT tab1e_schema,tab1ename,ENGINE
FROM information_schema.`TABLES`
WHERE ENGINE='innodb'；

9、concat()函数使用

concat()函数使用
mysql>select concat("hello",name)from world.city_new countrycode='CHN';

假如一个库中有107张表，都要执行以下语句
例子：atter table otdboy.xxx discard tabtespace;
select concat("alter table otdboy.",table_name," discard tablespace:")from information_schema.tables;

四、基于数据库文件恢复的案例
1、环境讲解
jira(bug追踪）、confluence(内部知识库）
————————
联想服务器（IBM)
磁盘500G没有raid
centos6·8
mysqt5．6．33 innodb引擎独立表空间
备份没有，日志也没开
————————

编译-制作rpm
/usr/bin/mysql
/var/lib/mysql
confulence jira
所有软件和数据都在”/”

2、问题记录
断电了，启动完成后“/”只读
fsck重启
启动mysql启动不了
结果：confulence库在,jira库不见了
这种情况怎么恢复？
连二进制日志都没有，没有备份，没有主从
没招了，jira需要硬盘恢复了。
求助：
1)jira问题拉倒中关村了
2)能不能暂时把confutence库（107张表）先打开用着
将生产库confulence，拷贝到1：1虚拟机上/var/lib/mysql，直接访问时访问不了的

3、思路分析
问：有没有工具能笪接读取文bd
我说：我查查，最后发现没有

想出一个办法来：

create table xxx
atter tabte confulence.tl discard tablespace;
atter table confulence.tl import tablespace;

4、面临的问题
confuence库中一共有107张表需要，创建107和和原，一模一样的表。

5、解决司题
表空间删除：

select concat('alter table',table_schema,'.'table_name,'discard tablespace;')from information_schema.tabtes
where tabte_schema='conftuene' into outfile '/tmp/discad.sqt';
source /tmp/discard.sql

执行过程中发现，有20-30个表无法成功。主外键关系
很绝望，一个表一个表分析表结构，很痛苦。
set foreign_key_checks=0 跳过外键检查。
把有问题的表表空间也删掉了。

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

select concat('altertable,table_schema,'.'tablename,'import tablespace;') from information_schema.tables
where table_schema='confluence' into outfile '/tmp/discad.sql';

验证数据
表都可以访问了，数据挽回到了出现问题时刻的状态（2-8）

6、解决司题发现
他有2016年的历史库，我让他去他同事电脑上mysqldump备份confulence库
mysqtdump -uroot -ppassword -B confulence –no-data >test.sql
拿到你的测试库，进行恢复
到这步为止，表结构有了。

文章版权归作者所有，未经允许请勿转载。

THE END