MySQL ¿Cómo restaurar tablespace?

MySQL ¿Cómo restaurar tablespace?

Esta no es información nueva, pero no la he cubierto mucho, así que la dirijo ahora para aquellos que la necesitan.

Si pierdes tus archivos ibd ... pierdes tus datos. Entonces, si tiene una copia de una disponible ... o incluso si está sincronizando desde otra base de datos, aún puede importarla. ¿Qué / cómo pierdes tablespace?

Aquí hay un ejemplo simple para recuperar 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;

Ahora almacenamos algunos datos ...


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 ahora vamos a romperlo ...


# 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`.

Espacio de tabla roto y perdido ... Ahora podemos recuperarlo ..


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 |
+----------------+--------+----------+---------------------------------------------------------+

Note que ahora también tenemos otro error. Esto generalmente está vinculado al espacio disponible para tmpdir, y la reparación no funciona para .ibd de todos modos.


mysql> select @@tmpdir;
+----------+
| @@tmpdir |
+----------+
| /tmp |
+----------+

# vi /etc/my.cnf
tmpdir=/var/lib/mysql-files/

# systemctl restart mysqld
# mysql demo

OK usé el directorio mysql-files solo por ejemplo.
Ahora podemos intentarlo de nuevo.


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 funcionó
Ahora, todo esto es agradable y simple si solo tienes una mesa. Pero ¿qué hay de los 100?

Automatícelo, por supuesto, y use su esquema de información para ayudar.

Haga algunas copias más para la prueba.


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)

Romperlos a todos ..


demo]# mv *.ibd /tmp/

Ahora, utilizando su tabla information_schema.tables, puede construir todos los comandos que necesitará.


# 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)

Y funcionó.