Re: [rt-users] Backing up RT
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
Re: [rt-users] Backing up RT
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
Re: [rt-users] Backing up RT
On Tue, Jan 04, 2011 at 06:27:24PM -0600, Max McGrath wrote: *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.
Re: [rt-users] Backing up RT
On 01/05/2011 09:07 AM, Max McGrath wrote: 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 mmcgr...@carthage.edu mailto:mmcgr...@carthage.edu Max, thank you for the list. I will follow your footsteps. -Andy
Re: [rt-users] Backing up RT
On 01/05/2011 09:23 AM, Konstantin Khomoutov wrote: On Wed, 05 Jan 2011 07:36:46 -0500 Andy Graybealandy.grayb...@casanueva.com wrote: 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. 1. http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html Again, thanks for the response. Very helpful. -Andy
Re: [rt-users] Backing up RT
On Wed, Jan 05, 2011 at 07:36:46AM -0500, Andy Graybeal wrote: 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
Re: [rt-users] Backing up RT
On Wed, 05 Jan 2011 07:45:33 -0500 Andy Graybeal andy.grayb...@casanueva.com wrote: 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.
Re: [rt-users] Backing up RT
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 mmcgr...@carthage.edu On Wed, Jan 5, 2011 at 6:45 AM, Andy Graybeal andy.grayb...@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
Re: [rt-users] Backing up RT
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 mmcgr...@carthage.edu On Wed, Jan 5, 2011 at 6:45 AM, Andy Graybeal andy.grayb...@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
[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
Re: [rt-users] Backing up RT
On Tue, 04 Jan 2011 17:38 -0500, Andy Graybeal andy.grayb...@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. 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).
Re: [rt-users] Backing up RT
--- On Tue, 1/4/11, Andy Graybeal andy.grayb...@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)).
Re: [rt-users] Backing up RT
*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 mmcgr...@carthage.edu On Tue, Jan 4, 2011 at 4:56 PM, Joseph Spenner joseph85...@yahoo.comwrote: --- On Tue, 1/4/11, Andy Graybeal andy.grayb...@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)).
[rt-users] BACKING UP RT WITH POSTGRES
What is the best way to do this? also what should be backed up? thanks ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
Re: [rt-users] BACKING UP RT WITH POSTGRES
Kurt A. Brust wrote: What is the best way to do this? also what should be backed up? Just use pg_dump to create a self-consistent snapshot of the database. Hopefully you have some general backup scheme to back up your systems, which will safeguard your RT customizations (RT_SiteConfig.pm at a minimum) as well as the database snapshot you create. E.g., I have a crontab for the 'postgres' superuser, which backs up my RT database (called rt3) to a single compressed SQL file: # Do a dump every day at 6:45pm 45 18 * * * /usr/local/pgsql/bin/pg_dump rt3 | gzip -c /usr/local/pgsql/dumps/rt3.daily.dump.gz This dump can be slurped right into psql to restore the database if needed: gunzip -c rt3.daily.dump.gz | psql You could also use the 'custom' option on pg_dump, which can be used in concert with the pg_restore utility for greater flexibility. See the man page for pg_dump. When you do major postgresql version upgrades, you need to do dumps/restores, so it's good to know how to do this. -Kevin Murphy ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com
RE: [rt-users] BACKING UP RT WITH POSTGRES
What is the best way to do this? also what should be backed up? I'm doing a Dump of the DB /usr/bin/pg_dump rt3 /root/backups/rt3-db-$TODAY_DATE.sql Then running tar and picking up that file, the whole of /opt/rt3 and rolling them into one package. You might also want to pick up the Apache config if you're not back it up elsewhere. - Scott ___ http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-users Community help: http://wiki.bestpractical.com Commercial support: [EMAIL PROTECTED] Discover RT's hidden secrets with RT Essentials from O'Reilly Media. Buy a copy at http://rtbook.bestpractical.com