CODEWITHSUNDEEP
mysqldatabase-backups
Priyanshu
Priyanshu

Reputation: 881

MySQL server with 2 TB of Data.

What is the best strategy to backup the 2 TB MySQL data and How often should one schedule the data backup ? I am using replication as a backup strategy, but i know that's not the good practice.

Please note : I am new to MySQL servers and this question may sound very basic and unsuitable to some old users.But I am trying to learn. Thanks.

Upvotes: 1

Views: 1899

Answers (1)

Kevin Bott
Kevin Bott

Reputation: 733

Size matters most in the fact that all operations take longer. There's no getting around that. Otherwise a lot of backup strategy remains the same.

First off, Replication is not a backup. It's for availability and scalability. Replication (with a delayed slave apply) is at best a single snapshot. Once a bad update/delete/truncate is replicated, the data is gone.

Your "best strategy" depends on several factors:
- Recovery Time Objective (how fast you need to restore).
- Recovery Point Objective (to what point in time to restore).
- Many small databases? One 2 TB database?
- How much money do you have to spend on resources.
- Are you held to regulatory requirements for being able to restore data for 1,3,7, years, etc.

A physical backup using Persona Xtrabackup will be able to take a point in time snapshot of all databases on your server. (caveat being non-transactional tables using myisam engine)

A logical backup with mysqldump may be faster to backup, be smaller, and compress better, but on restore, it needs to build indexes, so may take longer.

So... In a perfect situation, take regular physical and logical backups. Take continuous backups of the binary logs (https://www.percona.com/blog/2012/01/18/backing-up-binary-log-files-with-mysqlbinlog/). As long as your slave is up to date, you can do your backups there, as to not impact your master. To determine your frequency of backups, restore a backup, and time how long it takes to apply 1 week of logs. Did you meet your "Recovery Time Objective"? No, more frequent backups are needed.

Also, hang out at https://dba.stackexchange.com to get some more insight into these operational challenges of owning a database :)

Upvotes: 3

Related Questions