Month: October 2014

How To: Fetch Remote Production Databases For Local Development

How To: Fetch Remote Production Databases For Local Development

Ever had that feeling you’re working with outdated data? Sure you have, that’s about the time when you ssh into the production server, make an sql dump and import in into your local development project. I personally find the whole thing extremely boring and rather gay, considering the fact it could all be done automatically… which is what this post is about.

I made a bash script a while ago to help automate fetching of a remote database into my local environment and I’ve been using it with countless projects ever since. It has become a key part if many of my projects’ development branches and I’ve grown very attached to it.


  • You’re using MySQL;
  • You have SSH access to production server;
  • You have MySQL credentials (for the root user);
  • If you’re using Windows, you’re also using Git Bash and have your MySQL binaries added to the PATH environment variable.


Copy the following code into a new file and save it as (you can call it anything else once you earn its trust):


HOST=<ssh host address here>   # host address to connect via ssh (i.e. or
PORT=<ssh port here>           # port for ssh connection (default is 22)
SSH_USER=<ssh username>        # ssh username 
Password=<ssh password>        # password for ssh user
ROOT_PW=<sql root password>    # password for mysql root user
TMP_DIR=/tmp                   # temp remote directory for the sql dump

# Fetching the actual hostname of the ssh server
HOSTNAME=$(ssh -p $PORT $SSH_USER@$HOST "hostname")  

# Gracefully handling potential script arguments
echo "Choose remote database to fetch (<default database name>)"
read -p "Press (ENTER) for default, or type ALL: " DATABASE
if [ -z $DATABASE ] 
 echo "Fetching remote database..."
 DATABASES=<default database name>
 FILENAME=$HOSTNAME-$(date +"%Y-%m-%d")
 FILENAME=$HOSTNAME-$(date +"%Y-%m-%d")
 echo "Fetching remote databases..."
 DATABASES=$(ssh -p $PORT $SSH_USER@$HOST "mysql --user=root --password=$ROOT_PW -BNe \"SHOW DATABASES;\" | grep -Ev \"information_schema|mysql|performance_schema\" | awk '{ printf \" %s\", \$0 }'")

# Generating SQL dump for selected database(s)
IGNORE=$(ssh -p $PORT $SSH_USER@$HOST "mysql --user=root --password=$ROOT_PW -BNe \"SELECT CONCAT( TABLE_SCHEMA , '.' , TABLE_NAME ) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'rec%';\" | awk '{ printf \" --ignore-table=%s\", \$0 }'")
ssh -p $PORT $SSH_USER@$HOST "mysqldump --force --user=root --password=$ROOT_PW $IGNORE --databases $DATABASES | gzip > $TMP_DIR/$FILENAME.sql.gz"

# Fetching the SQL dump to local temp directory and cleanup the remote temp directory
scp -P$PORT -c blowfish $SSH_USER@$HOST:$TMP_DIR/$FILENAME.sql.gz $TMP_DIR
ssh -p $PORT $SSH_USER@$HOST "rm -f $TMP_DIR/$FILENAME.sql.gz" >/dev/null 2>&1
gunzip $TMP_DIR/$FILENAME.sql.gz

# Purge the local database (if existing) and import the remote database from sql dump
echo $DATABASES | awk '{ printf "DROP DATABASE %s;", $0 }' | mysql --user=root --password=$ROOT_PW
mysql --user=root --password=$ROOT_PW < $TMP_DIR/$FILENAME.sql
rm -f $TMP_DIR/$FILENAME.sql
echo "Updating local database(s) complete."

Make sure you replace the bootstrap variables with your own data and then make the script executable by running chmod:

chmod +x

Now what?

Now you just run it. If you configured it correctly, it will NOT expose your machine by opening all available ports, steal all your sensitive information, format your other partitions or burn your house down:


Here’s exactly what happens when you run it:

  • It says Press (ENTER) for default or type ALL, to which you respond by doing so dooh~
  • It connects to the SSH server and generates an sql dump in the temp directory, containing the selected remote database or all of them, except information_schema, mysql and performance_schema
  • It copies the sql dump file (its name would be something like <hostname>-<year>-<month>-<date>.sql.gz) to your local temp directory
  • It unzips the file on your local machine
  • It deletes the remote sql dump file
  • It drop the local database(s) if existing and imports the remote one(s) from the sql dump
  • It says Updating local database(s) complete.

The catch…

Almost too good to be entirely true, yes, the script has a few minor drawbacks… I cannot be entirely sure, but I think you shouldn’t use this script with the ALL option (or typing anything else before pressing ENTER for that matter) – by the looks of it, it might drop all existing local databases and import the ones in the sql dump (which is perfect only if your workstation doesn’t contain other databases than what the server does).

I’ve been successfully using it with the default option for too long to care anymore. Another downside would be that using it makes you about 30% less cool, as compared to your typical routine of manually ssh-ing into the server, dumping databases and scp-ing them back you sexy hacker, you…

How To: Upgrade To The Latest Version Of phpMyAdmin [Updated]

How To: Upgrade To The Latest Version Of phpMyAdmin [Updated]

Update: The ppa hasn’t been updated in over a year so, just go to, download the .deb and overwrite the installed packages and maybe do a reconfig.

If you installed phpMyAdmin from the main repositories, chances are you’re never going to be up to date with the latest stable release. Ubuntu users got it easy though, while in Windows you would download the zip file containing the latest stable release, dump it in your wamp/apps folder and change the .conf file every time a new version comes out, in Ubuntu you only need to add a ppa repository and let apt handle the rest.

To add the repository

sudo add-apt-repository ppa:tuxpoldo/phpmyadmin

To install the latest version

sudo apt-get update
sudo apt-get install phpmyadmin

The installation process is pretty straight-forward to the point where it asks you reconfigure the db (attempting to do that didn’t work in my case so you probably might as well abort that from the start).

Also it might be possible that you get left with a bunch of orphaned packages which you can get rid of by running a simple sudo apt-get autoremove or something.

To remove the repository

If you want to keep only the packages you installed from the ppa:

sudo add-apt-repository --remove ppa:tuxpoldo/phpmyadmin

If you want to remove everything (i.e. the repository and the installed packages):

sudo apt-get install ppa-purge; sudo ppa-purge ppa:tuxpoldo/phpmyadmin

That’s about it. Now in case you haven’t done this already, you need to check out my dark minimal theme for phpMyAdmin, always updated and optimized for the latest stable release. Clone the repository in your themes directory and go feel good about yourself.