Greetings,
I need a recommendation for backing up RT.
I am planning on using a cron job to run mysqldump to dump the db, then
rsync the db and /etc/ conf files to a host that runs a backup job every
night to tape.
What do you guys do?
-Andy
Greetings,
I need a recommendation for backing up RT.I am planning on using a cron job to run mysqldump to dump the db, then
rsync the db and /etc/ conf files to a host that runs a backup job every
night to tape.
We use mysqldump and logrotate to keep a week of DB dumps on the local
filesystem, we perform daily backups to a VTL and then tape, and we also
perform nightly SAN snapshots on the RT host’s LUN (making sure to lock
the databases before the snapshot, and then unlocking after so the MySQL
DB files are in a consistent state during the snap).
Greetings,
I need a recommendation for backing up RT.I am planning on using a cron job to run mysqldump to dump
the db, then rsync the db and /etc/ conf files to a host
that runs a backup job every night to tape.What do you guys do?
I run a nightly dump as root:
mysqldump --opt rt3 -u root -p > rt3.sql
(assuming the root user can perform that dump)
Then scp the file to another system.
Since the rest of the system doesn’t change much, I don’t bother with much of anything else. I have a snapshot of the whole system (my RTs are VMs under VirtualBox, so they’re easy to backup (single file)).
*I run a nightly dump as root:
mysqldump --opt rt3 -u root -p > rt3.sql
(assuming the root user can perform that dump)
Then scp the file to another system.
Since the rest of the system doesn’t change much, I don’t bother with much
of anything else. I have a snapshot of the whole system (my RTs are VMs
under VirtualBox, so they’re easy to backup (single file)).*
This is exactly what I do. Is this a decent way to be doing it – I’ve
always just assumed it was okay. I do it hourly, and then SCP it to another
system.
Should I be stopping mysql or anything before doing the mysqldump?
Max McGrath
Asst. Network Admin/Systems Specialist
Carthage College
262-552-5512
mmcgrath@carthage.eduOn Tue, Jan 4, 2011 at 4:56 PM, Joseph Spenner joseph85750@yahoo.comwrote:
— On Tue, 1/4/11, Andy Graybeal andy.graybeal@casanueva.com wrote:
Greetings,
I need a recommendation for backing up RT.I am planning on using a cron job to run mysqldump to dump
the db, then rsync the db and /etc/ conf files to a host
that runs a backup job every night to tape.What do you guys do?
I run a nightly dump as root:
mysqldump --opt rt3 -u root -p > rt3.sql
(assuming the root user can perform that dump)Then scp the file to another system.
Since the rest of the system doesn’t change much, I don’t bother with much
of anything else. I have a snapshot of the whole system (my RTs are VMs
under VirtualBox, so they’re easy to backup (single file)).
We use mysqldump and logrotate to keep a week of DB dumps on the local
filesystem, we perform daily backups to a VTL and then tape, and we also
perform nightly SAN snapshots on the RT host’s LUN (making sure to lock
the databases before the snapshot, and then unlocking after so the MySQL
DB files are in a consistent state during the snap).
Wow, you have a complicated system. I was considering backing up the
whole machine with LVM snapshots, but I don’t think I have the space
overall to deal with full machine snapshots.
If I ever ended up doing LVM snaps, do I need to lock the db first…
or can I get away with not locking the db with LVM snaps?
You use logrotate to manage your mysqldumps! Excellent idea.
I need to learn logrotate. I was wondering how I can keep mysqldumps
from each day and not lose control of them. I’m excited.
Thank you for your response. (I need to google VTL)
-Andy
I run a nightly dump as root:
mysqldump --opt rt3 -u root -p> rt3.sql
(assuming the root user can perform that dump)Then scp the file to another system.
Since the rest of the system doesn’t change much, I don’t bother with much of anything else. I have a snapshot of the whole system (my RTs are VMs under VirtualBox, so they’re easy to backup (single file)).
Good this sounds like I’m on the right track then.
How do you manage your virtual machine snapshots?
Why vbox and not kvm?
Thank you for your response.
-Andy
This is exactly what I do. Is this a decent way to be doing it – I’ve
always just assumed it was okay. I do it hourly, and then SCP it to
another system.Should I be stopping mysql or anything before doing the mysqldump?
Max,
What made you do hourly dumps? I’m thinking now instead of nightly, I
should be doing hourly dumps like you.
-Andy
*I run a nightly dump as root:
mysqldump --opt rt3 -u root -p > rt3.sql
(assuming the root user can perform that dump)Then scp the file to another system.
Since the rest of the system doesn’t change much, I don’t bother with much
of anything else. I have a snapshot of the whole system (my RTs are VMs
under VirtualBox, so they’re easy to backup (single file)).*
*
*
This is exactly what I do. Is this a decent way to be doing it – I’ve
always just assumed it was okay. I do it hourly, and then SCP it to another
system.Should I be stopping mysql or anything before doing the mysqldump?
“–opt” locks all the tables in a given database before backing it up so
that only reads can be done on it. This is for MyISAM engine, for
engines supporting ACID there’s more effective option available –
bother to read mysqldump man page.
If you think preventing read access to the db during backup affects the
RT performance (but you should really measure the dump time first),
you can look at mysqlhotcopy script (MyISAM only) which trades speed for
disk space.
I forgot to mention, I also backup a few other files/folder critical to RT:
######################################
BACKUP DATABASES
######################################
mysqldump -u root rt3 > rt3_backup.sql
mysqldump -u root mysql > mysql_backup.sql
#mysqldump -u root information_schema > information_schema_backup.sql######################################
COPY OTHER FILES/FOLDERS
######################################
cp /etc/aliases /opt/rt3/lib/RT/rt-backup/
cp /etc/apache2/httpd.conf /opt/rt3/lib/RT/rt-backup/
cp /opt/rt3/etc/RT_SiteConfig.pm /opt/rt3/lib/RT/rt-backup/
cp -r /opt/rt3/local/ /opt/rt3/lib/RT/rt-backup/This way I have a copy of my e-mail addresses for my queues, my apache
config, my SiteConfig, and my whole local folder…Max McGrath
Asst. Network Admin/Systems Specialist
Carthage College
262-552-5512
mmcgrath@carthage.edu mailto:mmcgrath@carthage.edu
Max, thank you for the list. I will follow your footsteps.
-Andy
If I ever ended up doing LVM snaps, do I need to lock the db first…
or can I get away with not locking the db with LVM snaps?
In this case locking is less important than flushing as you would be
backing up the filesystem and hence you will need the on-disk database
representation to be consistent before taking the snapshot. To do this,
you either have to stop the server just before taking a snapshot, or
write a complicated script which would work just like mysqlhotcopy but
would take the snapshot instead of copying the data.
Note that mysqlhotcopy explicitly states it works only with MyISAM and
ARCHIVE engines, and the comments on [1] hint that backing up InnoDB
this way is at least tricky and error-prone.
Argh… Thank you for the knowledge. This is too much for my plate at the
moment. I’ll put off the LVM snaps until needed and stick with config /
db backups for now. Thanks again.
You use logrotate to manage your mysqldumps! Excellent idea.
I need to learn logrotate. I was wondering how I can keep mysqldumps
from each day and not lose control of them. I’m excited.
One commonly used straightforward approach is to encode the formatted
timestamp into the names of generated backup files, like this:
$ mysqldump … | gzip -c>/path/to/db-backup-$(date +‘%F-%T’).sql.gz
Wonderful, I will make use of this technique. I have seen and used this
technique just last week. Though the addition of gzip is a spin on what
I saw. I like it. Here’s what I used: date +%Y%m%d%H%M%S.xml out of
the PF book or website.
Again, thanks for the response. Very helpful.
-Andy
You use logrotate to manage your mysqldumps! Excellent idea.
if you are using Debian, the command “savelog” is also often convenient
to rotate a file at the beginning or end of a script.
saveloq -q /var/backups/mysql/dump.sql.gz
mysqldump … | gzip -c > /var/backups/mysql/dump.sql.gz
We use mysqldump and logrotate to keep a week of DB dumps on the
local filesystem, we perform daily backups to a VTL and then tape,
and we also perform nightly SAN snapshots on the RT host’s LUN
(making sure to lock the databases before the snapshot, and then
unlocking after so the MySQL DB files are in a consistent state
during the snap).
[…]
If I ever ended up doing LVM snaps, do I need to lock the db first…
or can I get away with not locking the db with LVM snaps?
In this case locking is less important than flushing as you would be
backing up the filesystem and hence you will need the on-disk database
representation to be consistent before taking the snapshot. To do this,
you either have to stop the server just before taking a snapshot, or
write a complicated script which would work just like mysqlhotcopy but
would take the snapshot instead of copying the data.
Note that mysqlhotcopy explicitly states it works only with MyISAM and
ARCHIVE engines, and the comments on [1] hint that backing up InnoDB
this way is at least tricky and error-prone.
You use logrotate to manage your mysqldumps! Excellent idea.
I need to learn logrotate. I was wondering how I can keep mysqldumps
from each day and not lose control of them. I’m excited.
One commonly used straightforward approach is to encode the formatted
timestamp into the names of generated backup files, like this:
$ mysqldump … | gzip -c >/path/to/db-backup-$(date +‘%F-%T’).sql.gz
This is exactly what I do. Is this a decent way to be doing it –
I’ve always just assumed it was okay. I do it hourly, and then SCP
it to another system.
[…]
What made you do hourly dumps? I’m thinking now instead of nightly,
I should be doing hourly dumps like you.
It’s just about the amount of stuff you’re OK to lose in case of a
problem requiring restoring the DB state from backups: if you have a
very busy RT instance where several ticket transactions per hour is
normal, you would want to do per-hour backups. If you have an RT
instance which only receives few tickets per week (as we do), even
weekly backups would do just fine.
So it’s not about one backup schedule being more correct than the other.
I use that to create the sql backups and it gives me a week of dailys, weeklys, monthlys, and yearlys (obviously depending on how long you have used it). I then rsync only changed files from my backup dir and rt dir to a dfs server that goes to tape. Automysqlbackup is very easy to configure and run.-----Original Message-----
From: rt-users-bounces@lists.bestpractical.com [mailto:rt-users-bounces@lists.bestpractical.com] On Behalf Of Andy Graybeal
Sent: Tuesday, January 04, 2011 5:39 PM
To: rt-users@lists.bestpractical.com; tech@casanueva.com
Subject: [rt-users] Backing up RT
Greetings,
I need a recommendation for backing up RT.
I am planning on using a cron job to run mysqldump to dump the db, then rsync the db and /etc/ conf files to a host that runs a backup job every night to tape.
What do you guys do?
-Andy
I just figured in the case of a crash or any data loss, it’d be nice to have
only lost an hour or less worth of tickets!
Max McGrath
Asst. Network Admin/Systems Specialist
Carthage College
262-552-5512
mmcgrath@carthage.eduOn Wed, Jan 5, 2011 at 6:45 AM, Andy Graybeal andy.graybeal@casanueva.comwrote:
This is exactly what I do. Is this a decent way to be doing it – I’ve
always just assumed it was okay. I do it hourly, and then SCP it to
another system.Should I be stopping mysql or anything before doing the mysqldump?
Max,
What made you do hourly dumps? I’m thinking now instead of nightly, I
should be doing hourly dumps like you.-Andy
I forgot to mention, I also backup a few other files/folder critical to RT:
BACKUP DATABASES
mysqldump -u root rt3 > rt3_backup.sql
mysqldump -u root mysql > mysql_backup.sql
#mysqldump -u root information_schema > information_schema_backup.sql
COPY OTHER FILES/FOLDERS
cp /etc/aliases /opt/rt3/lib/RT/rt-backup/
cp /etc/apache2/httpd.conf /opt/rt3/lib/RT/rt-backup/
cp /opt/rt3/etc/RT_SiteConfig.pm /opt/rt3/lib/RT/rt-backup/
cp -r /opt/rt3/local/ /opt/rt3/lib/RT/rt-backup/
This way I have a copy of my e-mail addresses for my queues, my apache
config, my SiteConfig, and my whole local folder…
Max McGrath
Asst. Network Admin/Systems Specialist
Carthage College
262-552-5512
mmcgrath@carthage.eduOn Wed, Jan 5, 2011 at 6:45 AM, Andy Graybeal andy.graybeal@casanueva.comwrote:
This is exactly what I do. Is this a decent way to be doing it – I’ve
always just assumed it was okay. I do it hourly, and then SCP it to
another system.Should I be stopping mysql or anything before doing the mysqldump?
Max,
What made you do hourly dumps? I’m thinking now instead of nightly, I
should be doing hourly dumps like you.-Andy
You use logrotate to manage your mysqldumps! Excellent idea.
if you are using Debian, the command “savelog” is also often
convenient to rotate a file at the beginning or end of a script.saveloq -q /var/backups/mysql/dump.sql.gz
mysqldump … | gzip -c > /var/backups/mysql/dump.sql.gz
Speaking about Debian, I would also add that there’s no need to create
a special user to do backups as Debian already provides one with
sufficient privileges for maintenance purposes, so one can just pass
the relevant config file to various MySQL utilities, like this:
mysqldump --defaults-file=/etc/mysql/debian.cnf --opt …
I’m quite sure other distros do something akin to this, so it could
probably be adapted.