Welcome! » Log In » Create A New Profile

what are your procedures for MySQL backups?

Posted by enchanter 
what are your procedures for MySQL backups?
January 23, 2019 01:59PM
All-

I'm looking for input, advice, and accumulated wisdom on what other sites
with a lot of MySQL do for backups.

We've had MySQL databases for almost as long as we've had NetWorker, but
we're going through a transition where our MySQL databases are both
becoming larger and more important to our operation. At this point
we actually have MySQL (I think only 5.5), MariaDB (5.5 and 10.2),
and Percona 5.7, so most of the flavors of the MySQL rainbow.


If you don't have NMDA and MySQL Enterprise Backup
==================================================

If you're not using NMDA are you just using mysqldump and then backing
up the export with NetWorker? If so, any particular parameters you're
using with mysqldump? If you have multiple logical databases hosted on
one MySQL install, are you dumping everything to a combined file, or
splitting the dumps into separate files?

What about the live database file(s)? Are you using directives (which?
skip, null, or something else?) for them, or are they being treated as
regular file backups too?

What about the binary log? Are you doing something special for it?


If you do have NMDA and MEB
===========================

Do you like it? Are there any limitations you've run into that you've
found problematic?

Once you completed initial configuration of NMDA, has it worked reliably
for you?

How have restores been?


Thanks,

Tim
--
Tim Mooney Tim.Mooney@ndsu.edu
Enterprise Computing & Infrastructure 701-231-1076 (Voice)
Room 242-J6, Quentin Burdick Building 701-231-8541 (Fax)
North Dakota State University, Fargo, ND 58105-5164


--
This list is hosted as a public service at Temple University by Stan Horwitz
If you wish to sign off this list or adjust your subscription settings, please do so via http://listserv.temple.edu/archives/emc-dataprotection-l.html
If you have any questions regarding management of this list, please send email to owner-emc-dataprotection-l@listserv.temple.edu
This message was imported via the External PhorumMail Module
Re: what are your procedures for MySQL backups?
January 24, 2019 10:59AM
In regard to: Re: [EMC-DataProtection-L] what are your procedures for MySQL...:

> What we do may be worthless to you if you dont or cant host your MySQL
> DBs on zfs, but this works very well for us.

They're not on zfs, but they are on top of Linux LVM, which also supports
snapshots of volumes. However, I'm not aware of any LVM equivalent for
replicating to a different server, a la 'zfs send', so that part probably
wouldn't be something we could implement.

> We basically do the following on our MySQL/MariaDB servers:
>
> FLUSH TABLES WITH READ LOCK
> zfs snapshot <time-tagged-snap-name>
> UNLOCK TABLES.
>
> Basically, this ensures on-disk database consistency when the snapshot
> is taken. Once you have the snaps, you can do things with them to move
> the data around. You can stream them to backups, or replicate the pool
> to a secondary server (which is what we do before backing the data up).

This is a really interesting idea. I wasn't aware of the FLUSH TABLES
WITH READ LOCK, so although I had considered LVM snapshots earlier, I
wasn't sure how I could make it work to ensure database consistency.

Are you using pre- and post-backup scripts in the NetWorker client
definition for the client to trigger the flush + snapshot and then later
unlock, or are you scheduling the flush+lock through cron?

> You will need to build your own scripts, or find examples online, to
> manage the zfs snapshots etc. But its all pretty straight forward.
> Hope this helps.

It definitely does help! Thanks much for taking the time to respond.

Tim

>> On Jan 23, 2019, at 1:36 PM, Tim Mooney <Tim.Mooney@NDSU.EDU> wrote:
>>
>> All-
>>
>> I'm looking for input, advice, and accumulated wisdom on what other sites
>> with a lot of MySQL do for backups.
>>
>> We've had MySQL databases for almost as long as we've had NetWorker, but
>> we're going through a transition where our MySQL databases are both
>> becoming larger and more important to our operation. At this point
>> we actually have MySQL (I think only 5.5), MariaDB (5.5 and 10.2),
>> and Percona 5.7, so most of the flavors of the MySQL rainbow.
>>
>>
>> If you don't have NMDA and MySQL Enterprise Backup
>> ==================================================
>>
>> If you're not using NMDA are you just using mysqldump and then backing
>> up the export with NetWorker? If so, any particular parameters you're
>> using with mysqldump? If you have multiple logical databases hosted on
>> one MySQL install, are you dumping everything to a combined file, or
>> splitting the dumps into separate files?
>>
>> What about the live database file(s)? Are you using directives (which?
>> skip, null, or something else?) for them, or are they being treated as
>> regular file backups too?
>>
>> What about the binary log? Are you doing something special for it?
>>
>>
>> If you do have NMDA and MEB
>> ===========================
>>
>> Do you like it? Are there any limitations you've run into that you've
>> found problematic?
>>
>> Once you completed initial configuration of NMDA, has it worked reliably
>> for you?
>>
>> How have restores been?
>>
>>
>> Thanks,
>>
>> Tim
>> --
>> Tim Mooney Tim.Mooney@ndsu.edu
>> Enterprise Computing & Infrastructure 701-231-1076 (Voice)
>> Room 242-J6, Quentin Burdick Building 701-231-8541 (Fax)
>> North Dakota State University, Fargo, ND 58105-5164
>>
>>
>> --
>> This list is hosted as a public service at Temple University by Stan Horwitz
>> If you wish to sign off this list or adjust your subscription settings, please do so via http://listserv.temple.edu/archives/emc-dataprotection-l.html
>> If you have any questions regarding management of this list, please send email to owner-emc-dataprotection-l@listserv.temple.edu
>
>

