Backup and Rotate MySQL Databases Simple Bash Script

Make a directory ( it can anywhere ) called baks/mysql

mkdir -p /baks/mysql

Create a file (it can be anywhere) called /root/mysql_backups.sh and put this script in it

#!/bin/bash

# modify the following to suit your environment
export DB_BACKUP="/baks/mysql"
export DB_USER="root"
export DB_PASSWD="your-mysql-password-goes-here"

# title and version
echo ""
echo "Backup and rotate all mysql databases"
echo "--------------------------"

rm -rf $DB_BACKUP/04
mv $DB_BACKUP/03 $DB_BACKUP/04
mv $DB_BACKUP/02 $DB_BACKUP/03
mv $DB_BACKUP/01 $DB_BACKUP/02
mkdir $DB_BACKUP/01

echo "* Creating backup..."
mysqldump --user=$DB_USER --password=$DB_PASSWD --all-databases | bzip2 > $DB_BACKUP/01/mysql-`date +%Y-%m-%d`.bz2
echo "----------------------"
echo "Done"
exit 0

Install it via cron and have it run at 3:10 am every morning.

crontab -e

10 3 * * * /root/mysql_backups.sh  > /baks/status.log

This script will save the last 4 days of data.

Dump MySQL Database without Drop Table Syntax

Output .sql file for MySQL but without the drop table syntax before table name use the –skip-add-drop-table flag

mysqldump -u root -p database_name --skip-add-drop-table --skip-lock-tables > database_name.sql

Managing Timestamps in MySQL with a Trigger

MySQL doesn’t support having two columns with time stamping on both initialization and/or on updating at the same time. It would be nice to be able to do *this* where the created_at column gets the current_timestamp on initialization and the updated_at gets changed on updating the row.

# like so doesn't work...
create table entries(
  body blob,
  created_at datetime default current_timestamp,
  updated_at timestamp default current_timestamp on update current_timestamp
);

Seems like a feature a lot of folks would like. There are two work-arounds. The first is baking it into your application code with something like

create table entries(
  body blob,
  created_at datetime default null,
  updated_at timestamp default current_timestamp on update current_timestamp
);
insert into entries (body, created_at) values ('hello world', now());

The second way is to create a trigger and call the trigger on your insert action on a row.

create table entries (
  body  blob,
  created_at datetime default null,
  updated_at timestamp default null on update current_timestamp
);
create trigger init_created_at before insert on entries for each row set new.created_at = now();

Now whenever a new row is created the trigger will be executed and set the time to the current timestamp. You can forget about the created_at column in your code because it’s not meant to be changed.

Install do_mysql Ruby Gem on Mac OS X

I ran into the same problem when installing mysql gem for Rails development. This fix worked for me http://seanbehan.com/programming/fixing-mysql-for-rails-2-2-development-on-mac-os-x/

The same thing works with the data objects gem. Just specify the path the mysql config that it’s using and the gem should install just fine.

gem install do_mysql -- --with-mysql-config=/opt/local/lib/mysql5/bin/mysql_config

Installing Redis Server and Client on Mac OS X and Ubuntu

wget http://redis.googlecode.com/files/redis-0.900_2.tar.gz
tar xzvf redis-0.900_2.tar.gz
cd redis-0.900
make
mv redis-server /usr/bin/
mv redis-cli /usr/bin/