Category: Software

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.

How To: Make phpMyAdmin Look Sexy In Under 10 Seconds

How To: Make phpMyAdmin Look Sexy In Under 10 Seconds

PMA8165 Theme

A clean, dark and minimal theme for phpMyAdmin 4.2.x

This theme was born simply out of my deepest needs – more precisely the need to not have to stare at the ugly default phpMyAdmin theme all day long (sorry, guy who made it). But seriously now, it had to come to this – in a multiple screen setup where Gnome looks awesome, terminal windows look awesome and Sublime Text tops everything, phpMyAdmin just didn’t belong.

I guess one could say PMA8165 was somewhat inspired from the Numix GTK3 theme which, in combination with a few more things, can make Gnome look lethal as detailed in one of my fascinating blog posts on How To: Make Linux Look Sexy In Only 3 Fast Steps.

Installation

Go to your phpmyadmin themes directory:

cd /usr/share/phpmyadmin/themes

Clone the repository from github:

sudo git clone https://git@github.com/mjohnson8165/pma8165-theme.git pma8165

Then just go to your phpmyadmin page and select the pma8165 theme.

Screenshots

Here are a couple of screenshots so you can get an idea of what you’ll be getting:

PMA8165 Home

PMA8165 Table View

That’s about it, but if you’re in doubt, feel particularly constipated or for any number of other reasons you have a bad feeling about this, you should head over to the repository page and read the disclaimer section – that should give you an idea of what this is and what it isn’t. Enjoy!

How to: Use tagging in Git (Part VII)

How to: Use tagging in Git (Part VII)

Tagging is a great feature of Git and most other VCSs for that matter, that allows you to hmmm… tag (a warm round of applause for Captain Obvious) specific points in history as being important.

And this is the exact context in which Semantic Versioning starts to make more sense than ever. Following the wisdom within these two posts will at least make you look serious about software development, releasing new versions and fancy patches and stuff…

Listing Your Tags

Running the tag command with no options or arguments will list all the available tags in your repository:

git tag

But you can also list only the tags with a particular pattern. The Git source repo, for instance, contains more than 240 tags. If you’re only interested in looking at the 1.4.2 series, you can run this:

$ git tag -l 'v1.4.2.*'
v1.4.2.1
v1.4.2.2
v1.4.2.3
v1.4.2.4

Creating New Tags

We can have two different types of tags: lightweight and annotated. I’m not going to go on about the differences between those two, or why one of them is better than the other, they are both acting like pointers to a specific commit, but I prefer annotated tags as they can store additional information and be signed and verified with GNU Privacy Guard (GPG) – which you can probably read more about in the man page or the official docs.

To create an annotated tag, just use the -a option with the git tag command like this:

git tag -a v1.0.0 -m 'Version 1.0.0'

Tagging

The -m option specifies the tagging message which is stored within the tag object, but if you don’t specify it, Git will launch your editor so you can type it in.

Note to self: I’m planning on writing some script at some point that would help me automate the creation of change logs for major and minor versions, based on the commit messages between versions or todo list entries.

Also if, say you forgot to tag an important commit sometime earlier, you can still do it now by referencing its checksum:

git tag -a <tagname>. -m '<message>' <checksum>

Update: One more thing, in case you made a horrible typo on the tag you just created (like I just shamefully did), there’s something you can do that works pretty much like the --amend option for git commit:

git tag <tag-name> <tag-name> -f -m "<new-message>"

That will create a new tag with the same name by overwriting the original, or you could pop up your editor to type in more complex messages by running:

git tag <tag-name> <tag-name> -f -a

Viewing Tags

Viewing detailed information about individual tags, to be more precise, can be done using the git show command followed by the tag’s name:

git show v1.0.0

Again, this command has tons of useful options but you’re gonna try them all out on your own. I’m now giving you some more interesting combos that might come in handy:

git tag -l v1* -n999

That one will display the first maximum of 999 lines of all the tags starting with v1 – feel free to filter these as you like and display more or less lines of the tag message.

git log --oneline --decorate --reverse v1.0.0 v2.0.0

Use the one above to display a list of all the commits between major versions v1 and v2 in reverse order.

git log --oneline --decorate --graph --reverse --all

Or list the entire repository history in the same format as the above while also displaying branch graphs.

Sharing Tags

For some reason, git push doesn’t transfer tags too by default, so it needs to be done separately. You can either do it one tag at a time:

