Backup solution for huge 150GB database

bmxakias · 11-28-2017, 09:51 PM

Hello

I am using to backup a (MariaDB) database of size 150GB:

Code:

mysqldump --opt --single-transaction

but server start to not respond at the time of backup for a few minutes so i am looking for any other solution to replace mysqldump.

Any other solutions for huge databases?

Thank you

jefro · 11-28-2017, 10:11 PM

Some ideas here. https://www.urbackup.org/backup_mysql.html

Here too. https://twindb.com/mydumper-rpm-for-centosrhel/

I assume you are correct in that the size is the problem.

TenTenths · 11-29-2017, 04:41 AM

Do your database dumps on one of your replication slaves (you are replicating such a large database for resilience?) where the lock lag won't matter to the slave.

bmxakias · 11-29-2017, 09:50 AM

Quote:

Do your database dumps on one of your replication slaves

I do not replicate it yet....

TB0ne · 11-29-2017, 09:58 AM

Quote:

Originally Posted by bmxakias

I do not replicate it yet....

Then perhaps it's a great time to start, since you want backups with no lag/performance degradation. Or you can pay a sack of money to someone and purchase an enterprise-class backup system (Netbackup, Tivoli Storage Manager, etc.), that will let you do hot backups.

bmxakias · 11-29-2017, 11:20 AM

I thought replication will add a delay as the data must go from the master to slave ....

TB0ne · 11-29-2017, 12:32 PM

Quote:

Originally Posted by bmxakias

I thought replication will add a delay as the data must go from the master to slave ....

It's best to not speculate but find out what's really going on. Replication is real-time, and it outlines all of this in the documentation. And even if there IS a little bit of a delay, you still have two options:

Live with a delay from whatever source (mysqldump, replication, whatever)
Pay a sack of money to buy a hot-backup tool

Pick one.

TenTenths · 11-30-2017, 05:21 AM

Quote:

Originally Posted by bmxakias

I thought replication will add a delay as the data must go from the master to slave ....

A delay to what?

MYSQL replication is fairly robust and your backup will still be a point of time backup of the slave at that time, any changes during the time the backup was "locked" will queue on the master and when the backup has completed these changes will replicate to the slave.

There is no degradation of performance on the master.

My personal preference is to pause the replication on the slave and do the backups (I have multiple databases) and then unpause the replication.

bmxakias · 11-30-2017, 04:34 PM

Quote:

My personal preference is to pause the replication on the slave and do the backups

How do you pause it and start it again?

TenTenths · 11-30-2017, 04:42 PM

Quote:

Originally Posted by bmxakias

How do you pause it and start it again?

Code:

mysqladmin stop-slave

My backup script is fairly documented.

https://centos.tips/mysql-backup-1-1/

chrism01 · 11-30-2017, 10:42 PM

One other option that may be available: do you have multiple schemas in the db or just the one? If multiple, are they entirely independent.
My point being, backing them up separately can give you smaller/faster backups.

bmxakias · 12-01-2017, 07:57 AM

Ok great thank you all