puPHPet Box MySQL backup

As you all know by now, I love puPHPet. And I like tinkering with things. Which of course leads to the occasional breaking of things. And then I realised, okay, your sites files are being mounted into this virtual machine, what about the databases though?

The great thing about puPHPet and Vagrant is you can build a full server up from scratch in minutes. The one downside is that it cant be smart enough to uninstall things. If you add lines to your config.yaml it will install something, but removing those lines means puPHPet won’t even know it was there, so how would it know to uninstall it? The exceptions of course being the ones which have their own config.yaml entry, with an install: ‘1’ or install: ‘0’

Anyway, I generated me a new config and was about to blitz my old VM when the usual gut instinct checks kicked in, and the DB sprung to mind immediately. So I made a quick backupdbs.sh shell script (courtesy of a StackOverflow post) which makes individual sql files for each of your DB’s. I decided I would keep the script in my mounted sites folder in a bin directory. So hence it lives in /var/www/bin/backupdbs.sh

#!/bin/bash

USER="root"
PASSWORD="YOURPASSWORDHERE"

databases=`mysql -u $USER -p$PASSWORD -e "SHOW DATABASES;" | tr -d "| " | grep -v Database`

for db in $databases; do
    if [[ "$db" != "information_schema" ]] && [[ "$db" != "performance_schema" ]] && [[ "$db" != "mysql" ]] && [[ "$db" != _* ]] ; then
        echo "Dumping database: $db"
        mysqldump -u $USER -p$PASSWORD --databases $db > `date +%Y%m%d`.$db.sql
    fi
done

To back everything up, vagrant ssh into the machine, then (you’ll probably need to switch user to www-data) do the following:

sudo su www-data
cd /var/www/bin
./backupdbs.sh

Now you’ll find all your DB’s SQL files date stamped in the folder! You can continue to break, tweak, fix, and run your VM to your hearts content 😀

Advertisements

MySQL socket DSN connection syntax

Usually I just bung in 127 or localhost in order to connect up to my DB. However, if you aren’t allowing remote connections, then running on a unix socket is much faster and skips all the networking overhead involved with TCP/IP.

So! To use a socket, you use a DSN for the connection. I’ve done this a few times, but I keep forgetting the syntax, sop here it is, solely for my own memory’s sake:

mysql:unix_socket=/tmp/mysql.sock;dbname=my_db_name_here

Slouch on the CouchDB

No doubt you will know about the new generation of document oriented databases designed to work using the HTTP protocol, such as Mongo and CouchDB. They come pretty much with a built in API! So it’s time to mess around with it!

I downloaded Couchbase Server community edition (which is pretty nifty I’ll admit). Once you get that installed you can access localhost on port 8091 and it will take you into a cool looking admin panel:

One of the admin screens on couchbase server
One of the admin screens on couchbase server

It comes with a sample database, and you can access the API on port 8092. The full range of ports is as follows:

Port Description Node to Node Node to Client Cluster Administration XDCR
8091 Web Administration Port Yes Yes Yes Yes
8092 Couchbase API Port Yes Yes No Yes
11209 Internal Cluster Port Yes No No No
11210 Internal Cluster Port Yes Yes No No
11211 Client interface (proxy) Yes Yes No No
4369 Erlang Port Mapper (epmd) Yes No No No
21100 to 21199 (inclusive) Node data exchange Yes No No No

There are some great videos at http://www.relaxed.tv that show you how couch works. This is the introductory video:

The curl syntax should be something like:

curl  localhost:8092/
{"couchdb":"Welcome","version":"1.2.0a-be4fa61-git","couchbase":"2.0.0-1976-rel-community"}

As you can know, being able to query a database and perform CRUD over HTTP would be incredibly useful, so this is very promising!

It’s stupid O’clock and I’ve been up all night but this looks great! I will update this post when I return to have a play with my new toy! 🙂