git push origin <tagname>

or by using the --tags option for all tags at once (it will only transfer tags that are not already on the remote anyway):

git push origin --tags

After this, any clone or pull operation will fetch all the tags as well so you can just go and take a long nice poop because:

  • you deserve it
  • your repo looks legit
  • according to semver, it now also has a meaning

What is Semantic Versioning and Why Care?

In the world of software management there exists a dread place called “dependency hell.” The bigger your system grows and the more packages you integrate into your software, the more likely you are to find yourself, one day, in this pit of despair.

What is this?

If you aren’t already using Semantic Versioning, you should. It makes it much easier to figure out how to version your releases. Even if you’re ‘just’ building software for your internal organization, or a single customer, you should still care about versioning of the software you release. Instead of an ad-hoc versioning scheme, Semantic Versioning offers a set of easy-to-understand rules about when to increment which version number.

Semantic Versioning

Basically, you:

  • increment the patch version (e.g. from 2.3.4 to 2.3.5) when you only release bug fixes
  • increment the minor version (e.g. from 1.3.2 to 1.4.0) when you add new features
  • increment the major version (e.g. from 3.2.9 to 4.0.0) when you introduce breaking changes

Why should I care?

This makes it much easier for you when you need to make a decision on a new version number, and it also makes it much easier for consumers of your software to understand when an update is ‘safe’, and when they should set aside some time to test compatibility.

If all of this sounds desirable, all you need to do to start using Semantic Versioning is to declare that you are doing so and then follow the rules. Link to this website from your README so others know the rules and can benefit from them.

This is somewhat of a rip-off of Mark Seemann’s post on Semantic Versioning with Continuous Deployment, which somewhat of a lesser rip-off of the original page on Semantic Versioning, created by Tom Preston-Werner, co-founder of Github and also the guy who made Gravatars.

OK, I want in.

All of that being said, check out the official page at semver.org for detailed specifications, examples and a very useful F.A.Q. section. I think this is a great starting point for when we dive into How to: Use tagging in Git (Part VII).

How to: Work with remotes in Git (Part VI)

How to: Work with remotes in Git (Part VI)

As described much earlier, Git is a distributed version control system and each machine that taps into it holds the entire history of all repositories. All that information is stored inside .git folders in the root directory of projects. This is already helpful as you can run git locally and maintain your own repositories.

Well, it gets even better once you get to share those repositories with any number of your split personalities and better yet, store them safely on your backup remote location. In short, a remote is much like a regular repository and is usually initialized as a bare repository.

Only difference is that it really doesn’t contain and actual project files and it can be accessed by any number of people who have access to it and act as a central hub that other repositories sync data to and from.

git remote

This command will be used to create, rename, delete and basically manage a set of tracked repositories. There’s not all that much to it. Here’s how you add a remote repository to your already existing local one:

git remote add origin ssh://<user>@<server>/srv/git/repo.git

Supposedly, you can add any number of remotes to a repository, but I’ve never tried it and therefore I’m not a fan of it and all my independent remotes are conventionally called origin.

Again, for all the information on the matter, check the man pages – I’m only giving you the bare minimum important can’t-live-without stuff.

git pull

Not too much interesting information on pulls, except that it is generally considered wise to do them often in order to prevent merge conflicts, earthquakes, tsunamis, alien invasions and other catastrophes. Pull your sh*t together:

git pull

or

git pull <repository> <refspec>

Pull your shit together

A command that incorporates changes from a remote repository into your current branch. It is widely recommended to do one before any push.

There would actually be some background stuff to know about pulls: for instance, git pull is actually a shorthand for git fetch, followed by a git merge FETCH_HEAD. Yeah… bet you didn’t know about that, now did you?

But, when ran with the --rebase option, it actually uses git rebase instead of git merge. I’m not gonna get into more detail because at this point, I’m not sure how viable rebases really are, at least not in the context of the workflow I have in mind, which is inspired by a source that shall only be revealed once I write my post on it (and remember where it came from).

git push

Pushing is one of the most important part during your second stage of labor, but also the operation you’ll find yourself repeating over and over again once you go remote.

Typically, when working with remotes you would still make changes to your local working copy, commit them, make some more changes and then at some point you will want to push them to the remote repository, so that they’re both in sync so to say.

Push

git push

Well, this is all fun and painless until new branches come along or the contractions grow stronger.

