Mysql
MySQL 每個表一個文件不起作用
我已按照在 MySQL（v8，在 Ubuntu 20 上）中啟用每個表一個文件的所有說明進行操作：
- 添加了 innodb_file_per_table=ON
- 重新啟動 MySQL
- 一個一個地轉儲所有數據庫。
但是，當我重新導入數據庫時，所有內容都會寫入 ibdata1 文件。
但是，當我通過 MySQL 客戶端創建一個全新的數據庫並向其中添加一個隨機表時，數據將寫入其自己的文件夾 + table.ibd 文件。
知道我做錯了什麼嗎？
我嘗試了幾個故障排除步驟：
- 重啟
- 顯示像’innodb_file_per_table’這樣的變數；（值 = 開）
首先，innodb_file_per_table預設為 MySQL 8 啟用。
如果您想知道 ibdata1 中有哪些表，請執行以下查詢
select * from information_schema.innodb_tables;這將告知您每個表及其所在的表空間
例子
我旋轉了一個執行 MySQL 8.0.17 的流浪實驗室
我載入了聖經的KJV並添加了全文索引
這是我提到的查詢的輸出
mysql> select * from information_schema.innodb_tables; +----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+ | 1025 | mysql/db | 161 | 25 | 4294967294 | Dynamic | 0 | General | 0 | | 1026 | mysql/user | 161 | 54 | 4294967294 | Dynamic | 0 | General | 0 | | 1027 | mysql/default_roles | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | | 1028 | mysql/role_edges | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | | 1029 | mysql/global_grants | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | | 1030 | mysql/password_history | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | | 1031 | mysql/func | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | | 1032 | mysql/plugin | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | | 1054 | mysql/help_topic | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | | 1055 | mysql/help_category | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | | 1057 | mysql/help_relation | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | | 1036 | mysql/servers | 161 | 12 | 4294967294 | Dynamic | 0 | General | 0 | | 1037 | mysql/tables_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | | 1038 | mysql/columns_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | | 1056 | mysql/help_keyword | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | | 1040 | mysql/time_zone_name | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | | 1041 | mysql/time_zone | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | | 1042 | mysql/time_zone_transition | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | | 1043 | mysql/time_zone_transition_type | 161 | 8 | 4294967294 | Dynamic | 0 | General | 0 | | 1044 | mysql/time_zone_leap_second | 161 | 5 | 4294967294 | Dynamic | 0 | General | 0 | | 1045 | mysql/procs_priv | 161 | 11 | 4294967294 | Dynamic | 0 | General | 0 | | 1046 | mysql/component | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | | 1047 | mysql/slave_relay_log_info | 161 | 12 | 4294967294 | Dynamic | 0 | General | 0 | | 1048 | mysql/slave_master_info | 161 | 31 | 4294967294 | Dynamic | 0 | General | 0 | | 1049 | mysql/slave_worker_info | 161 | 16 | 4294967294 | Dynamic | 0 | General | 0 | | 1050 | mysql/gtid_executed | 161 | 6 | 4294967294 | Dynamic | 0 | General | 0 | | 1051 | mysql/server_cost | 161 | 7 | 4294967294 | Dynamic | 0 | General | 0 | | 1052 | mysql/engine_cost | 161 | 9 | 4294967294 | Dynamic | 0 | General | 0 | | 1053 | mysql/proxies_priv | 161 | 10 | 4294967294 | Dynamic | 0 | General | 0 | | 1058 | sys/sys_config | 33 | 7 | 1 | Dynamic | 0 | Single | 0 | | 1101 | bible/verses | 33 | 8 | 44 | Dynamic | 0 | Single | 0 | | 1102 | bible/fts_000000000000044d_being_deleted | 33 | 4 | 45 | Dynamic | 0 | Single | 0 | | 1103 | bible/fts_000000000000044d_being_deleted_cache | 33 | 4 | 46 | Dynamic | 0 | Single | 0 | | 1104 | bible/fts_000000000000044d_config | 33 | 5 | 47 | Dynamic | 0 | Single | 0 | | 1105 | bible/fts_000000000000044d_deleted | 33 | 4 | 48 | Dynamic | 0 | Single | 0 | | 1106 | bible/fts_000000000000044d_deleted_cache | 33 | 4 | 49 | Dynamic | 0 | Single | 0 | | 1107 | bible/fts_000000000000044d_00000000000000cd_index_1 | 33 | 8 | 50 | Dynamic | 0 | Single | 0 | | 1108 | bible/fts_000000000000044d_00000000000000cd_index_2 | 33 | 8 | 51 | Dynamic | 0 | Single | 0 | | 1109 | bible/fts_000000000000044d_00000000000000cd_index_3 | 33 | 8 | 52 | Dynamic | 0 | Single | 0 | | 1110 | bible/fts_000000000000044d_00000000000000cd_index_4 | 33 | 8 | 53 | Dynamic | 0 | Single | 0 | | 1111 | bible/fts_000000000000044d_00000000000000cd_index_5 | 33 | 8 | 54 | Dynamic | 0 | Single | 0 | | 1112 | bible/fts_000000000000044d_00000000000000cd_index_6 | 33 | 8 | 55 | Dynamic | 0 | Single | 0 | | 1113 | bible/books | 33 | 5 | 56 | Dynamic | 0 | Single | 0 | | 1150 | ft_db/ft_test_innodb | 33 | 6 | 93 | Dynamic | 0 | Single | 0 | | 1151 | ft_db/fts_000000000000047e_being_deleted | 33 | 4 | 94 | Dynamic | 0 | Single | 0 | | 1152 | ft_db/fts_000000000000047e_being_deleted_cache | 33 | 4 | 95 | Dynamic | 0 | Single | 0 | | 1153 | ft_db/fts_000000000000047e_config | 33 | 5 | 96 | Dynamic | 0 | Single | 0 | | 1154 | ft_db/fts_000000000000047e_deleted | 33 | 4 | 97 | Dynamic | 0 | Single | 0 | | 1155 | ft_db/fts_000000000000047e_deleted_cache | 33 | 4 | 98 | Dynamic | 0 | Single | 0 | | 1156 | ft_db/fts_000000000000047e_0000000000000107_index_1 | 33 | 8 | 99 | Dynamic | 0 | Single | 0 | | 1157 | ft_db/fts_000000000000047e_0000000000000107_index_2 | 33 | 8 | 100 | Dynamic | 0 | Single | 0 | | 1158 | ft_db/fts_000000000000047e_0000000000000107_index_3 | 33 | 8 | 101 | Dynamic | 0 | Single | 0 | | 1159 | ft_db/fts_000000000000047e_0000000000000107_index_4 | 33 | 8 | 102 | Dynamic | 0 | Single | 0 | | 1160 | ft_db/fts_000000000000047e_0000000000000107_index_5 | 33 | 8 | 103 | Dynamic | 0 | Single | 0 | | 1161 | ft_db/fts_000000000000047e_0000000000000107_index_6 | 33 | 8 | 104 | Dynamic | 0 | Single | 0 | +----------+-----------------------------------------------------+------+--------+------------+------------+---------------+------------+--------------+ 55 rows in set (0.00 sec) mysql> desc information_schema.innodb_tables; +---------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+---------------------+------+-----+---------+-------+ | TABLE_ID | bigint(21) unsigned | NO | | | | | NAME | varchar(655) | NO | | | | | FLAG | int(11) | NO | | | | | N_COLS | int(11) | NO | | | | | SPACE | bigint(21) | NO | | | | | ROW_FORMAT | varchar(12) | YES | | | | | ZIP_PAGE_SIZE | int(11) unsigned | NO | | | | | SPACE_TYPE | varchar(10) | YES | | | | | INSTANT_COLS | int(11) | NO | | | | +---------------+---------------------+------+-----+---------+-------+ 9 rows in set (0.00 sec) mysql>請注意，任何
space值為 0 的表都位於ibdata1. 它在MySQL 8.0 Docs 中提到 INFORMATION_SCHEMA.INNODB_TABLES；目前， 中沒有表
ibdata1。讓我們看看我是否可以將一個注入
ibdata1.mysql> use bible Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> mysql> mysql> set global innodb_file_per_table = 'OFF'; Query OK, 0 rows affected (0.00 sec) mysql> create table injection (id int not null auto_increment, name varchar(20), primary key (id)); Query OK, 0 rows affected (0.04 sec) mysql> insert into injection (name) values ('Hello, World !!!'); Query OK, 1 row affected (0.01 sec) mysql> select * from injection; +----+------------------+ | id | name | +----+------------------+ | 1 | Hello, World !!! | +----+------------------+ 1 row in set (0.00 sec) mysql>好的，現在讓我們檢查一下 information_schema
mysql> select * from information_schema.innodb_tables where space = 0; +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1162 | bible/injection | 33 | 5 | 0 | Dynamic | 0 | System | 0 | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) mysql>然後 ！！！
好的，現在開始執行
select * from information_schema.innodb_tables where space = 0;如果您沒有看到任何返回的行，那麼您在
ibdata1.如果我還有桌子
ibdata1怎麼辦？如果你是在 MySQL 中開始全新的。你不應該從一開始就有這個問題。儘管如此，假設您在 MySQL 8.0 中遇到這種情況。你怎麼把桌子拉出來
ibdata1？？？咱們試試吧
ALTER TABLE ... ENGINE=InnoDB不幸的是，您不能像以前那樣執行 NULL ALTER：
mysql> select * from information_schema.innodb_tables where space = 0; +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1162 | bible/injection | 33 | 5 | 0 | Dynamic | 0 | System | 0 | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) mysql> set global innodb_file_per_table = 'ON'; Query OK, 0 rows affected (0.00 sec) mysql> alter table bible.injection engine=InnoDB; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_tables where space = 0; +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1163 | bible/injection | 161 | 5 | 0 | Dynamic | 0 | System | 0 | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) mysql>你可以在 MySQL 5.7 和 back 中擺脫這個問題。對於 MySQL 8.0，使用的臨時表維護表的目前表空間元數據。
咱們試試吧
CREATE TABLE LIKEmysql> create table bible.injection2 like bible.injection; Query OK, 0 rows affected (0.03 sec) mysql> select * from information_schema.innodb_tables where space = 0; +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ | 1163 | bible/injection | 161 | 5 | 0 | Dynamic | 0 | System | 0 | | 1164 | bible/injection2 | 161 | 5 | 0 | Dynamic | 0 | System | 0 | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ 2 rows in set (0.00 sec) mysql> select @@global.innodb_file_per_table; +--------------------------------+ | @@global.innodb_file_per_table | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0.00 sec) mysql>好的哎喲！！！討厭的表空間元數據像行李一樣被拖來拖去。
咱們試試吧
ALTER TABLE ... TABLESPACE=innodb_file_per_table開始了 …
mysql> create table bible.injection2 like bible.injection; Query OK, 0 rows affected (0.03 sec) mysql> select * from information_schema.innodb_tables where space = 0; +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ | 1163 | bible/injection | 161 | 5 | 0 | Dynamic | 0 | System | 0 | | 1164 | bible/injection2 | 161 | 5 | 0 | Dynamic | 0 | System | 0 | +----------+------------------+------+--------+-------+------------+---------------+------------+--------------+ 2 rows in set (0.00 sec) mysql> select @@global.innodb_file_per_table; +--------------------------------+ | @@global.innodb_file_per_table | +--------------------------------+ | 1 | +--------------------------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE bible.injection2 TABLESPACE=innodb_file_per_table; Query OK, 0 rows affected (0.05 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_tables where space = 0; +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | TABLE_ID | NAME | FLAG | N_COLS | SPACE | ROW_FORMAT | ZIP_PAGE_SIZE | SPACE_TYPE | INSTANT_COLS | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ | 1163 | bible/injection | 161 | 5 | 0 | Dynamic | 0 | System | 0 | +----------+-----------------+------+--------+-------+------------+---------------+------------+--------------+ 1 row in set (0.00 sec) mysql> ALTER TABLE bible.injection TABLESPACE=innodb_file_per_table; Query OK, 0 rows affected (0.18 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from information_schema.innodb_tables where space = 0; Empty set (0.00 sec) mysql>然後 ！！！
結論
如果您懷疑有任何表格
ibdata1，請使用select * from information_schema.innodb_tables where space = 0;然後執行
ALTER TABLE mydb.mytable TABLESPACE=innodb_file_per_table;請記住：預設的innodb_file_per_table是
ON.無需 mysqldump 和重新載入。
如果您嘗試縮小 ibdata1，您將把數據載入到全新的安裝中。您不能像在 MySQL 5.5 和 back 中那樣刪除 ibdata1。
mysqlMySQL 5.6 + 在模式數據庫中有 innodb 表。因此，您不能玩相同的文件操作遊戲。MySQL 8.0 現在要求 DBA 給予更多的尊重。