# MySQL Backup Failed

Common Error Messages

bash
mysqldump: Got error: 1044: Access denied for user 'backup'@'localhost' when doing LOCK TABLES

Or:

bash
mysqldump: Error 2013: Lost connection to MySQL server during query

Or:

bash
mysqldump: Got error: 23: Out of resources when opening file

Or:

bash
ERROR 1 (HY000): Can't create/write to file '/backup/dump.sql' (Errcode: 13 - Permission denied)

Root Causes

  1. 1.Insufficient permissions - User lacks required privileges
  2. 2.Disk space issues - Not enough space for backup file
  3. 3.Lock timeout - Tables locked too long
  4. 4.Memory issues - Insufficient memory for large dumps
  5. 5.Network issues - Connection drops during backup
  6. 6.File permissions - Cannot write to backup directory
  7. 7.Large table issues - Timeout on huge tables

Diagnosis Steps

Step 1: Check Backup User Permissions

```sql -- Check user grants SHOW GRANTS FOR 'backup_user'@'localhost';

-- Required privileges for mysqldump -- Minimum: SELECT, RELOAD, LOCK TABLES, SHOW VIEW, TRIGGER GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost'; ```

Step 2: Verify Disk Space

```bash # Check available disk space df -h /backup

# Estimate backup size mysql -e " SELECT ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) as size_gb FROM information_schema.tables WHERE table_schema = 'your_database'; " ```

Step 3: Check MySQL Error Log

```bash # Find error log location mysql -e "SHOW VARIABLES LIKE 'log_error';"

# Check for errors tail -100 /var/log/mysql/error.log ```

Step 4: Test Backup Command

bash
# Test with verbose output
mysqldump -u backup_user -p --verbose your_database > /dev/null 2>&1
echo "Exit code: $?"

Solutions

Solution 1: Fix User Permissions

```sql -- Create backup user with required privileges CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'secure_password';

-- Grant necessary privileges GRANT SELECT, RELOAD, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';

-- For stored procedures and functions GRANT SELECT ON mysql.proc TO 'backup_user'@'localhost';

-- Apply changes FLUSH PRIVILEGES; ```

Solution 2: Handle Large Tables Without Locking

```bash # Use single-transaction for InnoDB (no locking) mysqldump -u backup_user -p \ --single-transaction \ --routines \ --triggers \ --events \ your_database > backup.sql

# For mixed storage engines mysqldump -u backup_user -p \ --single-transaction \ --lock-tables=false \ your_database > backup.sql ```

Solution 3: Backup with Limited Memory

```bash # Use quick mode (no buffering) mysqldump -u backup_user -p \ --quick \ --single-transaction \ your_database > backup.sql

# Limit memory usage mysqldump -u backup_user -p \ --quick \ --single-transaction \ --max-allowed-packet=16M \ your_database > backup.sql ```

Solution 4: Backup Large Databases in Chunks

```bash # Backup structure only first mysqldump -u backup_user -p --no-data your_database > structure.sql

# Backup tables separately for table in $(mysql -u backup_user -p -Nse "SHOW TABLES" your_database); do echo "Backing up $table..." mysqldump -u backup_user -p --single-transaction \ your_database $table > "tables/${table}.sql" done

# Or use mydumper for parallel dumps mydumper -u backup_user -p -B your_database -o /backup/dumps -t 4 ```

Solution 5: Fix File Permission Issues

```bash # Check backup directory permissions ls -la /backup

# Create backup directory with proper permissions sudo mkdir -p /backup/mysql sudo chown mysql:mysql /backup/mysql sudo chmod 750 /backup/mysql

# Run backup as mysql user sudo -u mysql mysqldump your_database > /backup/mysql/backup.sql ```

Solution 6: Handle Connection Timeouts

```bash # Increase timeout settings in my.cnf [mysqldump] max-allowed-packet = 512M net-buffer-length = 16M

# Or in command mysqldump -u backup_user -p \ --single-transaction \ --max-allowed-packet=512M \ --connect-timeout=600 \ your_database > backup.sql ```

Solution 7: Compress Backups on the Fly

