when will mySQL/mariaDB hot backup integration get ported to B&R?

[mcz]

Dear veeam-PM's,

the veeam agents support hot backup of mySQL/mariaDB since version 5 - when will this functionality be ported to backup and replication? In version 12? Why is it currently only available for agents?

Thanks!

[HannesK]

Hello,
We are always working on supporting more workloads in more setups. For now, it's too early to talk about features of V12 or later.

We count your request as +1

Best regards,
Hannes

PS: while backing up MariaDB can work with VAL, it's not officially supported today.

[PTide]

Just keep in mind that MariaDB is not officially supported yet.

[AOK-BV]

We need hot backup support for MariaDB Enterprise Server as well (Windows and Linux).
Locking the database for the whole time is not an option (DB sizes > 15 TB).

Can this functionality integrated?
https://mariadb.com/de/resources/blog/u ... se-server/

[HannesK]

"can" is "yes", but there are no plans for foreseeable future. Pre- and post-scripts can do what the link you posted does: dumping the database into a folder.

What I think is more interesting, is shown at end of the video... involving storage snapshots (in this case VM snapshots): https://mariadb.com/kb/en/storage-snaps ... -commands/

If you put that into a script, then the size of the database is relatively irrelevant and classic dumps of the database are not needed.

[AOK-BV]

Both are no good options...
A database dump would take hours and lots of space.

And storage snapshots (when not directly integrated into the Backup software) will have other issues:
-Was the database properly shut down when the snap was done?
-Did the snap volume ran out of space?
-...

We already had corrupted backups because of the issues mentioned above and noone noticed because the backup software showed everything is OK.
And the database servers are running on bare metal, no vmware involved.

Another issue is that Veeam does not support storage integration with NetApp E(F)-Series, and FAS is too slow for our workloads.

We need a solution properly integrated into Veeam so we can rely on the backup state.

From another backup vendor focussed on mysql backups:
"... is based on InnoDB‘s crash-recovery functionality. It copies your
InnoDB data files, which results in data that is internally inconsistent; but then it
performs crash recovery on the files to make them a consistent, usable database
again"

Would be nice if Veeam would offer the same.

[mcz]

AOK-BV, another option is to shutdown the database prior to the backup. I'm using that option with the post-script and had a downtime of say 10 seconds per backup. Now you might argue that this cannot be done since it's a production database and so on. But you might do that on a (replicated) read-only database and then it's totally fine. Of course, you need additional resources and the restore-process might not be that simple, but at least it's an option

[PetrM]

By the way, this white paper might be useful as well. Speaking about MySQL, you may also have a look at the scripts on VeeamHub.

Thanks!

[PTide]

Hi,

I would like to chime in and discuss some of the points that were made:

1.

And storage snapshots (when not directly integrated into the Backup software) will have other issues:
-Was the database properly shut down when the snap was done?

I believe this one is irrelevant if you don't use non-transactional engines (or at least make sure that no changes are made to the tables that are server by such engines during before the backup).

InnoDB, for example, is supposed to survive a server-crash/hard-reboot/snapshot.

However, older versions of MariaDB/MySQL still use MyISAM for system tables. That engine requires some preparatory work before you take a snapshot. On the other hand, if you don't update system tables before/during snapshot it should work just fine too.

Regardless, this approach is rock solid - you flush the tables, read-lock it, take a snapshot, release the lock, copy data from the snapshot.

Therefore, I am very interested to learn more details about this accident (including the name of the software):

We already had corrupted backups because of the issues mentioned above and noone noticed because the backup software showed everything is OK.

2.

-Did the snap volume ran out of space?
-...

You mean "ran out of space while you were copying data from it", or "ran out of space after having kept roughly a dozen of snapshots for several weeks"?

Thanks!

[HannesK]

Hello,

And the database servers are running on bare metal, no vmware involved.

then it's the Veeam Agent for Linux snapshot instead of a VM snapshot (this thread is in the general Backup & Replication forum instead of the Veeam Agent for Linux forum, so I assumed that you are running a VM).

In general, every relational ACID database should survive a crash (power outage, recovery from a snapshot). Otherwise I would not call it a database

Was the database properly shut down when the snap was done?

The suggestion from your link and the ones my colleagues point out work without database shutdown. It's about "FLUSH TABLES WITH READ LOCK" or "BACKUP STAGE START [...]"

If the scripts have an exit code different than 0, a job will fail with Veeam. You can test recoverability for example with instant recovery to your virtual environment (assuming that you also have VMware or Hyper-V)

Best regards,
Hannes

[mcz]

