| 1 |
On Sunday 30 September 2007, Neil Bothwick wrote: |
| 2 |
> Other data in /var/lib. For example, any databases kept in /var/lib/mysql. |
| 3 |
|
| 4 |
Rather than backup MySQL's or Postgres' binary storage I prefer to use the |
| 5 |
relevant tool (mysqldump, pgdump[all]) to backup the database |
| 6 |
to /root/backups/ just prior to running the real backup job. This has the |
| 7 |
advantage that you can generally restore onto any version of the database |
| 8 |
server, rather than the specific version that was running when the backup was |
| 9 |
taken. |
| 10 |
|
| 11 |
I'm not 100% sure but I thought this method of dumping the database is the |
| 12 |
only way to guarantee consistency of the snapshot without having to stop the |
| 13 |
database server for the duration of the backup. From what I understand when |
| 14 |
backing up a live database Postgres guarantees the consistency using MVCC but |
| 15 |
MySQL may be a little less careful if INSERTs/UPDATEs happen during the |
| 16 |
backup. For keeping a hot-spare failover database server closely in sync |
| 17 |
with realtime changes I'd suggest Slony1. |
| 18 |
|
| 19 |
Unless the database contains blobs I prefer to dump as plain text INSERTs and |
| 20 |
compress using gzip patched with --rsyncable support which leads me to... |
| 21 |
|
| 22 |
After backing up the database I /always/ backup to a remote machine, |
| 23 |
sometimes two, using rdiff-backup. It does point-in-time recovery and |
| 24 |
supports ACLs and xattr. The backup is not in some odd file format; it's |
| 25 |
stored in a subfolder of your filesystem just like any other tree of files so |
| 26 |
it is instantly available. A subfolder called rdiff-backup-data stores all |
| 27 |
the rollbacks and also all the acl/xattr/other metadata so the destination |
| 28 |
filesystem doesn't need to support them. There was even a GSoC project to |
| 29 |
provide a FUSE interface to mount the backup folder as it appeared at the |
| 30 |
time of some previous backup. This is all /very/ nice for when used in |
| 31 |
combination with Linux-VServer. With a little thought about XIDs you can |
| 32 |
start a backed-up VServer on your development box ;-) |
| 33 |
|
| 34 |
This is the essence of what I run in a cron job on the machine needing to be |
| 35 |
backed up: |
| 36 |
#!/bin/bash |
| 37 |
|
| 38 |
#/root/backup_mysql.sh |
| 39 |
#/root/backup_postgres.sh |
| 40 |
|
| 41 |
mount /boot > /dev/null 2> /dev/null |
| 42 |
|
| 43 |
rdiff-backup --force --remove-older-than 90d \ |
| 44 |
backups@backuphost::~/backups/myhostname/ |
| 45 |
|
| 46 |
echo "--------------------" |
| 47 |
|
| 48 |
time rdiff-backup \ |
| 49 |
-v2 --print-statistics \ |
| 50 |
--exclude /mnt \ |
| 51 |
--exclude /media \ |
| 52 |
--exclude /dev \ |
| 53 |
--exclude /proc \ |
| 54 |
--exclude /tmp \ |
| 55 |
--exclude /var/tmp \ |
| 56 |
--exclude /var/cache/squid/ \ |
| 57 |
--exclude /var/cache/http-replicator/ \ |
| 58 |
--exclude /var/lib/mysql/ \ |
| 59 |
--exclude /var/lib/postgresql/data/base/ \ |
| 60 |
--exclude /var/lib/postgresql/data/global/ \ |
| 61 |
--exclude /var/lib/postgresql/data/pg_clog/ \ |
| 62 |
--exclude /var/lib/postgresql/data/pg_subtrans/ \ |
| 63 |
--exclude /var/lib/postgresql/data/pg_tblspc/ \ |
| 64 |
--exclude /var/lib/postgresql/data/pg_xlog/ \ |
| 65 |
--exclude /sys/ \ |
| 66 |
--exclude /usr/portage/ \ |
| 67 |
--exclude /usr/portage/distfiles \ |
| 68 |
/ backups@backuphost::~/backups/myhostname/ |
| 69 |
|
| 70 |
umount /boot > /dev/null 2> /dev/null |
| 71 |
-- |
| 72 |
gentoo-user@g.o mailing list |
Archives