Availability for the Always-On Enterprise
cda
Novice
Posts: 8
Liked: never
Joined: Dec 17, 2012 12:17 pm
Full Name: cda
Contact:

mysql consistent backup

Post by cda » Aug 02, 2016 1:20 pm

Hi all,
I'm trying to backup a VM with mysql database, hosted on a GNU/linux guest.

I've just read https://www.veeam.com/wp-hot-backup-of- ... ux-vm.html whitepaper written by Ricky El-Qasem but I've still some doubts.
In the aforementioned whitepaper, Ricky suggest to install VMware tools and to create two files (pre-freeze-script and post-thaw-script) on guest VM, located in /usr/sbin.
In my case script contents are very simple (stock files): "service mysql stop" and "service mysql start"

So far so good, but here starts my doubts.

1) Why script filenames has no extension (.sh)?
2) How to tell Veeam to use this scripts lauching a new job? Maybe ticking following flag?
Image
3) Should I create a full path to "service" command eg. "/usr/sbin/service mysql start" or "service mysql start" is enought?

On this forum I found another post that suggest to enable "application-aware processing"
Image
and copy freeze and thaw scripts on Windows server running Veeam Backup.
Image

4) Can both methods ("VMware tools quiescence" and "application-aware processing") coexist?
5) Which is the best way to backup a GNU/linux mysql guest VM?

Sorry for long post.
Best regards
cda

Vitaliy S.
Veeam Software
Posts: 21527
Liked: 1281 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: mysql consistent backup

Post by Vitaliy S. » Aug 02, 2016 2:58 pm

Hello,

1. Script formats are suggested by VMware, but they should have an "sh" extension. See this blog post for more info > https://www.virtuallifestyle.nl/2013/03 ... -database/
2. Yes, you need to enable VMware Tools Quiescence if you want to use VMware Tools pre-freeze and post-thaw scripts.
3. I guess it should be a full path
4. No, for all Windows VMs we highly recommend to use AAIP instead of VMware Tools Quiescence.
5. Feel free to use VMware Tools Quiescence option with pre-freeze and post-thaw scripts as it is described in the blog post above.

Hope this helps!

vmniels
Veeam Software
Posts: 2001
Liked: 442 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by vmniels » Aug 02, 2016 3:58 pm

Do keep in mind that stopping the services also means it becomes unavailable. It's better to either look at mysqldump or putting the database in a read only mode.
VCP-DCV
Veeam Certified Architect (VMCA)
http://foonet.be

Vitaliy S.
Veeam Software
Posts: 21527
Liked: 1281 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: mysql consistent backup

Post by Vitaliy S. » Aug 02, 2016 4:19 pm

Yep, this is what advised in the referenced blog post ;)

vmniels
Veeam Software
Posts: 2001
Liked: 442 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by vmniels » Aug 02, 2016 4:28 pm

An additional note is that if you want to do point in time restores don't look at the methods used in the blog post but enable the binary log as described on http://dev.mysql.com/doc/refman/5.7/en/ ... overy.html
VCP-DCV
Veeam Certified Architect (VMCA)
http://foonet.be

cda
Novice
Posts: 8
Liked: never
Joined: Dec 17, 2012 12:17 pm
Full Name: cda
Contact:

Re: mysql consistent backup

Post by cda » Aug 03, 2016 5:17 am

Thanks all for your kind help.
I will try suggested hints.
Best regards
cda

Stevet170
Lurker
Posts: 1
Liked: never
Joined: Nov 15, 2016 11:05 am
Full Name: Stephen Threlfall
Contact:

[MERGED] Red HAT Database Pause script help

Post by Stevet170 » Nov 15, 2016 12:23 pm

Hi
I am trying to pause a Unidata database running on Red Hat. There are unidata commands /db/unidata/bin/dbpause and /db/unidata/bin/dbresume but I cannot get them to run. Now this may well be my lack of Linux knowledge rather than Veeam but has anyone any ideas how I could run dbpause and dbresume from a script?

Thanks

PTide
Veeam Software
Posts: 4307
Liked: 355 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: mysql consistent backup

Post by PTide » Nov 15, 2016 2:21 pm

Hi,

Please review the thread to see an explanation of how to use VMware Tools Quiescence if you want to use VMware Tools pre-freeze and post-thaw scripts. Also there is an option to leverage Veeam pre-freeze/post-thaw scripts.

Thanks