On a side note, I’m sorry if this section is harder to read – it’s just that push sounds ridiculously like something you’d read about in a guide on how to manage labor pains or something – and now you know why I’ll never have an Android category on my blog.

Anyway, of course you could probably just branch out of your main tree locally and work there, but what if this is more of a persistent branch for a major feature or a general development branch that other people need access to as well? As the proud founder of the branch, you get to set an upstream remote branch:

git push --set-upstream origin <new-branch-name>

or, for short:

git push -u origin <new-branch-name>

That creates a new branch on the origin remote repository and sets your local branch to track it. Tracking is that thing that tells you how many commits behind or ahead is your local branch as compared to the remote one, whenever you switch to it, fetch from the remote or check the status.

I almost forgot, as of Git 2.0 the push command’s default mode changes from matching to simple, so you might get some annoying message every time you push. To discard it for now, wait until your cervix is fully dilated, then pick a mode you’d like to set as default and do it:

git config --global push.default simple

This only affects the behavior of using the command without any extra arguments. I’m going with simple because I’m a somewhat of a control freak, but here’s the long story short about the differences between those two:

  • matching mode will push all your local branches to their matching ones on the remote
  • simple mode will only push your currently active branch to the repository

For occasional matching pushes you can try the --all option, I dare you, I double dare you:

git push --all

It might also be useful to know how to delete a remote branch, for any number of reasons. We have a few different situations depending on whether you’re the one deleting a remote branch or the one who was working locally on his own copy and someone else deleted the upstream remote branch.

So, if you’re deleting the branch, do it properly. First delete your local branch (make sure you merge it into another one if it’s necessary).

git branch -D <branch-to-delete>

Then, remove the remote branch:

git push origin --delete <branch-to-delete>

I do this every once in a while to at least piss off some people who sometimes seem to just deliberately cause merge conflicts. Anyway, here’s what you can do if you ever find yourself in their shoes (willingly or not):

If you were just about to push some commits, but just as you were doing a overcautious fetch / pull right before of it, you realized your refs were gone, you can still make the push and the remote branch will be reborn, OR you can discard your own branch.

However, if your own local branch was clean (i.e. no pending commits) and its remote counterpart gone, it’s probably a sign you should also get rid of your own. But this is SPARTA, so if you would like, you could keep working on your local branch independently from the once prosperous remote one, but for that you first need to prune some refs:

Double check your assumptions first, before considering anything and do a dry run:

git remote show origin
git remote prune -n origin

Whenever you’re ready, you can run the prune command without the --dry-run or -n option and that will unset the upstream.

git remote prune origin

Congratulations, you are now a rogue developer! You are also on your own, with no one to help you out, or hear you scream for that matter. However, many things can happen to a stealth branch in time: it can change its name, can silently track the changes on the main branches and eventually come back mysteriously with some seriously valuable code that would make you employee of the year, or someone’s bitch in jail, even if only for one night… Either way, here’s a quote you might find useful, it’s from an older post on How to make a Git server:

This will get you a reputation and / or a night in jail so be ready for both. For more tips on moronic behavior, follow my guide on How To: Make A Rep In Jail In The First 12 Hours.

How to: Use branches in Git (Part V)

How to: Use branches in Git (Part V)

So, essentially branches are the way to go when trying out new ideas or isolating features or sections of work. One can branch out of the master line and work on a feature, independently from what’s going on the outside, little introvert f*ckers branches are…

Git handles context switching very fast, meaning that switching branches replaces the files in the working directory with those of the selected branch without too much hassle. Eventually, branches can be either merged into the master branch or discarded completely.

git branch

By itself, the command displays the branches of the current repository:

git branch          # Show the local branches
git branch -v       # Show the remote branches 
git branch -a       # Show all branches, including remotes

To create a branch out of thin air, use this command and give it a nice name (no, Branchie is not a nice name):

git branch <significant-branch-name>

Of course, if you f*ck up, you can always rename it using:

git branch -m <old-branch-name> <new-branch-name>

Git-Tree
It is also possible to delete the branch, but note that the more branches you remove, the more your git tree will start looking like a giant wooden buttplug:

git branch -d <delete-me>

Note that if a branch was not fully merged, that command won’t do it, but you can force it to do it anyway and it won’t even mind:

git branch -D <delete-me>

And now it’s probably the time to bring back our old pal, git checkout. Apart from replacing your local files, it can also switch branches, thus moving the pointer to the new currently active branch and replacing the contents of your working directory.