```bash # Compress during backup mysqldump -u backup_user -p --single-transaction your_database | gzip > backup.sql.gz

# Parallel compression (faster) mysqldump -u backup_user -p --single-transaction your_database | pigz > backup.sql.gz

# Encrypt and compress mysqldump -u backup_user -p --single-transaction your_database | \ gzip | openssl enc -aes-256-cbc -salt -pass pass:yourpassword > backup.sql.gz.enc ```

Solution 8: Use Percona XtraBackup (For Large Databases)

```bash # Install Percona XtraBackup sudo apt-get install percona-xtrabackup

# Full backup xtrabackup --backup --target-dir=/backup/full

# Prepare backup xtrabackup --prepare --target-dir=/backup/full

# Incremental backup xtrabackup --backup --target-dir=/backup/inc1 \ --incremental-basedir=/backup/full ```

Automated Backup Script

```bash #!/bin/bash # mysql_backup.sh

# Configuration DB_USER="backup_user" DB_PASS="secure_password" BACKUP_DIR="/backup/mysql" RETENTION_DAYS=7 DATE=$(date +%Y%m%d_%H%M%S)

# Create backup directory mkdir -p "$BACKUP_DIR"

# Get list of databases DATABASES=$(mysql -u"$DB_USER" -p"$DB_PASS" -Nse "SHOW DATABASES" | grep -v information_schema | grep -v performance_schema)

# Backup each database for DB in $DATABASES; do echo "Backing up $DB..."

mysqldump -u"$DB_USER" -p"$DB_PASS" \ --single-transaction \ --routines \ --triggers \ --events \ "$DB" | gzip > "$BACKUP_DIR/${DB}_${DATE}.sql.gz"

# Check if backup succeeded if [ ${PIPESTATUS[0]} -eq 0 ]; then echo "Backup of $DB completed successfully" else echo "ERROR: Backup of $DB failed" # Send alert fi done

# Delete old backups find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$RETENTION_DAYS -delete

echo "Backup completed. Old backups cleaned up." ```

Verify Backup Integrity

```bash # Check backup file is not empty if [ -s backup.sql ]; then echo "Backup file has content" fi

# Check SQL structure head -20 backup.sql

# Verify no errors in dump grep -i "error" backup.sql

# Test restore to different database mysql -u root -p -e "CREATE DATABASE test_restore;" mysql -u root -p test_restore < backup.sql mysql -u root -p -e "DROP DATABASE test_restore;"

# For compressed files zcat backup.sql.gz | head -20 ```

Restore from Backup

```bash # Restore database mysql -u root -p database_name < backup.sql

# Restore from compressed backup gunzip < backup.sql.gz | mysql -u root -p database_name

# Restore single table from backup sed -n '/CREATE TABLE users/,/UNLOCK TABLES/p' backup.sql | mysql -u root -p database_name ```

Prevention

1. Monitor Backup Success

bash
# Add to backup script
if [ $? -eq 0 ]; then
    echo "$(date): Backup successful" >> /var/log/mysql_backup.log
    # Send success notification
else
    echo "$(date): Backup FAILED" >> /var/log/mysql_backup.log
    # Send failure alert
    exit 1
fi

2. Test Backups Regularly

```bash #!/bin/bash # Weekly backup test

# Create test database mysql -u root -p -e "CREATE DATABASE backup_test;"

# Restore backup mysql -u root -p backup_test < /backup/mysql/database_20240101.sql

# Verify tables exist TABLE_COUNT=$(mysql -u root -p -Nse "SELECT COUNT(*) FROM information_schema.tables WHERE table_schema='backup_test'")

if [ "$TABLE_COUNT" -gt 0 ]; then echo "Backup verification passed" else echo "Backup verification FAILED" fi

# Cleanup mysql -u root -p -e "DROP DATABASE backup_test;" ```

3. Configure Backup Rotation

bash
# Keep backups: 7 daily, 4 weekly, 12 monthly
# Using logrotate
cat > /etc/logrotate.d/mysql-backup << 'EOF'
/backup/mysql/*.sql.gz {
    daily
    rotate 7
    compress
    missingok
    notifempty
}
EOF
  • [MySQL Disk Full](./fix-mysql-disk-full)
  • [MySQL Connection Refused](./fix-mysql-connection-refused)