Backup MySQL Databases of a VM in VMware

[sandroalves]

Hi,

i have small databases on a mysql server.

I see that the vast majority indicate using a script with mysqldump and generating files on the local disk, but this is insecure because it exposes username and password in files (.bat).

I remember in backup tools we added files during backup in VMware Tools startup script to freeze the database or put it in shadow mode during snapshot.

I believe this would be the best option.

I honestly don't even know how to restore the database using this option, but I don't see another safer option.

Thanks.

[Mildur]

Hi Sandro

If it‘s a Linux VM, you can use Veeam Agent for Linux to have a application consistent backup of MySQL.
https://helpcenter.veeam.com/docs/agent ... tml?ver=50

For VM backup Jobs, you can use pre-freeze and post-thaw scripts:
https://helpcenter.veeam.com/docs/backu ... ml?ver=110

Our best practice guides provides this information and also example scripts on how to backup a MySQL database:
https://bp.veeam.com/vbr/4_Operations/O ... mysql.html

With such scripts or our veeam agent, database restore is restoring the folder or files of the database back to production.
If you only need a table or other items from the database, you can power on the vm in a instant recovery session, export the information and import it to the production database.

Thanks
Fabian

[sandroalves]

Hi, @Mildur,

Sorry not to tell you, but my VM is Windows with MySQL 8.

The documents mentioned are for Linux, I had already found these.

Turning on the VM and exporting the backup I found an interesting solution I hadn't thought of.

But I still need the script to put the bank in a consistent backup state.

If I need to use the agent, I can use it no problem if that's the solution.

Where can I find these files (pre-freeze.bat and post-thaw.bat)?

Tks.

[Mildur]

Hi Sandro

No worries. You wrote .bat in your topic. I should have seen that.

Pre-freeze and post-thaw scripts will also work for windows machines. If you can allow downtimes of the MySQL services, you could just stop the service before the freeze, and in the post-thaw script you can start the service again.

Or you can use hashed passwords instead of clear text passwords.
I found an example here:
https://github.com/hkotka/MySQL_backup_SecureString

Where can I find these files (pre-freeze.bat and post-thaw.bat)?

They are not provided by veeam for windows systems. But probably there are some examples in the forum, which you can find by forum search.

Thanks
Fabian

[Origin 2000]

We use the freeze, thaw and fail scripts a lot a looooooonnnng time ago. VMware have changed the logic or better added 2 location which works differently.
1. Dedicated file per action
2. Automaticly execute every single file within a directory named "backupScripts.d". So these Scripts need to work with a argument!

Example of our vcb.bat located in "backupScripts.d"

Code: Select all

@echo off

if "%1%" == ""		 goto noParam
if "%1%" == "freeze"     goto doFreeze
if "%1%" == "thaw"       goto dothaw
if "%1%" == "freezeFail" goto dofreezefail
goto wrongParam


:doFreeze
:: place your logic here 
goto Ende

:dothaw
:: place your logic here 
goto Ende

:dofreezefail
:: place your logic here 
goto Ende

:noParam
echo "Usage: %0 <freeze|thaw|freezeFail>"
echo At least one Parameter is needed!
goto Ende

:wrongParam
echo "Usage: %0 <freeze|thaw|freezeFail>"
echo "%1" is not a valid parameter!
goto Ende

:Ende

But in YOUR case i would do in in another way. Setup a scheduled tasks which dumps the databases you need on a regular bases and let Veeam pickup these files during the normal VM image based backup.

[sandroalves]

Yes, doing the dump is the best option even to return, where I simply use (.sql) to return to the previous state.

I don't need to worry about historical time window. They are very small sites, but there are many.

But I will need to use a script with username and password with access to the bank to perform this task.

I was even researching if there is a permission to do the backup instead of using user or root to do the dump on a schedule.

But I can turn the service off and on again, it's small sites but if the backup takes too long it's a problem.

[Mildur]

I provided a link to github with a powershell script to dump the database which uses hashed credentials instead of clear text.

[sandroalves]

I saw it, it's an option I'll study. Even so, I am very worried. I remember that at the time I used (.vbe) to create a script to reverse the key. Thank you anyway.

[AlexHeylin]

If it‘s a Linux VM, you can use Veeam Agent for Linux to have a application consistent backup of MySQL.
https://helpcenter.veeam.com/docs/agent ... tml?ver=50

While I'm not an expert on the inner workings of MyISAM or INNODB - isn't that still technically a crash-consistent backup, albeit one where the data in the tables isn't changing during backup?

If it's not a stupid question, why isn't this functionality in VAW or especially VBR? Is there an ER for this to be added?
We'd learned the hard way why it's required - and since them we have a major production system (MySQL on Windows - as required by the vendor) taken offline each night by pre-freeze scripts for VBR.

While offline backup is better than a backup that might be corrupt on restore - keeping it online would be highly beneficial. Assuming Veeam released the locks fast enough for application retries to handle it and pick up again without declaring a DB error.

Thanks
Alex

[HannesK]

Hello,

While I'm not an expert on the inner workings of MyISAM or INNODB - isn't that still technically a crash-consistent backup, albeit one where the data in the tables isn't changing during backup?

I guess, that depends on your definition of crash-consistent. Veeam defines "locking the tables" for backup as application consistent. Keep in mind

Veeam Agent locks base tables that are based on the MyISAM storage engine [...]. Tables based on the InnoDB storage engine do not require locking.

From "Hight Performance MySQL 4th Edition" book

Lock-free InnoDB backups with LVM snapshots