git checkout <branch-name>

It can also do super combos: create a new branch, make it reference another branch or tag and switch to it.

git checkout -b <new-branch> <parent-branch>

Another quick option that can come in real handy, git branch --merged will display branches that were merged and basically contain each other’s commits, more precisely it displays a list of all the branches that contain all the commits that the currently active branch has.

Note however, that Git will not allow branch switching if the current branch’s working directory is not clean, in the sense that there are no files in conflict – there’s no problem with new files or changed files that have not been altered in other branches. In the event of possible conflicts, either stage and commit the changes, or remove them.

git merge

To merge a branch, first checkout the branch you want to merge things into, then run the git merge command.

git checkout master
git merge <branch-name>

That command will add the commits from <branch-name>, on top of those on the master branch and then move the HEAD pointer at the top of the list. But, based on the level of their complexity, merges can be of two types: fast-forward merges and real merges.

Fast-forward merges occur when the parent branch doesn’t contain additional changes from the time of the branching, meaning that it’s absolutely safe to just move the source branch’s commits at the end of the parent branch and then simply update the HEAD pointer with the last position in the chain.

Real merges occur when the parent tree contains additional commits that were submitted in the meanwhile, between the times of branching and merging. Git tries to auto-merge branches using a recursive strategy. But there can always be such things as merge conflicts. 

Conflicts are usually handled automatically if the file differences are on different lines, but otherwise, put yourself in Git’s position. Without any knowledge of what any of those lines mean, how do you decide which version is more adequate or better yet come up with a hybrid solution that takes incorporates both changes?

When that happens, Git throws a conflict error and the merging operation is suspended until the conflicts are resolved. So, the files that are in conflict now contain both blocks of code, enclosed between lines of <<<<<< and ====== delimiters.

Manually reviewing those changes and removing the delimiters is preferable to using automatic merge tools as it is considered safer. After the conflicts are fixed, we can add the files to the staging index and perform a simple commit without any message – that will be prompted by the merging operation itself in a freshly opened editor.

Now that we talked about the two types of merges, here are a couple of useful options:

The first one inhibits fast-forward merges and forces git to copy all the commits from the branch to the parent tree, thus maintaining a detailed history log, even if fast-forwarding would have been possible, in which case only one commit would have been created on the parent branch to indicate the fact that there has been a merging operation:

git merge --no-ff <branch-name>

The other one will perform a merge only if fast-forwarding is possible and abort the operation otherwise:

git merge --ff-only <branch-name>

To reduce the probability of having conflicts occur when merging branches, we can merge the parent branches into the working branches, and do it often – this way when the working branches will pretty much contain the same thing as the parent ones, thus making it easy for git to perform successful auto-merges or even fast-forward merges. This strategy is called tracking and is commonly used when working with remotes.

Note: It is advisable to only perform merges with a clean working directory. Otherwise, things could get messy and we would have to talk about using stashes. Oh, what the hell – let’s get it over with:

git stash

I know it sounds like a porn term, but in actuality, the stash is a special fourth area in Git. It is separate from the repository, the staging area or the working directory and doesn’t have a checksum associated with it.

Stashes are commonly used when trying to switch into a branch while the working directory is not clean. Typically you would have two options: discard your changes or commit them. The third option would be to stash those changes and deal with them at a later time.

git stash save "Message goes here"

What the command really does, is it stores the changes into the stash with the specified message, so that they can be accessed later, and then performs a hard reset to HEAD e.g. git reset --hard HEAD, taking the pointer back to the previous commit.

Here’s another fun command, to display all the stashed entries, with their associated identifier and message.

git stash list

Just for future reference, a stash identifier looks something like this: stash@{<number-of-stash>}

This next one displays a diff between the changes in the stash and those in the HEAD.

git stash show -p <stash-identifier>

And well, here’s the rest of them:

git stash apply <stash-id>  # Apply <stash-id>
git stash pop <stash-id>    # Apply <stash-id> and remove it from stashes
git stash drop <stash-id>   # Remove <stash-id> from stashes
git stash clear             # Remove all stashes

That’s it for today. Marilyn Monroe once said “if you can make a woman laugh, you can make her do anything”, to which I must add that the same thing applies if you can make her branch. Next time we can already start talking about remotes, moving up in the world like that… Here you go: How to: Work with remotes in Git (Part VI).