Any "faster" methods of backing up databases in mariadb?

Question

I am coming from the Microsoft SQL Server world of databases, and have been there for about 7 years. My new role is solely based in various open source database engines.

As I have been prepping for some migrations to AWS RDS and/or newer versions of MariaDB, I have come up on an issue regarding a backup strategy for my company. ATM, and to spare the sensitive details, there isn't any solution in place. The databases that are the biggest concern are all running on older TokuDB storage engines, and are the ones in question that will need to be migrated. Backing up these databases has been done mainly through the following method instead of piping the whole database into one .sql file

mysqldump -u root -p -t -T/$source $database $databasetable --fields-terminated-by='|' --lines-terminated-by='\n' --order-by-primary

I could simply just say to backup the whole db and have it spit out each table one at a time, but regardless, this takes several hours. Doing a plain sql dump, whether that is using gzip or not, takes 24+ hours, if not more. This is clearly not the best for having a standard backup strategy like you could find with Ola Hallengren's Maintenance Plan for Microsoft SQL Server.However, the only alternative I could think of is writing something in python or using a cronjob to execute the backups on a schedule that has yet to be determined.

The only solution I found was Percona's XtraBackup, but wanted to see if the community had other ideas.

More background:

These are database servers running as EC2 instances in AWS, on Ubuntu and are provisioned as m4.large or m4.xlarge.

There are several replica's of the primary, so the backup would ideally be done ON the primary, not the replica

Final mentions Yes-migrating to RDS is an option, which would eliminate this issue with automated backups. However, the migration of these older Tokudb servers is not going to be "that" easy, so it may be some time before we get there.

I would appreciate any input or suggestions. Thank you.

Answer 1

Percona XtraBackup is a hot backup tool for InnoDB tables. It won't do squat for TokuDB or any other storage engine.

Percona also makes a hot backup tool for TokuDB: https://docs.percona.com/percona-server/8.0/tokudb/toku_backup.html

Both tools have caveats if you have a mixed environment, with some tables in both storage engines. Read the documentation carefully and do lots of testing.

Another option for a fast backup is to use an LVM snapshot. I recommend checking out mylvmbackup, which is just a handy script that wraps the multiple steps needed to make an LVM snapshot.

Regardless of storage engine, another piece of general advice applies: drop any data you don't need to store in this database.

In many databases I have analyzed, a lot of data is ripe for archiving, and then removing from the database. If you make your live database instance smaller, then any backup solution will be easier to manage. You don't have to destroy the data permanently, but move it to some cheaper storage.