Sample Header Ad - 728x90

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

2 votes
1 answer
1076 views
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.
Asked by Randoneering (135 rep)
Dec 9, 2022, 10:34 PM
Last activity: Dec 9, 2022, 10:47 PM