Error message

Deprecated function: implode(): Passing glue string after array is deprecated. Swap the parameters in drupal_get_feeds() (line 394 of /home1/tylerfra/public_html/includes/common.inc).

Drupal - Use Drush to Export/Import a MySQL Database Dump File

tyler's picture
Submitted by tyler on Tue, 02/08/2011 - 22:16
Updated: 2017-10-20
Category: 
Code
Tags: 
drupal 6.x
drush
mysql
ssh
planet drupal
drupal 7.x
drupal 8.x

Export Database to File

So today I wanted to learn how to export a Drupal database to a file quickly. Back in the day I would've logged in through cPanel and navigated to PHPMyAdmin, then manually select an export of the database and have to choose where to save the file. Not to mention all the previous steps listed would need to be preceeded by a Drupal cache flush (I don't like the cache inside a database backup). Typically this would've taken me about 5 minutes to complete, well my friends, no longer is that the case. Behold the power of Drush (once you are ssh'd in and navigated to your drupal root directory)

Drupal 6 and 7

drush cc
drush sql-dump > ~/my-sql-dump-file-name.sql

Drupal 8

drush cr
drush sql-dump > ~/my-sql-dump-file-name.sql

Those two commands clear all the Drupal caches and then dump the sql database to a file in your home directory. Awesome sauce!

Download Database (optional)

If the sql dump file is on a remote server, you can use this terminal command to download a copy of the sql file:

scp tyler@example.com:~/my-sql-dump-file-name.sql ~/Desktop/

Import Database

You can use this command to import the sql dump back into your drupal database.

drush sql-drop
drush sql-cli < ~/my-sql-dump-file-name.sql

A Drush-Less Approach

If you don't have Drush setup (which you should have setup if you want to be cool), you can check out this article to make a backup copy of your Drupal's MySQL database from a terminal: Drupal - How to Make a Backup Copy of MySQL Database

Comments

busla (not verified)

Sun, 06/24/2012 - 17:37

Nice one :-) worked like a charm!

busla (not verified)

Sun, 06/24/2012 - 17:48

wait.. it didn´t. Drush didn´t create the file but the whole export code is visible in terminal with "success" messages

tyler's picture

tyler

Sun, 06/24/2012 - 21:12

Did you remember to use 'standard out' after the drush sql-dump command? For example:

drush sql-dump > ~/my-sql-dump-file-name.sql

If you just use 'drush sql-dump' it will just output to the terminal window. You need to use '>' to write it to a file name. Hope this helps!

Santiago Navatta (not verified)

Tue, 11/06/2012 - 19:56

Hi,

How can I do if I must only export everything but the users and blogs post from a local development to a production server?

Cheers

tyler's picture

tyler

Tue, 11/06/2012 - 20:21

Depending on how many users and blog post nodes I had in my localhost environment, I would probably just delete them all. Views Bulk Operations works well for this. Or you can write a module that utilises the 'batch' process to delete them. Or you can do it the old fashioned way and just manually delete them before dumping your MySQL.

hkwhitten (not verified)

Sun, 02/08/2015 - 13:01

Thanks - very helpful.

Garry (not verified)

Sat, 02/28/2015 - 21:08

Thank You!

 

You Rock! Mr Frankeinstein

Garry

kiwimind (not verified)

Wed, 02/10/2016 - 10:21

Bear in mind that you can run the following couple of commands too:

drush sql-dump --gzip --result-file

will dump database and gzip it, placing it in drush's default directory under a timestamp

drush sql-dump --gzip --result-file=/path/to/file.sql

will dump database to file specified before gzipping it

Please note that running drush sql-dump can expose sensitive information though, so be aware of what else may be running on the server and/or what's being logged.

Erick (not verified)

Wed, 10/19/2016 - 16:00

I have a specific question, I have a proyect locally and i'm using acquia dev to use drush console, now I want to import my local DB to the remote server my question is

can I import DB from my local drush or do I need to install drush in my remote server?

 

If I need install drush in my remote server, how can I install it?

 

Thank you!

tyler's picture

tyler

Wed, 10/19/2016 - 16:21

Although it's nice to have Drush on your remote server, it's not required. You can use this technique to import the DB on the remote server after using Drush to export it on the local server: http://tylerfrankenstein.com/code/drupal-how-make-backup-copy-mysql-data...

Please consult the drush project home page for information on installing it.