Backup and Restore MySQL Database on VPS Step by Step

A MySQL database is the backbone of most applications running on a VPS — whether WordPress, Laravel, Node.js, or an e-commerce platform. The data inside your database is irreplaceable, and losing it can mean hours or days of downtime and potentially permanent data loss. Knowing how to back up reliably and restore correctly is one of the most essential skills for any VPS administrator. This guide walks you through everything: from a simple manual backup with mysqldump to fully automated nightly cron jobs, with real commands you can use immediately.

Why Database Backup Matters on a VPS

Unlike shared hosting where the provider often handles server-level snapshots, a VPS places the responsibility squarely on you. You have full root access, which means full responsibility. The most common causes of data loss on a VPS include:

• Human Error — Running DROP TABLE or DELETE without a WHERE clause is more common than you'd think, even among experienced developers.
• Hardware Failure — While rare, underlying disk failures on virtualized infrastructure can corrupt or destroy data files.
• Ransomware / Cyberattack — Exposed MySQL ports or weak credentials can lead to data being encrypted or wiped by automated bots.
• Failed Software Updates — A botched framework migration or a buggy plugin update can alter schema or delete rows unintentionally.
• Disk Full — When the disk fills up entirely, MySQL may corrupt tables that are being actively written to.

The industry-standard 3-2-1 rule applies here: keep 3 copies of your data, on 2 different storage types, with 1 copy stored off-site. At minimum, you should have a local daily backup and at least a weekly upload to a remote location such as cloud storage.

MySQL Backup Tools Compared

MySQL offers several backup utilities, each suited to different scenarios. Choosing the right one depends on your database size, storage engine, and how much downtime you can tolerate.

For most VPS users, mysqldump is the best starting point. It comes pre-installed with MySQL, requires no additional packages, and produces a human-readable SQL file that you can inspect, modify, or restore on any compatible MySQL instance.

Backing Up with mysqldump

mysqldump exports your database structure and data into a .sql file containing CREATE TABLE and INSERT statements. This file can be restored on any MySQL server with the same or newer version.

Back Up a Single Database

mysqldump -u root -p mydb > /backup/mydb_$(date +%Y%m%d).sql

To save disk space, compress the output on the fly with gzip:

mysqldump -u root -p mydb | gzip > /backup/mydb_$(date +%Y%m%d).sql.gz

Consistent Backup for InnoDB (No Table Lock)

The --single-transaction flag is critical for live databases. It tells mysqldump to open a consistent snapshot transaction before reading data, so your application can keep writing during the backup without any downtime:

mysqldump -u root -p \
  --single-transaction \
  --routines \
  --triggers \
  --events \
  mydb | gzip > /backup/mydb_$(date +%Y%m%d_%H%M).sql.gz

The --routines, --triggers, and --events flags ensure stored procedures, triggers, and scheduled events are included in the backup — essential for a complete restore.

Back Up All Databases at Once

mysqldump -u root -p \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  | gzip > /backup/all_databases_$(date +%Y%m%d).sql.gz

Schema-Only Backup (No Data)

Useful when you want to clone the table structure to a development server without copying production data:

mysqldump -u root -p --no-data mydb > /backup/mydb_schema_only.sql

Restoring a MySQL Database

Restoration must be done carefully. Always test your restore procedure in a staging environment before an actual emergency occurs — a backup you have never tested is not a real backup.

Restore from a Plain SQL File

# Step 1: Create the database if it doesn't exist
mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"

# Step 2: Restore
mysql -u root -p mydb < /backup/mydb_20260609.sql

Restore from a Compressed File (.sql.gz)

gunzip -c /backup/mydb_20260609.sql.gz | mysql -u root -p mydb

Alternatively, use zcat which may be slightly faster on some systems:

zcat /backup/mydb_20260609.sql.gz | mysql -u root -p mydb

Restore with Progress Tracking (Large Files)

For large backup files, install pv (pipe viewer) to see real-time progress:

# Install pv if not already installed
apt install pv -y

# Restore with progress indicator
pv /backup/mydb_20260609.sql.gz | gunzip | mysql -u root -p mydb

