2011-03-24 / Automysqlbackup
automysqlbackup 2011/03/24 01:30AM

I needed a restore solution for my database before launch. I found automysqlbackup which does everything I need. Installation took a bit of trial and error, so here's what works:

curl http://tiny.cc/ey3ap -L > automysqlbackup
sudo mv automysqlbackup /opt
sudo ln /opt/automysqlbackup /usr/bin/automysqlbackup -s
sudo chmod +x /usr/bin/automysqlbackup

Now the script is installed, but is not configured. The settings are read from /etc/automysqlbackup/automysqlbackup.conf, and a sample is inside the script itself, so:

sudo vim automysqlbackup
/START CFG
ma
/END CFG
:'a,. y
:e /etc/automysqlbackup/automysqlbackup.conf
p
ggVG:norm 0dw
:bn :bd :x

All that's left to do here is fill out the information requested by the script and save the file. I wanted the backups written to /var/backups/mysql, so I took a moment to set up the permissions for that.

sudo mkdir /var/backups/mysql
sudo chgrp webdev mysql
sudo chmod 770 mysql

I also noticed that automysqlbackup takes a password variable, but uses mysqldump internally. The man page of mysqldump recommends against passing passwords through the shell. Since this is due to a security issue with ps, I wanted to use the recommended method, a configuration file. I found the dbdump() function in the source of automysqlbackup (line 506 for me), and added a --defaults-file option to the mysqldump call. Due to a bug? this must be the first option given.

# Database dump function
dbdump () {
${MYSQLDUMP} --defaults-file=/home/dylan/.autobackup.cnf --user=${USERNAME}     --host=${DBHOST} ${OPT} ${1} > ${2}
return $?
}
 

Then finish up by creating your .cnf file wherever you chose.

[client]
password=your_password

At this point, the automysqlbackup script is all set to go, but will still get rejected by MySQL. There is no reason to that automysqlbackup needs to do anything but read the database to back it up, so create a new user constrained to the loopback address with these rights alone. Make sure this user is set in automysqlbackup.cnf.

CREATE USER 'autobackup'@'localhost' IDENTIFIED BY 'password';
GRANT select, lock tables ON *.* TO 'autobackup'@'localhost';

All that's left is to schedule it, so toss it into cron.

crontab -e
Go@daily automysqlbackup<Esc>

Here is the form to request a custom PTR record for an Amazon EC2 Elastic IP. It's included under Request to Remove Email Sending Limitations, which is not helpful.

This is good when setting up a mail server in Amazon's cloud. Without this request, the reverse DNS lookup will the default PTR record - something like ec2-50-16-219-8.compute-1.amazonaws.com. When the reverse DNS record doesn't match the origin domain, mail providers like GMail are likely to mark your mail as spam.

git post-receive hook for live branch 2011/02/14 01:30AM

It's easiest to use a git branch to manage the rollout of updates to my website. I wanted to add a message to the `git push` output when the commit is pushed live. The documentation notes that the post-recieve hook has access to the the ref-name. This script is placed in dylanstestserver.git/hooks/post-receive to do this. It took me some time to realize there are no arguments, the information is available on stdin.

if ! [ -t 0 ]; then
  read -a ref
fi
IFS='/' read -ra REF <<< "${ref[2]}"
branch="${REF[2]}"
if [ "live" == "$branch" ]; then 
  git --work-tree=/var/www/dylanstestserver.com/ --git-dir=.. checkout -f
  echo 'Changes pushed live.'
fi

This is a macro that's been very helpful for me while writing short scripts in Vim. It adds and comments out the output of your script (or any command) to the end of the current file.

G o<Esc> ma:r!./filename.sh<Enter> $ v 'a:norm 0i## <Enter>
qt 'ajdG :r!./filename.sh<Enter> $ v 'a:norm 0i## <Enter> 'acc## Test output:<Enter> 'aq

@t

  • G - cursor to end of file
  • o<Esc> - insert a new line
  • ma - create a mark in registry a
  • :r!./filename.sh - execute filename.sh and print output on next line
  • $ - cursor to end of line
  • v - switch to visual mode
  • 'a - cursor to mark a
  • :norm - applies the following action to each line selected
  • 0i## <Enter> - cursor to the start of the line, switch to insert mode, and write "## "
  • qt - start recording a macro in register t
  • 'ajdG - cursor to mark a and delete to end of file
  • :r!./filename.sh<Enter> $ - insert the output and cursor to end of file
  • 'a:norm 0t## <Enter> - comment out output
  • 'acc## Test output:<Enter> 'aq - cursor to mark a, adds comment, and ends the macro
  • @t will now test your output
2011-02-13 / Mysql Is My Cms

I've searched for a CMS to use. I want something small, to edit my notes in Vim and sort them easily in code. I finally decided to write a pagination script for the site, and use the MySQL shell to keep track of things. Python scripts take care of the most repetitive tasks.

mysql> SHOW tables FROM dylanstestserver;
+----------------------------+
| Tables_in_dylanstestserver |
+----------------------------+
| notes                      |
| comments                   |
+----------------------------+
2 rows in set (0.00 sec)

Once everything was running the way I wanted on my development stack, it was a one line command to copy over the database.

mysqldump dylanstestserver -p | mysql -u dylan -h dylanstestserver.com -D dylanstestserver -p
previous