--
Tim Mooney Tim.Mooney@ndsu.edu
Enterprise Computing & Infrastructure 701-231-1076 (Voice)
Room 242-J6, Quentin Burdick Building 701-231-8541 (Fax)
North Dakota State University, Fargo, ND 58105-5164


--
This list is hosted as a public service at Temple University by Stan Horwitz
If you wish to sign off this list or adjust your subscription settings, please do so via http://listserv.temple.edu/archives/emc-dataprotection-l.html
If you have any questions regarding management of this list, please send email to owner-emc-dataprotection-l@listserv.temple.edu
This message was imported via the External PhorumMail Module
Tossavainen, Atro (Consultant)
Re: what are your procedures for MySQL backups?
January 25, 2019 01:59AM
We are doing something similar with Red Hat Satellite, which is based on PostgreSQL and therefore does not come with a native database backup capability that would work with NetWorker.


Basically, stop services, create a LVM snapshot, restart services. Mount snapshot somewhere, back up to NetWorker, release snapshot. (The native backup capability in the Satellite product stops the database and makes explicit copies through "gzip", which takes much longer.)


It used to work just fine with savepnpc and pre- and post- scripts in NetWorker 8. I have yet to implement it properly after the v9 transition.


--
Atro Tossavainen
System Administrator (Consultant)
Oy IGT Finland Ab, a fully owned subsidiary of IGT plc (Business ID FI08945519)https://tietopalvelu.ytj.fi/yritystiedot.aspx?yavain=732317&tarkiste=0086DB0700906F9BA97F0CB46E633AADEC57A6B8
tel. +358-44-5000-600 (cell)
________________________________
From: EMC Data Protection Q & A <EMC-DATAPROTECTION-L@LISTSERV.TEMPLE.EDU> on behalf of Tim Mooney <Tim.Mooney@NDSU.EDU>
Sent: Thursday, January 24, 2019 8:32:03 PM
To: EMC-DATAPROTECTION-L@LISTSERV.TEMPLE.EDU
Subject: Re: [EMC-DataProtection-L] what are your procedures for MySQL backups?

[THIS MESSAGE ORIGINATED FROM A NON-IGT EMAIL ADDRESS]



In regard to: Re: [EMC-DataProtection-L] what are your procedures for MySQL....:

> What we do may be worthless to you if you don’t or can’t host your MySQL
> DB’s on zfs, but this works very well for us.

They're not on zfs, but they are on top of Linux LVM, which also supports
snapshots of volumes. However, I'm not aware of any LVM equivalent for
replicating to a different server, a la 'zfs send', so that part probably
wouldn't be something we could implement.

> We basically do the following on our MySQL/MariaDB servers:
>
> FLUSH TABLES WITH READ LOCK
> zfs snapshot <time-tagged-snap-name>
> UNLOCK TABLES.
>
> Basically, this ensures on-disk database consistency when the snapshot
> is taken. Once you have the snaps, you can do things with them to move
> the data around. You can stream them to backups, or replicate the pool
> to a secondary server (which is what we do before backing the data up).

This is a really interesting idea. I wasn't aware of the FLUSH TABLES
WITH READ LOCK, so although I had considered LVM snapshots earlier, I
wasn't sure how I could make it work to ensure database consistency.

Are you using pre- and post-backup scripts in the NetWorker client
definition for the client to trigger the flush + snapshot and then later
unlock, or are you scheduling the flush+lock through cron?

> You will need to build your own scripts, or find examples online, to
> manage the zfs snapshots etc. But it’s all pretty straight forward.
> Hope this helps.

It definitely does help! Thanks much for taking the time to respond.

Tim

