> ## Documentation Index > Fetch the complete documentation index at: https://help.freakhosting.com/llms.txt > Use this file to discover all available pages before exploring further. # How to Back Up and Restore MySQL Databases with mysqldump > Back up a MySQL or MariaDB database with mysqldump --single-transaction shop > shop.sql, restore it with mysql shop < shop.sql, and schedule daily dumps. To back up a MySQL database, run `sudo mysqldump --single-transaction --routines --triggers shop > shop.sql` (on MariaDB the command is `mariadb-dump`). To restore it, create an empty database and run `sudo mysql shop < shop.sql`. The `.sql` file holds every table and row as SQL statements, so you can restore it on the same or a new server. | Difficulty | Time | Works on | Needs | | - | - | - | - | | Beginner | 10 minutes | MySQL 8.0+ and MariaDB 10.5+ on any supported Linux | sudo access, free disk space for the dump | ## Before you start * Check you have enough free space for the dump. See [check disk space](https://help.freakhosting.com/portals/other/how-to-check-disk-space-on-linux.md). * On Ubuntu and Debian, `sudo` lets the dump tool log in as database root without a password. On other setups add `-u root -p` (or another admin user) to each command. * On MariaDB, `mysqldump` and `mysql` still work as older names for `mariadb-dump` and `mariadb`. ## How to back up one database **1. Make a backup folder** ```bash theme={null} sudo mkdir -p /var/backups/mysql ``` **2. Dump the database** ```bash theme={null} sudo mysqldump --single-transaction --routines --triggers shop | gzip | sudo tee /var/backups/mysql/shop-$(date +%F).sql.gz > /dev/null ``` This writes a compressed file such as `shop-2026-11-04.sql.gz`. **3. Check the file** ```bash theme={null} ls -lh /var/backups/mysql/ gunzip -t /var/backups/mysql/shop-2026-11-04.sql.gz && echo OK ``` `OK` means the archive is complete. What the options do: | Option | Meaning | | - | - | | `--single-transaction` | Takes a consistent snapshot of InnoDB tables without locking your site | | `--routines` | Includes stored procedures and functions | | `--triggers` | Includes triggers (on by default, listed for clarity) | | `--events` | Includes scheduled events (add it if you use them) | ## How to back up all databases ```bash theme={null} sudo mysqldump --single-transaction --routines --triggers --events --all-databases | gzip | sudo tee /var/backups/mysql/all-$(date +%F).sql.gz > /dev/null ``` `--all-databases` includes the `mysql` system database, so it also keeps your users and passwords. A single-database dump does not. [Red Hat notes](https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/configuring_and_using_database_servers/using-mariadb) that a dump also does not include configuration files such as `/etc/my.cnf`, so copy those separately. ## How to restore a MySQL database > [!WARNING] > > Restoring into a database that already has tables overwrites tables with the same names. Restore into a new, empty database first if you are not sure, then switch your app over. **1. Create an empty database** ```bash theme={null} sudo mysql -e "CREATE DATABASE shop CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;" ``` Skip this if the database already exists and you want to overwrite it. **2. Import the dump** For a plain `.sql` file: ```bash theme={null} sudo mysql shop < shop.sql ``` For a compressed `.sql.gz` file: ```bash theme={null} gunzip < /var/backups/mysql/shop-2026-11-04.sql.gz | sudo mysql shop ``` **3. Check the data** ```bash theme={null} sudo mysql -e "SHOW TABLES FROM shop;" ``` To restore a full `--all-databases` dump, leave out the database name: `gunzip < all-2026-11-04.sql.gz | sudo mysql`. ## How to schedule daily MySQL backups with cron **1. Open root's crontab** ```bash theme={null} sudo crontab -e ``` **2. Add two lines** ```bash theme={null} 30 3 * * * mysqldump --single-transaction --routines --triggers --events --all-databases | gzip > /var/backups/mysql/all-$(date +\%F).sql.gz 50 3 * * * find /var/backups/mysql -name '*.sql.gz' -mtime +7 -delete ``` The first line dumps everything at 03:30 every day. The second deletes dumps older than 7 days. In a crontab, `%` must be written as `\%`. **3. Save and check** Save and close the editor, then run `sudo crontab -l` to confirm. Learn more in [schedule tasks with cron](https://help.freakhosting.com/portals/other/how-to-schedule-tasks-with-cron.md). > [!TIP] > > A backup on the same disk is lost if the server is lost. Copy the files off the server with [rclone](https://help.freakhosting.com/portals/other/how-to-back-up-to-cloud-storage-with-rclone.md) or [restic](https://help.freakhosting.com/portals/other/how-to-make-encrypted-backups-with-restic.md). On a VPS you can also [schedule panel backups](https://help.freakhosting.com/portals/vps-panel/how-to-schedule-and-restore-backups.md) of the whole server. ## How to check a backup works The only real test is a restore. Once a month, restore the latest dump into a test database: ```bash theme={null} sudo mysql -e "CREATE DATABASE shop_test;" gunzip < /var/backups/mysql/shop-2026-11-04.sql.gz | sudo mysql shop_test sudo mysql -e "SELECT COUNT(*) FROM shop_test.orders;" sudo mysql -e "DROP DATABASE shop_test;" ``` Replace `orders` with one of your own tables. ## Frequently asked questions ### Does mysqldump lock my website while it runs? No. `--single-transaction` turns off table locking and dumps everything from one consistent snapshot. That snapshot is only guaranteed for InnoDB tables (the default engine). Avoid `ALTER TABLE` or `DROP TABLE` while the dump runs. ### How do I move a database to a new server? Dump it on the old server, copy the file with [scp or rsync](https://help.freakhosting.com/portals/other/how-to-copy-files-with-scp-and-rsync.md), create the database and user on the new server, and import the file. ### Why do I get "Unknown database" when restoring? A single-database dump has no `CREATE DATABASE` line, so the database you name in `mysql shop < shop.sql` must already exist. Create it first, then import. ### How do I back up PostgreSQL? Use `pg_dump` instead. See [install PostgreSQL](https://help.freakhosting.com/portals/other/how-to-install-postgresql.md). ## Related guides > [card] [VPS and dedicated server guides](https://help.freakhosting.com/portals/other/vps-and-dedicated-server-guides.md) > > Every guide in this section. > [card] [Back up a Linux server](https://help.freakhosting.com/portals/other/how-to-back-up-a-linux-server.md) > > Files, configs and databases. > [card] [Schedule tasks with cron](https://help.freakhosting.com/portals/other/how-to-schedule-tasks-with-cron.md) > > Run backups automatically. > [card] [Encrypted backups with restic](https://help.freakhosting.com/portals/other/how-to-make-encrypted-backups-with-restic.md) > > Store dumps off the server. > [card] [Create a database and user](https://help.freakhosting.com/portals/other/how-to-create-a-mysql-database-and-user.md) > > Recreate users after a restore. *** > [!NOTE] > > **Checked against the official [MySQL mysqldump documentation](https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html) and [Red Hat MariaDB backup guide](https://docs.redhat.com/en/documentation/red_hat_enterprise_linux/10/html/configuring_and_using_database_servers/using-mariadb)** on 4 November 2026; dump, gzip and restore commands tested on Ubuntu 24.04 LTS with MariaDB 10.11. Commands can differ on other distributions or releases. > [!NOTE] > > ### Need Extra Help? > > If you encounter any issues, our support team is ready to assist: > > * **Live Chat:** Quick assistance via our website. > * **Support Ticket:** [Open a Ticket](https://freakhosting.com/clientarea/submitticket.php) > * **Discord:** [Join our Community](https://discord.gg/freakhosting) > * **Email:** [support@freakhosting.com](mailto:support@freakhosting.com) > [!TIP] > > ### Save on Your Hosting > > Ready to upgrade your hosting? Use code **KB20** at checkout for **20% off** your first order! *** **Last Updated:** November 2026 This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.