MySQL如何恢復表空間？ 這不是新的信息，但我沒有多說，所以現在為那些需要它的人解決它。 如果您丟失了ibd文件......您將丟失數據。 因此，如果您有一個可用的副本..或者即使您從另一個數據庫同步，您仍然可以導入它。 什麼/你如何失去表空間？ 這是一個恢復表空間的簡單示例。
mysql> Create database demo;
mysql> use demo;
mysql> CREATE TABLE `demotable` (
-> `id` int(11) NOT NULL AUTO_INCREMENT,
-> `dts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
-> PRIMARY KEY (`id`)
-> ) ENGINE=InnoDB;
現在我們存儲一些數據......
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.10 sec)
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
+----+---------------------+
2 rows in set (0.00 sec)
好的，現在讓我們打破它..
# systemctl stop mysqld
# cd /var/lib/mysql/demo/
# ls -ltr
total 80
-rw-r-----. 1 mysql mysql 114688 Jul 12 23:31 demotable.ibd
# mv demotable.ibd /tmp/
# systemctl start mysqld
# mysql demo
mysql> show tables;
+----------------+
| Tables_in_demo |
+----------------+
| demotable |
+----------------+
1 row in set (0.00 sec)
mysql> desc demotable;
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| dts | timestamp | NO | | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+-------+-----------+------+-----+-------------------+-----------------------------------------------+
2 rows in set (0.01 sec)
mysql> INSERT INTO demotable (id) VALUES (NULL);
ERROR 1812 (HY000): Tablespace is missing for table `demo`.`demotable`.
破損和丟失的表空間......現在我們可以恢復它了..
demo]# cp /tmp/demotable.ibd .
mysql> ALTER TABLE demotable DISCARD TABLESPACE;
demo]# cp /tmp/demotable.ibd .
demo]# ls -ltr
total 112
-rw-r-----. 1 root root 114688 Jul 12 23:50 demotable.ibd
demo]# chown mysql:mysql demotable.ibd
demo]# mysql demo
mysql> ALTER TABLE demotable IMPORT TABLESPACE;
ERROR 1034 (HY000): Incorrect key file for table 'demotable'; try to repair it
mysql> REPAIR TABLE demotable;
+----------------+--------+----------+---------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+----------------+--------+----------+---------------------------------------------------------+
| demo.demotable | repair | note | The storage engine for the table doesn't support repair |
+----------------+--------+----------+---------------------------------------------------------+
現在註意我們還有另一個錯誤..這通常與tmpdir可用的空間有關，而且無論如何修復都不適用於.ibd。
mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+
# vi /etc/my.cnf
tmpdir=/var/lib/mysql-files/
# systemctl restart mysqld
# mysql demo
OK只使用了mysql-files目錄。 現在我們可以再試一次。
mysql> ALTER TABLE demotable IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (0.61 sec)
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.11 sec)
mysql> SELECT * FROM demotable;
+----+---------------------+
| id | dts |
+----+---------------------+
| 1 | 2019-07-12 23:31:34 |
| 2 | 2019-07-12 23:31:35 |
| 3 | 2019-07-12 23:56:08 |
+----+---------------------+
好的工作。 現在，如果您只有一張桌子，這一切都很簡單。 但是100多歲...... 當然，自動化它，並使用您的information_schema來提供幫助。 再做幾個副本進行測試。
mysql> create table demotable1 like demotable;
Query OK, 0 rows affected (0.51 sec)
mysql> create table demotable2 like demotable;
Query OK, 0 rows affected (1.04 sec)
mysql> create table demotable3 like demotable;
Query OK, 0 rows affected (0.74 sec)
mysql> create table demotable4 like demotable;
Query OK, 0 rows affected (2.21 sec)
打破他們所有..
demo]# mv *.ibd /tmp/
現在使用您的information_schema.tables表，您可以構建所需的所有命令。
# vi build_discard.sql
# cat build_discard.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," DISCARD
TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE
TABLE_SCHEMA='demo';
# vi build_import.sql
# cat build_import.sql
SELECT CONCAT(" ALTER TABLE ",TABLE_SCHEMA,".",TABLE_NAME," IMPORT
TABLESPACE; ") as CMD FROM information_schema.TABLES WHERE
TABLE_SCHEMA='demo';
# mysql -N < build_import.sql > import_tablespace.sql
# mysql -N < build_discard.sql | mysql demo
demo]# cp /tmp/*.ibd .
demo]# chown mysql:mysql *.ibd
# systemctl restart mysqld
# mysql demo < import_tablespace.sql
# mysql demo
mysql> INSERT INTO demotable (id) VALUES (NULL);
Query OK, 1 row affected (0.08 sec)
mysql> INSERT INTO demotable1 (id) VALUES (NULL);
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO demotable2 (id) VALUES (NULL);
Query OK, 1 row affected (0.09 sec)
mysql> INSERT INTO demotable3 (id) VALUES (NULL);
^[[AQuery OK, 1 row affected (0.37 sec)
mysql> INSERT INTO demotable4 (id) VALUES (NULL);
Query OK, 1 row affected (0.12 sec)
它奏效了。
