MySQL的Innodb存储引擎（一）

ClickHouse周边 2021-03-16

488

InnoDB On-Disk Structures 物理结构（磁盘结构）

介绍：表空间的概念源于Oracle数据库。最初的目的是为了能够很好的做存储的扩容。

位置：The InnoDB Storage Engine --->InnoDB On-Disk Structures --->Tablespaces

1. 共享表空间（系统表空间）

① 表空间存储方式：ibdata1~ibdataN

5.5+版本默认的表空间存储类型。

ibdata1存储：系统数据，日志，undo，临时表，用户数据（索引段+数据段）。
frm存储：存每张表的数据字典信息

看完上面的存储结构，那么问题来了,Innodb表中拷贝xxx.frm和xxx.ibd文件到其他实例中，是不是代表备份了这个数据表？

事实其实不是。因为没有拷贝全局的数据字典。xxx.frm和xxx.ibd文件只是部分数据，缺失的ibdataN中还有系统相关数据，日志信息、临时表信息。需要拷贝时，关库将存储的整个目录拷贝相应目录上。

那myisam表中拷贝user.frm、user.MYD和user.MYI文件到其他实例中，可以使用吗？

可以。因为myisam是没有全局的数据字典信息（系统数据信息）。但是权限必须正确，重启生效。

② IbdataN共享表空间在各个版本的变化

InnoDB数据字典由包含元数据的用于跟踪对象的如表，索引，和表中的列的内部系统表。元数据实际上位于InnoDB系统表空间中。由于历史原因，数据字典元数据在某种程度上与InnoDB表元数据文件（.frm文件--->desc city）中存储的信息重叠。

官方文档链接：https://dev.mysql.com/doc/refman/5.7/en/innodb-architecture.html

5.5版本

共享表空间存系统数据和用户数据

系统相关：（全局）数据字典信息、Undo回滚信息、DoubleWrite buffer信息、临时表信息、change buffer

