表空间管理

2022-01-25 19:13:43

共享表空间

查看

查看表空间信息：

mysql> select @@innodb_data_file_path;
+-------------------------+
| @@innodb_data_file_path |
+-------------------------+
| ibdata1:12M:autoextend  |
+-------------------------+
1 row in set (0.00 sec)

mysql> select @@innodb_autoextend_increment;
+-------------------------------+
| @@innodb_autoextend_increment |
+-------------------------------+
|                            64 |
+-------------------------------+
1 row in set (0.00 sec)

上述两个参数的作用：ibdata1 文件，默认初始大小 12M，不够用会自动扩展，默认每次扩展 64M。

扩容

后期扩容

先查看原来 ibdata1 实际大小：

# 进入 datadir 目录
$ ll -h ibdata*
-rw-r----- 1 mysql mysql 12M May 10 14:04 /data/3306/data/ibdata1

在配置文件将原来的 ibdata1 设定为和实际大小一致，其它文件的大小可以随意指定：

$ vim /etc/my.cnf
innodb_data_file_path=ibdata1:12M;ibdata2:100M;ibdata3:100M:autoextend

重启 MySQL，查看会发现新的 ibdata 文件已经生成并且是我们指定的大小：

# 进入 datadir 目录
$ ll -h ibdata*
-rw-r----- 1 mysql mysql  12M May 10 14:06 ibdata1
-rw-r----- 1 mysql mysql 100M May 10 14:06 ibdata2
-rw-r----- 1 mysql mysql 100M May 10 14:06 ibdata3

试一下如果上面的 ibdata1 文件没有按原来设置会是啥情况：

$ vim /etc/my.cnf
innodb_data_file_path=ibdata1:76M;ibdata2:100M;ibdata3:100M:autoextend

重启 MySQL，报错信息如下：

2020-05-10T06:26:59.831836Z 0 [ERROR] InnoDB: The innodb_system data file './ibdata1' is of a different size 768 pages (rounded down to MB) than the 4864 pages specified in the .cnf file!
2020-05-10T06:26:59.831862Z 0 [ERROR] InnoDB: Plugin initialization aborted with error Generic error
2020-05-10T06:27:00.439187Z 0 [ERROR] Plugin 'InnoDB' init function returned error.
2020-05-10T06:27:00.439230Z 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
2020-05-10T06:27:00.439236Z 0 [ERROR] Failed to initialize builtin plugins.
2020-05-10T06:27:00.439262Z 0 [ERROR] Aborting

上面报错很明显了，即 ibdata1 的大小和 my.cnf 文件中指定的大小不一致，它的实际大小是 768 pages，而我们指定的大小为 4864 pages，一个 page 的大小是 16KB，计算一下它的大小：

768 pages = (768*16/1024)M = 12M
4964 pages = (4864*16/1024)M = 76M

所以在后期扩容时我们一定要保证已有的 ibdata1 文件大小和配置中是一致的。

初始化时设置

5.7 中建议：设置共享表空间 2-3 个，大小建议 512M 或者 1G，最后一个定制为自动扩展。
8.0 中建议：设置 1 个就 ok，大小建议 512M 或者 1G。
清理数据：