Restore a Specific Table Only

When only one table is corrupted, you can dump and restore just that table instead of the entire database:

# Dump a specific table
mysqldump -u root -p mydb users > /backup/users_table.sql

# Restore just that table
mysql -u root -p mydb < /backup/users_table.sql

Automating Backups with Cron

Manual backups are better than nothing, but automated scheduled backups are what actually protect production data. A well-written cron job runs every night without requiring any human intervention.

Create a Backup Script

Create the file /usr/local/bin/mysql-backup.sh:

#!/bin/bash
# MySQL Automated Backup Script
# AsiaGB VPS Guide — 2026-06-09

BACKUP_DIR="/backup/mysql"
MYSQL_USER="root"
MYSQL_PASS="YourSecurePassword"
KEEP_DAYS=30
DATE=$(date +%Y%m%d_%H%M%S)

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

# Dump all databases with compression
mysqldump -u "$MYSQL_USER" -p"$MYSQL_PASS" \
  --all-databases \
  --single-transaction \
  --routines \
  --triggers \
  | gzip > "$BACKUP_DIR/all_db_${DATE}.sql.gz"

# Log result
if [ $? -eq 0 ]; then
    echo "[$(date)] SUCCESS: all_db_${DATE}.sql.gz" >> /var/log/mysql-backup.log
else
    echo "[$(date)] ERROR: Backup failed!" >> /var/log/mysql-backup.log
fi

# Remove backups older than KEEP_DAYS
find "$BACKUP_DIR" -name "*.sql.gz" -mtime +$KEEP_DAYS -delete
echo "[$(date)] Cleaned up backups older than $KEEP_DAYS days" >> /var/log/mysql-backup.log

Make the script executable:

chmod +x /usr/local/bin/mysql-backup.sh

Add the Cron Job

crontab -e

Add this line to run the backup every night at 02:00:

# Nightly MySQL backup at 02:00
0 2 * * * /usr/local/bin/mysql-backup.sh

For higher-frequency backups (every 6 hours):

0 */6 * * * /usr/local/bin/mysql-backup.sh

Verify the Cron Job is Working

# Check the backup log
tail -f /var/log/mysql-backup.log

# List backup files
ls -lh /backup/mysql/

# Test the script manually
/usr/local/bin/mysql-backup.sh

Using mysqlpump for Large Databases

mysqlpump (with a "p") was introduced in MySQL 5.7 and supports parallel export across multiple threads, making it significantly faster than mysqldump for large databases.

# Backup with mysqlpump using 4 parallel threads
mysqlpump -u root -p \
  --default-parallelism=4 \
  --compress-output=LZ4 \
  --all-databases \
  > /backup/all_db_pump_$(date +%Y%m%d).sql

# Backup selected databases only
mysqlpump -u root -p \
  --default-parallelism=4 \
  --include-databases=mydb,mydb2 \
  > /backup/selected_db_$(date +%Y%m%d).sql

Key advantages of mysqlpump over mysqldump:

• Parallel export reduces backup time significantly on multi-core servers
• Built-in progress indicator shows completion percentage during the dump
• Native support for multiple compression algorithms (LZ4, ZLIB) built into the tool
• More granular include/exclude options for databases and tables
• Output files are generally smaller thanks to better compression efficiency

Backup Security and File Management

A MySQL backup file contains your entire database in plain SQL. Protecting these files is just as important as making them.

Set Correct Permissions

# Backup directory: readable only by root
chmod 700 /backup/mysql
chown root:root /backup/mysql

