表空间管理

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 表空间相关变量参数:

-- undo 表空间文件的个数,0 时表示 undo 表空间依旧还在 ibdata 中。
mysql> SELECT @@innodb_undo_tablespaces;
+---------------------------+
| @@innodb_undo_tablespaces |
+---------------------------+
|                         0 |
+---------------------------+
1 row in set (0.00 sec)
-- 单个 undo 表空间文件大小
mysql> SELECT @@innodb_max_undo_log_size;
+----------------------------+
| @@innodb_max_undo_log_size |
+----------------------------+
|                 1073741824 |
+----------------------------+
1 row in set (0.00 sec)
-- 是否让表空间自动回收。
mysql> SELECT @@innodb_undo_log_truncate;
+----------------------------+
| @@innodb_undo_log_truncate |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (0.00 sec)
-- 指定 purge 操作被唤起多少次之后才释放 rollback segments。当 undo 表空间里面的rollback segments  被释放时,undo表空间才会被 truncate。由此可见,该参数越小,undo 表空间被尝试 truncate 的频率越高。
mysql> SELECT @@innodb_purge_rseg_truncate_frequency;
+----------------------------------------+
| @@innodb_purge_rseg_truncate_frequency |
+----------------------------------------+
|                                    128 |
+----------------------------------------+
1 row in set (0.01 sec)

配置

注意: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。