On backup run a command before and after snapshot to lock mysql db inside VM

[alexc]

Hello,

may I please ask for a little advice?

I have a KVM VM (Centos 7 on ext4 fs, no LVM inside VM) with mysql server running inside. I'd like to create whole host backup (via snapshot method to minimize mysql server downtime) but what I afraid of, when I do snapshot of VM the fs consistency seems to be ok but the database binary files itself may not be consistently saved. That is, as I restore such a backup I can see mysql errors which will be the problem to combat later.

On the other hand, the PVE's backup script does the following:
1. Lock the VM for backup
2. Create snapshot of VM's volumes
3. Create archive but the VM itself can run since the backup uses snapshoted volumes.
4. Unlock the VM to be run as usual.

So I'd like to add something to the process: to do LOCK statement(s) on MySQL DBs inside the VM. That is, add FLUSH and LOCK before #2 and UNLOCK after #2. This way the MySQL will be backed up consistently (I suppose) and the downtime will be minimized.

So the question is, how can I add my commands to backup process? I can copy standard backup script and add changes needed but which script should I change then? I can even run it on my own cron, not on PVE-controlled one.

P.S. How's my idea, if it is alive, or I will get extra troubles on that?

 

[wolfgang]

Hi,
you can install qemu-guest-agent inside of your VM.
This will freeze the ext4 and hold the ram and fs persistent.
For this feature you must set in your VMID.conf the agent flag.
https://pve.proxmox.com/wiki/Manual:_vm.conf

 

[alexc]

Hi,
you can install qemu-guest-agent inside of your VM.
This will freeze the ext4 and hold the ram and fs persistent.
https://pve.proxmox.com/wiki/Manual:_vm.conf

That's the news, thank you! I've never heard about this feature.

But may I ask you again to prove my mind: this way the database setup will be completely consistent? I mean, if I freeze fs it will not necessary results in consistent database files on disk, and as I start the restored VM it may complains that database binary files are broken even that fs itself is logically consistent. I ask about as I face that under windows when MSSQL won't run after restore due to internal problems in DB as a result of it unawareness that the backup in progress.

 

[wolfgang]

First at all this feature works only with Linux.

Freezing the fs means the kernel is not able to write. Therefore the db never believe the data are written on the disk before the fs will unfreeze.
Consider if the db can't write the data, it will keep it in ram and when the rollback is happen the ram will be restored with this information and the fs state before the freeze.

 

[alexc]

First at all this feature works only with Linux.
Consider if the db can't write the data, it will keep it in ram and when the rollback is happen the ram will be restored with this information and the fs state before the freeze.

Oh, I see. Thank you for explaining me that, but this way I'd prefer to issue "FLUSH ..." and "LOCK ..." in the MySQL inside the VM before Agent will freeze the fs. Are there any smallest way to do that? Surely I don't like to duplicate original backup script.

 

[wolfgang]

We have no possibility where you can add a pre or post snapshot script.
you must write your own one.

 

[alexc]

We have no possibility where you can add a pre or post snapshot script.
you must write your own one.

Ok, I see. May I please ask which script exactly does the backup (should be the one which output I see in the browser log window as I start the backup) so I can use it as a draft? I understand this is not the thing you should support or even help with, I just hope this is not hard to suggest and this will solve the problem (as I expect, at least).

Thank you in advance, you're the greatest team (and answer really really quickly, I've never expected that)!

 

[wolfgang]

You can use
"qm snapshot"
see man qm

 

[alexc]

You can use
"qm snapshot"
see man qm

