MySQL Dump using the mysqldump command on Linux

Written by Rudy Saturday, 01 September 2012 09:33

Print

This tutorial shows you how to perform a MySQL dump (backup) using the mysqldump command on Linux, this backups your entire database by dumping it from MySQL into a flat file you can then store the MySQL dump file or transfer your databases to another server. The examples in this tutorial use the SQL root user account, you can use another account with sufficient permissions if you prefer.

Basic mysqldump to .sql

To perform a MySQL dump of your database you need to enter the mysqldump command below as the root user (or a user with sufficient access) on your Linux server:

mysqldump -u root -p database-name > database-backup.sql

Note: The above will prompt you for the password on the command line once entered it will perform a MySQL dump to a file called database-backup.sql this file is uncompressed.

MySQL Dump to .gz

mysqldump command linux

To perform a dump to .gz (gzip) you would enter the following command at the command line:

mysqldump -u root -p database-name | gzip -v > database-backup.sql.gz

Backup a remote MySQL database using SSH & mysqldump

To backup a remote MySQL server and download the database to your local server with .gz compression you would enter the following on the command line:

mysqldump -u root -p database-name | gzip -c | ssh user@your-local-machine 'cat > /tmp/database-backup.sql.gz'

Note the above command is ran on the REMOTE server that has the MySQL server running the database you want to push to your local machine via SSH.

Check the status / size of a SQL dump file

If you are performing a dump of a large database and wish to check the status to see how long is left then use the du command in another shell to find out how large the file is, example given below:

du -h /tmp/database-backup.sql

This will tell you how large the file is, I have tried other methods using pv and other SQL patches but have found nothing reliable to give me a progress bar during MySQL dumps. If you have a solution for a progress bar please feel free to sign up below and drop me a comment, I will include it in this tutorial.