MySQL How do you restore tablespace?
This is not new information but I have not covered it much so addressing it now for those that need it.
If you lose your ibd files... you lose your data. So if you have a copy of one available.. or even if you are syncing from another database you can still import it. What/how do you lose tablespace?
Here is a simple example to recover tablespace.
Now we store some data...
OK now lets break it..
Broken and lost tablespace... Now we can recover it..
Notice now we also got another error.. This is usually tied to space available to tmpdir, and repair doesn't work for .ibd anyway.
OK used the mysql-files directory just for example.
Now we can try again.
OK worked.
Now, this is all nice and simple if you just have one table. But what about 100s...
Automate it, of course, and use your information_schema to help.
Make a few more copies for test.
break them all..
Now using your information_schema.tables table, you can build out all the commands you will need.
And it worked.
This is not new information but I have not covered it much so addressing it now for those that need it.
If you lose your ibd files... you lose your data. So if you have a copy of one available.. or even if you are syncing from another database you can still import it. What/how do you lose tablespace?
Here is a simple example to recover tablespace.
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;
Now we store some data...
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)
OK now lets break it..
# 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`.
Broken and lost tablespace... Now we can recover it..
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 |
+----------------+--------+----------+---------------------------------------------------------+
Notice now we also got another error.. This is usually tied to space available to tmpdir, and repair doesn't work for .ibd anyway.
mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+
# vi /etc/my.cnf
tmpdir=/var/lib/mysql-files/
# systemctl restart mysqld
# mysql demo
OK used the mysql-files directory just for example.
Now we can try again.
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 |
+----+---------------------+
OK worked.
Now, this is all nice and simple if you just have one table. But what about 100s...
Automate it, of course, and use your information_schema to help.
Make a few more copies for test.
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)
break them all..
demo]# mv *.ibd /tmp/
Now using your information_schema.tables table, you can build out all the commands you will need.
# 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)
And it worked.
