How to Backup a MySQL Database: Step-by-Step

Updated 15 November 2026 · Standard formula, shown below

This guide walks you through backing up a MySQL database using the standard mysqldump utility. You’ll learn how to create a logical backup (a .sql file) that can be restored later, and how to automate the process. You’ll need command-line access to the MySQL server and the credentials of a user with at least SELECT and LOCK TABLES privileges.

What You’ll Need

Access to a terminal or command prompt on the machine where MySQL is installed, or network access to the MySQL server.
MySQL client tools installed (mysqldump comes with the MySQL server package).
MySQL username and password with sufficient privileges (usually root or a user with BACKUP_ADMIN).
Enough disk space on the destination to hold the dump file.

Step-by-Step

Open a terminal or command prompt. On Linux/macOS, use Terminal. On Windows, use Command Prompt or PowerShell. If MySQL is on a remote server, SSH into it first.
Navigate to a directory where you want to store the backup (optional but recommended). For example: cd /backups or cd C:\backups.
Run the mysqldump command. The basic syntax is:
mysqldump -u username -p database_name > backup.sql
Replace username with your MySQL user, database_name with the name of the database you want to back up, and backup.sql with the desired output filename. After pressing Enter, you’ll be prompted for the password.
Include the –single-transaction flag for InnoDB tables (recommended) to get a consistent backup without locking tables:
mysqldump -u username -p --single-transaction database_name > backup.sql
Back up multiple databases at once by using --databases followed by the names:
mysqldump -u username -p --databases db1 db2 > backup.sql
Back up all databases with --all-databases:
mysqldump -u username -p --all-databases > all_databases.sql
Compress the backup to save space by piping through gzip:
mysqldump -u username -p database_name | gzip > backup.sql.gz
Verify the backup file was created by listing the directory: ls -lh backup.sql (Linux/macOS) or dir backup.sql (Windows). Check that the file size is reasonable (not zero).
Test the backup by restoring it to a temporary database (optional but highly recommended). Create a new database, then import the dump:
mysql -u username -p -e "CREATE DATABASE test_restore;"
mysql -u username -p test_restore < backup.sql
Automate the backup with a cron job (Linux/macOS) or Task Scheduler (Windows). For example, to run daily at 2 AM, add this crontab entry:
0 2 * * * mysqldump -u username -p'password' dbname > /backups/db_$(date +\%Y\%m\%d).sql
Note: Storing the password in the command line is insecure; use a config file with proper permissions instead.

Common Mistakes

Forgetting to include the password prompt. If you omit -p, mysqldump will fail with an access denied error.
Using single quotes around the password in the command line. This can expose the password in process lists. Use a config file or environment variable.
Not using --single-transaction for InnoDB tables. This can cause table locks and inconsistent backups.
Overwriting a previous backup. Always use a unique filename (e.g., include a timestamp) to avoid losing older backups.
Backing up to the same disk as the database. If the disk fails, you lose both. Store backups on a different drive or remote server.

Tips

Use --routines and --triggers to include stored procedures, functions, and triggers in the backup: mysqldump --routines --triggers dbname > backup.sql.
For large databases, consider using --quick to avoid buffering the entire dump in memory.
Use --opt (which is on by default) to enable optimizations like --quick, --add-drop-table, and --extended-insert.
Encrypt the backup if it contains sensitive data. You can pipe through openssl: mysqldump ... | openssl enc -aes-256-cbc -out backup.sql.enc.
Test your backups regularly by restoring them to a development environment. A backup you can’t restore is worthless.
Keep a retention policy (e.g., keep daily backups for 7 days, weekly for a month) to manage disk space.

Troubleshooting

“Access denied” error: Ensure the username has the required privileges (SELECT, LOCK TABLES, SHOW VIEW, etc.). You can grant them with: GRANT SELECT, LOCK TABLES ON dbname.* TO 'user'@'localhost';
“mysqldump: Couldn't execute 'SHOW TABLES':” This often means the user lacks the SHOW VIEW privilege. Grant it if needed.
“The server is running with the --read-only option” – this doesn’t prevent mysqldump, but if you see “Can't create table” during restore, the target server is read-only. Disable read-only temporarily during restore.
Backup file is empty or too small: Check that the database name is correct and that you have access. Run mysql -u user -p -e "SHOW DATABASES;" to list databases.
Out of disk space: Check available space with df -h (Linux) or wmic logicaldisk get size,freespace (Windows). Free up space or backup to another location.
Timeout errors on large databases: Increase the net_read_timeout and net_write_timeout variables, or use --max_allowed_packet=512M to allow larger dumps.

Frequently Asked Questions

Can I back up a MySQL database without stopping the server?

Yes, using --single-transaction for InnoDB tables gives a consistent snapshot without locking the tables, so the server can keep running.

What is the difference between mysqldump and physical backup (copying data files)?

mysqldump creates a logical backup (SQL statements) that is portable across MySQL versions and platforms. Physical backups copy the raw data files and are faster for large databases but are tied to the same MySQL version and storage engine.

How do I restore a MySQL database from a backup?

Use the mysql client: mysql -u username -p database_name < backup.sql. If the database doesn’t exist, create it first with CREATE DATABASE database_name;.

How often should I back up my MySQL database?

It depends on how much data you can afford to lose. For critical production databases, daily backups are common, plus binary log backups for point-in-time recovery. For development, weekly may suffice.

Can I back up only specific tables from a database?

Yes, list the table names after the database name: mysqldump dbname table1 table2 > backup.sql.

Is it safe to store the MySQL password in a script?

It’s not recommended because it can be exposed. Use a MySQL option file (e.g., ~/.my.cnf) with permissions set to 600, or use environment variables.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.