MySQL Backup Options

[Shay2501]

I was using mysqldump to perform MySQL backups just fine. However, we recently had to restore the database and it took about 10 hours for 100GB to restore. Now they (managers) want me to find a different method, one that will restore faster.

So I tried using MySQL administrator's backup utility but it takes twice as long and is twice as big! I doubt this will speed up the restore.

So, my first question is why is it so big and why does it take so long? Is it giving me something that I won't get using mysqldump?

And my second question is how can I optimize one of these methods so a restore doesn't take half a day?

Thanks,

Shannon

 

[lgarner]

I don't know about the Administrator's backup, but restorations can take a long time if there are many or complex indexes on the tables.

One method would be to backup the database files themselves. Then your backup & restore times would be only what was required by the backup mechanism. To do this you'd need to shut down the database, so I'd recommend setting up a replica and backing that up so you have no downtime.

 

[Shay2501]

Thanks for the info.

I assume by replica you mean another MySQL machine somewhere that holds an identical database that I can shut that down and back up?

If I just replicated the tables in the current MySQL instance, I would still need to shut down the service to back them up.

Also, this is running on Windows, not Linux. I forgot to mention that.

 

[lgarner]

Yes, another MySQL server. You would need to shut down the current server to get a clean starting point, but you wouldn't need to shut it down for routine backups. Just stop the replica, back it up, and restart it.

This is for MySQL, not an OS-level backup. You'd use whatever tools you normally use for backing up a server.