>> On Jan 23, 2019, at 1:36 PM, Tim Mooney <Tim.Mooney@NDSU.EDU> wrote:
>>
>> All-
>>
>> I'm looking for input, advice, and accumulated wisdom on what other sites
>> with a lot of MySQL do for backups.
>>
>> We've had MySQL databases for almost as long as we've had NetWorker, but
>> we're going through a transition where our MySQL databases are both
>> becoming larger and more important to our operation. At this point
>> we actually have MySQL (I think only 5.5), MariaDB (5.5 and 10.2),
>> and Percona 5.7, so most of the flavors of the MySQL rainbow.
>>
>>
>> If you don't have NMDA and MySQL Enterprise Backup
>> ==================================================
>>
>> If you're not using NMDA are you just using mysqldump and then backing
>> up the export with NetWorker? If so, any particular parameters you're
>> using with mysqldump? If you have multiple logical databases hosted on
>> one MySQL install, are you dumping everything to a combined file, or
>> splitting the dumps into separate files?
>>
>> What about the live database file(s)? Are you using directives (which?
>> skip, null, or something else?) for them, or are they being treated as
>> regular file backups too?
>>
>> What about the binary log? Are you doing something special for it?
>>
>>
>> If you do have NMDA and MEB
>> ===========================
>>
>> Do you like it? Are there any limitations you've run into that you've
>> found problematic?
>>
>> Once you completed initial configuration of NMDA, has it worked reliably
>> for you?
>>
>> How have restores been?
>>
>>
>> Thanks,
>>
>> Tim
>> --
>> Tim Mooney Tim.Mooney@ndsu.edu
>> Enterprise Computing & Infrastructure 701-231-1076 (Voice)
>> Room 242-J6, Quentin Burdick Building 701-231-8541 (Fax)
>> North Dakota State University, Fargo, ND 58105-5164
>>
>>
>> --
>> This list is hosted as a public service at Temple University by Stan Horwitz
>> If you wish to sign off this list or adjust your subscription settings, please do so via http://listserv.temple.edu/archives/emc-dataprotection-l.html
>> If you have any questions regarding management of this list, please send email to owner-emc-dataprotection-l@listserv.temple.edu
>
>

--
Tim Mooney Tim.Mooney@ndsu.edu
Enterprise Computing & Infrastructure 701-231-1076 (Voice)
Room 242-J6, Quentin Burdick Building 701-231-8541 (Fax)
North Dakota State University, Fargo, ND 58105-5164


--
This list is hosted as a public service at Temple University by Stan Horwitz
If you wish to sign off this list or adjust your subscription settings, please do so via http://listserv.temple.edu/archives/emc-dataprotection-l.html
If you have any questions regarding management of this list, please send email to owner-emc-dataprotection-l@listserv.temple.edu
CONFIDENTIALITY NOTICE: This message is the property of International Game Technology PLC and/or its subsidiaries and may contain proprietary, confidential or trade secret information. This message is intended solely for the use of the addressee. If you are not the intended recipient and have received this message in error, please delete this message from your system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited.


--
This list is hosted as a public service at Temple University by Stan Horwitz
If you wish to sign off this list or adjust your subscription settings, please do so via http://listserv.temple.edu/archives/emc-dataprotection-l.html
If you have any questions regarding management of this list, please send email to owner-emc-dataprotection-l@listserv.temple.edu
This message was imported via the External PhorumMail Module
Re: what are your procedures for MySQL backups?
January 25, 2019 09:59AM
In regard to: Re: [EMC-DataProtection-L] what are your procedures for MySQL...:

> We are doing something similar with Red Hat Satellite, which is based on
> PostgreSQL and therefore does not come with a native database backup
> capability that would work with NetWorker.

We have Satellite (5.8) too, and I just migrated it from external Oracle
to embedded PostgreSQL. For us, Satellite is important for our RHEL
systems, but IT-internal only. We can get by without it for a while if
needed, so I've been fine with 1/day 'online-backup' exports that we
scoop up later with NetWorker.

We have one other PostgreSQL system that is more customer-facing, but so
far we're just using 'pg_dump' (and its default "plain" format) and
exported SQL files there too.

Thanks much for your response!

Tim
--
Tim Mooney Tim.Mooney@ndsu.edu
Enterprise Computing & Infrastructure 701-231-1076 (Voice)
Room 242-J6, Quentin Burdick Building 701-231-8541 (Fax)
North Dakota State University, Fargo, ND 58105-5164


--
This list is hosted as a public service at Temple University by Stan Horwitz
If you wish to sign off this list or adjust your subscription settings, please do so via http://listserv.temple.edu/archives/emc-dataprotection-l.html
If you have any questions regarding management of this list, please send email to owner-emc-dataprotection-l@listserv.temple.edu
This message was imported via the External PhorumMail Module
Sorry, only registered users may post in this forum.

Click here to login