If your MySQL database disappeared tomorrow, could you bring it back? Hardware fails, bad updates corrupt tables, and one wrong DROP TABLE can erase months of work in a second. This guide walks you through backing up and restoring MySQL databases with mysqldump on Ubuntu 22.04 and 24.04: full backups, compressed backups, automatic daily backups, and restoring everything from a single table to an entire server.

By the end, you will have a working backup routine you can trust, plus the exact restore commands for when disaster strikes.

Prerequisites

  • A server running Ubuntu 22.04 or 24.04 with MySQL 8.0 installed (works identically on Debian).
  • SSH access with sudo privileges.
  • The MySQL root password, or a dedicated backup user with the right privileges.
  • A place to store backups outside the database itself, for example /var/backups/mysql.

mysqldump ships with every MySQL installation, so there is nothing extra to install.

Step 1: Create a backup directory

Never store backups in /tmp (it gets wiped on reboot) or inside the MySQL data directory itself. Create a dedicated folder and lock it down:

sudo mkdir -p /var/backups/mysql
sudo chown root:root /var/backups/mysql
sudo chmod 700 /var/backups/mysql

chmod 700 means only the owner can read these files. Backups contain your actual data, so treat them like production data.

Step 2: Back up a single database

The simplest backup takes a consistent snapshot of one database into a SQL file:

mysqldump -u root -p mydb > /var/backups/mysql/mydb_backup.sql

You will be prompted for the root password. The resulting file contains every CREATE TABLE and INSERT needed to rebuild mydb from scratch.

For a production database that is actively being written to, use --single-transaction so the dump does not lock tables (requires InnoDB tables, which is the default in MySQL 8.0):

mysqldump -u root -p --single-transaction --quick --routines --triggers --events mydb > /var/backups/mysql/mydb_backup.sql

What each flag does:

  • --single-transaction: consistent snapshot without locking InnoDB tables, so your app keeps running.
  • --quick: streams rows one at a time instead of buffering the whole table in memory — essential for large tables.
  • --routines --triggers --events: includes stored procedures, triggers, and scheduled events, which plain dumps skip.

Step 3: Back up ALL databases at once

To dump every database on the server, including the mysql system database (users and grants), use:

mysqldump -u root -p --all-databases --single-transaction --routines --triggers --events > /var/backups/mysql/all_databases.sql

If you later restore this on a replica or a fresh server, add --set-gtid-purged=OFF to avoid GTID errors when the target is not part of the same replication setup:

mysqldump -u root -p --all-databases --single-transaction --set-gtid-purged=OFF > /var/backups/mysql/all_databases.sql

Step 4: Compress the backup

SQL dumps compress extremely well. Pipe the output through gzip to shrink it, often by 80-90%:

mysqldump -u root -p --single-transaction mydb | gzip > /var/backups/mysql/mydb_backup.sql.gz

Always verify the file looks sane before trusting it:

ls -lh /var/backups/mysql/mydb_backup.sql.gz
gunzip -c /var/backups/mysql/mydb_backup.sql.gz | head -40

You should see CREATE TABLE and INSERT statements in the first lines. A healthy dump also contains lines like -- MySQL dump 10.19 near the top.

Step 5: Automate daily backups with cron

Backups you run by hand stop the day you forget. Create a dedicated, least-privilege backup user first:

mysql -u root -p -e "CREATE USER 'backup'@'localhost' IDENTIFIED BY 'StrongPass123!';"
mysql -u root -p -e "GRANT SELECT, SHOW VIEW, TRIGGER, LOCK TABLES, EVENT ON *.* TO 'backup'@'localhost'; FLUSH PRIVILEGES;"

Then add a cron job that runs a full compressed backup every night at 2 AM and deletes dumps older than 7 days:

crontab -e
0 2 * * * /usr/bin/mysqldump -u backup -p'StrongPass123!' --all-databases --single-transaction --quick | gzip > /var/backups/mysql/all-$(date +\%F).sql.gz
30 2 * * * find /var/backups/mysql -name "*.gz" -mtime +7 -delete

Note the escaped percent signs (\%F) — cron requires this, since an unescaped % is treated as a newline. Also quote the password with single quotes so shell special characters survive.

Step 6: Restore a database from backup

To restore, reverse the process: create the database if needed, then feed the dump back into the mysql client.

For a plain (uncompressed) dump:

mysql -u root -p -e "CREATE DATABASE IF NOT EXISTS mydb;"
mysql -u root -p mydb < /var/backups/mysql/mydb_backup.sql

For a compressed dump:

gunzip -c /var/backups/mysql/mydb_backup.sql.gz | mysql -u root -p mydb

To restore all databases from an --all-databases dump, omit the database name — the dump contains CREATE DATABASE and USE statements itself:

mysql -u root -p < /var/backups/mysql/all_databases.sql

Step 7: Restore a single table

Sometimes you only need one table back. Dump and restore just that table by naming it after the database:

mysqldump -u root -p --single-transaction mydb users > /var/backups/mysql/mydb_users.sql
mysql -u root -p mydb < /var/backups/mysql/mydb_users.sql

The restore works because the dump begins with DROP TABLE IF EXISTS / CREATE TABLE. If the dump contains only INSERTs for an existing table (rare), make sure the table already exists or include the schema.

Step 8: Verify it worked

After any restore, confirm the data actually came back:

mysql -u root -p -e "USE mydb; SHOW TABLES; SELECT COUNT(*) AS row_count FROM users;"

Compare the row counts against a known-good value. For a full-server restore, also check that application users and grants survived:

mysql -u root -p -e "SELECT user, host FROM mysql.user;"
Golden rule: an untested backup is not a backup. Once a month, restore your latest dump to a test database (or a staging server) and confirm the application boots on it.

Common issues and fixes

ERROR 1045 (28000): Access denied for user 'root'@'localhost'

Wrong password or the user lacks privileges. On Ubuntu, MySQL's root account often uses the auth_socket plugin, which means password login fails — connect with sudo instead: sudo mysql. For mysqldump, either run it with sudo or create a dedicated backup user as in Step 5.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket

The MySQL service is not running. Start it and retry:

sudo systemctl start mysql
sudo systemctl status mysql

mysqldump locks the database and the site hangs during backup

You dumped MyISAM tables (or ran without --single-transaction), which takes table locks. Add --single-transaction --quick for InnoDB tables, and schedule dumps at low-traffic hours. Consider converting legacy MyISAM tables to InnoDB.

Restore fails with "MySQL server has gone away" on a large dump

The dump exceeds max_allowed_packet. Raise it temporarily in /etc/mysql/mysql.conf.d/mysqld.cnf:

max_allowed_packet = 512M

Then restart MySQL and re-run the restore.

Restoring an --all-databases dump on a replica fails with GTID errors

The dump recorded GTID information the target server cannot accept. Re-dump with --set-gtid-purged=OFF, or if you must use the existing dump, comment out the SET @@GLOBAL.GTID_PURGED line near the top before restoring.

Conclusion

You now have a complete mysqldump workflow: consistent single-database and full-server backups, compression, automated nightly cron backups with 7-day retention, and tested restore commands for full databases, single tables, and whole servers. Make the monthly restore test a habit — that is the step that separates a backup file from an actual recovery plan.

If you would rather not own this yourself, XpertBees sets up and manages automated, offsite MySQL backup routines for businesses — including monitoring so you know the backup ran before the day you need it.