mysql存储引擎 ,逻辑结构,存储结构,表空间迁移

宏观:

库,存储在操作系统目录中

表:

微观:

段 区 页

一个表就是一个段,mysql分配空间时至少分配一个区,每个区默认时1M(64个page页),mysql最小的IO单元就是PAGE(16KB)

mysql存储引擎 ,逻辑结构,存储结构,表空间迁移

InnoDB 核心

1、事务(Transaction)
2、MVCC(Multi-Version Concurrency Control多版本并发控制)
3、行级锁(Row-level Lock)
4、ACSR(Auto Crash Safey Recovery)自动的故障安全恢复
5、支持热备份(Hot Backup)
6、复制Replication: Group Commit , GTID (Global Transaction ID) ,多线程(MTS,Multi-Threads-SQL )

5. 存储引擎操作类命令

5.1 使用 SELECT 确认会话存储引擎
SELECT @@default_storage_engine;

mysql> show variables like '%engine%';

## 5.2 默认存储引擎设置(不代表生产操作)

会话级别:
set default_storage_engine=myisam;
全局级别(仅影响新会话):
set global default_storage_engine=myisam;
重启之后,所有参数均失效.
如果要永久生效:
写入配置文件
vim /etc/my.cnf
[mysqld]
default_storage_engine=myisam
存储引擎是表级别的,每个表创建时可以指定不同的存储引擎,但是我们建议统一为innodb.

扩展:
在线修改MySQL参数:
会话级别,例如:
set default_storage_engine=myisam;
功能:只会影响到当前会话
全局级别,例如:
set global default_storage_engine=myisam;
功能: 不影响当前和历史会话,只影响到新开的会话
以上两种方法,在重启之后会失效,除非参数添加至my.cnf

5.3 SHOW 确认每个表的存储引擎:

SHOW CREATE TABLE City\G;
SHOW TABLE STATUS LIKE 'CountryLanguage'\G

5.4 INFORMATION_SCHEMA 确认每个表的存储引擎

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

Master [world]>show table status;
Master [world]>show create table city;

mysql存储引擎 ,逻辑结构,存储结构,表空间迁移

mysql存储引擎 ,逻辑结构,存储结构,表空间迁移

修改一个表的存储引擎

>alter table t1 engine innodb;
注意:此命令我们经常使用他,进行innodb表的碎片整理

生产需求:
将oldboy数据库下的所有1000表,存储引擎从MyISAM替换为innodb

select concat("alter table ",table_name," engine innodb;")
from information_schema.tables
where table_schema='oldboy'
into outfile '/tmp/alter.sql';

mysql存储引擎 ,逻辑结构,存储结构,表空间迁移

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

环境:centos7.4,MySQL 5.7.20,InnoDB存储引擎
业务特点:数据量级较大,经常需要按月删除历史数据.
问题:磁盘空间占用很大,不释放
处理方法:
以前:将数据逻辑导出,手工drop表,然后导入进去
现在:
对表进行按月进行分表(partition,中间件)
业务替换为truncate方式

定期执行:
alter table t1 engine='innodb';

扩展:如何批量修改
需求:将zabbix库中的所有表,innodb替换为tokudb
select concat("alter table zabbix.",table_name," engine tokudb;") from
information_schema.tables where table_schema='zabbix' into outfile '/tmp/tokudb.sql';

InnoDB存储引擎物理存储结构

6.0 最直观的存储方式(/data/mysql/data)

  • ibdata1:系统数据字典信息(统计信息),UNDO(回滚)表空间等数据
  • ib_logfile0 ~ ib_logfile1: REDO(重做日志)日志文件,事务日志文件。
  • ibtmp1: 临时表空间磁盘位置,存储临时表
  • frm:存储表的列信息
  • ibd:表的数据行和索引

6.1 表空间(Tablespace)

6.1.1、共享表空间

需要将所有数据存储到同一个表空间中 ,管理比较混乱
5.5版本出现的管理模式,也是默认的管理模式。(数据字典,undo,临时表,索引,表数据)
5.6版本以,共享表空间保留,只用来存储:数据字典信息,undo,临时表。
5.7 版本,临时表被独立出来了
8.0版本,undo也被独立出去了
具体变化参考官方文档:
​​​https://dev.mysql.com/doc/refman/5.6/en/innodb-architecture.html​​​https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html
https://dev.mysql.com/doc/refman/5.8/en/innodb-architecture.html

