Creating Database Backups with mysqldump

Tutorial Overview

This tutorial explains how to back up MySQL databases using mysqldump, ensuring data recovery options are available in case of a database failure or corruption.

Prerequisites

• Root or sudo access.
• MySQL installed and configured.

Steps

Step 1: Basic Database Backup with mysqldump

1. Use mysqldump to back up a specific database (replace database_name):

mysqldump -u root -p database_name > /backup/database_name_backup.sql

• The .sql file contains all the SQL commands needed to recreate the database.

2. Verify the Backup File:

• Confirm the file size and inspect its contents:

ls -lh /backup/database_name_backup.sql
less /backup/database_name_backup.sql

Step 2: Back Up All Databases

1. To back up all MySQL databases simultaneously:

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

Step 3: Automate Database Backups with a Script

1. Create a backup script named mysql_backup.sh:

sudo nano /usr/local/bin/mysql_backup.sh

2. Add the following script contents:

#!/bin/bash
TIMESTAMP=$(date +"%F")
BACKUP_DIR="/backup/mysql/$TIMESTAMP"
mkdir -p "$BACKUP_DIR"
mysqldump -u root -p database_name > "$BACKUP_DIR/database_name.sql"

3. Make the Script Executable:

sudo chmod +x /usr/local/bin/mysql_backup.sh

4. Schedule the Script to Run Daily:

• Open crontab:

crontab -e

• Add an entry to run the script every night at 1:00 AM:

0 1 * * * /usr/local/bin/mysql_backup.sh

Step 4: Compress the Backup Files to Save Space

1. Compress the .sql file after the backup:

gzip /backup/database_name_backup.sql

• The resulting .gz file will save space and make storage more efficient.