Recuperación de MariaDB - Tablas del Sistema Corruptas

Incidente Crítico

Fecha: 17 de octubre de 2025
Sistema: MariaDB 11.8.3-MariaDB-ubu2404
Severidad: ⚠️ Crítica – Servicio no inicia Nivel técnico: Administrador de Sistemas Avanzado (leer notas finales)

1. Resumen del Problema

El servidor MariaDB no inicia debido a corrupción de tablas del sistema.

Error principal: Tablas de sistema implicadas en el fallo múltiple

2025-10-17  6:27:08 3 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:27:08 3 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:27:08 3 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:27:08 3 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:27:08 3 [ERROR] mariadbd: Table './sitelight2_ma3/ma_api_token_table' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/column_stats'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/columns_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/event'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/func'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/global_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_category'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_keyword'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_relation'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/help_topic'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/index_stats'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/proc'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/procs_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/proxies_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/roles_mapping'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/tables_priv'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_leap_second'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_name'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_transition'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/time_zone_transition_type'
2025-10-17  6:27:12 4 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/global_priv'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:28:01 5 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:28:01 5 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:01 6 [ERROR] mariadbd: Got error '144 "Table is crashed and last repair failed"' for './mysql/table_stats'
2025-10-17  6:29:01 6 [ERROR] mariadbd: Table 'table_stats' is marked as crashed and last (automatic?) repair failed
2025-10-17  6:29:24 43 [Note] Found 15228 of 2114 rows when repairing './sitelight2_ma3/ma_api_token_table'
2025-10-17  6:29:29 150 [Note] Found 3138 of 2928 rows when repairing './sitelight2_matest/ma_bug_file_table'

Tablas afectadas:

• mysql.plugin
• mysql.servers
• mysql.db
• mysql.proxies_priv
• ...

2. Estado del Servicio

Active: failed (Result: exit-code)
Main PID: 1101684 (code=exited, status=1/FAILURE)
Status: "MariaDB server is down"

---

3. Análisis Preliminar

3.1. Secuencia del log:

• Inicio normal de InnoDB: buffer pool y undo tablespaces OK
• Resize de redo log: 96MB → 16MB (LSN=1718365178)
• Socket creado en 0.0.0.0:3306
• Falla: Error 144 al abrir tablas de privilegios
• MariaDB aborta

3.2. Características del entorno:

• Motor tablas sistema: InnoDB (no MyISAM)
• Buffer pool: 64MB
• Instrucciones CPU: AVX512
• I/O: io_uring
• Rollback segments: 128 (en 3 undo tablespaces)

3.3. Causas probables:

• Apagado inesperado del sistema
• Fallos de disco (errores I/O)
• Falta de espacio en disco (Confirmado)
• Corrupción de memoria
• Bug en proceso de resize de redo log

---

4. Plan de Corrección

FASE 1: Diagnóstico Inicial

# 1. Chequear integridad del sistema
df -h /var/lib/mysql
dmesg | grep -i "error\|i/o"
smartctl -H /dev/sda

# 2. Revisar logs
tail -200 /var/log/mysql/error.log

# 3. Confirmar tipo de archivos
cd /var/lib/mysql/mysql/
ls -lh *.ibd | head -20

---

FASE 2: Backup Preventivo

Importante: Realizar backup completo antes de realizar cambios.

Opción A local si hay espacio en disco

Crea archivo de recuperación:

# 1. Verificar espacio disponible
df -h /var/lib/
du -sh /var/lib/mysql/

# 2. Realizar backup
cd /var/lib/
tar -czf mysql-backup-$(date +%Y%m%d-%H%M%S).tar.gz mysql/

# 3. Verificar integridad del backup
tar -tzf mysql-backup-*.tar.gz | head -20

Se entiende que el motor de bases de datos MySQL|MariaDB no esta activo

Opción B: Backup Remoto on-the-fly (Espacio local insuficiente)

• **Escenario: **Incidente causado por falta de espacio en disco, ahora solventado pero insuficiente para crear backup local.
• Requisito: SSH configurado con autenticación por llave pública. Comando único (one-liner):

systemctl stop mariadb && cd /var/lib/ && tar -czf - mysql/ | ssh usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"

Variantes

# Con progreso visual (requiere pv)
systemctl stop mariadb && cd /var/lib/ && tar -czf - mysql/ | pv | ssh usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"

# Con compresión paralela (requiere pigz, más rápido)
systemctl stop mariadb && cd /var/lib/ && tar -cf - mysql/ | pigz | ssh usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"

# Con SSH optimizado para transferencia rápida
systemctl stop mariadb && cd /var/lib/ && tar -czf - mysql/ | ssh -c aes128-gcm@openssh.com -o Compression=no usuario@servidor-remoto "cat > /backups/mysql-$(hostname)-$(date +%Y%m%d-%H%M%S).tar.gz"

