MySQL运维—存储引擎

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

图片[1]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维-存储引擎 - 众客华禹众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹
2、支持的第三方存储引擎
项目:监控系统架构改造(innodb-》tokudb,5.6-5.7)
TokuDB(mariadb 5.7以上版本自带): insert快2-3倍,压缩比8-10倍,用于监控类(zabbix)
myrocks
3、修改表存储引擎

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

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

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

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

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
喜欢就支持一下吧
点赞1 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容