How to Backup MySQL Database on Linux: A Practical Step-by-Step Guide

Backing up your MySQL database is one of the most important tasks for any developer or system administrator working on Linux. Whether you’re planning a server upgrade, want to safeguard against data loss, or need to migrate your database elsewhere, having reliable backups ensures you won’t lose crucial information.

In this post, I’ll walk you through how to back up your MySQL database on a Linux system using simple command-line tools and scripts. No fluff—just practical steps!

Why Backup Your MySQL Database Regularly?

Before we dive into commands, let’s quickly remind ourselves why backups matter:

• Data Loss Prevention: Hardware failures, accidental deletion, or corruption can cause irreversible data loss.
• Easy Migration: A backup file can easily be transferred and restored on another server.
• Quick Recovery: Having backups means faster recovery in case your database gets compromised.
• Peace of Mind: Regular backups reduce stress during updates or configuration changes.

Prerequisites

To follow along, you’ll need:

• A Linux server with MySQL installed
• Access to the terminal with appropriate privileges (usually root or a user with sudo)
• The MySQL username and password that has permission to access the database you want to back up

Step 1: Use mysqldump for Backup

The most common way to backup a MySQL database is using the mysqldump utility. It exports your database into an SQL text file that contains all commands needed to recreate your schema and data.

Basic Syntax

mysqldump -u [username] -p [database_name] > [backup_file.sql]

• -u [username]: Your MySQL username
• -p: Prompts for password (you won’t include password directly here for security)
• [database_name]: The name of the database you want to backup
• [backup_file.sql]: The file where the dump will be saved

Example: Backing up a database called myappdb

mysqldump -u root -p myappdb > myappdb_backup_$(date +%F).sql

This command will prompt you to enter the root password and then save a dump file named like myappdb_backup_2024-06-15.sql, where the date reflects today’s date.

Step 2: Compress Your Backup (Optional but Recommended)

Because SQL dumps can get large quickly, compressing them saves disk space and makes it easier to transfer backups.

gzip myappdb_backup_$(date +%F).sql

This will create a gzipped file named something like myappdb_backup_2024-06-15.sql.gz.

You can combine both commands in one line too:

mysqldump -u root -p myappdb | gzip > myappdb_backup_$(date +%F).sql.gz

Step 3: Automate Backups with Cron Job

Manual backups are great for ad-hoc tasks. But for ongoing maintenance, automation is key.

Create a backup script — e.g., /usr/local/bin/mysql_backup.sh

#!/bin/bash

BACKUP_DIR="/var/backups/mysql"
DATE=$(date +%F)
DB_USER="root"
DB_PASS="your_password_here"   # Note: Storing passwords in plain scripts is not recommended. See notes below.
DB_NAME="myappdb"

mkdir -p $BACKUP_DIR

mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_backup_$DATE.sql.gz

# Remove backups older than 7 days (optional)
find $BACKUP_DIR -type f -name "*.gz" -mtime +7 -exec rm {} \;

Make it executable:

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

Schedule daily backup at 2 AM using cron:

Edit cron jobs for root (or appropriate user):

sudo crontab -e

Add the following line:

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

This runs the script daily at 02:00 AM.

Security Tips

1. Avoid Plain Passwords: Instead of keep passwords inside scripts, use a .my.cnf file in your home directory with proper permissions (chmod 600) like this:

[client]
user=root
password=your_password_here

Then run mysqldump simply with:

mysqldump myappdb > backup.sql

and it will read credentials automatically.

1. Secure Backup Files: Backup files contain your data and are sensitive. Store them in secure locations with restricted access.
2. Encrypt Backups: For higher security needs, consider encrypting backups.

Step 4: Restore From Backup (Optional)

If you need to restore from your backup file:

First unzip if compressed:

gunzip myappdb_backup_2024-06-15.sql.gz  # produces .sql file again.

Run this command to restore:

mysql -u root -p myappdb < myappdb_backup_2024-06-15.sql

Final Thoughts

Backing up your MySQL databases on Linux doesn’t have to be complicated. With just mysqldump, compression tools like gzip, and a little automation via cron jobs, you can make sure your data stays safe with minimal effort.

Try setting up automated backups today so you’re prepared when disaster strikes!

If you’d like me to add information on backing up multiple databases at once or using other tools like Percona XtraBackup or MariaDB’s features, just let me know!