Bryce on Technology

In my backup script I previously posted, we begin by dumping the entire contents of the database to a file. This is not necessarily a good idea and there may be better ways to backup your data. However, the total size of my database is only a few MBs so it isn’t worth the hassle of creating a master-slave setup.

The goal is to dump the database to the filesystem, add it to the tar file, encrypt it and then delete the clear text version of the file.

Security considerations abound. In a nutshell however, it is important not to publish any unencrypted database dump anywhere. Dump files can be searched on easily by Google and if you house even a morsel of private user data, you’ll be in trouble very quickly if it’s not encrypted.

Getting started, the MySQL command to dump a database is:

mysqldump -u user -psupersecret –all-databases > /some/dir/backupsql.sql

-p will prompt for a password

-all-databases will dump every database you have running on the mysql instance. If you want a specific database, replace –all-databases with your_db_name

This command pushes the output of the mysqldump command to a file on the filesystem.

Since I don’t want my cron job to be prompted for a user’s password when dumping the db, I have created a special backup user with only enough permissions to read the tables, and write their contents to a file. You can create such a user by running the following command inside a mysql prompt:

GRANT SELECT, LOCK TABLES ON *.* TO ‘backup’@'localhost’ IDENTIFIED BY ’supersecret’

Make sure to flush privileges after creating users. There’s not much more frusterating than trying to figure out why that script didn’t work.

One security consideration is that if you were to run the ps command while the dump script is running, it will expose the username and password of the backup user potentially allowing other users of the system to make select statements to databases they otherwise shouldn’t be on.

By limiting the backup user access only from the localhost, this eliminates the possibility of someone else dumping your database from some other host; the user must be logged into the box the database resides on.

At the conclusion of this script, we will have all the database data and structure backed up to a file on the filesystem. In the next post, I’ll explain how to add this data to a tar archive and then encrypt the whole mess.

HowTo, Stuff I'm Working On backing up to git, technology

I am becoming a better systems administrator every day secondary to my work as a ruby on rails and PHP developer. As a very small development shop I have very limited resources to perform the backup and recovery policies bigger shops and huge enterprises employ.

However, after just a morning of futzing with a few key linux commands and better utilizing a service I already back up my source code to (www.github.com) I have a found a robust and secure way to handle automated, off-site, redundant backups in a way that will let me compete with some bigger shops. I’ve posted the code below so I hope you will find it useful. Over the next few posts, I’ll unpack what I’ve written and the philosophy behind it.

A few things bothered me in the way I was doing traditional backups:

1. I knew I had to get them off-site, but actually finding time to get off-site (to a secure location) wasn’t happening.
2. The backup had to be absolutely secure. My clients’ source code is too precious and leakage too damaging to make even one mistake with security breach
3. Had to be simple and automated. I usually have 10 other things I need to do at the same time. I didn’t want backups to be number 11.
4. Small file size. Again, being a small dev shop, I didn’t want to put a lot of cost into storage of incremental backups
5. Incremental backups were key since I don’t want to go to all this trouble only to restore a copy of the bad data I was trying to replace. If I a problem isn’t made known until after the next set of backups are made, I’d be overwriting bad data with bad data; better to restore to the point before the problem happened.

How-To, HowTo, Stuff I'm Working On HowTo, technology

I’ve been fighting my Leopard installation recently with getting the IMAP libraries compiled correctly. I wanted the IMAP features so that I could use my sugarCRM application better. After a lot of fighting, compiling and adjusting, I finally got a stable installation of Apache 2.2 and PHP-5.2.5 with IMAP. Here’s how I did it:

I didn’t want to ruin the installation of Apache2.2 that comes native on Leopard, so I started from scratch:

cd /usr/local
mkdir src
curl -O http://www.mirrorgeek.com/apache.org/httpd/httpd-2.2.8.tar.gz
tar -zxvf httpd-2.2.8.tar.gz # a bunch of output will fly by; that’s normal
cd httpd-2.2.8
./compile –enable-so –prefix=/usr/local/apache2 # a ton more output will fly by
make
sudo make install

Now we have a new apache install in your /usr/local directory. If you want to connect this installation to your usual web directory on your mac, you need to edit your httpd.conf to direct your web root folder to /Library/WebServer/Documents. You do this by executing the following commands:

sudo vi /usr/local/apache2/conf/httpd.conf
Change DocumentRoot “/usr/local/apache2/htdocs” to: DocumentRoot “/Library/WebServer/Documents”
Next, change to
issue the vi command :wq

Your Apache Installation should be set to go now.

Before we can install php, we need to cimpile the IMAP c libraries. Go to and download these libraries at ftp://ftp.cac.washington.edu/imap/ . Then, move them to your source directory with the following command:

mv ~/Downloads/[your-imap-download] /usr/local/src
./compile –prefix=/usr/local/imap
make
sudo make install

When this finishes, you can install php

PHP Installation:

Go to http://us3.php.net/get/php-5.2.5.tar.gz/from/a/mirror and download a package
On Leopard, you would issue the following command to move the package to our source directory:

mv ~/Downloads/php-5.2.5.tar.gz /usr/local/src

Now, we compile:

tar -zcvf php-5.2.5.tar.gz
cd php-5.2.5
./configure –prefix=/usr/local/apache2/php –with-apx2=/usr/local/apache2/bin/apxs –with-imap=/usr/local/imap –with-mysql=/usr/local/mysql
make
sudo make install

Following the completion of this install, you should be able to issue the command:

sudo /usr/local/apache2/bin/apachectl start

and your installation is complete. SugarCRM should recognize the imap libraries and php should run smoothly.

Please note that this is not a particularly secure installation, but will work great for local intranets to keep your users involved with the crm.

Please post comments if you run into trouble.

HowTo, technology

I’ve been using Skype now for quite awhile. It’s an excellent way to keep in touch with others without using your cell phone minutes. It’s also a free way to video conference, chat and make voice calls.

HowTo, technology

To connect your AOL high speed internet service to a router is fairly simple if you have good information. AOL tech support gave me some bad advice in the beginning which caused me about an hour of headache before I called them back. To connect takes a couple of AOL specific setup instructions which I’ll show you here:

HowTo, technology Consumer Technology, How-To