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.

Installing Ruby on Rails 3, MySQL, Git, Ruby Enterprise Edition, Passenger (Mod_Rails) on Ubuntu with Rackspace Cloud.

Short and sweet. Here all the commands I run in this order to set up a brand new box. It usually takes about 10 – 15 minutes on a 256 MB RAM instance. Compiling Ruby Enterprise Edition, which is super easy, will take the most amount of time. It will seem to have gotten stuck. It hasn’t. It just takes a little while.

# Update, upgrade and install all necessary packages for Ruby on Rails server if you've got a fresh Ubuntu slice
apt-get update
apt-get upgrade

apt-get install build-essential patch libssl-dev libreadline5-dev

apt-get install ruby1.8-dev ruby1.8 ri1.8 rdoc1.8 irb1.8 libreadline-ruby1.8 libruby1.8 libopenssl-ruby imagemagick librmagick-ruby1.8 librmagick-ruby-doc libfreetype6-dev xml-core postfix
# postfix will prompt you for details
# use Internet Site and enter in the domain name you are planning on sending email from 

apt-get install apache2 apache2-prefork-dev libapr1-dev libaprutil1-dev libcurl4-openssl-dev git-core mysql-server mysql-client libmysqlclient15-dev libmysql-ruby
# mysql will also prompt you to set up a root user account. set the password to be anything you like

# next, download the latest release of ruby enterprise edition but when you're installing it on your own machine version numbers and release dates may have changed.
# pay attention to the version and release date before the file extension. it will be something like
# ... 1.8.7-2010.02
# this will change to something like 2011.03, 2011.04... etc in the future.
# just double check the paths on when you are installing and make the necessary substitutions

# ruby enterprise edition is available at http://www.rubyenterpriseedition.com/download.html
wget http://rubyforge.org/frs/download.php/71096/ruby-enterprise-1.8.7-2010.02.tar.gz
tar xzvf ruby-enterprise-1.8.7-2010.02.tar.gz 

./ruby-enterprise-1.8.7-2010.02/installer
# this may take a little while (just follow the instructions)
# and hit enter to install in default location (recommended) when prompted 

# and to install passenger (which is mod_rails for apache)
/opt/ruby-enterprise-1.8.7-2010.02/bin/passenger-install-apache2-module 

# i take the output from the above script and add it to my available modules directory
vim /etc/apache2/mods-available/passenger.conf

# and enter something like this in the newly created file (your version numbers will prob. be different)
LoadModule passenger_module /opt/ruby-enterprise-1.8.7-2010.02/lib/ruby/gems/1.8/gems/passenger-3.0.2/ext/apache2/mod_passenger.so
PassengerRoot /opt/ruby-enterprise-1.8.7-2010.02/lib/ruby/gems/1.8/gems/passenger-3.0.2
PassengerRuby /opt/ruby-enterprise-1.8.7-2010.02/bin/ruby

# and then sym link it to the enabled directory so that apache knows about it
ln -s /etc/apache2/mods-available/passenger.conf /etc/apache2/mods-enabled/passenger.conf

# and now i want to include ruby enterprise edition in my path so i add it to my profile (again make sure the path is correct)
vim /etc/profile.d/passenger.sh
export PATH=/opt/ruby-enterprise-1.8.7-2010.02/bin:$PATH

. /etc/profile.d/passenger.sh
# the "." file will make the setting available for the current terminal session
rails -v
ruby -v
rake -v
# should all be working now
# and
which ruby
# should point to the ruby enterprise edition under /opt

# next i
# set up public/private keys
# so i can do
# ssh localhost without using a password
cd
test -e ~/.ssh/id_dsa.pub || ssh-keygen -t dsa
cat ~/.ssh/id_dsa.pub >> ~/.ssh/authorized_keys2

# and finally install git
apt-get install git-core

You should now have a server ready to server ruby on rails applications!

Deploying to Dreamhost

Remember to include the host declaration in the database.yml file when you deploy to Dreamhost. Dreamhost does not use “localhost” which is typically the default setting when using the mysql adapter and developing locally or even on a small site.

At least for me, when I ported a Rails app to Dreamhost, this was the only “Gotcha”, because my log files were not reporting any errors and were instead serving the 500 something went wrong file.
A sample config/database.yml file

production:
  adapter: mysql
  username: youruser
  password: yourpasswd
  database: ror_production_db
  host: mysql.yourdomain.com

To port, I unpack my gems, if I haven’t already

rake gems:unpack

Then I freeze and package rails w/ my app just in case versions aren’t exact

rake rails:freeze:gems

Then I upload to Dreamhost!

My Review of Moodle 1.9 Extension Development

I wrote a review for Joseph Thibault’s Moodle News on extension development for Moodle. The book is quite good and I think an essential resource for anyone wanting to develop in Moodle. The book focuses on plugin development, but it will also give you an overview of the architecture, api and best practices.

I wish I had this book about 3 years ago when I first started fooling around in the code base! At any rate, you can read the review on Moodle News at http://www.moodlenews.com/2010/moodle-1-9-extension-development-review-by-bseanvt/

There is also an interesting discussion underway about the ‘bloat’ in the Moodle code at http://www.moodlenews.com/2010/opinion-1000000-lines-of-code/

Workshop Dog

Workshop Dog is a free events calendar for dog training workshops and group lessons.
more »

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

Generate MySQL Datetime Type Using PHP Date() Function

If you want to insert a datetime that matches the default mysql datetime type format use this

date('Y-m-d H:i:s');

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

Fixing MySQL for Rails 2.2 Development on Mac OS X

Oh what trouble Rails 2.2 and MySQL (on Mac OS X) can be. Rails, as of version >= 2.2, no longer comes bundled with the MySQL adapter. This means you’ll need to install it yourself, but it appears that the gem for installing it is also broken.

This will fail

gem install mysql

What you need to do is tell the gem which MySQL to use. I installed MySQL with mac ports, http://macports.org , so I need to specify this when I run the installation command.

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

Check out an earlier post explaining how to install MySQL with mac ports http://seanbehan.com/programming/installing-sphinx-search-engine-on-mac-os-x-or-ld-library-not-found-for-lmysqlclient/

You’re not out of the woods yet. Rails needs to know the location of the socket it uses to connect to the MySQL server. I created a symbolic link to the location Rails normally looks for this socket. You could specify the location of the socket in the config/database.yml file, however, then you’ll need to do this with each of your applications.

ln -s /private/tmp/mysql.sock /opt/local/var/run/mysql5/mysqld.sock

If there is an easier way to get MySQL back for Rails on Mac OS X please let me know. I’d love a quick fix to this problem.