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

```bash
# 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:

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

```bash
# 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)**



```bash
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**


```bash
#!/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**

```bash
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**

```bash
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**

```bash
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

```bash
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:
```bash
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](https://mariadb.com/kb/en/innodb-recovery/)
2. [innodb_force_recovery Documentation](https://mariadb.com/kb/en/innodb-system-variables/#innodb_force_recovery)
3. [Repair Table](https://mariadb.com/kb/en/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](https://castris.com)

Si necesitas soporte profesional puedes contratar con Castris [soporte profesional](https://intranet.castris.com/store/soporte-profesional).