Tech Blog :: Database Sync Script with SSH and Drush
Database Sync Script with SSH and Drush
This is a script I wrote in 2009 and updated a few months ago, to automate the creation of Bash scripts for synchronizing a database from a remote server to a local server via SSH. It's a single PHP file which presents a form, you fill in the form with the connection and DB info on both sides, and it creates a .sh file that you run to synchronize. (This script does not synchronize anything itself.) The original script used mysqldump and asked for database credentials; the new version uses Drush, so it's only for Drupal but doesn't need DB credentials anymore. (And if you use an SSH key it doesn't need SSH credentials either.) You will need Drush on both sides for it to work.
I'm not hosting this script because I don't want to be responsible for the security of your server information. Instead download the .tar file below and host it on your own server. Check out the code of the .sh script that it creates before you run it so you understand exactly what's going on.
(I'll be mentioning this in my Developer's Arsenal of Productivity Hacks presentation tomorrow at DrupalCampNYC, so stay tuned for that!)
I welcome any and all bug reports, fixes, or other feedback.
Update: Someone pointed out correctly in the comments that Drush already has sql-sync, so I realized I need to explain what this does better. sql-sync makes a remote connection to a database and transfers directly between them. Most of the servers I've worked with, however, do not allow remote login to their database server (except maybe from a separate web server). All the MySql security recommendations I've read say the same thing. So this script will dump the database on the remote server via SSH (run locally on that server), zip the file, transfer it down, and import it. It also backs up both databases which Drush doesn't.
(If Drush does in fact do this, please let me know!)
Update 2: I stand corrected! Thanks to the commenters for enlightening me, Drush does in fact do this. I'll take the script off of here, since it doesn't add anything (except the form maybe). The method is:
drush sql-sync SRC DEST --source-remote-host=XX --source-dump=YY --target-dump=ZZ (and some other options available at drush help sql-sync).
@thebuckst0p
Google: TheBuckSt0p
Facebook: BenBuckman
LinkedIn
Skype: thebuckst0p
AIM: thebuckst0p
Amazon Wish List
Delicious: thebuckst0p
Drupal.org: thebuckst0p
This is funny.
db syncs can be done with
drush @live_site @dev_site sql-sync
Thanks for the comment dereine, see the update I added to the post.
Um, this is exactly what drush sql-sync does. drush sql-sync logs into the remote server with SSH, dumps the database with mysqldump, gzips it and transfers it to the target host with rsync.
It does do that. Its smarter than your script since it uses rsync to minimize the transfer time on subsequent syncs. also members of the same dev team can share source dumps.
note that --simulate and --verbose are your drush friends. if you run sql-sync with those, you see exactly what shell commands would have been issued.
Oh cool! Is that new in Drush3 or was it always there?
I'll take this script down, it doesn't add anything after all...
This really helped me out. I had it working fine at first. But now, on my dest server, the mysql command seems to be hanging on the import with a message like this: "Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A". Adding the '-A' option fixes it from the command line, but how can I add that on the alias?
Post new comment