Priya survives five production MySQL crises

sudo systemctl status mysql
# Shows: failed

Step 1: Read the error log:
sudo tail -50 /var/log/mysql/error.log

Common reasons it won't start:
- Disk full: check df -h
- Port already in use: ss -tlnp | grep 3306
- Corrupted InnoDB files: look for [ERROR] in the log
- Wrong permissions: ls -la /var/lib/mysql/

-- Disk full fix:
df -h
# If /var/lib/mysql is 100%:
du -sh /var/lib/mysql/* | sort -rh | head  -- find what is large
-- Often: binary logs or undo logs
PURGE BINARY LOGS BEFORE DATE_SUB(NOW(), INTERVAL 3 DAY);

-- Port in use fix:
sudo kill $(sudo lsof -t -i:3306)
sudo systemctl start mysql

-- Error in app logs: "Too many connections" or "Can't connect to MySQL server"

-- Check current connections:
SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Max_used_connections';

-- Check the max_connections setting:
SHOW VARIABLES LIKE 'max_connections';

-- See who has connections open:
SELECT user, host, COUNT(*) AS connections
FROM information_schema.PROCESSLIST
GROUP BY user, host
ORDER BY connections DESC;

-- Kill idle connections (sleeping for over 300 seconds):
SELECT CONCAT('KILL ', id, ';')
FROM information_schema.PROCESSLIST
WHERE command = 'Sleep' AND time > 300;

-- Temporary emergency fix -- increase max_connections:
SET GLOBAL max_connections = 500;
-- Permanent: edit my.cnf and restart

-- Someone ran: DELETE FROM orders WHERE user_id = 1;
-- But they forgot the WHERE should have been: DELETE FROM orders WHERE id = 1;

-- If you have binary logging enabled:
SHOW VARIABLES LIKE 'log_bin';        -- is it ON?
SHOW BINARY LOGS;                     -- list log files

-- Find the DELETE statement in the binary log:
mysqlbinlog --start-datetime="2026-03-16 17:00:00"                 --stop-datetime="2026-03-16 18:00:00"                 /var/log/mysql/mysql-bin.000123 | grep -A5 -B5 "DELETE"

-- To recover, restore from last backup and replay binary logs up to just before the DELETE:
mysql -u root -p myapp_db < backup_20260316.sql
mysqlbinlog --stop-datetime="2026-03-16 17:45:00"                 /var/log/mysql/mysql-bin.000123 | mysql -u root -p myapp_db

-- Prevention: enable binary logging, take daily backups, test restore monthly

SHOW SLAVE STATUS\G
-- Slave_IO_Running: No   or   Slave_SQL_Running: No

-- Read the error:
-- Last_Error: Could not execute Write_rows event...

-- Quick fix: skip the problem event and continue:
STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
START SLAVE;
SHOW SLAVE STATUS\G

-- If replication lag is growing:
-- Seconds_Behind_Master: 3600   (1 hour behind!)
-- Check replica server load: top, iostat
-- Replica disk/CPU may be struggling with the write load

-- App error: "Table './myapp_db/users' is marked as crashed"

-- For MyISAM tables (older):
REPAIR TABLE users;

-- For InnoDB tables (modern, default):
-- InnoDB rarely corrupts. If it does:
sudo systemctl stop mysql
-- Add to my.cnf: innodb_force_recovery = 1
sudo systemctl start mysql
mysqldump -u root -p myapp_db > emergency_backup.sql
-- Remove innodb_force_recovery
sudo systemctl restart mysql
mysql -u root -p myapp_db < emergency_backup.sql

-- Check for table issues:
CHECK TABLE users;
-- Shows status: OK (good) or errors (problem)

1. Is MySQL running? sudo systemctl status mysql
2. Can I connect? sudo mysql -e "SELECT 1;"
3. What does SHOW PROCESSLIST show? Any blocking queries?
4. Is disk full? df -h /var/lib/mysql
5. What does the error log say? sudo tail -100 /var/log/mysql/error.log
6. Is replication healthy? SHOW SLAVE STATUS
7. When did the problem start? Compare with recent deployments and cron jobs.