Verificación del remoto

# Listar archivo creado
ssh usuario@servidor-remoto "ls -lh /backups/mysql-*"

# Verificar integridad
ssh usuario@servidor-remoto "tar -tzf /backups/mysql-*.tar.gz | head -20"

FASE 3: Arranque en Modo Recovery (innodb_force_recovery)

c# Crear archivo de configuración de recuperación
cat > /etc/my.cnf.d/recovery.cnf << 'EOF'
[mysqld]
innodb_force_recovery = 1
EOF

# Intentar inicio
systemctl start mariadb
systemctl status mariadb

Aumenta el valor de innodb_force_recovery (1→6) si sigue fallando.
Referencia rápida:

Valor	Función
1	Ignora páginas corruptas
2	Deshabilita hilos en background
3	No ejecuta rollback de transacciones
4	No merge de insert buffer
5	No escaneo undo log
6	No aplica redo log (Modo Solo Lectura Crítico)

---

FASE 4: Reparación de Tablas

Con servidor en recovery se aconseja el script si hay muchas tablas

Se puede modificar para añadir las tablas que pedieran faltar en el ejemplo

#!/bin/bash
# repair_mariadb.sh

if ! mysqladmin ping &>/dev/null; then
    echo "ERROR: MariaDB no está corriendo"
    exit 1
fi

# Reparar tablas del sistema
mysql -u root << 'SQL'
USE mysql;
REPAIR TABLE column_stats,columns_priv,db,event,func,global_priv,plugin,proc,procs_priv,proxies_priv,roles_mapping,servers,tables_priv,user;
SQL

# Reparar todas las bases de datos
for db in $(mysql -Bse "SHOW DATABASES;" | grep -Ev "^(information_schema|performance_schema|sys)$"); do
    echo "=== Reparando base: $db ==="
    for table in $(mysql -D "$db" -Bse "SHOW TABLES;"); do
        mysql -D "$db" -e "REPAIR TABLE \`$table\`;"
    done
done

---

FASE 5: Salida del Modo Recovery y Upgrade de Sistema

rm -f /etc/my.cnf.d/recovery.cnf
systemctl restart mariadb
mysql_upgrade --force -u root -p # Importante tras desastre con tablas de sistemas
systemctl restart mariadb
systemctl status mariadb
mysql -u root -e "SELECT VERSION(); SHOW DATABASES;"

---

5. Contingencias

En caso de no finalizar la solución propuesta es hora de tirar de backups.

A) Instancia nueva y recuperación manual

tar -czf mysql-critical-backup-$(date +%Y%m%d-%H%M%S).tar.gz /var/lib/mysql/
mv /var/lib/mysql /var/lib/mysql.corrupted
mysql_install_db --user=mysql --datadir=/var/lib/mysql
systemctl start mariadb
# Restaurar datos desde backup/ibd/dump si es posible

B) Recuperar desde backup externo

systemctl stop mariadb
rm -rf /var/lib/mysql
tar -xzf /ruta/al/backup.tar.gz -C /var/lib/
chown -R mysql:mysql /var/lib/mysql
systemctl start mariadb

---

6. Verificación Post-Recuperación

mysqlcheck --all-databases --check -u root -p
mysql -u root -e "USE mysql; SHOW TABLES;"
mysql -u root -e "SELECT user,host FROM mysql.user;"
tail -100 /var/log/mysql/error.log | grep -i error

Realizar prueba de conexión desde aplicaciones (paneles de control, etc)

---

7. Notas y Referencias Técnicas

• Desde MariaDB 10.4 las tablas del sistema usan InnoDB, myisamchk ya no es válido
• innodb_force_recovery es crítico en estos casos [Referencia][1]
• Logs principales: /var/log/mysql/error.log, journalctl -u mariadb.service, /var/log/syslog

Herramientas avanzadas:

innochecksum /var/lib/mysql/mysql/db.ibd
innochecksum /var/lib/mysql/ib_logfile0
mysql -u root -e "SELECT * FROM information_schema.innodb_sys_tables WHERE name LIKE 'mysql/%';"

---

Notas finales

Este documento, requiere cierto grado de madurez en la administración de sistemas.

No es apto para un copy & paste

---

Referencias

1. MariaDB InnoDB Recovery
2. innodb_force_recovery Documentation
3. Repair Table

Documento actualizado: 17 de octubre de 2025
Autoría: Administración de Sistemas – Abdelkarim Mateos

Aviso

Esta documentación y su contenido, no implica que funcione en tu caso o determinados casos. También implica que tienes conocimientos sobre lo que trata, y que en cualquier caso tienes copias de seguridad. El contenido el contenido se entrega, tal y como está, sin que ello implique ningún obligación ni responsabilidad por parte de Castris

Si necesitas soporte profesional puedes contratar con Castris soporte profesional.