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.

mysql_hourly.sh

#!/bin/bash

# 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.

mysql_daily.sh

#!/bin/bash

# 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

mysql_weekly.sh

#!/bin/bash

# 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 http://wiki.dreamhost.com/index.php/Crontab

# 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

MAILTO=your@email.com

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

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 username@mydomain.com"

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.

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)

mt/tmpl/cms/dialog/asset_options.tmpl
mt/tmpl/cms/dialog/edit_asset.tmpl

Replace the Following Code (same in both files)

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

With the new code

<mtapp:setting
    id="tags"
    label="<__trans phrase="Select one tag from the list below.">"
    label_class="text-top">
<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>
</ul>
</mtapp:setting>

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".

Creating a remote website mirror for development.

Problem: Client needs access to up-to-date website files to make updates, but I don't want him to edit the live site.

Solution: Create dev.mysite.com and an ftpuser for that domain. Use that ftpuser to place a mirror of the website within that subdomain. Since my links are relative, browsing works fine. Absolute links, of course, will remove you from the dev subdomain. Either way, replacing "www" with "dev" in the url gets you to the counterpart in the dev website.

GoDaddy Hosting Issue - "Downgrading" from Windows server to Linux

A client has a GoDaddy shared hosting account, hosting several domains on a MS Windows server. I needed some functionality which is only available on a linux server, so I went to the GoDaddy control panel and "downgraded" to linux. Technical support assured me that the conversion would be seamless, and although the transfer to linux would take 24 hours, the website wouldn't be interuppted (and I had no asp scripts to worry about).

Needless to say, the _entire_ site, including several domains, broke completely upon transfer.

The way the GoDaddy sets up multiple domains under one account is to have the main domain as the "root", and subsequent domains are mapped to subdirectories (from the control panel). For example, here is a typical directory structure:

• /index.html serves http://domain_1.com/index.html
  
• /domain_2/index.html serves http://domain_2.com/index.html
  
• /domain_3/ serves http://domain_3.com/index.html
  

In other words, additional domains within a Godaddy account are mapped to subdirectories of the main account.

What broke during the switch from windows to linux server was this mapping. So while I could access http://domain_1.com/domain_2/index.html, I couldn't access this same file via http://domain_2.com/index.html (like it did before the transfer).

Technical support said I would have to way for up to 3 days for everything to straighten out. As these were high-traffic commerce sites, 3 days was unacceptable. So rather than changing _all_ the links as suggested by GoDaddy tech support (still leaving the secondary domains broke), I added the following lines to my .htaccess file:

Redirect 302 http://www.domain_2.com/(.*) http://domain_1.com/domain_2/$1
Redirect 302 http://www.domain_3.com/(.*) http://domain_1.com/domain_3/$1

Voila! All the sites (and containing links) were fixed. Note that 302 is for temporary redirects, while 301 is used for permanent redirects. I hope that I will be able to remove the redirects in a couple days, when GoDaddy fixes its internal mapping.

update: Once GoDaddy fixed their mapping, I removed the 302 redirects.