(全局）数据字典信息	表基本结构信息、状态、系统参数、属性等。相当于备份了所有表的数据字典之和。
Undo回滚信息	用来撤销操作的日志类似修改前先备份，改错了，备份回来。
DoubleWrite buffer信息	双写机制
临时表信息	比如order by 先排序，再去重，找一个地生成，系统处理，用完就丢
change buffer(insert buffer)	使用Change buffer 功能是临时缓冲辅助索引需要的更新数据。当需要查询新insert 的数据，会在内存中进行merge(合并)操作，此时辅助索引就是最新的。

用户数据：表数据行、表的索引数据

5.6版本

共享表空间只存储系统数据，把用户数据独立了（*.ibd文件独立出来），独立表空间管理。

系统相关：（全局）数据字典信息、Undo回滚信息、DoubleWrite buffer信息、临时表信息、change buffer

5.7版本

在5.6版本基础上，并把临时表独立出来（ibtmp1），Undo也可以人为设定独立。

系统相关：（全局）数据字典信息、Undo回滚信息、DoubleWrite buffer信息、change buffer

8.0.19版本

在5.7版本的基础上，将UNDO回滚信息默认独立（undo_00x），数据字典不再集中存储到共享表空间了，mysql库下的.ibd文件。

系统相关：DoubleWrite buffer信息、change buffer

8.0.20版本

在之前版本基础上，独立DoubleWrite buffer信息。

系统相关：change buffer

官方文档15.6.4双写缓冲区：在MySQL 8.0.20之前，doublewrite缓冲区存储区位于InnoDB系统表空间中，从MySQL 8.0.20开始，doublewrite缓冲区存储区位于doublewrite文件中。

独立存储作用：破坏一个文件，不影响全局，保证数据安全性

③共享表空间管理

搭建新环境时，初始化：mysql --initalize-insecure

初始化时是未设置共享表空间，默认一个：ibdata1，ibdata1:12M:autoextend ibdata1文件，默认初始大小12M，不够会自动扩展，默认每次扩展64M

-- 查询表空间的名称，大小：
select @@innodb_data_file_path;

查询默认扩展大小：
show variables like "%increm%"；
select @@innodb_autoextend_increment；64
复制

如何扩容共享表空间？

第一历程：查看表空间的大小
# ls -lh ibdata1
-rw-r----- 1 mysql mysql 12M May  7 10:04 ibdata1

第二历程：# vim etc/my.cnf
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend

第三历程：重启数据库
# systemctl restart mysqld

# ll -h ibdata*
-rw-r----- 1 mysql mysql  12M May  7 11:29 ibdata1
-rw-r----- 1 mysql mysql 100M May  7 11:29 ibdata2
-rw-r----- 1 mysql mysql 100M May  7 11:29 ibdata3

注意：ibdata1的大小是实际的大小，否则会报错

如果不一致：innodb_data_file_path=ibdata1:8M;ibdata2:100M;ibdata3:100M:autoextend
重启报错：# etc/init.d/mysqld restart
Shutting down MySQL.... SUCCESS!
Starting MySQL. ERROR! The server quit without updating PID file (/server/3306/data/db01.pid).
错误日志信息：
# tail -30 server/3306/data/db01.err
[ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than the 640 pages specified in the .cnf file!
错误提示得出：ibdata1文件的实际大小：
# awk "BEGIN{print 768*16/1024}"      768页*16KB/1024=12M
12
设置大小：
# awk "BEGIN{print 640*16/1024}"
10
复制

④ 在初始化时设置共享表空间（生产建议）

5.7 版本建议设置共享表空间2~3个，大小512M或者1G，最后一个定制为自动扩展

‍

第一历程：修改配置文件
# vim etc/my.cnf
innodb_data_file_path=ibdata1:1024M;ibdata2:1024M;ibdata3:1024M:autoextend

第二历程：初始化
# mysqld --initialize-insecure --user=mysql --basedir=/server/app/mysql --datadir=/server/3306/data

第三历程：重启
# systemctl restart mysqld
8.0 版本建议设置1个就ok,大小512M或者1G。
复制

2. 独立表空间

5.6版本中，针对用户数据，单独的存储管理，存储表的数据行和索引

① 查看独立表空间：

select @@innodb_file_per_table;
1      单独的存储管理      创建的表，磁盘所对应的文件：t1.frm和t1.ibd
0      共享的表空间存储    创建的表，磁盘所对应的文件：t1.frm，没有.ibd文件
复制

演示:
当前是独立的存储管理

mysql> create database t1;
mysql> create table t1(id int);

# ll server/3306/data/t1/
-rw-r----- 1 mysql mysql    65 May  7 12:07 db.opt
-rw-r----- 1 mysql mysql  8556 May  7 12:07 t1.frm
-rw-r----- 1 mysql mysql 98304 May  7 12:07 t1.ibd

mysql> set global innodb_file_per_table=0;
mysql> create table t2(id int);
mysql> insert into t2 values (1);

# ll server/3306/data/t1/
-rw-r----- 1 mysql mysql    65 May  7 12:07 db.opt
-rw-r----- 1 mysql mysql  8556 May  7 12:07 t1.frm
-rw-r----- 1 mysql mysql 98304 May  7 12:07 t1.ibd
-rw-r----- 1 mysql mysql  8556 May  7 12:34 t2.frm
此时存储的数据在ibdata中。
复制

② 利用独立表空间进行快速数据迁移

原理：
select @@innodb_file_per_table;
1                  --->      单独的存储管理
复制

通过.ibd文件做数据迁移

前提：接收端必须有ibdata文件和.frm文件

源端：3306 /test/t100w ----> 目标端：3307 /test/t100w

第一历程：锁定源端t100w表，只读
mysql> lock tables test.t100w write;
mysql> show create table t100w;
CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

第二历程：目标端创建test库和t100w的空表
mysql> create database test charset="utf8mb4";
mysql> use test
mysql> CREATE TABLE `t100w` (
`id` int(11) DEFAULT NULL,
`num` int(11) DEFAULT NULL,
`k1` char(2) DEFAULT NULL,
`k2` char(4) DEFAULT NULL,
`dt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


第三历程：单独删除目标端空的表空间文件（保留t100w的frm,ibdata1中关于t100w的系统数据）
# ll /server/3307/data/test/
-rw-r----- 1 mysql mysql    67 May  7 12:46 db.opt
-rw-r----- 1 mysql mysql  8662 May  7 12:52 t100w.frm
-rw-r----- 1 mysql mysql 98304 May  7 12:52 t100w.ibd


mysql> alter table test.t100w discard tablespace;
# ll /server/3307/data/test/
-rw-r----- 1 mysql mysql   67 May  7 12:46 db.opt
-rw-r----- 1 mysql mysql 8662 May  7 12:52 t100w.frm


第四历程：拷贝源端ibd文件到目标端目录，并设置权限    如果不是单独的表空间管理，此时是没有.ibd文件的
# cp /server/3306/data/test/t100w.ibd /server/3307/data/test/
# chown  -R mysql.mysql /server/3307/


第五历程：导入表空间
mysql> alter table test.t100w import tablespace;
mysql> select count(*) from test.t100w;
+--------------+
| count(*)     |
+--------------+
|  1000000   |
+--------------+


第六历程：解锁源端数据表
mysql> unlock tables;
复制

好，今天的内容先介绍到这里，关于迁移表空间恢复数据、Undo表空间，临时表空间等等，后续文章一一赘述。

mysql

文章转载自ClickHouse周边，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

MySQL的Innodb存储引擎（一）

评论

相关阅读