However, older versions of MariaDB/MySQL still use MyISAM for system tables. That engine requires some preparatory work before you take a snapshot. On the other hand, if you don't update system tables before/during snapshot it should work just fine too.

Does that mean that mariaDB on current versions uses InnoDB or any other transactional engine?

[PTide]

@mcz,

Yes, it uses InnoDB by default since v10.2. However, I am not sure whether it's all-InnoDB, or it still uses Aria (a reportedly crash safe version of MyISAM) for system tables.

Thanks!

[aj_potc]

I would love if Veeam would support a database backup method similar to this:

https://www.percona.com/doc/percona-xtr ... intro.html

Some other backup products seem to do exactly this under the hood. They make incremental backups by keeping track of the transaction log, and then they apply these changes to the database files when restoration is needed. This results in extremely fast and extremely small database backups -- no need to store an entire database dump for each restore point, only the deltas.

While the pre- and post- scripts are nice, they only serve to help you freeze and unlock the database while you make a snapshot of it. Unless I'm mistaken, you're still left with a full dump file for every backup. If you're backing up your databases hourly or more frequently, you're going to need a lot of capacity to store those.

[PTide]

While the pre- and post- scripts are nice, they only serve to help you freeze and unlock the database while you make a snapshot of it. Unless I'm mistaken, you're still left with a full dump file for every backup.

I don't understand where would those dump files come from if one does not trigger a database dump procedure?

The idea behind that freeze/unlock routine is to bring a database to a consistent state before VAL creates a volume snapshot so that the database remains consistent inside the backup.

If all volumes where the database files (including REDO logs) are located are included in the snapshot, then it is possible to skip freeze/unlock stage and take the snapshot right away. This method will produce a crash-consistent backup which usually results in the database having to recover (with REDO logs) after it is restored (provided that a transactional engine is used).

...no need to store an entire database dump for each restore point, only the deltas.

Changed block tracking allows Veeam to back up only those blocks that have changed since the last backup session. That is, backups are incremental.

Thanks!

[aj_potc]

I don't understand where would those dump files come from if one does not trigger a database dump procedure?

Your first backup would copy the files on disk in a consistent way. Percona, for example, does not use a dump, but rather does a smart database-aware file copy that can capture the data consistently. Each subsequent backup could then be incremental, which is super fast. When you're ready to restore, then you apply the incrementals to the original backup to bring it up to date. (Or you could use the incrementals to occasionally generate "synthetic fulls").

Changed block tracking allows Veeam to back up only those blocks that have changed since the last backup session. That is, backups are incremental.

It would be great if Veeam could target only the relevant database files, and not rely on a whole-volume backup to achieve this. If I have anything else on the same physical volume as the database files, then the freeze/unfreeze steps will have to pause the database until all of those changes are copied (if I'm not mistaken).

The Percona software I linked has a way to target only the backup files. Is anything like this possible with Veeam, or are you forced to back up an entire physical volume to take advantage of changed block tracking?

[mcz]

aj_potc,

taling about VM's: The freeze-action takes place by running the pre-script, then the snapshot is triggered. After that the unfreeze is done by running the post-script. Then veeam does the backup from the snapshot. No matter if you have a million files, terabytes or whatever, the downtime is the time it takes to take the snapshot. In our case (we don't use super super fast hardware) everything was done in under 10 seconds.

Now in case of a restore you could only restore those database-files by doing a FLR or you could restore the whole disk or vm, depending on what you like or need. The backups itself are incremental (as mentioned earlier here), so no space is wasted. Probably it takes less space then with the transaction logs (because only certain points are stored, not the whole transaction logs). Regarding VAL: I'll let others answer that question, I'm not 100 % sure there.

Any other questions?

[HannesK]

Percona, for example, does not use a dump,

I repeat: both approaches suggested by the video and my colleagues don't use a dump. I'm talking about the "FLUSH TABLES WITH READ LOCK" or "BACKUP STAGE START [...]" approaches.

Confirm what Michael said: we are talking about seconds until the snapshot is created and the post-script releases your database.

Block-based backup is faster than file-based backup (vendor independent). That's why people do that since more than a decade. We can do file-based backup, but I would always go for image-based (block-based) backup as we do per default. Entire machine / volume and that's it. I would not want to configure paths and files on hundreds or thousands of machines.

Yes, volume based backup is required for change block tracking. Snapshots can be used with file-based backup, but I doubt that this would be a good idea for a database server

[PTide]

Snapshots can be used with file-based backup, but I doubt that this would be a good idea for a database server

To be fair, if one includes all database-related stuff (REDO logs, data files, etc) into the backup scope, file-level backup should work just fine for a database.
However, it will be slower than block-level, indeed.

And yes, VAL does not use dump : )

Thanks!