[Micronet] MySQL backups

classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

[Micronet] MySQL backups

Bill Gross
Hello
Does anyone have a recommendation for a product to back up MySQL databases.
We are looking at Zmanda which integrates in with Tivoli, since Tivoli
does not have something native in the package for this task.  There are
a handful of other options out there.  I would appreciate any views or
experiences with this or other products.
Thanks
Bill Gross

 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.
Reply | Threaded
Open this post in threaded view
|

Re: [Micronet] MySQL backups

Bill Clark
I'm curious as to what benefit any such product would provide, over and
above what you'd get from a simple periodic mysqldump with binlogging
turned on, and then using normal non-mysql-specific filesystem backup?

-Bill Clark

> Hello
> Does anyone have a recommendation for a product to back up MySQL
> databases.
> We are looking at Zmanda which integrates in with Tivoli, since Tivoli
> does not have something native in the package for this task.  There are
> a handful of other options out there.  I would appreciate any views or
> experiences with this or other products.
> Thanks
> Bill Gross
>
>
> -------------------------------------------------------------------------
> The following was automatically added to this message by the list server:
>
> To learn more about Micronet, including how to subscribe to or unsubscribe
> from its mailing list and how to find out about upcoming meetings, please
> visit the Micronet Web site:
>
> http://micronet.berkeley.edu
>
> Messages you send to this mailing list are public and world-viewable, and
> the list's archives can be browsed and searched on the Internet.  This
> means these messages can be viewed by (among others) your bosses,
> prospective employers, and people who have known you in the past.
>



 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.
Reply | Threaded
Open this post in threaded view
|

Re: [Micronet] MySQL backups

Jonathan Felder
I was going to type the same exact thing.  Why not just do a dump and
then back up the dumps when you back up the rest of the filesystem?

Another option could potentially be lvm snapshots.

http://www.google.com/search?hl=&q=lvm+snapshot

On 7/21/2010 5:36 PM, Bill Clark wrote:

> I'm curious as to what benefit any such product would provide, over and
> above what you'd get from a simple periodic mysqldump with binlogging
> turned on, and then using normal non-mysql-specific filesystem backup?
>
> -Bill Clark
>
>> Hello
>> Does anyone have a recommendation for a product to back up MySQL
>> databases.
>> We are looking at Zmanda which integrates in with Tivoli, since Tivoli
>> does not have something native in the package for this task.  There are
>> a handful of other options out there.  I would appreciate any views or
>> experiences with this or other products.
>> Thanks
>> Bill Gross
>>
>>
>> -------------------------------------------------------------------------
>> The following was automatically added to this message by the list server:
>>
>> To learn more about Micronet, including how to subscribe to or unsubscribe
>> from its mailing list and how to find out about upcoming meetings, please
>> visit the Micronet Web site:
>>
>> http://micronet.berkeley.edu
>>
>> Messages you send to this mailing list are public and world-viewable, and
>> the list's archives can be browsed and searched on the Internet.  This
>> means these messages can be viewed by (among others) your bosses,
>> prospective employers, and people who have known you in the past.
>>
>
>
>
>
> -------------------------------------------------------------------------
> The following was automatically added to this message by the list server:
>
> To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:
>
> http://micronet.berkeley.edu
>
> Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.


 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.
Reply | Threaded
Open this post in threaded view
|

Re: [Micronet] MySQL backups

Jonathan Loran
In reply to this post by Bill Clark

The bottom line is this: how big is your database?  It's true that if you can dump it to ASCII SQL statements with mysqldump in a reasonable time, and you have enough disk space to hold the output, that's the way to go.  However, for large databases, this simply doesn't work.  We have one database of many tens of gigabytes that takes 8 hours, and something like 400GB to dump.  Mysqldump is simply not an option, in this case.  The solution is to put your database on a file system that supports snapshots, such as Linux LVM, Solaris ZFS, or a Netapp.  Then for each backup, you use the following incantation:

mysql -> FLUSH TABLES WITH READ LOCK
snapshot your file system (vary's by FS type)
mysql -> UNLOCK TABLES

With most file systems, this sequence can be performed in seconds, so the lockout time for the DB updates is minimal.  After the snapshot is taken, you would backup from the snapshot.  This backup can be recovered and will be fully consistent so mysql can restart cleanly from the recovered data.  After the back is completed, you can free the snapshot (recommended with Linux, since LVM caps the available snapshot space and hence the number of snapshots you can take).  

This entire process can be scripted quite readily.  No need for an additional "product".

Jon

On Jul 21, 2010, at 5:36 PM, Bill Clark wrote:

I'm curious as to what benefit any such product would provide, over and
above what you'd get from a simple periodic mysqldump with binlogging
turned on, and then using normal non-mysql-specific filesystem backup?

-Bill Clark

Hello
Does anyone have a recommendation for a product to back up MySQL
databases.
We are looking at Zmanda which integrates in with Tivoli, since Tivoli
does not have something native in the package for this task.  There are
a handful of other options out there.  I would appreciate any views or
experiences with this or other products.
Thanks
Bill Gross


-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe
from its mailing list and how to find out about upcoming meetings, please
visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and
the list's archives can be browsed and searched on the Internet.  This
means these messages can be viewed by (among others) your bosses,
prospective employers, and people who have known you in the past.





-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.



-     _____/     _____/      /           - Jonathan Loran -           -
-    /          /           /                IT Manager               -
-  _____  /   _____  /     /     Space Sciences Laboratory, UC Berkeley
-        /          /     /      (510) 643-5146 [hidden email]
- ______/    ______/    ______/           AST:7731^29u18e3
                                 




 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.
Reply | Threaded
Open this post in threaded view
|

Re: [Micronet] MySQL backups

Bill Clark
I don't have any mysql databases that large, but for the largest ones I do manage, I use rsync to create incremental backups (of a sort) of the binary files. I create the initial copy if the live (unlocked) database, then will often just perform more synchronization rounds until the copy is fully in sync. Finally I'll stop the database, perform one final sync (which completes in seconds, given that the files are nearly identical if not completely identical at this point) and then restart the database.

Obviously that process only works if you can afford the few seconds of downtime, if your frequency of updates is low enough that the iterative syncs can converge in a reasonable time, and (possibly) if your database is under a critical size. I don't know how well the rsync algorithm scales for really huge files, and I've only used this process on ones under 1gb, so YMMV. 

Bill Clark

On Jul 21, 2010, at 10:24 PM, Jonathan Loran <[hidden email]> wrote:


The bottom line is this: how big is your database?  It's true that if you can dump it to ASCII SQL statements with mysqldump in a reasonable time, and you have enough disk space to hold the output, that's the way to go.  However, for large databases, this simply doesn't work.  We have one database of many tens of gigabytes that takes 8 hours, and something like 400GB to dump.  Mysqldump is simply not an option, in this case.  The solution is to put your database on a file system that supports snapshots, such as Linux LVM, Solaris ZFS, or a Netapp.  Then for each backup, you use the following incantation:

mysql -> FLUSH TABLES WITH READ LOCK
snapshot your file system (vary's by FS type)
mysql -> UNLOCK TABLES

With most file systems, this sequence can be performed in seconds, so the lockout time for the DB updates is minimal.  After the snapshot is taken, you would backup from the snapshot.  This backup can be recovered and will be fully consistent so mysql can restart cleanly from the recovered data.  After the back is completed, you can free the snapshot (recommended with Linux, since LVM caps the available snapshot space and hence the number of snapshots you can take).  

This entire process can be scripted quite readily.  No need for an additional "product".

Jon

On Jul 21, 2010, at 5:36 PM, Bill Clark wrote:

I'm curious as to what benefit any such product would provide, over and
above what you'd get from a simple periodic mysqldump with binlogging
turned on, and then using normal non-mysql-specific filesystem backup?

-Bill Clark

Hello
Does anyone have a recommendation for a product to back up MySQL
databases.
We are looking at Zmanda which integrates in with Tivoli, since Tivoli
does not have something native in the package for this task.  There are
a handful of other options out there.  I would appreciate any views or
experiences with this or other products.
Thanks
Bill Gross


-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe
from its mailing list and how to find out about upcoming meetings, please
visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and
the list's archives can be browsed and searched on the Internet.  This
means these messages can be viewed by (among others) your bosses,
prospective employers, and people who have known you in the past.





-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.



-     _____/     _____/      /           - Jonathan Loran -           -
-    /          /           /                IT Manager               -
-  _____  /   _____  /     /     Space Sciences Laboratory, UC Berkeley
-        /          /     /      (510) 643-5146 [hidden email]
- ______/    ______/    ______/           AST:7731^29u18e3
                                 




-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.

 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.
Reply | Threaded
Open this post in threaded view
|

Re: [Micronet] MySQL backups

paul rivers

What Bill describes is one of the two things usually done for large
MySQL database backups.  The other is utilizing replication, whereas one
of the replication slaves is either brought down for some kind of
physical backup or else mysqldump is run.  Either way, one hopes the
replication can then catch up again.

If you are using MyISAM only, you can also make a consistent physical
backup by flushing and locking tables, then copying the physical MyISAM
files to a backup location, then releasing the locks.  This can be a
surprisingly good option if the database either isn't too large, or else
the main activities are select or insert.

I would also agree with Bill Clark's other comment-- unless you're doing
something specific, I'm not sure it's worth some special third party
product to integrate with TSM.  You'd need to say more about the
specific issue you're trying to solve.  Otherwise I'd make sure you
exclude unnecessary data files from TSM backups (e.g. the live data
files) and dump to a specific directory which you are sure is included
in a TSM scheduled backup.  Or, you can setup your backup script to push
to TSM and not wait for the scheduled backup time.  This is going to
take more scripting scaffolding than it sounds to work, so that you
automatically handle times when TSM is unable to accept a push.

If the integrity of your data is critical to what you do, be sure you're
aware that either replication for backups or binlogging+mysqldump for
backups have some broken-by-design data integrity issues in the event of
an instance crash.  MySQL's binlogging is not the same as the
write-ahead log, no matter what storage engine you are using.  Hence by
design there are corner cases where what is or isn't, for a very short
period of time, what ought to be in the data files.  The tradeoff for
MySQL's ease of replication is that it is not entirely correct all the
time.  Further, if you are using replication for backups, you'll need to
understand all the caveats of replication, so that replication isn't
potentially introducing changes on the backup slave.

Whatever you do, if the data matters, periodically test your restores.

Regards,
Paul



On 07/22/2010 07:10 AM, Bill Clark wrote:

> I don't have any mysql databases that large, but for the largest ones I
> do manage, I use rsync to create incremental backups (of a sort) of the
> binary files. I create the initial copy if the live (unlocked) database,
> then will often just perform more synchronization rounds until the copy
> is fully in sync. Finally I'll stop the database, perform one final sync
> (which completes in seconds, given that the files are nearly identical
> if not completely identical at this point) and then restart the database.
>
> Obviously that process only works if you can afford the few seconds of
> downtime, if your frequency of updates is low enough that the iterative
> syncs can converge in a reasonable time, and (possibly) if your database
> is under a critical size. I don't know how well the rsync algorithm
> scales for really huge files, and I've only used this process on ones
> under 1gb, so YMMV.
>
> Bill Clark
>
> On Jul 21, 2010, at 10:24 PM, Jonathan Loran <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>>
>> The bottom line is this: how big is your database?  It's true that if
>> you can dump it to ASCII SQL statements with mysqldump in a reasonable
>> time, and you have enough disk space to hold the output, that's the
>> way to go.  However, for large databases, this simply doesn't work.
>>  We have one database of many tens of gigabytes that takes 8 hours,
>> and something like 400GB to dump.  Mysqldump is simply not an option,
>> in this case.  The solution is to put your database on a file system
>> that supports snapshots, such as Linux LVM, Solaris ZFS, or a Netapp.
>>  Then for each backup, you use the following incantation:
>>
>> mysql -> FLUSH TABLES WITH READ LOCK
>> snapshot your file system (vary's by FS type)
>> mysql -> UNLOCK TABLES
>>
>> With most file systems, this sequence can be performed in seconds, so
>> the lockout time for the DB updates is minimal.  After the snapshot is
>> taken, you would backup from the snapshot.  This backup can be
>> recovered and will be fully consistent so mysql can restart cleanly
>> from the recovered data.  After the back is completed, you can free
>> the snapshot (recommended with Linux, since LVM caps the available
>> snapshot space and hence the number of snapshots you can take).  
>>
>> This entire process can be scripted quite readily.  No need for an
>> additional "product".
>>
>> Jon
>>
>> On Jul 21, 2010, at 5:36 PM, Bill Clark wrote:
>>
>>> I'm curious as to what benefit any such product would provide, over and
>>> above what you'd get from a simple periodic mysqldump with binlogging
>>> turned on, and then using normal non-mysql-specific filesystem backup?
>>>
>>> -Bill Clark
>>>
>>>> Hello
>>>> Does anyone have a recommendation for a product to back up MySQL
>>>> databases.
>>>> We are looking at Zmanda which integrates in with Tivoli, since Tivoli
>>>> does not have something native in the package for this task.  There are
>>>> a handful of other options out there.  I would appreciate any views or
>>>> experiences with this or other products.
>>>> Thanks
>>>> Bill Gross
>>>>
>>>>
>>>> -------------------------------------------------------------------------
>>>> The following was automatically added to this message by the list
>>>> server:
>>>>
>>>> To learn more about Micronet, including how to subscribe to or
>>>> unsubscribe
>>>> from its mailing list and how to find out about upcoming meetings,
>>>> please
>>>> visit the Micronet Web site:
>>>>
>>>> <http://micronet.berkeley.edu>http://micronet.berkeley.edu
>>>>
>>>> Messages you send to this mailing list are public and
>>>> world-viewable, and
>>>> the list's archives can be browsed and searched on the Internet.  This
>>>> means these messages can be viewed by (among others) your bosses,
>>>> prospective employers, and people who have known you in the past.
>>>>
>>>
>>>
>>>
>>>
>>> -------------------------------------------------------------------------
>>> The following was automatically added to this message by the list server:
>>>
>>> To learn more about Micronet, including how to subscribe to or
>>> unsubscribe from its mailing list and how to find out about upcoming
>>> meetings, please visit the Micronet Web site:
>>>
>>> <http://micronet.berkeley.edu>http://micronet.berkeley.edu
>>>
>>> Messages you send to this mailing list are public and world-viewable,
>>> and the list's archives can be browsed and searched on the Internet.
>>>  This means these messages can be viewed by (among others) your
>>> bosses, prospective employers, and people who have known you in the past.
>>
>>
>>
>> -     _____/     _____/      /           - Jonathan Loran -           -
>> -    /          /           /                IT Manager               -
>> -  _____  /   _____  /     /     Space Sciences Laboratory, UC Berkeley
>> -        /          /     /      (510) 643-5146
>> <mailto:[hidden email]>[hidden email]
>> <mailto:[hidden email]>
>> - ______/    ______/    ______/           AST:7731^29u18e3
>>                                  
>>
>>
>>
>>
>> -------------------------------------------------------------------------
>> The following was automatically added to this message by the list server:
>>
>> To learn more about Micronet, including how to subscribe to or
>> unsubscribe from its mailing list and how to find out about upcoming
>> meetings, please visit the Micronet Web site:
>>
>> http://micronet.berkeley.edu
>>
>> Messages you send to this mailing list are public and world-viewable,
>> and the list's archives can be browsed and searched on the Internet.
>>  This means these messages can be viewed by (among others) your
>> bosses, prospective employers, and people who have known you in the past.
>
>
>  
> -------------------------------------------------------------------------
> The following was automatically added to this message by the list server:
>
> To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:
>
> http://micronet.berkeley.edu
>
> Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.


 
-------------------------------------------------------------------------
The following was automatically added to this message by the list server:

To learn more about Micronet, including how to subscribe to or unsubscribe from its mailing list and how to find out about upcoming meetings, please visit the Micronet Web site:

http://micronet.berkeley.edu

Messages you send to this mailing list are public and world-viewable, and the list's archives can be browsed and searched on the Internet.  This means these messages can be viewed by (among others) your bosses, prospective employers, and people who have known you in the past.