Cannot Start mysql.service on ubuntu after restore of files

by

Summary

Restoring MySQL files from a backup on Ubuntu resulted in the mysql.service failing to start. The error logs indicated repeated start failures with an exit-code result, despite correct file ownership (mysql:mysql).

Root Cause

The restored MySQL files contained inconsistent or outdated database state files, specifically the ibdata1 and ib_logfile* files, which conflicted with the new MySQL instance’s initialization process.

Why This Happens in Real Systems

  • Backup/restore mismatch: Restored files may retain state information (e.g., transaction logs) incompatible with the target system.
  • Initialization conflicts: MySQL relies on consistent state files (ibdata1, log files) to start. Restored files can overwrite or conflict with existing ones.
  • Permissions/ownership: While ownership was corrected (mysql:mysql), underlying file inconsistencies remained unresolved.

Real-World Impact

  • Service downtime: MySQL service remained unavailable, impacting dependent applications.
  • Data integrity risk: Inconsistent state files could lead to data corruption if forced to start.
  • Resource waste: Systemd repeatedly attempted restarts, consuming CPU and memory.

Example or Code (if necessary and relevant)

# Check MySQL error logs for detailed failure reasons
sudo journalctl -u mysql.service --since "1 hour ago"

# Remove conflicting state files (ensure data is backed up)
sudo rm /var/lib/mysql/ibdata1 /var/lib/mysql/ib_logfile*

How Senior Engineers Fix It

  1. Verify file integrity: Compare restored files with the target system’s expected structure.
  2. Clean state files: Remove ibdata1 and ib_logfile* files to force MySQL to reinitialize.
  3. Reinitialize MySQL: Run mysql_install_db to recreate a clean data directory.
  4. Restore data safely: Use mysql -u root < backup.sql to restore data after initialization.

Why Juniors Miss It

  • Focus on permissions: Juniors often assume ownership issues are the root cause, ignoring underlying file inconsistencies.
  • Lack of MySQL internals knowledge: Understanding InnoDB state files (ibdata1, log files) is critical for diagnosing such issues.
  • Overlooking logs: Detailed error logs in journalctl or /var/log/mysql/error.log provide clues to the actual problem.

Leave a Comment