Mysql corruption

[sukerman]

I've got three mysql vms, running on latest proxmox.

Server version: 10.6.5-MariaDB-1:10.6.5+maria~focal-log mariadb.org binary distribution.

db1 - master, runs fine.
db2 - slave, runs fine, no apparent corruption, passes mysqlcheck etc

I am running backup to a proxmox backup server, but when I restore db2 and create it as db3, update server id's etc. When I start it I get lots of this:

InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on...

I am running the backups live and didn't used to have this problem / need to stop mysql to do the backup. I have qemu-guest-tools installed.

The backup on db2 runs without complaint

NFO: starting new backup job: vzdump --all 1 --quiet 1 --mode snapshot --storage datastore-elvis --mailnotification always --mailto xxxxxxxx
INFO: Starting Backup of VM 100 (qemu)
INFO: Backup started at 2022-03-24 03:00:01
INFO: status = running
INFO: VM Name: db2
INFO: include disk 'scsi0' 'pool-16:vm-100-disk-0' 232G
INFO: include disk 'scsi1' 'pool-16:vm-100-disk-1' 1500G
INFO: backup mode: snapshot
INFO: ionice priority: 7
INFO: creating Proxmox Backup Server archive 'vm/100/2022-03-24T03:00:01Z'
INFO: issuing guest-agent 'fs-freeze' command
INFO: enabling encryption
INFO: issuing guest-agent 'fs-thaw' command
INFO: started backup task '63c417bd-242c-4dcc-b648-ab72e9f4d08e'
INFO: resuming VM again
INFO: scsi0: dirty-bitmap status: created new
INFO: scsi1: dirty-bitmap status: created new
INFO:   0% (1.6 GiB of 1.7 TiB) in 3s, read: 529.3 MiB/s, write: 130.7 MiB/s
INFO:   1% (18.1 GiB of 1.7 TiB) in 42s, read: 434.8 MiB/s, write: 84.3 MiB/s
.......
INFO:  98% (1.7 TiB of 1.7 TiB) in 45m 36s, read: 5.1 GiB/s, write: 0 B/s
INFO:  99% (1.7 TiB of 1.7 TiB) in 45m 40s, read: 5.1 GiB/s, write: 0 B/s
INFO: 100% (1.7 TiB of 1.7 TiB) in 45m 43s, read: 4.6 GiB/s, write: 0 B/s
INFO: backup is sparse: 910.40 GiB (52%) total zero data
INFO: backup was done incrementally, reused 1.47 TiB (86%)
INFO: transferred 1.69 TiB in 2743 seconds (646.6 MiB/s)
INFO: Finished Backup of VM 100 (00:45:44)

I then restore this as a new vm and get this on startup

InnoDB: Not applying INSERT_HEAP_DYNAMIC due to corruption on...

I did wonder if data is cached / not written at time of backup etc but I'm not really familiar with the freeze/thaw process and whats safe. I have set the VM disks to 'Default - no cache', but it doesn't help. Do I need to set the slave disks to 'sync' mode, or just stop it before backup or something else?

I have set the following mysql vars in the vm:

innodb_file_per_table           = 1
innodb_data_home_dir            = /mnt/mysql/
innodb_data_file_path           = ibdata1:10M:autoextend
innodb_flush_log_at_trx_commit  = 2
innodb_flush_neighbors          = 0

# disable AIO on ZFS

innodb_use_atomic_writes        = 0
innodb_flush_method             = fsync
innodb_doublewrite              = 0 # ZFS is transactional
innodb_use_native_aio           = 0
innodb_read_io_threads          = 10
innodb_write_io_threads         = 10

Any advice please?

 

[Klaus Steinberger]

if you need a consistent database state on backup you have to use a hook script.

Read this thread: https://forum.proxmox.com/threads/mysql-databases-in-vm-backups.72912/

 

[sukerman]

Thanks. I switched to backup using the 'shutdown' mode, instead of snapshot and this does give a consistent backup and its only down for 44s at 2 in the morning.

But if you do this you lose the benefit of the dirty bitmap to just save the changes. I'll try the hook approach.

Cheers!

INFO: Starting Backup of VM 100 (qemu)
INFO: Backup started at 2022-04-07 00:00:01
INFO: status = running
INFO: backup mode: stop
INFO: ionice priority: 7
INFO: VM Name: db1
INFO: include disk 'scsi0' 'pool-128:vm-100-disk-0' 232G
INFO: include disk 'scsi1' 'pool-16:vm-100-disk-1' 1500G
INFO: include disk 'scsi2' 'pool-128:vm-100-disk-1' 32G
INFO: stopping virtual guest
INFO: creating Proxmox Backup Server archive 'vm/100/2022-04-07T00:00:01Z'
INFO: starting kvm to execute backup task
INFO: enabling encryption
INFO: started backup task '2ce345cb-2201-4ffa-812c-2888e9bd4e73'
INFO: resuming VM again after 44 seconds
INFO: scsi0: dirty-bitmap status: created new
INFO: scsi1: dirty-bitmap status: created new
INFO: scsi2: dirty-bitmap status: created new

 

[VictorSTS]

In case of a MySQL slave use the hook script and just STOP SLAVE SQL_THREAD, so it will keep receiving updates from the master but it won't apply them on disk. This should give you consistency both at disk/innodb/mysql level and at a logical level: all tables and db's will be backed up exactly at the same moment in time.

Be sure to START SLAVE SQL_THREAD when backup is done and place some alerts to monitor replication status just in case something fails and your SLAVE SQL_THREAD is not started.