Backup and Restore MySQL on a VPS with mysqldump

phpMyAdmin is fine for a quick export, but once your database crosses a few hundred MB it starts timing out, truncating exports, or just spinning until the browser gives up. If you're running a VPS - no cPanel, no GUI backup button - you need to know mysqldump cold. Here's how to back up and restore MySQL from the command line without losing data or locking up your live site.

Why the Browser-Based Export Breaks First

phpMyAdmin routes everything through PHP's execution time and memory limits, plus your browser's own connection timeout. A 50MB database exports fine. A 2GB WooCommerce or multisite database usually doesn't - the process either hits max_execution_time and dies mid-export, or the download itself gets cut off and you end up with a truncated .sql file that fails silently on restore.

mysqldump runs directly on the server, outside PHP entirely, so none of those limits apply. It's also the only practical option if you're managing a bare VPS running your own MySQL or MariaDB instance with no control panel in front of it.

Taking a Backup with mysqldump

SSH into the server and run:

mysqldump -u dbuser -p dbname > backup.sql

You'll be prompted for the password interactively - don't put it directly after -p on the command line, since that leaves it sitting in your shell history and in ps aux output for anyone else on the box to see.

For a WordPress site, add a couple of flags that matter more than people expect:

mysqldump -u dbuser -p --single-transaction --quick --lock-tables=false dbname > backup.sql

• --single-transaction - takes a consistent snapshot using InnoDB's transaction isolation instead of locking every table. Without it, a large dump can lock writes on a live site for the entire export.
• --quick - streams rows instead of buffering the whole table in memory first, which matters once a table has millions of rows.
• --lock-tables=false - pairs with --single-transaction so InnoDB tables aren't locked while MyISAM tables (if you have any left) still get a light lock.

"mysqldump: Got error: 1044: Access denied"

Cause: the MySQL user you're dumping with doesn't have SELECT, LOCK TABLES, and SHOW VIEW privileges on that database - common when you created a scoped-down app user for WordPress's wp-config.php and are now trying to reuse it for backups.

Fix: grant the missing privileges or dump as root:

GRANT SELECT, LOCK TABLES, SHOW VIEW, RELOAD ON dbname.* TO 'dbuser'@'localhost';
FLUSH PRIVILEGES;

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

Cause: usually one oversized row or BLOB column tripping max_allowed_packet on the server side, not a network issue.

Fix: bump the packet size for the dump session:

mysqldump -u dbuser -p --max_allowed_packet=512M dbname > backup.sql

If it still drops, check SHOW VARIABLES LIKE 'max_allowed_packet'; in a MySQL shell and raise the server-wide value in /etc/mysql/my.cnf under [mysqld], then restart MySQL.

Backup file is 0 bytes or stops halfway

Cause: almost always disk space. mysqldump doesn't check free space before it starts writing - it just writes until the disk is full, then fails partway with no obvious error in your terminal.

Fix: check df -h before every large dump, and dump straight to compressed output so the file never gets that big in the first place:

mysqldump -u dbuser -p --single-transaction dbname | gzip > backup.sql.gz

Restoring from a Dump

Create the target database first if it doesn't exist, then pipe the file in:

mysql -u dbuser -p -e "CREATE DATABASE IF NOT EXISTS dbname CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;"
mysql -u dbuser -p dbname < backup.sql

If your backup is gzipped, skip the intermediate unzip step:

gunzip < backup.sql.gz | mysql -u dbuser -p dbname

Set the character set explicitly when you create the database. If you skip it and MySQL falls back to latin1, every accented character, emoji, and non-English string in your content comes back as garbled question marks after the restore - and by then you've usually already dropped the old database.

"ERROR 1064: You have an error in your SQL syntax"

Cause: the dump file got corrupted or truncated in transit - common after an SFTP transfer in ASCII mode instead of binary, or a copy-paste through a terminal that mangled line endings.

Fix: re-download the file with scp or rsync rather than copy-paste, and confirm the file size matches the source before restoring. Run tail backup.sql - a healthy dump ends with a clean -- Dump completed comment, not a line cut off mid-statement.

Automating It with Cron

Manual backups are the ones you forget to take right before something breaks. Put this in a script at /root/backup-db.sh (no need for a shebang line, since we'll call it through bash directly in cron):

TIMESTAMP=$(date +%F)
mysqldump -u dbuser -p'yourpassword' --single-transaction dbname | gzip > /home/backups/db-$TIMESTAMP.sql.gz
find /home/backups -name "db-*.sql.gz" -mtime +14 -delete

The find ... -delete line prunes anything older than 14 days so backups don't quietly fill the disk over a few months. Make it executable and schedule it:

chmod 700 /root/backup-db.sh
crontab -e
0 2 * * * bash /root/backup-db.sh

That runs at 2 AM daily. Use chmod 700 on the script since it contains a plaintext password - readable only by root.

Prevention: Backups That Actually Save You

• Store copies off the server. A backup sitting on the same disk as the database it protects doesn't help if that disk fails or the VPS gets compromised. Sync nightly to S3, Backblaze, or another remote host with rclone or rsync.
• Test a restore quarterly. A backup you've never restored is a guess, not a backup. Spin up a throwaway database and restore into it to confirm the dump is actually usable.
• Keep at least one backup outside the retention window of the last hack or bad deploy. If you only keep 7 days and an issue goes unnoticed for 10, every backup you have is already compromised.
• Match the restore's character set to the dump's. Add --default-character-set=utf8mb4 to the mysql restore command if your database uses it, to avoid the garbled-text problem above.

If you're on a Getwebup-managed VPS and would rather not touch the command line at all, our support team can set up automated off-site database backups on your behalf - just open a ticket with the database name and how often you'd like it to run.