Date: Tue, 30 Sep 2008 20:37:46 -0500 From: Paul Schmehl <pschmehl_lists@tx.rr.com> To: John Almberg <jalmberg@identry.com>, freebsd-questions@freebsd.org Subject: Re: Best way to back up mysql database Message-ID: <C2A130F53817BE6A45037FEE@Macintosh-2.local> In-Reply-To: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com> References: <835F48BA-494E-44A0-8D2B-D9F139AB2125@identry.com>
next in thread | previous in thread | raw e-mail | index | archive | help
--==========15EE95FEBB9E7470EDD2========== Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: quoted-printable Content-Disposition: inline --On September 30, 2008 6:18:35 PM -0400 John Almberg=20 <jalmberg@identry.com> wrote: > First, I wanted to say how great this list is. I'm a newbie FreeBSD > admin and, besides the Handbook and "Absolute FreeBSD" (which never > seems to leave my desk), this list is the best resource I have. > > I just had a huge scare today... One of the websites on my server uses a > large Mysql database. Somehow, one of the tables got corrupted today. > > I have been blithely backing up mysql with a simple cron script that ran > mysqldump every night. Simple, reliable, and I've never needed it. > > Today, when I realized the database was corrupted, I scrambled for my > backup, and realized that if I hadn't caught the problem today, tomorrow > my backup would have been overwritten, and I would have been... well, > not a happy camper. > > Again, I have run into a problem which is stupidly obvious to > experienced admins, I'm sure. I want to slap myself, but don't have > time. I'll do that after I have a better backup system in place. > > I am just about to dive into Google in search of a solution, but thought > I would fire off a quick request, in case there is an obvious solution > that everyone uses. If there is, a name or URL will do. I'll figure out > the rest. > > Any hints much appreciated. Not going home until this is fixed... Found this on the mysql documentation site: #!/bin/sh date=3D`date -I` mysqldump --opt --all-databases | bzip2 -c > /var/backup/databasebackup-$date.sql.bz2 The date must be something from linux, but you can do it like this in = FSBD: #!/bin/sh date=3D`date "+%Y-%m-%d.%H:%M:%S"` mysqldump --opt --all-databases | bzip2 -c > /var/backup/databasebackup-$date.sql.bz2 Using this makes every dump uniquely named, even if you run several a day, = so you would need to edit newsyslog.conf to rotate the dumps after a=20 number of dumps that you choose so you don't keep writing dumps until the=20 hard drive is full. Paul Schmehl, If it isn't already obvious, my opinions are my own and not those of my employer. ****************************************** WARNING: Check the headers before replying --==========15EE95FEBB9E7470EDD2==========--
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?C2A130F53817BE6A45037FEE>