How to Use SQL-Dump and SCP

It’s been almost six months since I started at Zivtech as a Junior Developer, and I could never fit everything I’ve learned into one blog post. That said, one of the best things I’ve learned is how to compress my database and copy it across servers. These two commands are drush sql-dump and scp. If you’re unfamiliar with Drush, you can find some background information here.

I learned how to use drush sql-dump while using Probo.CI, which is our internally-developed continuous integration tool. Since we use Probo.CI on every project, I had to figure out how to set it up. Essentially, you have to upload your database to the Probo.CI app to ensure that your new feature will work on the site when you’re testing your pull request. Here is some helpful documentation. The fourth step in this documentation is:

Step 4: Use Drush to get and compress your database in a sql file.

If you wish to compress your database you’ll need to use gzip. Other zip files won’t work.

$ drush sql-dump | gzip > dev.sql.gz

To a new developer, this might look a little intimidating, but it’s just like it sounds; you’re dumping your database into a gzip file, dev.sql.gz, which will then be uploaded. In this example of using Probo, you’re uploading the gzip file with probo-uploader (a command line client for uploading files). Using Probo was a great entry point into learning drush sql-dump which, as you’ll see below, can be used in other capacities.

I’ve also had to use drush sql-dump alongside scp, which means secure copy. So I’ve dumped my database into a .gz file; great, but now what? Somehow I have to get the database to another environment or server. In the previous example, I didn’t need to copy the database to a server; everything could be done either locally or from my virtual machine. There are times when you need to import a database to a particular environment. If you need to copy the database from a remote host to the local host:

$ scp your_username@remotehost.edu:foobar.txt /some/local/directory

If you need to copy it from the local host to a remote host:

$ scp foobar.txt your_username@remotehost.edu:/some/remote/directory

Credit for the above examples goes to scott@hypexr.org. For more scenarios, go here.

Once you’ve copied the gzip file to the environment it needs to be in, you need to DROP your current database so you can import the new one you just copied. That’s where these commands come in:

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

For more information, here’s another great resource.

That’s all there is to it. You dumped the database you wanted into a gzip file, secure copied it to the environment, dropped the old database, and then imported the new one. That’s how it works!

This post originally appeared on the Zivtech blog.