claude-home/databases/troubleshooting.md

# Database Troubleshooting Guide

## Connection Issues

### Cannot Connect to Database
**Symptoms**: Connection refused, timeout errors, authentication failures
**Diagnosis**:
```bash
# Test basic connectivity
telnet db-server 3306  # MySQL
telnet db-server 5432  # PostgreSQL
nc -zv db-server 6379  # Redis

# Check database service status
systemctl status mysql
systemctl status postgresql
systemctl status redis-server
```

**Solutions**:
```bash
# Restart database services
sudo systemctl restart mysql
sudo systemctl restart postgresql

# Check configuration files
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
sudo nano /etc/postgresql/*/main/postgresql.conf

# Verify port bindings
ss -tlnp | grep :3306  # MySQL
ss -tlnp | grep :5432  # PostgreSQL
```

## Performance Issues

### Slow Query Performance
**Symptoms**: Long-running queries, high CPU usage, timeouts
**Diagnosis**:
```sql
-- MySQL
SHOW PROCESSLIST;
SHOW ENGINE INNODB STATUS;
EXPLAIN SELECT * FROM table WHERE condition;

-- PostgreSQL  
SELECT * FROM pg_stat_activity;
EXPLAIN ANALYZE SELECT * FROM table WHERE condition;
```

**Solutions**:
```sql
-- Add missing indexes
CREATE INDEX idx_column ON table(column);

-- Analyze table statistics
ANALYZE TABLE table_name;  -- MySQL
ANALYZE table_name;        -- PostgreSQL

-- Optimize queries
-- Use LIMIT for large result sets
-- Add WHERE clauses to filter results
-- Use appropriate JOIN types
```

### Memory and Resource Issues
**Symptoms**: Out of memory errors, swap usage, slow performance
**Diagnosis**:
```bash
# Check memory usage
free -h
ps aux | grep mysql
ps aux | grep postgres

# Database-specific memory usage
mysqladmin -u root -p status
sudo -u postgres psql -c "SELECT * FROM pg_stat_database;"
```

**Solutions**:
```bash
# Adjust database memory settings
# MySQL - /etc/mysql/mysql.conf.d/mysqld.cnf
innodb_buffer_pool_size = 2G
key_buffer_size = 256M

# PostgreSQL - /etc/postgresql/*/main/postgresql.conf
shared_buffers = 256MB
effective_cache_size = 2GB
work_mem = 4MB
```

## Data Integrity Issues

### Corruption Detection and Recovery
**Symptoms**: Table corruption errors, data inconsistencies
**Diagnosis**:
```sql
-- MySQL
CHECK TABLE table_name;
mysqlcheck -u root -p --all-databases

-- PostgreSQL
-- Check for corruption in logs
tail -f /var/log/postgresql/postgresql-*.log
```

**Solutions**:
```sql
-- MySQL table repair
REPAIR TABLE table_name;
mysqlcheck -u root -p --auto-repair database_name

-- PostgreSQL consistency check
-- Run VACUUM and REINDEX
VACUUM FULL table_name;
REINDEX TABLE table_name;
```

## Backup and Recovery Issues

### Backup Failures
**Symptoms**: Backup scripts failing, incomplete backups
**Diagnosis**:
```bash
# Check backup script logs
tail -f /var/log/backup.log

# Test backup commands manually
mysqldump -u root -p database_name > test_backup.sql
pg_dump -U postgres database_name > test_backup.sql

# Check disk space
df -h /backup/location/
```

**Solutions**:
```bash
# Fix backup script permissions
chmod +x /path/to/backup-script.sh
chown backup-user:backup-group /backup/location/

# Automated backup script example
#!/bin/bash
BACKUP_DIR="/backups/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

mysqldump -u root -p$MYSQL_PASSWORD --all-databases > \
    "$BACKUP_DIR/full_backup_$DATE.sql"

# Compress and rotate backups
gzip "$BACKUP_DIR/full_backup_$DATE.sql"
find "$BACKUP_DIR" -name "*.gz" -mtime +7 -delete
```

## Authentication and Security Issues

### Access Denied Errors
**Symptoms**: Authentication failures, permission errors
**Diagnosis**:
```sql
-- MySQL
SELECT user, host FROM mysql.user;
SHOW GRANTS FOR 'username'@'host';

-- PostgreSQL
\du  -- List users
\l   -- List databases
```

