mysql databases in VM backups

[Faris Raouf]

Is there any special handling of mysql databases required when using PBS to backup VMs via PVE?

Keeping in mind that I'm no mysql expert (and not a backup expert either!):

With Acronis backups, which need a client installed in the VM, a freeze script is launched that I think freezes mysql tables while /var/lib/mysql is backed up, then thaws them. At any rate it does a "flushes tables with read lock" at some point.

With PBS, which requires no client for VMs, obviously this doesn't happen.

I'm therefore wondering what happens when PBS (and for that matter vzbackup) backups up /var/lib/mysql. Is there potential for inconsistency in the backup?
We do regular mysqldumps in the VMs at the moment, but not daily.

 

[fabian]

just like for regular vzdump, it depends on the mode the backup is run in:

stop mode
This mode provides the highest consistency of the backup, at the cost of a short downtime in the VM operation. It works by executing an orderly shutdown of the VM, and then runs a background Qemu process to backup the VM data. After the backup is started, the VM goes to full operation mode if it was previously running. Consistency is guaranteed by using the live backup feature.

suspend mode
This mode is provided for compatibility reason, and suspends the VM before calling the snapshot mode. Since suspending the VM results in a longer downtime and does not necessarily improve the data consistency, the use of the snapshot mode is recommended instead.

snapshot mode
This mode provides the lowest operation downtime, at the cost of a small inconsistency risk. It works by performing a Proxmox VE live backup, in which data blocks are copied while the VM is running. If the guest agent is enabled (agent: 1) and running, it calls guest-fsfreeze-freeze and guest-fsfreeze-thaw to improve consistency.

depending on the OS, the guest agent freeze might trigger additional operations to improve consistency (e.g., hooking into VSS on Windows)

 

[LnxBil]

depending on the OS, the guest agent freeze might trigger additional operations to improve consistency (e.g., hooking into VSS on Windows)

Here is the official example of an hook on basis of a mysql database. Adapt as you like. Same can be done with any other database.

 

[Faris Raouf]

Thanks @LnxBil and @fabian

I had forgotten that the qemu-agent was of course installed in all VMs and assisted during backups. And I didn't realise that pbs backups of VMs would/could use a similar snapshot technology to vzbackup.

I exclusively use Centos 7 in all VMs (Centos 8 too in the not too distant future).

Is there any documentation on how to hook in the hook, as it were? This is all new to me. I understand the concept, but not the implementation, so I'm not sure what I should be searching for.

Is anybody out there using this specific hook script in production? I don't want to blunder about and end up causing more problems rather than solving them, nor do I want to reinvent the wheel

I'm afraid I'm paranoid about data loss, hence my excitement about pbs and my reluctance to walk off the beaten path.

 

[infra]

Is there any documentation on how to hook in the hook, as it were? This is all new to me. I understand the concept, but not the implementation, so I'm not sure what I should be searching for.

Had to look hard for a Centos7/qemu combination in my Debian/Ubuntu/FreeBSD landscape, but I found a customer that has it.
The directory is a bit different from other OSs, it is /etc/qemu-ga/fsfreeze-hook.d/ it executes any .sh found there on freeze/thaw.
The file https://github.com/qemu/qemu/blob/m...t-agent/fsfreeze-hook.d/mysql-flush.sh.sample is an example how that would work, carefully check the path's to commands though, they seem to vary between distributions and OSs

 

[Faris Raouf]

Thanks infra. That makes sense to me now and I have a great starting point to explore further.