Recuperar una BD rota!

Ayer salvamos de las cenizas (base de datos ilegible, corrupción de datos, posible falla de disco duro) cuatro meses de trabajo colectivo… ni yo sé cómo – ahora vamos repetir el experimento porque la otra base de datos rota ¡es la de la mismíma numérica latina en red! Por suerte quedó un rastro del trip anterior, notas sueltas, por ejemplo:

La clave:

docker run -it -v /tmp/my.cnf:/etc/mysql/conf.d/my.cnf -e MYSQL_ROOT_PASSWORD=alguna-pass -v /srv/BACKUP/alguna_db/_data/:/var/lib/mysql mariadb:10.3.22 bash

Aquí básicamente estamos iniciando un contenedor de mariadb (mysql) con dos particularidades:

una configuración en /etc/mysql/conf.d/my.cnf que se salta algunos pasos para ayudarla a iniciar.

[mysqld]
innodb_force_recovery = 3

monta en el lugar de la base de datos el repaldo de la base de datos rota, a la que se le hizo una recomposición.

Recomponiendo data

En este caso, el archivo que se habia ‘rayado’ en el disco era ibdata1, fue el único que no se copió al respaldo
En un volúmen vacío, inicié una base de datos nueva, a la que importé el último respaldo que existía (enero), en formato .sql
El archivo ibdata1 generado por esta base de datos, lo copié y pegué en el respaldo, era la pieza que faltaba, pesaban lo mismo: 76 Mb.

Al entrar a la basede datos así recompuesta, este fue el momento eureka: pude iniciarla, usando la opción –skip-grant-tables

root@cea015ea25a7:/# mysql -u root
root@cea015ea25a7:/# /usr/sbin/mysqld --skip-grant-tables --general-log
2020-04-28 15:26:47 0 [Note] InnoDB: !!! innodb_force_recovery is set to 3 !!!
2020-04-28 15:27:54 4 [ERROR] InnoDB: Expected tablespace id 69 but found 111 in the file ./web_db/wp_posts.ibd
2020-04-28 15:27:54 4 [ERROR] InnoDB: Table web_db.wp_posts is corrupted. Please drop the table and recreate.

Ahora, iniciaba, pero las tablas todavía eran ilegibles.

Tablespace

El primer mensaje daba un indicio de por qué, me imaginé que ese tablespace sería como un índice de las tablas, que difería por haber sido creado en otra base de datos… busqué por ahí y ahí fue que encontré el enlace de la solución https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.htm

Al final donde dice «Restoring Orphaned File-Per-Table ibd Files» : restaurar archivos ibd huérfanos…
eso parecía ser lo que necesitaba.

Resumiendo, el procedimiento es borrar la tabla, re crearla, descartar su tablespace así:

ALTER TABLE numerica.wp_posts DISCARD TABLESPACE;

Luego copiar encima el archivo .ibd correspondiente, y finalmente importar el nuevo tablespace:

ALTER TABLE numerica.wp_posts IMPORT TABLESPACE; SHOW WARNINGS;

Como esta secuencia hay que repetirla para cada tada tabla, pude desarrollar como procedimiento sus distintas etapas.

Procedimiento*

En la base de datos en recomposición: borro la tabla y la re-creo, luego descarto su tablespace

--SQL
SET @d_table = "wp_comments";
SET @d = CONCAT("DROP TABLE ", @d_table);
PREPARE stmt1 FROM @d;
EXECUTE stmt1;

La borro también ‘físicamente’ en el contenedor de la base de datos:

tt=wp_comments
rm /var/lib/mysql/numerica_DB/$tt.ibd

De vuelta en la base…

-- sql nuevamente
set @tt = "wp_comments";
SET @cr = CONCAT("show create table ", @tt); 
PREPARE crea FROM @cr; 
EXECUTE crea;

-- mas sql
SET @d_table = "wp_comments";
SET @d2 = CONCAT("ALTER TABLE ", @d_table, " DISCARD TABLESPACE");
PREPARE stmt2 FROM @d2;
EXECUTE stmt2;

En el servidor, copio el archivo .ibd de respaldo