6.1.2 共享表空间设置
共享表空间设置(在搭建MySQL时,初始化数据之前设置到参数文件中)
[(none)]>select @@innodb_data_file_path; #ibdata1:10M:autoextend
[(none)]>show variables like '%extend%'; #innodb_autoextend_increment 64
innodb_data_file_path=ibdata1:512M:ibdata2:512M:autoextend
innodb_autoextend_increment=64

6.1.3 独立表空间

从5.6,默认表空间不再使用共享表空间,替换为独立表空间。
主要存储的是用户数据
存储特点为:一个表一个ibd文件,存储数据行和索引信息
基本表结构元数据存储:
xxx.frm
最终结论:
元数据 数据行+索引
mysql表数据 =(ibdataX+frm)+ibd(段、区、页)
DDL DML+DQL

MySQL的存储引擎日志:
Redo Log: ib_logfile0 ib_logfile1,重做日志
Undo Log: ibdata1 ibdata2(存储在共享表空间中),回滚日志
临时表:ibtmp1,在做join union操作产生临时数据,用完就自动

6.1.4 独立表空间设置问题
db01 [(none)]>select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
| 1 |
+-------------------------+

alter table city dicard tablespace;
alter table city import tablespace;

.1.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)

快速导入数据

如果你有.ibd文件的一个干净的备份,你可以按如下操作从被起源的地方恢复它到MySQL安装中:相当快速

1. 发出这个ALTER TABLE语句:

2. ALTER TABLE tbl_name DISCARD TABLESPACE;
警告:这个语句删除当前.ibd文件。

3. 把备份的.ibd文件放回到恰当的数据库目录。

4. 发出这个ALTER TABLE语句:

5. ALTER TABLE tbl_name IMPORT TABLESPACE;

MYSQL表空间迁移。
  表空间迁移。
  有如下原因你可能需要将INNODB表复制到不同的数据库服务器上。
不增加生产负载的情况下生成 一个报表
在一个新的服务器上建立一个和生产上数据相同的表
做一个备份在发生问题或错误操作时用于恢复
快速将数据从一个服务器迁移到另一个服务器
  命令FLUSH TABLES ... FOREXPORT 使.ibd文件保持一致的状态。只有文件处于一致的状态我们才可以复制它。这个文件也会同时创建一个扩展名.cfg的二进制的文件。命令ALTER TABLE ...IMPORT TABLESPACE 会使用这个二进制文件对导入过程进行校验。
  对于 MySQL 5.6.8版本, ALTER TABLE ...IMPORT TABLESPACE 命令不再一定需要一个扩展名为.cfg二进制文件了。但如果真的没有这个文件我们会收到下面这样一个警告。
  Message:INNODB: IO READ error: (2, NO such FILE OR DIRECTORY) Error opening '.\
  test\t.cfg',will attempt TO IMPORT without SCHEMA verification
  1row IN SET (0.00 sec)
  这个特性有时候还是很有用的。比如,在模式不匹配的导入过程中,或者在一些需要恢复的情景下,元数据又不能从.ibd文件获得,则这个命令不需要一个扩展名为.cfg的二进制文件就可以导入的特性就很有用。
  可迁移表空间的限制:
innodb_file_per_table 一定要打开成 ON. 在共享表空间上的表不能使用这个特性。
当表处理静默状态时,只有只读语句可以使用这张表。
当导入表空间时,目的库的页尺寸要和源库的页尺寸相匹配。
DISCARD TABLESPACE 不支持分区表。如果你在分区表上使用命令 ALTER TABLE ... DISCARD TABLESPACE 你会看到如下错误: ERROR 1031 (HY000): 表引擎没有这个选项。
DISCARD TABLESPACE 命令不支持有父子关系的表。如果 FOREIGN_KEY_CHECKS 被设置成1. 在使用命令之前我们可以将这一参数设置为0. FOREIGN_KEY_CHECKS=0.
ALTER TABLE ... IMPORT TABLESPACE 命令在导入表时不会检查主外键关系。
如果是实时复制的时候, innodb_file_per_table 必需在主服务和从服务上设置为ON。

表空间迁移