Mouis Yafeh
Lurker
Posts: 1
Liked: never
Joined: Nov 06, 2017 6:45 am
Full Name: Mouis Yafeh
Contact:

[MERGED]Application Consistent Snapshots/replication for Uni

Post by Mouis Yafeh » Nov 06, 2017 6:52 am

Hello,

Has anyone created a pre-freeze/post-thaw scripts for Unidata database? I greatly appreciate it,

Thanks,

Mouis Yafeh

DGrinev
Veeam Software
Posts: 1219
Liked: 128 times
Joined: Dec 01, 2016 3:49 pm
Full Name: Dmitry Grinev
Location: St.Petersburg
Contact:

Re: Application Consistent Snapshots/replication for Unidata

Post by DGrinev » Nov 07, 2017 10:16 am

Hi Mouis and welcome to the community!

Please review this thread as it contains useful considerations on how to achieve what you're after. Thanks!

ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy » Mar 16, 2018 7:24 am

On the virtually lifestyle blog linked above one of the last comments says it doesn’t work. They haven’t justified that though.
This subject is discussed so much regarding Linux, I’m surprised with so many Linux cpanel shared hosting vps servers there isn’t a definitive answer anywhere.

Some of my research has even lead to people questioning what VMware tools quiescing actually does, as even with that files are corrupt in replicas. (Albeit possible repairable with fsck)
It shows they are still crash consistent though.

I wish I could find a verified solution to all this, I’ve spent hours on it over the last couple of years at various points

vmniels
Veeam Software
Posts: 2001
Liked: 442 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by vmniels » Mar 16, 2018 9:19 am

Best way for MySQL is still using native tools (mysqldump) and leverage scripts as explained in this thread. Keep in mind that MySQL has the ability to recover from crashes (InnoDB) and as mentioned here as well it may be better to enable the binary log.
VCP-DCV
Veeam Certified Architect (VMCA)
http://foonet.be

ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy » Mar 16, 2018 4:56 pm

We replicate every 30minutes so mainly looking at something which has no service impact on shared Cpanel hosting.

I've read that the dumps can take a long time and alot of space which isn't ideal on a Cpanel vm.

You're right though, generally yes Linux/mysql can recover from a crashed state, hence I think the errors when starting up the replica could be repaired with fsck. I also think starting a new replica each couple of months may be ideal so any errors don't build up in the incremental data/snapshots.

We actually use MariaDB, which I assume can be treated the same as Mysql in this context, even though they folked off in a different direction. (I've seen Percona mentioned alot too on the other side of the Mysql fence as Percona have a backup function I read about)

Saying all this, we do also back up hourly to R1soft which has database restores, I've never been quite sure how they back up the databases but it sounds like they do take a dump and could be used to restore back to a replica IF fsck didn't recover it from a crashed state.

I think we're covered, just would be nice if the replicas could be 100% of the original in every way, so we will lok into the scripts etc and attempt to get a proper file consistent replica

vmniels
Veeam Software
Posts: 2001
Liked: 442 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by vmniels » Mar 16, 2018 4:58 pm

May I ask if your databases are running in a specific setup? Active/active, master/slave,... ? This may help us with getting you in the correct direction.
VCP-DCV
Veeam Certified Architect (VMCA)
http://foonet.be

ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy » Mar 19, 2018 10:35 pm

Apologies the forum did not notify me of a reply even though it’s on.

We run cpanel shared hosting, mariaDB, standard set up, 1 centos 6 vm and several centos 7.

I ran some tests last night, another with no sleep!!
Found out that even a brand new replica has file inconsistencies and starts up by jumping into shell (fsck or xfs repair)

I tried fsck and it seems to repair ok. Xfs is abit more of a pain but sussed it and that seemed to repair too, albeit I did notice a lot of files in the shell when repairing, not just database. Loads of inodes etc.

All repaired but as soon as another incrementalreplica ran the errors returned.

Very odd, we used these replicas last year and all was fine, I’m convinced it’s somethingvwhich has changed in the last 6 months.

Just need to suss our what the best solution is, it’s not ideal to have to repair the vms before we can failover if ever needed.
We only found this issue after a routine backup /replica check so I’d advise others to do the same regularly.

Saying all that, one vm has no errors, that is a one user vps with a magenta website on.
Any pointers offered are very appreciated!!

Post Reply

Who is online

Users browsing this forum: Exabot [Bot], Google [Bot], j.rev, patrick.shea, rickrbyrne and 32 guests