d_table=wp_comments &&\

cp /var/lib/docker/volumes/latina_numerica_data/_data/numerica_DB/$d_table.ibd  /srv/BACKUP/crasj/latina_numerica_data/_data/numerica_DB/$d_table.ibd &&\

chown systemd-coredump:systemd-coredump  /srv/BACKUP/crasj/latina_numerica_data/_data/numerica_DB/$d_table.ibd &&\
chmod g+w  /srv/BACKUP/crasj/latina_numerica_data/_data/numerica_DB/$d_table.ibd

Finalmente, de vuelta en la base de datos

-- SQL
SET @d3 = CONCAT("ALTER TABLE ", @d_table, " IMPORT TABLESPACE");
PREPARE stmt3 FROM @d3;
EXECUTE stmt3;

Opcionalmente, para contar datos recuperados:

SET @d4 = CONCAT("SELECT COUNT(*) FROM ", @d_table);
PREPARE stmt4 FROM @d4;
EXECUTE stmt4;

Esto se debe repetir para cada tabla!

La prueba de fuego:

MariaDB [rk_DB]> select count() from wp_posts; 
+----------+ 
| count() |
+----------+
| 8081 |
+----------+
1 row in set (0.004 sec)

MariaDB [rk_DB]> select post_date from wp_posts order by post_date desc limit 5;
+---------------------+
| post_date |
+---------------------+
| 2020-04-27 22:08:11 |
| 2020-04-27 16:47:53 |
| 2020-04-27 16:47:53 |
| 2020-04-27 16:45:56 |
| 2020-04-27 16:44:23 |
+---------------------+
5 rows in set (0.418 sec)

EL enlace

https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html

Otros enlaces:

TODO DE NUEVO

¿Confus@?, Retrocedamos desde cero.

1- RESPALDO

Volviendo desde cero, siempre lo primero es tomar un respaldo (backup).

rsync -av /var/lib/docker/volumes/numerica_data/_data /srv/BACKUP/crasj/numerica_data/_data

Efectivamente estamos de nuevo en la misma situacion, el archivo dañado es solamente ibdata1

rsync: read errors mapping "/var/lib/docker/volumes/latina_numerica_data/_data/ibdata1": Input/output error (5)
ERROR: _data/ibdata1 failed verification -- update discarded.

El resto se respalda bien

sent 878,020,724 bytes received 6,108 bytes 11,477,474.93 bytes/sec
total size is 798,092,082 speedup is 0.91

2 – Recomponer ibdata1

En este caso le pegaré un ibdata1 de un respaldo viejo de medio año…

$ cp /srv/BACKUP/numerica_lab/data/_data/ibdata1 /srv/BACKUP/crasj/latina_numerica_data/_data/

$ chown systemd-coredump:systemd-coredump /srv/BACKUP/crasj/latina_numerica_data/_data/ibdata1 &amp;&amp;\

$ chmod g+w /srv/BACKUP/crasj/latina_numerica_data/_data/ibdata1

3 – innodb_force_recovery = 3

Luego creamos un archivo, por ejemplo, en /tmp/my.cnf, donde agregamos la configuracion innodb_force_recovery

[mysqld]
innodb_force_recovery = 1

https://docs.oracle.com/cd/E17952_01/mysql-8.0-en/innodb-recovery.html

Este archivo lo montaremos en /etc/mysql/conf.d/my.cnf al iniciar la base de datos en el siguiente paso.

(Nótese que esta vez intento con el valor 1, antes resultó con 3. Esto solamente lo hago para obtener maor información de un segundo experimento.)

4 – Iniciar base de datos en recomposición

Con esto podemos tratar de iniciarla

# iniciar la base de datos en un contenendor
$ docker run -it -v /tmp/my.cnf:/etc/mysql/conf.d/my.cnf -e MYSQL_ROOT_PASSWORD=alguna-pass -v /srv/BACKUP/alguna_db/_data/:/var/lib/mysql mariadb:10.3.22 bash
# de algun modo me meto a este contenendor...
root@caffee:$ su mysql
$ mysqld

E inicia!