I guess I miss the point. What I can get is qm snapshot will snapshot the virt. disks, but MySQL server is partly in memory (so unless I did FLUSH and LOCK on MySQL, I' not sure it start correctly next time).

Freezing filesystem is good idea but I just don't understand how sql server will handle that it will not be able to flush its buffers or logs.

Sorry for such a dumbness, just missed something important.

 

[tomstephens89]

I guess I miss the point. What I can get is qm snapshot will snapshot the virt. disks, but MySQL server is partly in memory (so unless I did FLUSH and LOCK on MySQL, I' not sure it start correctly next time).

Freezing filesystem is good idea but I just don't understand how sql server will handle that it will not be able to flush its buffers or logs.

Sorry for such a dumbness, just missed something important.

I have a number of MS SQL and MySQL servers running on my proxmox cluster. Since I am using the proxmox backup feature, I also had to deal with consistent state DB backups.

In the end, since I have the storage space available, I just run a native backup or dump of the database inside the VM so I have a consistent state backup also stored in the VM, and then use the proxmox backup feature to backup the whole VM.

In MS SQL I use a Maintenance plan to do a full db backup to disk... And with mySQL I use mySQL Dump + cron.

 

[alexc]

In the end, since I have the storage space available, I just run a native backup or dump of the database inside the VM so I have a consistent state backup also stored in the VM, and then use the proxmox backup feature to backup the whole VM.

I'd wish I can afform myself to do that. But the truth is, my DB is quite big and will be dumped for long time. On the other hand, I hate to backup such huge space of data keeping in mind I can't trust sql binary data files itself so I'll need to recreate it on my restore (and it will take another time). Not to mention the load that VM will get from DB routine dumping.

Yes I can establish replication to some slave VM and have dump in it. But then I'll see the same problem on slave (not sure for MySQL state during snapshot backup). So far, most times I see MySQL up and running with no problem as I restore such a VM, but sometimes there were some problems.

By the way, as I set up agent flag in VM config, shutdown and started VM itself but as I start the backup I see no lines about agent (or anything connected) in the backup log so it is hard to say if it is useful.

 

[wolfgang]

Did you install the qga in your VM?
normally you see that the vm fs will freeze in the log.

Back to the script you must write your own script like this.

->connect to your vm with ssh or terminal redirection
-> flush and Lock your SQL DB by your command.
-> call on host qm snapshot
-> unlock the SQL DB

and this script you can run with cron

 

[alexc]

Did you install the qga in your VM?
normally you see that the vm fs will freeze in the log.

Yes, but no traces in log. Strange.

I see there is -script parameter on vzdump execution, and as I come to test it I see it run on:

- job-start
- backup-start
- pre-stop
- pre-restart
- backup-end
- log-end
- job-end

events. If I treat that right, and the vzdump run to create backup via snapshot I can hook my code (connect to mysql server and issue "FLUSH..." and "LOCK...") on backup-start (or maybe pre-restart?) event and then "UNLOCK" on backup-end.

I see no events explanation on wiki so am I right with this approach? Which event should I hook at with LOCK: backup-start or pre-restart?

This allows me to include my code in the standard backup execution chain.

Thank you for your explanations!

 

[alexc]

Did you install the qga in your VM?
normally you see that the vm fs will freeze in the log.

I took another VM (KVM, qcow2, virtio, CentOS 7) and did install qm agent, but I still can't see anything about agent of fs freeze in the log. Here is the dump log:

INFO: starting new backup job: vzdump 101 --remove 0 --mode snapshot --compress lzo --storage backup_daily --node proxmox
INFO: Starting Backup of VM 101 (qemu)
INFO: status = running
INFO: update VM 101: -lock backup
INFO: backup mode: snapshot
INFO: ionice priority: 7
INFO: snapshots found (not included into backup)
INFO: creating archive '/var/lib/vz/dump_daily/dump/vzdump-qemu-101-2015_07_07-15_18_45.vma.lzo'
INFO: started backup task 'b2d29652-6f03-4bab-9e5d-bdce8c6b9d1e'
INFO: status: 2% (699400192/34359738368), sparse 1% (416636928), duration 3, 233/94 MB/s
INFO: status: 5% (1811742720/34359738368), sparse 3% (1339621376), duration 6, 370/63 MB/s
INFO: status: 7% (2547384320/34359738368), sparse 5% (1815236608), duration 9, 245/86 MB/s
INFO: status: 15% (5239668736/34359738368), sparse 13% (4507521024), duration 12, 897/0 MB/s
INFO: status: 16% (5754454016/34359738368), sparse 14% (5022306304), duration 15, 171/0 MB/s
INFO: status: 19% (6607077376/34359738368), sparse 17% (5874929664), duration 18, 284/0 MB/s
INFO: status: 20% (7117406208/34359738368), sparse 18% (6385258496), duration 21, 170/0 MB/s
INFO: status: 25% (8754757632/34359738368), sparse 23% (8022609920), duration 24, 545/0 MB/s
INFO: status: 29% (9990701056/34359738368), sparse 26% (9082740736), duration 27, 411/58 MB/s
INFO: status: 30% (10424090624/34359738368), sparse 26% (9222422528), duration 30, 144/97 MB/s
INFO: status: 38% (13214941184/34359738368), sparse 34% (12013273088), duration 33, 930/0 MB/s
INFO: status: 46% (15948513280/34359738368), sparse 42% (14746845184), duration 36, 911/0 MB/s
INFO: status: 52% (18068144128/34359738368), sparse 48% (16794529792), duration 39, 706/23 MB/s
INFO: status: 53% (18365939712/34359738368), sparse 48% (16796479488), duration 42, 99/98 MB/s
INFO: status: 59% (20579549184/34359738368), sparse 55% (18960506880), duration 45, 737/16 MB/s
INFO: status: 65% (22548709376/34359738368), sparse 60% (20929667072), duration 48, 656/0 MB/s
INFO: status: 68% (23622451200/34359738368), sparse 64% (22003408896), duration 51, 357/0 MB/s
INFO: status: 76% (26200899584/34359738368), sparse 71% (24557760512), duration 54, 859/8 MB/s
INFO: status: 77% (26516389888/34359738368), sparse 71% (24560771072), duration 57, 105/104 MB/s
INFO: status: 83% (28607447040/34359738368), sparse 77% (26544975872), duration 60, 697/35 MB/s
INFO: status: 91% (31379816448/34359738368), sparse 85% (29317345280), duration 63, 924/0 MB/s
INFO: status: 99% (34290073600/34359738368), sparse 93% (32227602432), duration 66, 970/0 MB/s
INFO: status: 100% (34359738368/34359738368), sparse 93% (32297267200), duration 67, 69/0 MB/s
INFO: transferred 34359 MB in 67 seconds (512 MB/s)
INFO: archive file size: 1011MB
INFO: Finished Backup of VM 101 (00:01:09)
INFO: Backup job finished successfully
TASK OK

In 101.conf I added "agent=1" line.

But yes I am successful with creating snapshots with RAM content but I can't find a way to use it for backup purpose. I'd wish I can simply backup the whole snapshot sho I will be able to run it afterwards in case of disaster, but keep snapshots on the same PVE server and on the same storage is kind of misuse the idea of backup

Can I ask you for an advice how should I use snapshot to backup the VM, please?

 

[e100]

You can create hook scripts for qga. That would be where you would add code to issue your db commands.

You will need to read qga documentation to figure out how to do this, I only know it's possible and have never done it myself.

I believe qga writes logs in the guest, did you look there to see if it's working?

 

[alexc]

I think we talk wrong things. qga can work or not, but snapshot mode of backup itself works just like fs freeze. This way, I see no good in extra freezing (as far as I see no difference with or without qga)

What I do not understand and what I would like to do is have some hook in vzdump script so right before creating snapshot the MySQL will flush its logs and local tables (that is, the DB stay stable at that moment). I found no docs for --script parameter, so I don't know which event should I care for (backup-start or pre-restart).

This is land unknown and mostly unneeded for many of PVE users but this feature looks promising.

 

[wolfgang]

When you use vzdump and the snapshot option this is not the same like snapshot the vm.
so qga will not freeze the fs.

vzdump --script the script will be execute before the backup start.

 

[alexc]

When you use vzdump and the snapshot option this is not the same like snapshot the vm.
so qga will not freeze the fs.

vzdump --script the script will be execute before the backup start.

Got the idea. Will try to play with qga.

But again, as I try to run vzdump with demo script as --script I see there are a lot of 'events' in the log (I've listed them above). Any documentation which events fires before/after which backup stages? Playing blind may give me weird result afterwards.

 

[alexc]

Just did a small test: I created a script that ssh to VM and add line with current timestamp to the text file. I run vzbackup in snapshot mode with this script as --script parameter so on each invocation there should be a line added to the file in VM.

The idea was a simple one: as there was some lie seven lines with timestamps on each backup process (if to check its content from within VM that's under backup), I can restore VM into another VM number and check how many lines were there added at all (that is, how many steps/events were processes before virtual disk snapshot was made).

And guess the number of line? Zero = there was no lines added at all, which means that the virtual disk snapshot was created even before the first "--script" run.

A bit unexpected. Yes I can do mysql flush/lock, then vzdump, and unlock mysql on first run of "--script", by that time the VM is already snapshoted and can be modified further. But if this is the best approach?

 

[e100]

I think you did not understand my suggestion.

In your guest run qga
In your guest add a qga hook script so when fsfreeze-freeze is requested the qga hook script performs whatever db actions you want to freeze the db.
In your guest add qga hook script so when fsfreeze-thaw is requested the qga hook script performs whatever db actions you want to unfreeze the db.

Set agent=1 in VM.conf

Qga was created to do precisely what I am suggesting to ensure systems can be backed up cleanly like you desire.

If you search Google with these terms you will find plenty of examples: qemu guest agent hook script MySQL

Such as this qga hook script:
https://github.com/qemu/qemu/blob/m...t-agent/fsfreeze-hook.d/mysql-flush.sh.sample