$ /etc/init.d/mysqld stop
$ rm -rf /data/3306/data/*
$ vim /etc/my.cnf
innodb_data_file_path=ibdata1:100M;ibdata2:100M;ibdata3:100M:autoextend

重新初始化：

$ mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data

重启数据库生效：

$ /etc/init.d/mysqld start

独立表空间

在 5.6 版本后可以配置独立表空间，独立表空间是针对用户数据、单独的存储管理，仅存储表的数据行和索引。

查看独立表空间设定是否开启：

mysql> select @@innodb_file_per_table;
+-------------------------+
| @@innodb_file_per_table |
+-------------------------+
|                       1 |
+-------------------------+
1 row in set (0.00 sec)

我这里使用的是 MySQL 5.7，所以是默认开启的。

测试

在开启情况下测试创建表 t1：

mysql> use test;
mysql> create table t1 (id int primary key auto_increment,name varchar(24));

$ ll
total 112
-rw-r----- 1 mysql mysql    65 May 10 14:42 db.opt
-rw-r----- 1 mysql mysql  8586 May 10 14:43 t1.frm
-rw-r----- 1 mysql mysql 98304 May 10 14:43 t1.ibd

即在开启独立表空间设定的情况下每个表有独立的 idb 文件存储其索引信息和数据行信息。

测试一下关闭独立表空间设定，然后创建表：

mysql> use test;
mysql> set global innodb_file_per_table=0;
mysql> create table t2(id int primary key auto_increment,name varchar(24) );
Query OK, 0 rows affected (0.00 sec)

$ ll
total 124
-rw-r----- 1 mysql mysql    65 May 10 14:42 db.opt
-rw-r----- 1 mysql mysql  8586 May 10 14:43 t1.frm
-rw-r----- 1 mysql mysql 98304 May 10 14:43 t1.ibd
-rw-r----- 1 mysql mysql  8586 May 10 14:44 t2.frm

即在关闭独立表空间设定的情况下每个表仅有独立的 frm 文件保存该表的元数据信息，而其索引数据和行数据被保存在共享表空间即 ibdata 中。

数据迁移

这里的数据迁移是基于独立表空间的迁移，目标是将 3306 实例中 test 库的 t100w 表的数据无损迁移到 3307 实例的 test 库中。

1、登入 3306 实例锁定 t100w 表，并获取建表语句：

mysql> lock tables test.t100w read;
mysql> show create table t100w;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | 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 |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2、登入 3307 实例建立 t100w 表，然后删除表空间文件：

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;
mysql> alter table test.t100w discard tablespace;
Query OK, 0 rows affected (0.08 sec)

3、拷贝 3306 实例的 t100w 表的 ibd 文件到目标端目录：

$ cp -a /data/3306/data/test/t100w.ibd /data/3307/data/test/

注意此处的权限问题。

4、在 3307 实例中导入 t100w 的表空间：

mysql> alter table test.t100w import tablespace;
Query OK, 0 rows affected, 2 warnings (0.19 sec)

5、在 3306 实例中解锁 t100w 表：

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

undo 表空间

作用：提供撤销功能。

存储位置： 5.7 版本，默认存储在共享表空间中（ibdataN）。8.0版本以后默认就是独立的（undo_001-undo_002）。

生产建议： 5.7 版本后，将 undo 手工进行独立。

配置

注意：undo 表空间仅可在初始化之前配置。

1、配置参数如下：

$ vim /etc/my.cnf
# 添加参数
innodb_undo_tablespaces=3           
innodb_max_undo_log_size=128M
innodb_undo_log_truncate=ON
innodb_purge_rseg_truncate_frequency=32

2、初始化数据库并启动：

$ mysqld --initialize-insecure --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data
$ systemctl start mysqld

3、查看数据目录生成的 undo 表空间文件如下：

$ ll /data/3306/data/undo*
-rw-r----- 1 mysql mysql 10485760 May 10 15:35 /data/3306/data/undo001
-rw-r----- 1 mysql mysql 10485760 May 10 15:35 /data/3306/data/undo002
-rw-r----- 1 mysql mysql 10485760 May 10 15:35 /data/3306/data/undo003

注： 8.0 undo表空间与 5.7 稍有区别，可参考：https://dev.mysql.com/doc/refman/8.0/en/innodb-undo-tablespaces.html

临时表空间

作用：存储临时表。

配置：

$ vim /etc/my.cnf
innodb_temp_data_file_path=ibtmp1:12M;ibtmp2:128M:autoextend:max:500M

重启生效。

建议数据初始化之前设定好，一般 2-3 个，大小 512M-1G。