Simple MySQL backup to gmail on Ubuntu/Debian

Posted on by

Backing up your MySQL database (if it’s a reasonable size, like < 100s of MB) can be done with a cronjob that runs mysqldump, gzip, and mpack.

First set up your server to use gmail as the Mail Transfer Agent:

$ sudo apt-get install ssmtp mpack

Then edit /etc/ssmtp/ssmtp.conf, replacing the YOUR… values as appropriate:

# Where will the mail seem to come from?
rewriteDomain=YOURDOMAIN.com
# The full hostname of localhost:
hostname=HOSTNAME
root=root@YOURDOMAIN.com
AuthUser=YOURGMAILUSER
AuthPass=YOURGMAILPASSWORD
mailhub=smtp.gmail.com:587
UseSTARTTLS=YES
FromLineOverride=YES

Then copy the following into a new file called /etc/cron.daily/nightly-backup. Note that this is using the MySQL username and password that the Debian/Ubuntu package has installed. If you installed MySQL from source, you’ll want to change the defaults-file location.

#!/bin/sh
mkdir -p /backup/mysqldump/
mysqldump --defaults-file=/etc/mysql/debian.cnf --all-databases | gzip > /backup/mysqldump/$(date '+%Y%m%d').sql.gz
mpack -s "mysql backup" /backup/mysqldump/$(date '+%Y%m%d').sql.gz "YOURGMAILUSER"

Related posts:

  1. Faster MySQL dumps and loads with –tab and –use-threads
    By default, mysqldump writes a series of sql DDL and inserts to standard out, that you can then pipe to another database server to recreate a given database. The problem......
  2. How to view only unread gmail conversations
    Gmail doesn’t have an obvious way to only look at conversations that are unread. If you don’t “archive” conversations out of your inbox, it can get pretty crufty. It’s easy......
  3. HOWTO enable the query log on MySQL on Mac OS X
    Tailing the MySQL query log in real time can be a lifesaver for any developer, and it’s pretty easy to do: Make a file for the mysqld process to write......
  4. Apache2, PHP, and MySQL on Mac OS X using MacPorts
    1. Install MacPorts Follow the instructions here: http://www.macports.org/install.php. 2. Install apache2 sudo port install apache2 Note that the macports instructions suggest installing the launchctl script now, but we’ll do that......