CODEWITHSUNDEEP
mysqlperformancebackupmyisam
janpio
janpio

Reputation: 10902

How to backup a MySQL database without slowing the application?

I was wondering about our database backup strategy: Right now our sysadmins backup our database every 8 hours. I don't really know how the do it, but while the backup is running our application response time goes through the roof. So every 8 hours, the application is slow and sometimes even produces errors.

Is there a way to backup a MySQL database with MyISAM tables without slowing application code and still get a consistent backup?

Upvotes: 2

Views: 1195

Answers (2)

Snowflow
Snowflow

Reputation: 149

Doing the backup with binlogs is definitely faster, you could also check out setting up another server as a slave and take backup from that to remove the application slowdown completely.

Maybe you could research how they do it today, if they just do mysql dumps then ask them to rethink.

MYSQL servers can be ran as either master or slave servers. In a master->slave mode one server retrieves all mysql inserts and updates(master) and all the slaves only serve selects. the master updates the slaves. What you do then is just do the backup from the slave.

The master doesnt know that a backup happens and it doesn't slow it down at all. check out dev.mysql.com/doc/refman/5.0/en/replication.html for more info about replication.

Upvotes: 2

AronMiller
AronMiller

Reputation: 202

Having not implemented this yet myself I can just point you in the [possibly] right direction: http://dev.mysql.com/doc/refman/5.0/en/binary-log.html

The idea is to stream out the writes to a buffer log, pick those up in a second server, and then make backups of that server.

Upvotes: 2

Related Questions