How to Backup MySQL Database on Command Line

For all of your SQL backup needs, the mysqldump command does everything for you. This handy utility creates a plain text file with the SQL commands needed to re-create the database.

This makes it much easier to transfer databases to other servers as well as creating backups.

How To Backup a Single Database On The Command Line

In order to backup a single database, you’ll just need to run this statement at your command prompt:

mysqldump > dump.sql

In this case, “dump.sql” is the backup file you want to generate. You can change that filename to whatever you want.

How To Backup All Databases on The Command Line

If you maintain multiple sites with multiple databases, you can also back them all up with one command:

mysqldump --all-databases > dump.sql

How To Backup a WordPress Database On Command Line

Using the command line is simply faster and more efficient. For example, with two commands, you can locate and backup a WordPress database.

First, make sure you change directories into the root of the WordPress site (where the core files are located).

Then drop this command:

dw(){ dr="${1-$PWD}"; config="${dr%/}/wp-config.php"; db=$(grep "DB_NAME" $config|cut -d\' -f4); usr=$(grep "DB_USER" $config|cut -d\' -f4); pw=$(grep "DB_PASSWORD" $config|cut -d\' -f4); pre=$(grep "table_prefix" $config|cut -d\' -f2);}; dw

Now the database name and user/password information are stored in variables that will be used when you run the next command:

mysqldump -Q --add-drop-table --routines -h${host-localhost} -p"$pw" -u$usr $db

So with two commands you now have a backup of the WordPress database.

Christopher Maiorana Content Writer II

Christopher Maiorana joined the InMotion community team in 2015 and regularly dispenses tips and tricks in the Support Center, Community Q&A, and the InMotion Hosting Blog.

More Articles by Christopher

Was this article helpful? Let us know!