As the old chineese saying states : “Only true man does not make any backups of his data”, besides replication of all mysql data, we decided to additionally back up ALL mysql data to Amazon with compressed mysqldump overnight. Since we are running mainly debian machines, we created unified nice looking script for this task.
This script assumes that you have python “boto” module installed. In case you didn’t already install it just type :
root@app:~# easy_install boto
Searching for boto
Best match: boto 2.0rc1
[...]
You should then create ~/.boto file with credentials needed to log into Amazon S3 .
My .boto file looks like this :
[Credentials]
aws_access_key_id = {your key}
aws_secret_access_key = {your secret}
Next thing is just to add following script to crontab :
#!/usr/bin/env python
import ConfigParser
import os
import time
import boto
s3 = boto.connect_s3()
bucket = s3.get_bucket(‘backup.314t.com’)
config = ConfigParser.ConfigParser()
config.read(“/etc/mysql/debian.cnf”)
username = config.get(‘client’, ‘user’)
password = config.get(‘client’, ‘password’)
hostname = config.get(‘client’, ‘host’)
filestamp = time.strftime(‘%Y-%m-%d’)
database_list_command=”mysql -u %s -p%s -h %s –silent -N -e ‘show databases’” % (username, password, hostname)
for database in os.popen(database_list_command).readlines():
database = database.strip()
if database == ‘information_schema’:
continue
filename = “/backups/mysql/%s-%s.sql” % (database, filestamp)
print “Backing up %s database” % database
os.popen(“mysqldump -u %s -p%s -h %s -e –opt -c %s | gzip -c > %s.gz” % (username, password, hostname, database, filename))
newdir = bucket.new_key(filestamp)
virtualpath = filestamp + “/” + filename + “.gz”
newfile = bucket.new_key(virtualpath)
hddpath = filename + “.gz”
newfile.set_contents_from_filename(hddpath)
It gets username and password for mysql from Debian’s root-like account so there’s no need to create special user.
Place above script in e.g. /root/scripts/amazon_mysql_backup.py and add it to cron with crontab -e command (from root account) just adding following line on the end :
30 4 * * * python /root/scripts/amazon_mysql_backup.py > /dev/null 2>&1
This will create daily offline compressed backups on amazon.
Good luck with your data !