2020-04-29 15:42:43 0 [Note] InnoDB: !!! innodb_force_recovery is set to 1 !!!
2020-04-29 15:42:43 0 [Note] mysqld: ready for connections.
Pero no puede leer las tablas:
2020-04-29 15:42:43 0 [ERROR] InnoDB: Could not find a valid tablespace file for numerica_db`.`wp_terms.
2020-04-29 15:42:43 0 [Warning] InnoDB: Ignoring tablespace for numerica_lab_DB.wp_terms because it could not be opened.
2020-04-29 15:42:43 0 [ERROR] InnoDB: Operating system error number 2 in a file operation.
2020-04-29 15:42:43 0 [ERROR] InnoDB: The error means the system cannot find the path specified.

y desde otra consola entro con

mysql -u root

MariaDB [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| una_DB |
| performance_schema |
+--------------------+
4 rows in set (0.000 sec)
MariaDB [(none)]> use una_DB
MariaDB [una_DB]> select count(*) from wp_posts;
ERROR 1932 (42S02): Table 'numerica_DB.wp_posts' doesn't exist in engine

(3.2 -) innodb_force_recovery = 3

Con innodb_force_recobery = 1 dice que la tabla no existe, ni se puede decartar el tablespace (lo cual es parte del procedimiento a continuación).

ERROR 1813 (HY000): Tablespace for table 'numerica_DB.wp_commentmeta' exists. Please DISCARD the tablespace before IMPORT
ERROR 1146 (42S02): Table 'numerica_DB.wp_commentmeta' doesn't exist

En cambio con nivel 3 el log es el mismo que antes

[ERROR] InnoDB: Expected tablespace id 113 but found 77 in the file ./numerica_DB/wp_woocommerce_tax_rates.ibd

Confirmo entonces, después de intentarlo en innodb_force_recovery 1 y 2, que tiene que ser 3 (tres), de lo contrario es imposible ejecutar el procedimiento, ya que no se puede descartar el tablespace.

5 – Ejecutar procedimiento

Ahora estamos en condiciones de ejecutar el procedimiento, para cada una de las tablas….

6 – Pasos finales

6.1 Exportación a un dump sql

Una vez recuperadas todas las tablas, desde la base de datos recompuesta, tomamos un respaldo (dump) en formato .sql (instrucciones de base de dato)

db=numerica
mysqldump --skip-lock-tables $db > /srv/BACKUP/$db.sql

6.2 – Importación del dump

Este comando finamente importa el resultado de la base de datos recompuesta (en sql), a una base de datos nueva

docker run -it -e MYSQL_ROOT_PASSWORD=xxx -e MYSQL_DATABASE=numerica_DB -e MYSQL_USER=xxx -e MYSQL_PASSWORD=xxx -v /var/lib/docker/volumes/numerica_data/_data/:/var/lib/mysql -v /srv/BACKUP/numerica_DB/:/docker-entrypoint-initdb.d/:ro mariadb:10.3.22

La clave:

Recomponiendo data

Tablespace

Procedimiento*

La prueba de fuego:

EL enlace

Otros enlaces:

TODO DE NUEVO

1- RESPALDO

2 – Recomponer ibdata1

3 – innodb_force_recovery = 3

4 – Iniciar base de datos en recomposición

(3.2 -) innodb_force_recovery = 3

5 – Ejecutar procedimiento

6 – Pasos finales

6.1 Exportación a un dump sql

6.2 – Importación del dump

Cotización

Blog

Archivo Numérica

Contacto

Telegram

Links

Pagos en

Recuperar una BD rota!

La clave:

Recomponiendo data

Tablespace

Procedimiento*

La prueba de fuego:

EL enlace

Otros enlaces:

TODO DE NUEVO

1- RESPALDO

2 – Recomponer ibdata1

3 – innodb_force_recovery = 3

4 – Iniciar base de datos en recomposición

(3.2 -) innodb_force_recovery = 3

5 – Ejecutar procedimiento

6 – Pasos finales

6.1 Exportación a un dump sql

6.2 – Importación del dump

Cotización

Blog

Temáticas

Archivo Numérica