**Solutions**:
```sql
-- MySQL user management
CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON database.* TO 'newuser'@'localhost';
FLUSH PRIVILEGES;

-- PostgreSQL user management
CREATE USER newuser WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE database_name TO newuser;
```

## Replication Issues

### Master-Slave Replication Problems
**Symptoms**: Replication lag, sync errors, slave disconnection
**Diagnosis**:
```sql
-- MySQL Master
SHOW MASTER STATUS;

-- MySQL Slave
SHOW SLAVE STATUS\G

-- Check replication lag
SELECT SECONDS_BEHIND_MASTER FROM SHOW SLAVE STATUS\G
```

**Solutions**:
```sql
-- Reset replication
STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4;
START SLAVE;

-- Fix replication errors
SET GLOBAL sql_slave_skip_counter = 1;
START SLAVE;
```

## Storage and Disk Issues

### Disk Space Problems
**Symptoms**: Out of disk space errors, database growth
**Diagnosis**:
```bash
# Check database sizes
du -sh /var/lib/mysql/*
du -sh /var/lib/postgresql/*/main/*

# Find large tables
SELECT table_schema, table_name, 
       ROUND((data_length + index_length) / 1024 / 1024, 2) AS 'Size (MB)'
FROM information_schema.tables 
ORDER BY (data_length + index_length) DESC;
```

**Solutions**:
```sql
-- Clean up large tables
DELETE FROM log_table WHERE created_date < DATE_SUB(NOW(), INTERVAL 30 DAY);
OPTIMIZE TABLE log_table;

-- Enable log rotation
-- For MySQL binary logs
SET GLOBAL expire_logs_days = 7;
PURGE BINARY LOGS BEFORE DATE(NOW() - INTERVAL 7 DAY);
```

## Emergency Recovery

### Database Won't Start
**Recovery Steps**:
```bash
# Check error logs
tail -f /var/log/mysql/error.log
tail -f /var/log/postgresql/postgresql-*.log

# Try safe mode start
sudo mysqld_safe --skip-grant-tables &

# Recovery from backup
mysql -u root -p < backup_file.sql
psql -U postgres database_name < backup_file.sql
```

### Complete Data Loss Recovery
**Recovery Procedure**:
```bash
# Stop database service
sudo systemctl stop mysql

# Restore from backup
cd /var/lib/mysql
sudo rm -rf *
sudo tar -xzf /backups/mysql_full_backup.tar.gz

# Fix permissions
sudo chown -R mysql:mysql /var/lib/mysql
sudo chmod 755 /var/lib/mysql

# Start database
sudo systemctl start mysql
```

## Monitoring and Prevention

### Database Health Monitoring
```bash
#!/bin/bash
# db-health-check.sh

# Check if database is responding
if ! mysqladmin -u root -p$MYSQL_PASSWORD ping >/dev/null 2>&1; then
    echo "ALERT: MySQL not responding" | send_alert
fi

# Check disk space
DISK_USAGE=$(df /var/lib/mysql | awk 'NR==2 {print $5}' | sed 's/%//')
if [ $DISK_USAGE -gt 80 ]; then
    echo "ALERT: Database disk usage at ${DISK_USAGE}%" | send_alert
fi

# Check for long-running queries
LONG_QUERIES=$(mysql -u root -p$MYSQL_PASSWORD -e "SHOW PROCESSLIST" | grep -c "Query.*[0-9][0-9][0-9]")
if [ $LONG_QUERIES -gt 5 ]; then
    echo "ALERT: $LONG_QUERIES long-running queries detected" | send_alert
fi
```

### Automated Maintenance
```bash
# Daily maintenance script
#!/bin/bash
# Optimize tables
mysqlcheck -u root -p$MYSQL_PASSWORD --auto-repair --optimize --all-databases

# Update table statistics
mysql -u root -p$MYSQL_PASSWORD -e "FLUSH TABLES; ANALYZE TABLE table_name;"

# Backup rotation
find /backups -name "*.sql.gz" -mtime +30 -delete
```

This troubleshooting guide provides systematic approaches to resolving common database issues in home lab environments.