MYSQL DBs and Transaction logs backup

[mogan2055847]

Hello,

We have a requirement to backup MySQL database, can Veeam backup for AWS do live backup of DBs and also the transaction logs without stopping the DB Service?

Thank you in advance.

[nielsengelen]

If they are running in RDS, we support this using native snapshots.

If they are running in EC2, you can use scripts within the policy to perform the necessary database tasks. This highly depends on your setup what works best. Usually a mysqldump is sufficient.

If you have some more details on the setup, I can probably point you in a better direction.

[mogan2055847]

Hi Nielsengelen,

Appreciate your quick commends. We are running EC2.

Does mysqldump need to stop\freeze the databases when its run? Can it handle transaction logs also? For example, transaction log backup every 4 hours.

Thank you in advance.

[nielsengelen]

MySQL works differently compared to MSSQL. By default, the transaction log (called Binary log) is not enabled for a database but it isn't actually fully required as it offers options to grab specific logs (see below). Enabling the binary log does enable point-in-time restores from the default product capabilities.

It also depends on how the database is set up as there are 2 types (MyISAM and InnoDB). InnoDB is the default type nowadays as it is the fastest and stores the database in files and allows for quick reading from the in-guest memory.

Mysqldump does not require the database to stop when using InnoDB. There are quite some parameters that can be customized to fit your needs (as seen in the user guide). An easy way to "dump all databases in a file" is: mysqldump [options] --all-databases > backup.sql.
Options are specific but if you use InnoDB, you want to use " --single-transaction" to have a consistent state of the database dump.

You can create a script for this (bash, python, perl) or benefit from the many out there on the internet already (we cover this also in the VBR best practice guide. Once you have a script, there are 2 options to make dumps.
1. Schedule it using cron every 4 hours, or
2. Within VB for AWS, configure a policy to run every 4 hours and call the script within the EC2 instance.

I would recommend using option 2 here as we will automatically create a snapshot (and potentially backup&replica if enabled) whereas, with option 1, you just make a dump within the EC2 instance.