Skip to main content

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 pudieran faltar en el ejemplo

#!/bin/bash
# Script de reparación masiva de tablas MariaDB
# Usar con MariaDB ya iniciado en modo recovery
# reparir_mariadb.sh

set -e

RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

echo -e "${GREEN}=== Script de reparación masiva MariaDB ===${NC}\n"

# Verificar que MySQL esté corriendo
if ! mysqladmin ping &>/dev/null; then
    echo -e "${RED}ERROR: MariaDB no está corriendo${NC}"
    echo "Asegúrate de que esté iniciado en modo recovery"
    exit 1
fi

# Conectar a MySQL y obtener lista de todas las tablas corruptas
echo -e "${YELLOW}Identificando tablas corruptas...${NC}"

# Crear script SQL temporal
cat > /tmp/repair_all.sql << 'SQLEOF'
-- Seleccionar base de datos mysql
USE mysql;

-- Intentar reparar todas las tablas del sistema
REPAIR TABLE column_stats;
REPAIR TABLE columns_priv;
REPAIR TABLE db;
REPAIR TABLE event;
REPAIR TABLE func;
REPAIR TABLE general_log;
REPAIR TABLE global_priv;
REPAIR TABLE gtid_slave_pos;
REPAIR TABLE help_category;
REPAIR TABLE help_keyword;
REPAIR TABLE help_relation;
REPAIR TABLE help_topic;
REPAIR TABLE host;
REPAIR TABLE index_stats;
REPAIR TABLE innodb_index_stats;
REPAIR TABLE innodb_table_stats;
REPAIR TABLE plugin;
REPAIR TABLE proc;
REPAIR TABLE procs_priv;
REPAIR TABLE proxies_priv;
REPAIR TABLE roles_mapping;
REPAIR TABLE servers;
REPAIR TABLE slow_log;
REPAIR TABLE table_stats;
REPAIR TABLE tables_priv;
REPAIR TABLE time_zone;
REPAIR TABLE time_zone_leap_second;
REPAIR TABLE time_zone_name;
REPAIR TABLE time_zone_transition;
REPAIR TABLE time_zone_transition_type;
REPAIR TABLE transaction_registry;
REPAIR TABLE user;

-- Mostrar estado
SELECT 'Reparación de tablas del sistema completada' AS Status;
SQLEOF

# Ejecutar reparación
echo -e "${YELLOW}Ejecutando reparación de tablas del sistema...${NC}"
mysql -u root < /tmp/repair_all.sql 2>&1 | tee /tmp/repair_output.log

# Reparar tablas de todas las bases de datos
echo -e "\n${YELLOW}Obteniendo lista de todas las bases de datos...${NC}"

DBS=$(mysql -u root -Bse "SHOW DATABASES;" 2>/dev/null | grep -v -E "^(information_schema|performance_schema|sys)$")

for db in $DBS; do
    echo -e "\n${GREEN}=== Procesando base de datos: $db ===${NC}"

    # Obtener tablas de esta BD
    TABLES=$(mysql -u root -D "$db" -Bse "SHOW TABLES;" 2>/dev/null)

    if [ -z "$TABLES" ]; then
        echo -e "${YELLOW}  No hay tablas en $db${NC}"
        continue
    fi

    for table in $TABLES; do
        echo -n "  Reparando $db.$table ... "

        # Intentar reparar
        RESULT=$(mysql -u root -D "$db" -e "REPAIR TABLE \`$table\`;" 2>&1)

        if echo "$RESULT" | grep -q "OK\|Already up to date"; then
            echo -e "${GREEN}OK${NC}"
        else
            echo -e "${RED}FAILED${NC}"
            echo "$RESULT" >> /tmp/repair_failed.log
        fi
    done
done

# Resumen
echo -e "\n${GREEN}=== Resumen de reparación ===${NC}"
echo -e "Log completo: /tmp/repair_output.log"

if [ -f /tmp/repair_failed.log ]; then
    echo -e "${RED}Tablas que fallaron: /tmp/repair_failed.log${NC}"
    echo -e "\n${YELLOW}Tablas con errores:${NC}"
    cat /tmp/repair_failed.log
else
    echo -e "${GREEN}Todas las tablas reparadas exitosamente${NC}"
fi

# Optimizar tablas después de reparar
echo -e "\n${YELLOW}¿Deseas optimizar todas las tablas? (s/n)${NC}"
read -r optimize

if [ "$optimize" = "s" ]; then
    echo -e "${YELLOW}Optimizando tablas...${NC}"

    for db in $DBS; do
        echo -e "${GREEN}Optimizando base de datos: $db${NC}"
        TABLES=$(mysql -u root -D "$db" -Bse "SHOW TABLES;" 2>/dev/null)

        for table in $TABLES; do
            echo -n "  Optimizando $db.$table ... "
            mysql -u root -D "$db" -e "OPTIMIZE TABLE \`$table\`;" &>/dev/null && echo -e "${GREEN}OK${NC}" || echo -e "${YELLOW}SKIP${NC}"
        done
    done
fi

echo -e "\n${GREEN}=== Proceso completado ===${NC}"
echo -e "${YELLOW}Siguiente paso: Detener modo recovery y reiniciar MariaDB normalmente${NC}"

# Limpiar
rm -f /tmp/repair_all.sql

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.

Back to top