CertGrid CertGrid
Troubleshooting·MySQL

MySQL Crash Recovery

`kill -9` on the server, and 400,000 rows are still there afterwards. The error log shows XA crash recovery running, and both replicas reconnect on their own - durability demonstrated rather than asserted.

Troubleshooting Guide 39 of 45 Advanced

Written against the versions above. This depends on `innodb_flush_log_at_trx_commit = 1`, the default. At 0 or 2 the server is faster and a crash can lose up to a second of committed transactions.

db-a01 is killed. The replicas are watched to see how they react to their source disappearing.
Server NameIP AddressOSRolesCPURAMHDD
db-a01192.168.0.81Ubuntu 26.04 LTSPrimary / Source / Replica Set Member 12 Core4 GB50 GB
db-b01192.168.0.82Ubuntu 26.04 LTSStandby / Replica / Replica Set Member 22 Core4 GB50 GB
db-c01192.168.0.83Ubuntu 26.04 LTSReplica Set Member 3 / Cascading and Delayed Replica2 Core4 GB50 GB

Before you start

  1. Record what is there, then kill the server outright

    400,000 rows before.

    pkill -9 mysqld sends SIGKILL. There is no shutdown, no flush, no chance to close files - the process stops mid-instruction. This is the worst case a power loss or an OOM kill produces.

    Then systemd restarts it, because the unit has a Restart= policy - the same policy that produced the restart loop in the service management guide. Here it does exactly what it should: active within seconds, with no intervention.

    bash Example session 
    sudo mysql --table appdb -e "SELECT COUNT(*) AS before_crash FROM events"+--------------+| before_crash |+--------------+|       400000 |+--------------+sudo pkill -9 mysqld ; echo "killed=$?" ; sleep 6 ; systemctl is-active mysqlkilled=0active

    Expected result400,000 rows, killed=0, then active after the restart.

    Success conditionThe server was killed without warning and came back on its own.

  2. Count the rows again

    400,000. Not one row lost.

    This is InnoDB's redo log doing its job. Every committed change is written to the log and flushed to disk before the commit returns to the client, so a crash can lose work in progress but never work that was acknowledged. On restart, InnoDB replays the log to bring the data files up to date.

    It depends entirely on innodb_flush_log_at_trx_commit = 1, the default. The settings that make MySQL faster by relaxing this are the settings that turn this step into data loss.

    bash Example session 
    sudo mysql --table appdb -e "SELECT COUNT(*) AS after_crash FROM events"+-------------+| after_crash |+-------------+|      400000 |+-------------+

    Expected result400,000 rows again.

    Success conditionYou have evidence that committed data survives a hard kill.

  3. Read the recovery in the error log

    The error log is the only place the server explains itself, and after a crash it is the first thing to read.

    The sequence: the process starts, InnoDB initialization has started, then ended, then Starting XA crash recovery and XA crash recovery finished. Those last two are the tell - a clean shutdown does not produce them.

    Here recovery took about four milliseconds, because there was little in flight. On a busy server with a large redo log it can take minutes, and the server refuses connections throughout. A long startup after a crash is usually recovery working, not the server hanging - a distinction worth knowing before you kill it again out of impatience.

    bash Example session 
    sudo grep -iE "crash recovery|Starting|shutdown|InnoDB initialization" /var/log/mysql/error.log | tail -82026-08-27T13:06:39.132118Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.10-0ubuntu0.26.04.1) starting as process 175892026-08-27T13:06:39.138989Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.2026-08-27T13:06:39.495069Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.2026-08-27T13:30:31.014533Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.4.10-0ubuntu0.26.04.1) starting as process 216252026-08-27T13:30:31.019429Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.2026-08-27T13:30:31.581786Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.2026-08-27T13:30:31.696384Z 0 [System] [MY-010229] [Server] Starting XA crash recovery...2026-08-27T13:30:31.700533Z 0 [System] [MY-010232] [Server] XA crash recovery finished.

    Expected resultInnoDB initialisation followed by XA crash recovery starting and finishing.

    Success conditionYou can confirm from the log that recovery ran and completed.

  4. See what the replicas did about it

    Both replicas, checked while db-a01 was down and restarting.

    Replica_IO_Running: Connecting - not No. The IO thread lost its connection and is retrying, which is the correct behaviour and the reason no intervention is needed. Last_IO_Errno: 2003 is the same connection-refused error the foundations track produced by hand, arriving here because the source genuinely was not listening.

    Replica_SQL_Running: Yes throughout - the applier had nothing left to apply but never failed. Seconds_Behind_Source: NULL, which here means "not currently connected" rather than a fault.

    So a source crash does not break replication. It interrupts it, and the replicas resume by themselves.

    bash Example session 
    sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running:|Replica_SQL_Running:|Last_IO_Errno:|Seconds_Behind_Source:"           Replica_IO_Running: Connecting          Replica_SQL_Running: Yes        Seconds_Behind_Source: NULL                Last_IO_Errno: 2003sudo mysql -e "SHOW REPLICA STATUS\G" | grep -E "Replica_IO_Running:|Replica_SQL_Running:|Seconds_Behind_Source:"           Replica_IO_Running: Connecting          Replica_SQL_Running: Yes        Seconds_Behind_Source: NULL

    Expected resultConnecting on both, Last_IO_Errno: 2003, SQL thread still Yes.

    Success conditionYou know what a source outage looks like from a replica.

  5. Prove all three still agree

    The check that ends any incident: not "is it up" but "is it right".

    A count alone would not catch divergence - two servers can hold the same number of different rows, which is exactly what happened in the replication track. Summing a CRC32 over the row contents does catch it.

    10 rows and checksum 22885343858 on all three. The source crashed, both replicas lost their connection, everything reconnected, and the data is identical.

    Keep a check like this. It is cheap, it is the only real definition of a healthy topology, and it is the thing SHOW REPLICA STATUS cannot tell you.

    bash Example session 
    sudo mysql -N appdb -e "SELECT CONCAT(COUNT(*),' rows / checksum ',COALESCE(SUM(CRC32(CONCAT_WS('|',id,name,email))),0)) FROM customers"10 rows / checksum 22885343858

    Expected resultIdentical row count and checksum from db-a01, db-b01 and db-c01.

    Success conditionYou have verified consistency rather than assuming it from a green status.

Troubleshooting

Official sources

← All MySQL guides