mysql consistent backup

[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?

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"

and copy freeze and thaw scripts on Windows server running Veeam Backup.


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.]

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]

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.

[Vitaliy S.]

Yep, this is what advised in the referenced blog post

[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

[cda]

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

[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]

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]

Hello,

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

Thanks,

Mouis Yafeh

[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]

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]

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.

[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]

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.

[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]

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]

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)?

[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]

Ps I wonder if it’s cbt corruption?
May be worth Resetting CBT and running a new replication

[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]

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?

[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!!