When you run MySQL 8+ with only InnoDB tables, using GTIDs and full ACID-compliant mode, making a backup is incredibly easy. While MySQL is running, simply take a snapshot, mount the snapshot, and then copy the files to your backup location. There’s no need to lock any files, capture any output, or do anything special. Restoring the files from one of these backups will perform InnoDB crash recovery, and the GTID settings will already know which transactions have been processed.

If it's not a stupid question, why isn't this functionality in VAW or especially VBR?

There are only very few requests for MySQL on Windows compared to other pending feature requests. InnoDB as default engine for most installations makes it relatively low priority, because nothing needs to be done.

Best regards,
Hanne

[AlexHeylin]

Thanks @HannesK for the response.

When you run MySQL 8+ with only InnoDB tables, using GTIDs and full ACID-compliant mode

I think it's important to note that that is a non-trivial set of prerequisites which many installs won't meet, and may not be able to due to application requirements (for example, app might require non-ACID mode).

Restoring the files from one of these backups will perform InnoDB crash recovery

So, yes - they're crash consistent. I think unless it locks and tells mysqld to flush and call sync on the filesytem too (so every bit is written out to disk) then it's not really a fully app-consistent backup, although it is one that's likely to be recoverable because crash recovery should be able to recover from the backup state. However without confirming the crash recovery settings / prerequisites there is a risk in that assumption.

IMO stopping the DB engine completely as a means of forcing a full flush of app state to disk is a safer and more reliable means of backing up. If people want to use the app-aware, they should ENSURE they have met the MySQL prerequisites and have tested their restores. No-one wants to go through the hell of a corrupted 50+GB MySQL DB in a core LOB application, and finding out that the backups aren't entirely good either. Ask me how I know...!

While dumping the tables / DB to SQL files will produce files which are (in theory) restorable - this approach REALLY does not scale! Try it with a DB of a couple of thousand tables, with many indexes and triggers, and 20++GB of data and you're looking at days of importing the SQL files even with the whole lot running on SSD. The order of the SQL statements in the files is often not the optimal order to restore (for example - you should import a 15GB table, THEN add the indexes and triggers - NOT the other way around, which is how it gets exported), but they can easily be too large to edit, and manually editing takes time too. This approach is just about usable IF it's properly planned and you've scripted the restore and tested the script and the restore time tests as acceptable.

Here end the lessons learned the hard way - learn from my pain

[adam900331]

Hy!

I create a backup job to backup Linux VM with mysql, I select the "Enable VMware Tools quiescence" on the job advanced configuration. Also enable the application aware processing on the Guest Processing window and define a pre-freeze and post-thaw script. Is this a good solution to backup the VM in consistent state? What permission do I need to the credential what I use to this VM? Is it enough the default root permission user? Or do I need further permission to the mysql database?

Thanks.

[Mildur]

Hi Adam

Moved your post to this topic. Please see the comments above.

What permission do I need to the credential what I use to this VM?

Root user or sudo permissions are required to run scripts in Linux VMs. Permissions within the database are not required to run a pre-freeze script. Whether additional permissions are required depends on the script you are using.

Thank you
Fabian

[adam900331]

Hy Fabian!

Something is bot clear for me. I read this blog:
https://www.veeam.com/wp-consistent-pro ... .html?wpty

I know there are three option to bring mysql database to consistent state before snapshot. For me the ,,Hot Backup - Database Dump,, is suitable. The blog say: ,,These commands must be executed by user with RELOAD privileges on the database.,,

So when I configure the backup job, I have to add a root permission user in Guest Processing windows credebtials button. Can I specify another user credential which has mysql RELOAD permission on databases? Where can I specify this account? In /etc/my.cnf file on the VM?

So in this case I need two account, one for root permission on guest os and one for RELOAD permission on mysql databases?

Can you confirm that I understand correctly?

Thanks.

[HannesK]

Hello,
well, that whitepaper is 6 years old... but as you mention, that you use root: root has all privileges

https://dba.stackexchange.com/questions ... -databases
https://dev.mysql.com/doc/mysql-securit ... leges.html

Best regards,
Hannes

[adam900331]

Hello Hannes.

Ok, If I use a root permission user, can I give this account to the pre-freeze script? If, yes, where to? use_credetials section?

Code: Select all

#!/bin/bash
# config:
# when running on debian we can use existing debian-sys-maint account using defaults file
# otherwise, specify username and password below using use_credentials
#use_credentials="-uroot -p"
defaults_file="/etc/my.cnf"
dump_file="/tmp/mysql_dump.sql"
database="--all-databases"
if [ -f $defaults_file ]
then
opts="--defaults-file=$defaults_file"
elif [ -n $use_credentials ]
then
opts="$opts $use_credentials"
else
echo "$0 : error, no mysql authentication method set" | logger
exit 1
fi

[Mildur]

Yes, "use_credentials" as explained in the code you provided.

# otherwise, specify username and password below using use_credentials
#use_credentials="-uroot -p"

But here the password of the root user of the mysql database is needed. Not OS root.

[adam900331]

Now its clear for me.

So in this case I need two root account:
- One for can access with root permission to guest OS, for example connect via SSH.
- Another account which has root permission for mysql databases. This account I can give in the Linux server conf file: /etc/my.cnf OR directly in pre-freeze script in cleartext mode.

Can you confirm it?

Thanks.

[Mildur]

Yes.

directly in pre-freeze script in cleartext mode.

Cleartext or use a option file. I found some interesting blogs which are talking about encrypting the option file.
You should find plenty of examples in the internet.

Thanks
Fabian