# Individual backup files: readable only by root
chmod 600 /backup/mysql/*.sql.gz

Verify Backup Integrity

After every backup, verify that the file is not corrupted and that the dump completed successfully:

# Test that the gz file is not corrupted
gunzip -t /backup/mysql/all_db_20260609.sql.gz && echo "File OK"

# Check the last few lines of the dump for "Dump completed"
gunzip -c /backup/mysql/all_db_20260609.sql.gz | tail -5

Offsite Backup with rclone

For true disaster recovery, send backups off-site to cloud storage at least once a week. Using rclone makes this straightforward with any S3-compatible provider, Google Drive, or Backblaze B2:

# Example: sync backup folder to S3
rclone copy /backup/mysql/ s3:mybucket/mysql-backup/ --min-age 1h

# Add to cron to run 30 minutes after the local backup
30 2 * * * rclone copy /backup/mysql/ s3:mybucket/mysql-backup/ --min-age 1h

Troubleshooting Common Issues

Even with straightforward commands, backup and restore operations can fail in predictable ways. Here are the most common problems and how to fix them.

Error: Access Denied for User 'root'@'localhost'

This occurs when credentials are wrong or the user lacks privileges. Create a dedicated backup user for better security:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'StrongPassword';
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT ON *.* TO 'backup_user'@'localhost';
FLUSH PRIVILEGES;

Restore Is Extremely Slow on Large Databases

Temporarily tune MySQL for faster imports by adding these settings to /etc/mysql/my.cnf:

[mysqld]
innodb_buffer_pool_size = 2G
innodb_log_file_size = 512M
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

Also wrap your restore in a session that disables foreign key checks:

mysql -u root -p mydb <<EOF
SET FOREIGN_KEY_CHECKS=0;
SET UNIQUE_CHECKS=0;
SOURCE /backup/mydb_20260609.sql;
SET FOREIGN_KEY_CHECKS=1;
SET UNIQUE_CHECKS=1;
EOF

Best Practices for Production MySQL Backup

A robust backup strategy goes beyond running a daily cron job. Consider these additional practices for any production deployment:

• Backup Before Every Deploy — Before running database migrations or deploying new code, take a manual backup as an immediate safety net.
• Test Your Restores Monthly — A backup you have never restored is unproven. Spin up a test server and restore the latest backup every month to confirm it works.
• Monitor Backup File Sizes — If today's backup file is significantly smaller than yesterday's, the dump may be incomplete. Add size-based alerting to your script.
• Enable Binary Logging — MySQL Binary Logs enable Point-in-Time Recovery, allowing you to restore to the exact second before data was lost.
• Alert on Backup Failure — Your cron script should notify you by email or messaging when the exit code is non-zero. Silent failures are the worst kind.
• Encrypt Sensitive Backups — For databases containing personal data, consider encrypting backup files at rest using GPG before uploading to remote storage.

Frequently Asked Questions (FAQ)

What is the difference between mysqldump and mysqlpump?

mysqldump is the traditional single-threaded tool that exports SQL as plain text, suitable for small to medium databases. mysqlpump is a newer tool that supports parallel export of multiple databases simultaneously, has a built-in progress indicator, and can compress output natively. It is recommended for databases larger than 1 GB or when you need to back up multiple databases at the same time.

After restoring a MySQL database, some data is missing. What should I check?

If data is missing after a restore, check three main areas: 1) Charset mismatch — the dump may use utf8mb4 but the target database is utf8. Inspect the CHARACTER SET declarations in the .sql file. 2) Transactions rolled back due to foreign key constraints — add SET FOREIGN_KEY_CHECKS=0 before importing and re-enable after. 3) Incomplete dump — verify the backup completed by checking the last line of the .sql file for the "Dump completed on" message.

How do I set up an automated nightly MySQL backup with cron?

Open crontab with crontab -e and add: 0 2 * * * /usr/bin/mysqldump -u root -p'YOURPASSWORD' --all-databases | gzip > /backup/db_$(date +\%Y\%m\%d).sql.gz — this runs every night at 02:00 and compresses the output with gzip. Store backups outside the web root and automatically purge files older than 30 days with find /backup -name '*.sql.gz' -mtime +30 -delete.

How can I speed up backups of a large MySQL database?

Several techniques speed up large database backups: 1) Use mysqlpump instead of mysqldump with --default-parallelism=4 for parallel threads. 2) Use Percona XtraBackup for InnoDB as a hot backup that requires no table locks. 3) Add --single-transaction to mysqldump for consistent InnoDB backups without locking. 4) Pipe output through gzip or zstd to reduce I/O time. 5) Split backups per database or per table instead of dumping everything at once.