Posts Tagged ‘mysql’

Backing up a MySQL database to Subversion

Monday, October 26th, 2009

As you may know, I recently set up subversion. As of this morning I was backing up my database with a daily cron job that sent mysqldump’s output to a file called database_[date].sql.bz2. That worked, but I’d rather not have to manage the files myself.

When I created the script to commit the newly created backup files, I intentionally removed the zipping step because I wanted to be able to use subversion’s diff feature, which would be useless if I commit binaries.

#!/bin/bash
# Dump the database and put it in a repository.
# Inspired by http://www.petersblog.org/node/959
# Written on Mon Oct 26 02:59:02 PST 2009
 
STORE_BACKUPS='/home/kj/backup'
WORKING_DIR='working_dir'
DATE=`/bin/date +%s`
FILE='server_'$DATE'.sql'
 
cd $STORE_BACKUPS
/usr/bin/mysqldump -u user -ppassword --skip-opt --comments=0 --all-databases>$FILE
 
# Must commit a whole directory even though
# we're only dealing with one file. The file name
# cannot change from day to day.
/bin/cp $FILE $WORKING_DIR/server.sql
cd $WORKING_DIR
/usr/bin/svn commit -m "daily backup; file saved as $FILE"
 
# I could delete $FILE now that $WORKING_DIR has
# been committed, but I like to have a backup
# backup in case the commit fails.
#     If you want to live dangerously and assume
# everything went as planned, feel free to
# uncomment the following line to delete.
#/bin/rm $FILE

I believe this will fail if cron tries to commit the file a remote repository over ssh because the user is unable to address the password prompt. I’m told ssh is designed to not let you redirect input or otherwise script around the password prompt. A potential solution is having the server authenticate ssh users by key instead of password.

Oct 26 Edit: Minor bug fix. I haven’t tested it post-change. I don’t think I added any new bugs, but nobody ever thinks that, so use discretion before running this script.

Fixing WordPress’ “Incoming Links” after server migration

Friday, October 2nd, 2009

I recently put this blog public, which means moving it from the address where I accessed it on localhost (127.0.0.1) on my dev box to it’s publicly visible address (kjcoop.com).

Most of the move was straightforward, but I noticed tonight that the “Incoming Links” box was still excitedly giving me the buzz about 127.0.0.1

The options table has a long string of what I believe to be Javascript in the option_value column for the row with option_name dashboard_widget_option. Rather than retype all 841 characters, MySQL’s good ol’ replace function will just update the relevant string:
update options set option_value=replace(option_value, '127.0.0.1/wordpress', 'kjcoop.org') where option_id=112;

I’m noting this here both in the interest of better remembering how useful the replace function is and for the sake of other WordPress users who may experience the same problem with the “Incoming Links” box.