Using drush to load a database file

Note: Updated Dec. 5, 2011 to reflect the latest changes in drush commands.

I backup databases, sometimes several times a week using drush.

$ drush sql-dump --result-file=backup.sql

(By the way, using the --result-file option instead of a redirect to prevent corrupting the charset in your data).

However, when reloading a backup I would always use the mysql command directly because drush doesn't have an import command. This means looking up the password and manually typing it in.

$ drush sql-connect
mysql  -hlocalhost -unathan -psomething some_database
$ mysql  -hlocalhost -unathan -psomething some_database < backup.sql

I finally figured out how to use drush for loading mysql dump files. From the command line you can convert the output of a command into a command itself, using the following syntax.

$ $(drush sql-connect) < backup.sql

Or, similarly, using the backtick character to wrap the command.

$ `drush sql-connect` < backup.sql
Tags:

Comments

using drush to load a dumped database backup

Great! ... Except.. what about when you get: :www bronius$ $(drush sql connect) < pp-site.sql -bash: Warning:: command not found :www bronius$ drush sql connect < pp-site.sql -bash: Warning:: command not found

I know drush itself is working just fine, because I used it to create the sql-dump in the first place.

2 possible solutions

Maybe you are using the latest version of Drush and should instead use the hyphenated command:

$(drush sql-connect) < test.sql

The other possibility is that you are using a bash alias to point to the drush script instead of a symlink. I'm not sure, but that might cause the wrapped drush command to fail.

yes, sql-connect not sql connect

Thanks! You're so correct. Using the appropriate sql-connect syntax does the trick.

In case someone doesn't know: Using just drush sql-connect places you in command line mysql in your drupal mysql context, and using drush sql-connect (without backticks) reports back your drupal mysql connection info (db, user, pass) in plain text.

Hmm.. inability to connect but password is correct

Have you ever seen this: drush sql-connect < pp-site.sql ERROR 1045 (28000): Access denied for user '*****'@'localhost' (using password: YES) for an otherwise valid connection string? sql-dump, for instance, works just fine.

I suspect it's due to need of escaping a special character in my ultra-secure password. Should I file a drush issue on it?

`drush sql-connect < thesql.sql` works to load db with drush

Just wanted to round back to say thanks again: I use this command about 20 times a day when pushing to staging sometimes. No need to track local vs remote db users/passwords. Also, to answer my own question, indeed the problem was weird characters in the password. I created a new user just for this with a simpler password, and voila, no errors :)

Great to hear

Great to hear and glad this post was helpful.

Drush: Command Not Found

I'm having an issue where I can execute the drush commands without an issue with an SSH connection, but the cron job (same script) cannot find the drush command.

Set PATH

When a script runs from cron it doesn't alway have the same environment variables as when you login via shell. You'll either need to replace "drush" in the script to the full path, like "/usr/local/bin/drush" or set the path at the beginning of the script:

PATH=$PATH:/usr/local/bin

Thanks

It's working now, I knew it would be something simple like the environment or a permission issue.

Thanks!

Great post, simple - concise and easy to translate to newer versions.

Ummm no.

(drush sql-connect) does just that; it connects. But it does not load the database. In fact, what the result is the mysql string that it builds

mysql --database=dbname --host=localhost --user=username --password=password.

That's what the drush command

That's what the drush command does by itself, but if you wrap the command in $() and follow it with a redirect (ie. < file.sql) then you get the desired result of loading the file into your database.