Comprehensive data protection for all workloads
Post Reply
cda
Novice
Posts: 8
Liked: never
Joined: Dec 17, 2012 12:17 pm
Full Name: cda
Contact:

mysql consistent backup

Post by cda »

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.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: mysql consistent backup

Post by Vitaliy S. »

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!
nielsengelen
Product Manager
Posts: 5619
Liked: 1177 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by nielsengelen »

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.
Personal blog: https://foonet.be
GitHub: https://github.com/nielsengelen
Vitaliy S.
VP, Product Management
Posts: 27055
Liked: 2710 times
Joined: Mar 30, 2009 9:13 am
Full Name: Vitaliy Safarov
Contact:

Re: mysql consistent backup

Post by Vitaliy S. »

Yep, this is what advised in the referenced blog post ;)
nielsengelen
Product Manager
Posts: 5619
Liked: 1177 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by nielsengelen »

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
Personal blog: https://foonet.be
GitHub: https://github.com/nielsengelen
cda
Novice
Posts: 8
Liked: never
Joined: Dec 17, 2012 12:17 pm
Full Name: cda
Contact:

Re: mysql consistent backup

Post by cda »

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 »

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
Product Manager
Posts: 6408
Liked: 724 times
Joined: May 19, 2015 1:46 pm
Contact:

Re: mysql consistent backup

Post by PTide »

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 »

Hello,

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

Thanks,

Mouis Yafeh
DGrinev
Veteran
Posts: 1943
Liked: 247 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 »

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 »

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
nielsengelen
Product Manager
Posts: 5619
Liked: 1177 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by nielsengelen »

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.
Personal blog: https://foonet.be
GitHub: https://github.com/nielsengelen
ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy »

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
nielsengelen
Product Manager
Posts: 5619
Liked: 1177 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by nielsengelen »

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.
Personal blog: https://foonet.be
GitHub: https://github.com/nielsengelen
ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy »

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!!
ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy »

Few scripts here
http://documentation.commvault.com/comm ... =32208.htm
One for MySQL I wonder if they also work with Veeam, probably the same as thevscripts are VMware not soecificvto the backup software.

Just need to work out if all my file inconsistency is down to not having scripts.
Seems odd that tools quiescing is not working
nielsengelen
Product Manager
Posts: 5619
Liked: 1177 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by nielsengelen »

I'm surprised you are facing file system issues forcing you to run fsck. Are you 100% sure there are no issues on the original system? Since everything was running fine you may need to trace things that could have happened during those 6 months.

- Did you upgrade the VBR server (Update 3 was released)?
- Did you perform Linux OS upgrades which may have created an issue?
- Did you do any huge changes towards the MariaDB itself (upgrade, alternations)?
Personal blog: https://foonet.be
GitHub: https://github.com/nielsengelen
ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy »

Hi the 6 months would be a guess with my assumption we have used replicas before to restore.

Nothing unusual, source vms seem totally fine, console showing no errors.

Usual kernel updates, cpanel updates, mariaDB is part of cpanel, we switched to Maria long before this.

Recently upgraded to vsphere 6.5 and hardware v11 as part of spectre patches ongoing

Vms on latest centos 7 kernel.

The corruption only seems to exist on the replicas. I ran fsck on one tonight and it took ages, loads of
lost found unattached inode
deleted/unused inode etc

All very odd! I’m convinced there is a big issue with queuing or file consistency in vmtools.

In fsck tonight I was seeing 1000s of files which were Wordpress, web files etc too. Some of the errors were about linking etc.
ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy »

Ps I wonder if it’s cbt corruption?
May be worth Resetting CBT and running a new replication
ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy »

Just to follow up on this for anyone reading.
Even with pre freeze scripts, etc, 2 of our cpanel linux vm replicas were corrupt, one would take an hour to run fsck before we could failover.
I have spent 6 weeks on and off on this, far far too many hours, no solution.
nielsengelen
Product Manager
Posts: 5619
Liked: 1177 times
Joined: Jul 15, 2013 11:09 am
Full Name: Niels Engelen
Contact:

Re: mysql consistent backup

Post by nielsengelen »

I do wonder if you are getting fsck as a result if there is nothing else wrong with the whole filesystem within the VM. Which distro are you running? And which filesystem is being used to host these files? Is this all related to 1 VM or does it happen on every VM?
Personal blog: https://foonet.be
GitHub: https://github.com/nielsengelen
ukguy
Novice
Posts: 9
Liked: never
Joined: Jul 26, 2016 2:30 pm
Contact:

Re: mysql consistent backup

Post by ukguy »

Hi they are cpanel vms.
centos 6/7 ext4 on esxi/Vsphere.
One is xfs

Had corruption in 4 vms in March, one didn’t even repair with fsck.

However the xfs and one ext4 ok now. Nothing changed only kernel updates.

The centos 6 and one centos 7 are corrupted.
Centos 6 can’t use quiesce due to the ffrezze bug
But the centos 7 one has quiescing and pre/post scripts, tried everything.

When fuck repairs you see loads of user files not just MySQL. I
Any help appreciated as it’s doing my head in!!
Post Reply

Who is online

Users browsing this forum: Bing [Bot], Semrush [Bot] and 181 guests