MySQL Dump using the mysqldump command on Linux
Written by Rudy Saturday, 01 September 2012 09:33
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
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.
