Backing up a MySQL database

Backup MySQL database

Why make backups?

Backups are essential to protect your data. They allow you to restore your database in case of:

• ✅ Accidental data loss
• ✅ Database corruption
• ✅ Attacks or security issues
• ✅ Migration to another server

Method 1: Backup from phpMyAdmin

Step 1: Access phpMyAdmin

• Log in to phpMyAdmin
• Select the database you want to backup

Step 2: Export the database

• Click on the Export tab
• Select Quick or Custom method
• For backups, the quick method is usually sufficient

Step 3: Configure options

• Format: SQL (recommended for backups)
• Compression: Select gzip or zip for large files
• Click Go or Continue

The file will download automatically.

Method 2: Backup from terminal (mysqldump)

Backup a specific database:

mysqldump -u user -p database_name > backup.sql

Example:

mysqldump -u root -p my_webapp > backup_2024.sql

It will ask for the password and create the backup.sql file.

Backup with compression:

mysqldump -u user -p database_name | gzip > backup.sql.gz

This creates a compressed file that takes up less space.

Backup all databases:

mysqldump -u root -p --all-databases > all_databases_backup.sql

Method 3: Backup with automatic date

To create backups with date in the name:

mysqldump -u root -p database_name > backup_$(date +%Y%m%d_%H%M%S).sql

This will create a file like: backup_20241215_143022.sql

Advanced mysqldump options

Include only structure (without data):

mysqldump -u root -p --no-data database_name > structure.sql

Include only data (without structure):

mysqldump -u root -p --no-create-info database_name > data.sql

Include stored procedures and functions:

mysqldump -u root -p --routines database_name > backup.sql

Include events:

mysqldump -u root -p --events database_name > backup.sql

Complete backup with everything:

mysqldump -u root -p --routines --events --triggers database_name > complete_backup.sql

Automate backups with cron

You can create a script to make automatic backups:

Create backup script:

#!/bin/bash
# Configuration
DB_USER="root"
DB_PASS="your_password"
DB_NAME="database_name"
BACKUP_DIR="/path/backups"
DATE=$(date +%Y%m%d_%H%M%S)

# Create directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Make backup
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/backup_$DATE.sql.gz

# Delete old backups (older than 7 days)
find $BACKUP_DIR -name "backup_*.sql.gz" -mtime +7 -delete

echo "Backup completed: backup_$DATE.sql.gz"

Make script executable:

chmod +x /path/to/script/backup.sh

Add to cron (daily backup at 2 AM):

0 2 * * * /path/to/script/backup.sh

Remote backup

To save backups on another server or storage service:

mysqldump -u root -p database_name | gzip | ssh user@remote_server "cat > /remote/path/backup.sql.gz"

Or using SCP:

mysqldump -u root -p database_name | gzip > backup.sql.gz
scp backup.sql.gz user@remote_server:/remote/path/

Verify the backup

After creating a backup, verify that it's valid:

# Verify that the file exists and has content
ls -lh backup.sql

# Verify that it's valid SQL (should show SQL commands)
head -20 backup.sql

Or try importing it into a test database to verify it works.

Backup best practices

• ✅ Make backups regularly (daily for important databases)
• ✅ Store backups in multiple locations (server, local, cloud)
• ✅ Compress backups to save space
• ✅ Test restoring backups periodically to verify they work
• ✅ Keep multiple backup versions (last 7-30 days)
• ✅ Document the backup and restore process

Common issues

Backup is too large

• Use compression (gzip)
• Consider backing up only important tables
• Delete old data before backup

"Access denied" error

• Verify that the user has sufficient permissions
• Verify that the password is correct
• Use root user if necessary

Backup takes too long

• This is normal for large databases
• Make backups during low traffic hours
• Consider using options like --single-transaction for large databases

Need help?

If you're having trouble making backups or need to configure automatic backups, open a ticket from the billing.baires.host panel or contact us for support.

You can also reach us through our social media:

• Instagram: @baires_host
• Discord (active support): https://discord.gg/dzjauatAFN
• Linktree: https://linktr.ee/baires.host