MySQL How do you restore tablespace

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.



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.

MySQL Binlogs:: How to recover

So I realized I had not made a post about this after this situation that recently came up.

Here is the scenario: A backup was taken at midnight, they used MySQL dumps per database. Then at ten am the next day the database crashed. A series of events happened before I was called in, but they got it up to a version of the database with MyISAM tables and the IBD files missing from the tablespace.

So option 1, restoring from backup would get us to midnight and we would lose hours of data. Option 2, we reimport the 1000's of ibd files and keep everything. Then we had option 3, restore from backup, then apply the binlogs for recent changes.

To make it more interesting, they didn't have all of the ibd files I was told, and I did see some missing. So not sure how that was possible but option 2 became an invalid option. They, of course, wanted the least data loss possible, so we went with option 3.

To do this safely I started up another instance of MySQL under port 3307. This allowed me a safe place to work while traffic had read access to the MyISAM data on the port 3306 instance.

Once all the backup dump files uncompressed and imported into the 3307 instance I was able to focus on the binlog files.

At first this concept sounds much harder risky than it really is. It is actually pretty straight forward and simple.

So first you have to find the data your after. A review of the binlog files gives you a head start as to what files are relevant. In my case, somehow they managed to reset the binlog so the 117 file had 2 date ranges within it.

First for binlog review, the following command outputs the data in human-readable format.

mysqlbinlog --defaults-file=/root/.my.cnf  --base64-output=DECODE-ROWS  --verbose mysql-bin.000117 >   review_mysql-bin.000117.sql


*Note... Be careful running the above command. Notice I have it dumping the file directly in same location as binlog. So VALIDATE that your file name is valid.  This mysql-bin.000117.sql is different than this mysql-bin.000117 .sql  . You will loose your binlog with the 2nd option and a space before .sql.

Now to save the data so it can be applied. Since I had several binlogs I created a file and I wanted to double-check the time ranges anyway.


mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-09 00:00:00" --stop-datetime="2019-07-10 00:00:00" mysql-bin.000117 > binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000118 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf mysql-bin.000119 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --start-datetime="2019-07-10 00:00:00" --stop-datetime="2019-07-10 10:00:00" mysql-bin.000117 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000120 >> binlog_restore.sql
mysqlbinlog --defaults-file=/root/.my.cnf --stop-datetime="2019-07-10 10:00:00" mysql-bin.000121 >> binlog_restore.sql

mysql --socket=/var/lib/mysql_restore/mysql.sock -e "source /var/lib/mysql/binlog_restore.sql"



Now I applied all the data from those binlogs for the given time ranges. The client double-checked all data and was very happy to have it all back.

Several different options existed for this situation, this happened to workout best with the client.

Once the validated all was ok on the restored version it was a simple stop both databases, moved the data directories (wanted to keep the datadir defaults intact) , chown the directories just to be safe and start up MySQL. Now the restored instance was up on port 3306.