Cliff,
Of course everything depends on the type of database one has in place, but for
the style of work I'm involved in I've kept everything on a "keep it simple"
backup plan. I wrote 2 vbs scripts that copy out the data folder to a shared
drive. One does so on an hourly schedule, and the other once a night after
hours and archives for two weeks. I don't shut down MySQL service -- perhaps
that's bad practice but to date I have had no ill effects. I've used the
process for about a year now.
In fact, one step further, half of my department only needs read only access.
So therefore I go one step further and FTP the data folder to a second MySQL
server and the staff reads off that one. If they meltdown the database a new
one will be FTP'd next hour. Granted it was an adhoc data back up and access
plan, but it has worked out.
Hope that gives you ideas.
******************************
Sincerely,
Gary R. Ciszewski
"Orbis non sufficit"
******************************
----- Original Message ----
From: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]>
To: [email protected]
Sent: Tuesday, August 21, 2007 10:45:20 AM
Subject: mysql Digest, Vol 8, Issue 3
Send mysql mailing list submissions to
[email protected]
To subscribe or unsubscribe via the World Wide Web, visit
http://lists.nyphp.org/mailman/listinfo/mysql
or, via email, send a message with subject or body 'help' to
[EMAIL PROTECTED]
You can reach the person managing the list at
[EMAIL PROTECTED]
When replying, please edit your Subject line so it is more specific
than "Re: Contents of mysql digest..."
Today's Topics:
1. InnoDB backup suggestions (Cliff Hirsch)
2. Re: InnoDB backup suggestions (Rob Marscher)
3. Re: InnoDB backup suggestions (Cliff Hirsch)
4. Re: InnoDB backup suggestions (Jon Baer)
5. Re: InnoDB backup suggestions (Jon Baer)
6. Re: InnoDB backup suggestions (Cliff Hirsch)
----------------------------------------------------------------------
Message: 1
Date: Tue, 21 Aug 2007 09:38:36 -0400
From: Cliff Hirsch <[EMAIL PROTECTED]>
Subject: [mysql] InnoDB backup suggestions
To: <[email protected]>
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset="iso-8859-1"
How do you back up your MySQL InnoDB database?
My DB is on a dedicated server in a hosted environment. From what I can
gather, I have two choices:
1. Stop the DB and copy the files at say 2am to reduce user disruption
2. Use the InnoDB hot backup program (with InnoBackup to capture the MyISAM
files in sync)
A third choice, which would be my preference if I didn¹t have budget
constraints, would be to use a replication server for backup and then stop
that to do files copies. As I understand it, it would catch up with the log
files once the DB starts up again.
Am I missing anything? Any other choices?
Cliff Hirsch
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.nyphp.org/pipermail/mysql/attachments/20070821/1c1ddfae/attachment-0001.htm
------------------------------
Message: 2
Date: Tue, 21 Aug 2007 09:57:15 -0400
From: Rob Marscher <[EMAIL PROTECTED]>
Subject: Re: [mysql] InnoDB backup suggestions
To: MySQL SIG <[email protected]>
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset="windows-1252"
On Aug 21, 2007, at 9:38 AM, Cliff Hirsch wrote:
> How do you back up your MySQL InnoDB database? From what I can
> gather, I have two choices:
> Stop the DB and copy the files at say 2am to reduce user disruption
> Use the InnoDB hot backup program (with InnoBackup to capture the
> MyISAM files in sync)
> A third choice, which would be my preference if I didn’t have
> budget constraints, would be to use a replication server for backup
> and then stop that to do files copies.
>
> Am I missing anything? Any other choices?
You can use mysqldump piped to gzip or bzip2 to create a backup.
It's a bit more intensive a process than copying files, but doesn't
require stopping the server or commercial product.
-Rob
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.nyphp.org/pipermail/mysql/attachments/20070821/3407da48/attachment-0001.htm
------------------------------
Message: 3
Date: Tue, 21 Aug 2007 10:33:06 -0400
From: Cliff Hirsch <[EMAIL PROTECTED]>
Subject: Re: [mysql] InnoDB backup suggestions
To: NYPHP -- MySQL <[email protected]>
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset="iso-8859-1"
> On Aug 21, 2007, at 9:38 AM, Cliff Hirsch wrote:
>> How do you back up your MySQL InnoDB database? From what I can gather, I
>> have two choices:
>>
>> 1. Stop the DB and copy the files at say 2am to reduce user disruption
>> 2. Use the InnoDB hot backup program (with InnoBackup to capture the MyISAM
>> files in sync)
>> 3.
>> A third choice, which would be my preference if I didn¹t have budget
>> constraints, would be to use a replication server for backup and then stop
>> that to do files copies.
>>
>> Am I missing anything? Any other choices?
>
> You can use mysqldump piped to gzip or bzip2 to create a backup. It's a bit
> more intensive a process than copying files, but doesn't require stopping the
> server or commercial product.
>
But is mysqldump viable for large tables with millions of records?
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.nyphp.org/pipermail/mysql/attachments/20070821/d3441743/attachment-0001.htm
------------------------------
Message: 4
Date: Tue, 21 Aug 2007 10:37:22 -0400
From: Jon Baer <[EMAIL PROTECTED]>
Subject: Re: [mysql] InnoDB backup suggestions
To: MySQL SIG <[email protected]>
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset="windows-1252"
Id recommend Chapter 17 of Pro MySQL (Jay Pipe's book) for great
backup/restoration tips on the subject. I don't think you are
missing anything but he points out you could just copy raw files like
you suggest (although when you say "replication" I think you may need
binary logging enabled which may/may not be running in your
environment). The administrator tool is also a pretty good option.
- Jon
On Aug 21, 2007, at 9:38 AM, Cliff Hirsch wrote:
> How do you back up your MySQL InnoDB database?
>
> My DB is on a dedicated server in a hosted environment. From what I
> can gather, I have two choices:
> Stop the DB and copy the files at say 2am to reduce user disruption
> Use the InnoDB hot backup program (with InnoBackup to capture the
> MyISAM files in sync)
>
> A third choice, which would be my preference if I didn’t have
> budget constraints, would be to use a replication server for backup
> and then stop that to do files copies. As I understand it, it would
> catch up with the log files once the DB starts up again.
>
> Am I missing anything? Any other choices?
>
> Cliff Hirsch
> _______________________________________________
> New York PHP Community MySQL SIG
> http://lists.nyphp.org/mailman/listinfo/mysql
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.nyphp.org/pipermail/mysql/attachments/20070821/63c3865a/attachment-0001.htm
------------------------------
Message: 5
Date: Tue, 21 Aug 2007 10:41:30 -0400
From: Jon Baer <[EMAIL PROTECTED]>
Subject: Re: [mysql] InnoDB backup suggestions
To: MySQL SIG <[email protected]>
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset="windows-1252"
I think that is what the commercial product is really for ...
http://www.innodb.com/hot-backup
- Jon
On Aug 21, 2007, at 10:33 AM, Cliff Hirsch wrote:
>> On Aug 21, 2007, at 9:38 AM, Cliff Hirsch wrote:
>>> How do you back up your MySQL InnoDB database? From what I can
>>> gather, I have two choices:
>>>
>>> Stop the DB and copy the files at say 2am to reduce user disruption
>>> Use the InnoDB hot backup program (with InnoBackup to capture the
>>> MyISAM files in sync)
>>>
>>> A third choice, which would be my preference if I didn’t have
>>> budget constraints, would be to use a replication server for
>>> backup and then stop that to do files copies.
>>>
>>> Am I missing anything? Any other choices?
>>
>> You can use mysqldump piped to gzip or bzip2 to create a backup.
>> It's a bit more intensive a process than copying files, but
>> doesn't require stopping the server or commercial product.
>>
> But is mysqldump viable for large tables with millions of records?
> _______________________________________________
> New York PHP Community MySQL SIG
> http://lists.nyphp.org/mailman/listinfo/mysql
>
> NYPHPCon 2006 Presentations Online
> http://www.nyphpcon.com
>
> Show Your Participation in New York PHP
> http://www.nyphp.org/show_participation.php
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.nyphp.org/pipermail/mysql/attachments/20070821/e81a8431/attachment-0001.htm
------------------------------
Message: 6
Date: Tue, 21 Aug 2007 10:44:30 -0400
From: Cliff Hirsch <[EMAIL PROTECTED]>
Subject: Re: [mysql] InnoDB backup suggestions
To: NYPHP -- MySQL <[email protected]>
Message-ID: <[EMAIL PROTECTED]>
Content-Type: text/plain; charset="iso-8859-1"
On 8/21/07 10:37 AM, "Jon Baer" <[EMAIL PROTECTED]> wrote:
> Id recommend Chapter 17 of Pro MySQL (Jay Pipe's book) for great
> backup/restoration tips on the subject. I don't think you are missing
> anything but he points out you could just copy raw files like you suggest
> (although when you say "replication" I think you may need binary logging
> enabled which may/may not be running in your environment). The administrator
> tool is also a pretty good option.
>
> - Jon
>
Its in front of me and open to the backup section as I write this. Excellent
book ‹ I highly recommend it for everyone.
Cliff
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.nyphp.org/pipermail/mysql/attachments/20070821/b2e54d77/attachment.htm
------------------------------
_______________________________________________
mysql mailing list
[email protected]
http://lists.nyphp.org/mailman/listinfo/mysql
End of mysql Digest, Vol 8, Issue 3
***********************************
____________________________________________________________________________________
Sick sense of humor? Visit Yahoo! TV's
Comedy with an Edge to see what's on, when.
http://tv.yahoo.com/collections/222_______________________________________________
New York PHP Community MySQL SIG
http://lists.nyphp.org/mailman/listinfo/mysql
NYPHPCon 2006 Presentations Online
http://www.nyphpcon.com
Show Your Participation in New York PHP
http://www.nyphp.org/show_participation.php
