In a LAMP project, what's the best way to get a prod-like database locally?

I'm developing locally and I need the latest prod data to get me moving faster. What's the best way for me to get that data? What about assets, is there a way for me to get prod assets too?

Answer

For sites that Blend hosts, you will probably SSH need access to the production (or at least QA) environment to get this working. If you don't have access, request it from the sys admin (Jackson or Joe usually as of this writing). If you do have it, be careful and ask for help if you're unsure about what you're doing. Keep in mind, even if you're safe on prod, databases sometimes contain information like client email addresses, notification timers, etc. When working locally with a prod database in an unfamiliar project, you may want to talk to the project owners / maintainers just to make sure you don't accidentally cause problems for site users.

Once you have access, there's a long-way and a short-way to get the database.

The Hard Way

The tried and tested (But less convenient) way to get DB's is using a dump command, SSH, and SCP. Most blend-hosted lamp projects have SSH connection information documented in their /devenv/devconfig.yml file. Look for the ssh_user and prod_host settings in that file. If prod_host is an array, but you can generally just choose one of the list items. Nearly all Blend "LAMP" sites use Mysql or MariaDB, so we'll assume that for these examples.

1. SSH out to the remote environment: ssh {$SSH_USER}@{$PROD_HOST}.
2. Get the prod database credentials (these are probably different from local/qa). How to find these depends on the CMS. Here are a few examples:
   • Drupal (usually): cd docroot; drush sql-connect
   • Wordpress: cat wp-config.php |grep DB
   • Legacy eZ (usually): head -n 45 ezpublish_legacy/settings/override/site.ini.append.php
   • concrete5: cat public/application/config/database.php
3. Use mysqldump to dump the database using the credentials you just found: mysqldump -h{$HOST} -u{$USER} {$DATABASE} -p > {$PATH_TO_FILE_YOU_WANT.sql} (you'll be prompted for a password). Please give the file a meaningful name, so anyone who finds it if you forget to delete it knows what it is.
4. Exit the shell exit
5. Use scp to copy the file back to your machine: scp {$SSH_USER}@{$PROD_HOST}:{$PATH_TO_FILE_YOU_WANT}.sql ./
6. Import the database locally
7. After database is imported, you can use SCP to get the remote var/asset files locally. scp -rv {$SSH_USER}@{$PROD_HOST}:./{$PATH_TO_VAR_DIRECTORY} ./{$PATH_TO_VAR_DIRECTORY} . Note that these directories can often get quite large.

The Easy Way

If you're using bobafetch, there are two commands for fetching a remote database: bobafetch fetch (download to a file only) and bobafetch sync (download and attempt to wipe-out/replace your dev database.

Install Bobafetch if you don't have it. If you're in a vagrant VM terminal, you probably already have it. Otherwise, try wget https://composer.blendinteractive.com/bobafetch/current.phar && sudo mv current.phar /usr/local/bin/bobafetch && sudo chmod +x /usr/local/bin/bobafetch.

1. From your locally-cloned project root directory, run bobafetch fetch. Note the file that's downloaded.
2. Import that into your local site.

If you run into problems, try again after a sudo bobafetch self-update and use bobafetch --help to look at more options.

The Easier Way

1. Install Bobafetch as in "The Easy Way"
2. Run bobafetch sync --skip-var-sync to export, transfer, and locally import the database, as well as do some clean up. You may need to be in your local VM to do this. Note that this will replace your local development database.

If you also want to transfer the remote assets, you can run bobafetch sync instead, which will use rsync to copy the files down locally, but these can get large.

If you run into problems, try again after a sudo bobafetch self-update and use bobafetch --help to look at more options.

Note that some bobafetch commands offer a -v verbose option or a --dry-run option, so you can get a better idea of what they'll do. E.g. bobafetch sync --skip-db -v --dry-run -r qa. Again, check bobafetch --help.