Category: Web development

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.

Prerequisites:

  • 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.

Script:

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

#!/bin/bash

HOST=<ssh host address here>   # host address to connect via ssh (i.e. 123.123.123.123 or somehost.net)
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 ] 
then
 echo "Fetching remote database..."
 DATABASES=<default database name>
 FILENAME=$HOSTNAME-$(date +"%Y-%m-%d")
else
 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 }'")
fi

# 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 update-db.sh

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:

./update-db.sh

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 phpmyadmin.net, 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.

Gource: Visualize your Git logs in style

Gource: Visualize your Git logs in style

While generally there isn’t any real benefit in using this tool, it has been known to be able to increase your swag factor by up to 83% and it is, in the context of version control, the ultimate eye candy, an absolute must-have for catholic priests and Pedobear fanboys.

Gource was developed by Andrew Caudwell as a software version control visualization tool with built-in log generation support for Git, Mercurial, Bazaar and SVN. To find out more about it, you can check out the official page on code.google.com.

Software projects are displayed by Gource as an animated tree with the root directory of the project at its centre. Directories appear as branches with files as leaves. Developers can be seen working on the tree at the times they contributed to the project.

Gource Screenshot

Windows users can download it from the Downloads section of the project page, install it and run it normally from a command prompt, while in Linux, you can just install it from the main repository:

sudo apt-get install gource

To use it, just navigate to the working directory of a git repository and run it:

cd /path/to/your/repo
gource

or, run it directly by just providing the directory as an argument:

gource /path/to/your/repo

Here’s an absolutely remarkable video showing 21 years (1991 – 2012) of the Linux Kernel development process in somewhere around almost three hours:


Check out the help/man pages for a list of all the options and how to use them. For instance, I sometimes use this as a screensaver:

gource -1920x1080 --fullscreen --multi-sampling -a 0.1 --time-scale 3.0 --loop -i 3600

Oh, and here are some fun interactive keyboard commands:

(V)         Toggle camera mode
(C)         Displays Gource logo
(K)         Toggle file extension key.
(M)         Toggle mouse visibility
(N)         Jump forward in time to next log entry.
(S)         Randomize colours.
(+-)        Adjust simulation speed.
(<>)        Adjust time scale.
(TAB)       Cycle through visible users
(F12)       Screenshot
(Alt+Enter) Fullscreen toggle
(ESC)       Quit

The documentation is a lot, but well structured and worth reading + there’s even a guide for recording / encoding videos if you’re not into third party video capture software.

How to install LAMP manually in 60 seconds

How to install LAMP manually in 60 seconds

It usually takes me a few good minutes to get the LAMP stack (Linux Apache MySQL PHP) installed and many more to get it configured to fit my needs. Well, I’ve finally managed to get it all together into a little something that can be done in under a minute. Assuming you’re doing this on a reasonably fresh Linux installation, these following lines will get you through with no problems and you’ll be the proud user of Apache 2.4 + PHP 5.6 + MySQL 5.6 in no time:

First, install a mother-load of packages (basically Apache, PHP, MySQL, phpMyAdmin and MySQL Workbench):

sudo apt-get install apache2 mysql-server mysql-workbench php5 curl php5-curl mcrypt php5-mcrypt php5-imagick php5-gd php5-xdebug phpmyadmin

Then, a few useful Apache modules:

sudo a2enmod rewrite headers expires deflate

Yeah… we might get a nasty looking message every time we restart the apache server, something like this:

apache2: Could not reliably determine the server’s fully qualified domain name, using 127.0.1.1. Set the ‘ServerName’ directive globally to suppress this message.

To get rid of that, we have to define the ServerName and include it in our configuration file.

httpd.conf

sudo nano /etc/apache2/httpd.conf

This file might be blank, but add the following line to it anyway:

ServerName localhost

Then add these references to the apache2.conf file

apache2.conf

Include httpd.conf
Include /etc/phpmyadmin/apache.conf

Cool, now as a security measure, it would seem that in newer Apache releases, the default document root points to /var/www/html instead of directly to /var/www. We might as well rename that html to public_html and update some configs.

000-default.conf

sudo nano /etc/apache2/sites-available/000-default.conf

And change the DocumentRoot to this:

DocumentRoot /var/www/public_html

Also, while we’re in here we should add a few lines to prevent mod_rewrite from having girl problems later in life:

<Directory />
    Options FollowSymLinks
    AllowOverride None
</Directory>
<Directory /var/www/public_html/>
    Options Indexes FollowSymLinks MultiViews
    AllowOverride all
    Order allow,deny
    allow from all
</Directory>

Also, there might be some trouble with PHP’s mcrypt extension, so let’s make sure it actually gets loaded:

php.ini

sudo nano /etc/php5/apache2/php.ini

Go down to Dynamic Extensions and add this line if it doesn’t exist:

extension=mcrypt.so

And… that should be it – restart the web server and go to http://localhost/ to test the installation:

sudo service apache2 restart

Also, just for the thrill, you can make a phpinfo.php file in /var/www/public_html with just this:

<?php phpinfo(); ?>

to review all the modules and extensions that have been miraculously installed on your system.

Also, a quick tip on the nano editor if you haven’t moved on to vim already. Tab size should be 4 spaces – that’s it – period. If there’s already a .nanorc file in your home directory, add this line to it or create if first otherwise (dooh~)

set tabsize 4

CSS font shorthand property explained

At some point in the past I stumbled upon a picture or an article describing an example of using the CSS shorthand for the font property in one line. I found the sketch I made in an old notebook and decided to post it here (reproduced of course because my handwriting is terrible). So I guess the only credit that goes to me is for taking the time with it, sorry I can’t remember the original source and so anyway, the following diagram should be pretty self-explanatory.

CSS font shorthand example

How to center a DIV both vertically and horizontally

How to center a DIV both vertically and horizontally

This quick and lightweight method offers the benefit of getting the job done in just a few lines of CSS, in those situations where using a framework / boilerplate would be an absolute overkill, i.e. a simple separate standalone login screen for an already existing application, where the actual login form would look just lovely in the middle of the screen. The only rather insignificant downside of this technique is that the size of the centered element is fixed.

body {position:relative}
#centered {position:fixed; top:50%; left:50%;}
#centered {width:18em; height:8em; margin-top:-4em; margin-left:-9em}

We basically just set relative positioning on the body and fixed or absolute positioning on the div element to be aligned. Define the div dimensions in em units and set the top and left attributes to 50%. Set the margin-top and margin-left attributes to minus half the width and height values.

HTML for effective website logos

HTML for effective website logos

Ideally, the logo in the header section of the website should be in fact textual, in the form of a H1 heading. But that alone would look like something from back in the ’90s where the Internet was text.

Luckily, with a bit of CSS magic, we can have both the SEO benefit of a textual H1 and the awesomeness of the perfect eye-candy logo you’ve been designing for hours. The following example will accomplish just that and also make the logo clickable, offering a backlink to the frontpage of the website.

HTML

<html><body><header>
<h1><a href=”SITE_ROOT” target=”_self” title=”SITE_NAME”>SITE_NAME</a></h1>
</header></body></html>

CSS

body > header h1 a {
display: block;
background: url(logo.png) no-repeat;
text-indent: -9999px;
}

That’s it, really, plus this method is not considered a blackhat technique and does contribute to over-optimization.