MySQL How do you restore tablespace

Дата публикации: 13-07-2019 05:23:00

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 import_tablespace.sql
# mysql -N < build_discard.sql  | mysql demo
demo]# cp /tmp/*.ibd .
demo]# chown mysql:mysql *.ibd
# systemctl restart mysqld
# mysql demo 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 How do you restore tablespace appeared first on MariaDB.org

Основное содержимое страницы с новостью.

Схожие новости

#	Наименование новости	Тональность	Информативность	Дата публикации
1	Migration of your data from one database to another	0	5.05	17-11-2022
2	MySQL Binlogs:: How to recover	0	5.9	13-07-2019
3	Shrinking the InnoDB system tablespace	0	7.28	17-06-2024
4	Using your FRM file to get Schema and then import idb files..	0	4.63	12-11-2020
5	Backups Using the MySQL Clone Operation	0	4.93	28-07-2026
6	MySQL GTID tags and binlog events	0	5.43	02-12-2024
7	MariaDB has broken the concept of dynamically configurable buffer pools!	0	10.18	09-02-2026
8	Understanding InnoDB Tablespace Duplicate Check (MySQL Startup with Many Tables)	0	4.76	19-11-2024
9	How to downgrade MariaDB or MySQL	0	6.01	21-05-2024
10	Partial physical database restore for MariaDB and MySQL	0	5.64	02-07-2024

Классификация: . Схожих патентов: 0. Схожих новостей: 10. Тональность: 0. Информативность: 7.63. Источник: mariadb.org.