Backup and Restore MySQL Database with Mysqldump

The mysqldump tool is a command-line utility that backs up and restores MySQL databases. It saves your data as a text file full of SQL commands. A backup like this protects your website and app data from hard drive crashes, server errors and accidental deletions. It works across many MySQL versions.

You can use this built-in program to export an entire database or just specific tables, for example when you move your data to a new server. The output is a standard text file, so you can read it, copy it and restore it whenever something goes wrong.

Backup MySQL database

To back up a MySQL database, run the mysqldump command in a terminal. It exports all tables and events into a single SQL file, which is an exact copy of your data that you can restore later.

Before you start, check two things:

• You need a MySQL account with enough privileges to read every database you want to dump. On a Windows server, you also need permission to write to the folder where the backup file will be saved.
• The mysqldump program must be reachable from your terminal. If Windows says the command is not recognized, open the terminal in the bin folder of your MySQL installation, or add that folder to your PATH.

To open an elevated terminal, click Start, type cmd, right-click Command Prompt and select Run as administrator. A black console window opens, and its title bar starts with “Administrator”.

Use Command Prompt rather than Windows PowerShell 5.1 if the command saves its output with the redirection symbol (>). Windows PowerShell 5.1 writes redirected output as UTF-16 by default. That can leave you with a backup file that fails to restore.

The command below backs up all databases and locks the tables while the dump runs. The lock keeps the data from changing mid-backup, so every table in the file matches the same moment in time. While it is active, other sessions cannot write to the tables and your application may stall. Run this version when the server is quiet.

When the command finishes, you are returned to the prompt with no message. That is normal. Open the folder and check that the backup file exists and is not empty.

sudo mysqldump --lock-all-tables --all-databases --events > mysql_backup.sql

The single-transaction option also backs up all databases, but it keeps the data consistent without locking the tables for the whole run. The dump reads from a snapshot, so your site or app can keep working while the backup runs.

This works reliably for InnoDB tables, which is the default storage engine in current MySQL versions. If you still have older MyISAM tables, a transaction cannot protect them. For those, use the locking method from the previous section.

sudo mysqldump --single-transaction --all-databases --events > mysql_backup.sql

If you only need one database, use the command below instead. The backup file is smaller, it runs faster, and it is easier to restore on its own. Replace the database name and the output file name with your own values.

To restore a MySQL database from a backup file, use the mysql command. It can bring back all your databases or just the one you need. Before you start, keep these points in mind:

• A restore replays the SQL in the file. Any tables with the same names that already exist can be overwritten, so check which server you are connected to.
• If you can, take a fresh backup of the current state first. You can then roll back if the restore is not what you wanted.
• Large files can take a while. The prompt only returns when the import is done.

Run the command below to restore every database from a complete backup. Use it when the backup file came from an all-databases dump. When it finishes without an error message, the import is done. Log in to MySQL and check that your databases and tables are back.

sudo mysql < mysql_backup.sql

To restore a single database, create an empty database with the same name first, then run the restore into it. If you skip that step, the restore can fail with an "Unknown database" error. Do the two parts in order:

1. Create the empty database.
2. Run the restore command into that database.

sudo mysql database_name < mysql_backup.sql

That completes the process. Your data is back in MySQL. Open your site or app and confirm that it can read the restored data. If it cannot, check the database name and the user's privileges on it.

Conclusion:

Backing up and restoring MySQL databases with mysqldump guards against data loss. Using the commands outlined here keeps your database safe. Here are some key takeaways:

• Regular Backups: Schedule regular backups to avoid data loss due to unexpected issues. On Windows, Task Scheduler can run the backup command for you.
• Use Locking Wisely: Choose between locking tables and single-transaction methods based on your needs. Locking suits quiet servers and older table types. A single transaction suits busy InnoDB databases.
• Specific Database Backups: If required, you can focus on specific databases rather than backing up all databases.
• Test Restores: Regularly test your backup and restore process to ensure reliability. A restore into a test database on another machine is the safest check.
• Monitor Backup Files: Monitor backup file sizes and retention policies to manage storage efficiently. A file that suddenly shrinks often means the backup failed. Keep the files somewhere safe, because they contain all your data.

Following these practices keeps your MySQL databases healthy and leaves you prepared if something goes wrong.