Monday, April 27, 2009

mysql backup scripts with cron

Here's how I automatically backup my mysql databases on my DreamHost account. I use three shell scripts and a cron job to run them at regular intervals. The databases get backed up hourly. The backups are saved hourly, daily, and weekly. The weekly archive gets a unique name and is therefore not overwritten.


# This script backs up mysql databases; appends hour 00 to 23
# Overwritten daily
# Run from crontab ever hour on 30-minute mark (30 * * * *)
# Requires mysql_daily script

suffix=$(date +%H)

# Repeat for each database
mysqldump --opt -u USER -pPASSWORD -h HOST DATABASE > ~/backups/hourly/my_mysql_db.$suffix

Note that I use the short option form (-p), so you cannot have a space between the option and the password. Complete documentation for mysqldump.


# This script copies the last backup of the day to a daily archive 00 to 06
# overwritten weekly
# Run at hour 23 hours 40 minutes every day (40 23 * * *)
# Requires mysql_hourly script

suffix=$(date +%d)

cp ~/backups/hourly/my_mysql_db.23 ~/backups/daily/my_mysql_db.$suffix


# This script copies the last backup on friday to a weekly archive
# Never overwritten
# Run at hour 23 hours 45 minutes every friday. (45 23 * * 5)
# Requires mysql_hourly script

suffix=$(date +%Y%m%d)

cp ~/backups/hourly/my_mysql_db.23 ~/backups/weekly/my_mysql_db.$suffix

The crontab runs the scripts on a regular schedule. Here is how I run the scripts above. By the way, don't forget to make the scripts ececutable (chmod +x from the command line or use your ftp program).

my dreamhost crontab

# see

# minute (0-59),
# | hour (0-23),
# | | day of the month (1-31),
# | | | month of the year (1-12),
# | | | | day of the week (0-6 with 0=Sunday).
# | | | | | commands

# Runs the mysqlbackup script hourly (on the half hour), and the daily at 11:45pm every day
30 * * * * ~/lib/scripts/
45 23 * * * ~/lib/scripts/
45 23 * * 5 ~/lib/scripts/

Of course put your email address on the MAILTO line.

DreamHost has a very nice cron job front end in its "Goodies" section of the control panel. However, it runs the hourlys on the hour, and the dailys at midnight. I like to set the time to the minute, so I use ssh from the terminal to log in and st up the cron job. Here is the drill (commands shown in double quotes - don't type the double quotes} :

First login to your server via SSH in terminal(enable ssh from the "Users" web panel): "ssh"

Once logged into your server, list your current crontab file: "crontab -l"

Set your editor to vim (you can also put this in your bashrc or bash_profile): "export EDITOR='vim'"

now edit the crontab: "crontab -e"

type "i" to enter insert mode, type or cut and paste your code in (if you cut and paste, use notepad or textedit or other real text editor, not MS Word, etc.) I think you need to have a line return (blank line) at the end of your code.

Hit the "esc" button to exit insert mode, then type ":wq!" or ":x" to save and exit. Type "crontab -l" to check your work, then "exit" to log out from ssh.

Restoring from your backup: "mysql -u user_name -p -h hostname database_name < dumped_file"

More Docs from the DreamHost Wiki:

Backup your database.
Restore your database.

Labels: ,

Movable Type Asset Tags

Asset Tag Input : Convert text field to Radio Buttons or checkboxes

Movable Type assets (images, docs, pdfs, xls, virtually any document) can be uploaded and managed through MT's dashboard. A label, description, and tag(s) can be assigned. I use the asset tags to selectively list assets on my blog's index pages. The problem is that it is hard to remember what is the exact wording of the tag (capitalized, plural, etc.), because tags are entered in a text form. And I certainly don't want my clients to have to remember which tags to use.

Here's the Original "Edit Asset" Panel

Same Panel with Radio Buttons

This limits the choice of asset tags, but then that is the point. I had to edit two MT system files to accomplish this. Fortunately, it's the same change in both files.

Edit These Files (backup first)


Replace the Following Code (same in both files)

<mtapp:setting id="tags" label="<__trans phrase=" tags="">"
<div class="textarea-wrapper">
<input name="tags" id="tags" class="full-width" value="<$mt:var name=" tags="" escape="html">" autocomplete="off" />

With the new code

label="<__trans phrase="Select one tag from the list below.">"
<ul class="edit-tag-radio">
<li><label><input type="radio" name="tags" id="tags" value="minutes"<mt:if name="tags" eq="minutes"> checked="checked"</mt:if> /> <__trans phrase="Meeting Minutes"></label></li>
<li><label><input type="radio" name="tags" id="tags" value="financial"<mt:if name="tags" eq="financial"> checked="checked"</mt:if> /> <__trans phrase="Financials"></label></li>
<li><label><input type="radio" name="tags" id="tags" value="welcome-document"<mt:if name="tags" eq="welcome-document"> checked="checked"</mt:if> /> <__trans phrase="Welcome Documents"></label></li>
<li><label><input type="radio" name="tags" id="tags" value="image"<mt:if name="tags" eq="image"> checked="checked"</mt:if> /> <__trans phrase="Image"></label></li>

Note that the "value" and "__trans phrase" values are particular to your project. Also more options can be added.

Prefer a Checkbox?

If you prefer a checkbox list instead of radio (allowing multiple selections), just use type="checkbox".

Labels: ,