Jim,
You may want to integrate using something like MaatKit's
mk-parallel-dump...multi-threaded mysqldump. It is much faster at
dumping large databases.
http://www.maatkit.org/tools.html
http://maatkit.sourceforge.net/doc/mk-parallel-dump.html
Andrew
-----Original Message-----
From: mediawiki-l-bounces(a)lists.wikimedia.org
[mailto:mediawiki-l-bounces@lists.wikimedia.org] On Behalf Of Jim Hu
Sent: Monday, June 23, 2008 3:23 PM
To: MediaWiki announcements and site admin list
Subject: Re: [Mediawiki-l] backup advice wanted
What the heck, might as well post it to the list; that way others can
suggest improvements! This is my changed version from Dan's
Jim
==============
#!/bin/bash
DBname=<mysql_db_name>
DBuser=<mysql_username>
DBpassword=xxxx
DUMPDIR=/usr/local/sql_dumps # path to where you want the dumps saved
TIMESTAMP=`date +%a`
DUMPFILE=$DBname.Dump.$TIMESTAMP.sql
echo "Dumping $DBname to ... $DUMPFILE"
mysqldump $DBname -u $DBuser -p$DBpassword > $DUMPDIR/$DUMPFILE
echo "Zipping $DUMPFILE to ... $DUMPFILE.gz"
tar -czf $DUMPDIR/$DUMPFILE.tgz $DUMPDIR/$DUMPFILE ==============
Notes:
- I don't think the specification of bash matters.
- replace the obvious values for dbname, user, pass etc.
- the date +%a gives a 3 letter day of the week
- tar is probably overkill, Dan's original used gzip. I had some
problems with that, which I suspect is OSX weirdness. Or just store it
uncompressed.
- when running from cron, I comment out the echos.
On Jun 23, 2008, at 1:43 PM, dKosopedia admin wrote:
I too would like a copy of this shell script.
thanks
On Jun 23, 2008, at 12:27 PM, Scott Thile wrote:
> This sounds like a good backup solution for my situation too.
>
> Dan, any chance I can get a copy of your modified shell script? If
> so, my address is:
> mailto:sethile.pipes@gmail.com
>
> Hopefully I can figure out how to modify and implement it for our
> situation too.
>
> Thanks, Scott
>
>> -----Original Message-----
>> From: mediawiki-l-bounces(a)lists.wikimedia.org
>> [mailto:mediawiki-l-bounces@lists.wikimedia.org] On Behalf Of Jim Hu
>> Sent: Monday, June 23, 2008 10:26 AM
>> To: MediaWiki announcements and site admin list
>> Subject: Re: [Mediawiki-l] backup advice wanted
>>
>>
>> Thanks. Good to know that this works.
>>
>> Even though our largest are quite a bit bigger, I decided that this
>> would work, esp. if we do it at 1AM. I modified a shell script that
>> Dan Bolser sent me off-list and we're now
doing it daily where the
>> filename includes the day of the week so we have a rotating set
>> going back one week. These get backed up via the regular file
>> backup so we should be able to go back further.
>>
>> Jim
>>
>> On Jun 23, 2008, at 9:25 AM, Christensen, Courtney wrote:
>>
>>> Hi Jim,
>>>
>>> We routinely use mysqldump on live wikis. The only
>> consideration is,
>>> "Is content likely to change while the dump is happening?" So you
>>> could lock the wiki for editing while you dump if you want. Our
>>> largest wikis seem to take 30 minutes to dump at most. But then
>>> our
>> largest aren't
>>> very large, about ten thousand pages.
>>>
>>> Does that help?
>>> -Courtney
>>>
>>> PS. Don't forget to back up your uploads.
>>>
>>>
>>>
>>> -----Original Message-----
>>> From: mediawiki-l-bounces(a)lists.wikimedia.org
>>> [mailto:mediawiki-l-bounces@lists.wikimedia.org] On Behalf Of Jim
>>> Hu
>>> Sent: Friday, June 13, 2008 2:51 AM
>>> To: MediaWiki announcements and site admin list
>>> Subject: [Mediawiki-l] backup advice wanted
>>>
>>> Hi all,
>>>
>>> Having learned the hard way that just doing Retrospect filesystem
>>> backups does not always work on the database... I'm looking
>> for advice
>>> on how to do backups for our public wikis to minimize downtime when
>>> we're doing server maintenance.
>>>
>>> I've looked at the docs at:
>>>
>>>
http://www.mediawiki.org/wiki/Backup
>>>
>>> and I'm still a bit confused. Assuming that I'll be using
>> mysqldump...
>>> do I need to take the wiki offline during the dump process (the
>>> discussion of mysqldump vs mysqlhotcopy seems to be
>> inconsistent with
>>> what's at the
mysql.com website wrt using hotcopy with
>> InnoDB tables)?
>>> Are there any tips people can share?
>>>
>>> This came up during a migration of /usr/local/mysql and the apache
>>> root directory to a new set of disks. I had backed
>> everything up to
>>> another set of disks (actually this was temporarily using our dev
>>> server as a firewire drive). I pointed usr/local and
>> apache root to
>>> the backups in the hope that I could keep the wikis online while we
>>> replaced the main disks on the production
server. The plan
>> was to keep
>>> this running while we set up the new disks and then copy
>> data back to
>>> the new disks, change the symlinks for /usr/local etc to the new
>>> places, and restart mysql and apache. While this is going on, I've
disabled
editing on the backup copy, but users should be
able to view/
browse/search. But mysql was very unhappy and
wouldn't
run from the
backups. I ended up deleting all of the files
from the
backup copy of
the mysql data directory and rebuilding all the
databases in the
backup from an SQL dump, with apache turned off for several hours.
I feel like there has to be a better way, even with our small
operation.
Thanks!
Jim
=====================================
Jim Hu
Associate Professor
Dept. of Biochemistry and Biophysics
2128 TAMU
Texas A&M Univ.
College Station, TX 77843-2128
979-862-4054
_______________________________________________
MediaWiki-l mailing list
MediaWiki-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
_______________________________________________
MediaWiki-l mailing list
MediaWiki-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
=====================================
Jim Hu
Associate Professor
Dept. of Biochemistry and Biophysics
2128 TAMU
Texas A&M Univ.
College Station, TX 77843-2128
979-862-4054
_______________________________________________
MediaWiki-l mailing list
MediaWiki-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
_______________________________________________
MediaWiki-l mailing list
MediaWiki-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
_______________________________________________
MediaWiki-l mailing list
MediaWiki-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l
=====================================
Jim Hu
Associate Professor
Dept. of Biochemistry and Biophysics
2128 TAMU
Texas A&M Univ.
College Station, TX 77843-2128
979-862-4054
_______________________________________________
MediaWiki-l mailing list
MediaWiki-l(a)lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/mediawiki-l