问题描述：将information_schema导出sql文件到新库中恢复，sql中的表都是临时表，存储引擎都是memory，在导入的过程中实际大量会占用临时表。
报错信息：ERROR 1114 (HY000) at line 2397 in file: 'HKUATZABBIXDB-2022-10-06_22-24-01-information_schema.sql': The table 'INNODB_BUFFER_PAGE' is full
环境：mariadb10.6.4
1.排查磁盘空间是否占满
2.查询数据文件增长是否受限制
MariaDB [test01]> show variables like '%innodb_data_file_path%';
+-----------------------+------------------------+
| Variable_name | Value |
+-----------------------+------------------------+
| innodb_data_file_path | ibdata1:12M:autoextend |
+-----------------------+------------------------+
3.导入的都是临时表，查询临时表大小
MariaDB [(none)]> show variables like '%tmp%';
+----------------------------+----------------------+
| Variable_name | Value |
+----------------------------+----------------------+
| default_tmp_storage_engine | |
| encrypt_tmp_disk_tables | OFF |
| encrypt_tmp_files | OFF |
| innodb_tmpdir | |
| max_tmp_tables | 32 |
| slave_load_tmpdir | /tmp |
| tmp_disk_table_size | 18446744073709551615 |
| tmp_memory_table_size | 16777216 |
| tmp_table_size | 16777216 |
| tmpdir | /tmp |
+----------------------------+----------------------+
10 rows in set (0.001 sec)
4.修改临时表大小，重启数据库，要导入的information_schema文件，所以临时表暂时调大
[mysqld]
tmp_table_size = 2048M
max_heap_table_size = 2048M
5.查看information_schema建表语句，使用临时表，存储引擎放在memory中。这种内存临时表在show tables时查询不到，但是可以被直接select看到，退出当前会话就会消失
DROP TABLE IF EXISTS `INNODB_BUFFER_PAGE`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TEMPORARY TABLE `INNODB_BUFFER_PAGE` (
`POOL_ID` int(11) unsigned NOT NULL DEFAULT 0,
`BLOCK_ID` bigint(21) unsigned NOT NULL DEFAULT 0,
`SPACE` int(11) unsigned NOT NULL DEFAULT 0,
`PAGE_NUMBER` int(11) unsigned NOT NULL DEFAULT 0,
`PAGE_TYPE` varchar(64) DEFAULT NULL,
`FLUSH_TYPE` int(11) unsigned NOT NULL DEFAULT 0,
`FIX_COUNT` int(11) unsigned NOT NULL DEFAULT 0,
`IS_HASHED` int(1) NOT NULL DEFAULT 0,
`NEWEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
`OLDEST_MODIFICATION` bigint(21) unsigned NOT NULL DEFAULT 0,
`ACCESS_TIME` bigint(21) unsigned NOT NULL DEFAULT 0,
`TABLE_NAME` varchar(1024) DEFAULT NULL,
`INDEX_NAME` varchar(64) DEFAULT NULL,
`NUMBER_RECORDS` bigint(21) unsigned NOT NULL DEFAULT 0,
`DATA_SIZE` bigint(21) unsigned NOT NULL DEFAULT 0,
`COMPRESSED_SIZE` bigint(21) unsigned NOT NULL DEFAULT 0,
`PAGE_STATE` enum('NOT_USED','MEMORY','REMOVE_HASH','FILE_PAGE') NOT NULL,
`IO_FIX` enum('IO_NONE','IO_READ','IO_WRITE','IO_PIN') NOT NULL,
`IS_OLD` int(1) NOT NULL DEFAULT 0,
`FREE_PAGE_CLOCK` bigint(21) unsigned NOT NULL DEFAULT 0
) ENGINE=MEMORY DEFAULT CHARSET=utf8mb3;
内存临时表和磁盘是临时表
-- 内存中的临时表：
create temporary table mytrans
(
itemnumber int,
groupnumber int,
branchnumber int
)engine = memory
; -- 创建一个同样结构的临时表 就是普通的临时表 MySQL 会默认存储引擎是 innoDB
create temporary table mytran
(
itemnumber int,
groupnumber int,
branchnumber int
);
结论
1.information_schema可以作为sql文件导入到新的数据库中，sql文件中的表都是以内存临时表的形式存在
2.内存临时表和磁盘临时表 show tables都查询不到，但是可以被select，退出当前会话，临时表就会释放
3.可以修改tmp_table_size、max_heap_table_size 来解决The table 'INNODB_BUFFER_PAGE' is full的问题
