Export from server to server recommended for very large DB?

microUgly posted 6 years ago in Import/Export
I have a 15 GB database I need to copy from a remote server to a local server. Is HeidiSQLs export feature where it can export directly to another server a recommended method for such a large DB?

I'm thinking it will be easier than dumping, tar-ing and copy the SQL file to the local server before untar-ing and importing. But it might not be as safe.
ansgar posted 6 years ago
My colleague recently did exactly the same with a 13 gb database. It took quite a while, and the GUI was inresponsive inbetween, but the export was finished in the end, without errors. But to be honest, I don't have more than this one test I can tell you of. I personally tested the export scenarios only with smaller databases.
microUgly posted 6 years ago
If the UI becomes unresponsive during that time, I think I might skip it. If it's unresponsive, I will have doubts about if it's working.
ansgar posted 6 years ago
That's what I meant. You will have a gutt feeling that something's wrong, during quite a long time. Well, if you should at least give it a test run, or?
charlretief posted 6 years ago
I am also working with large MySQL databases I have a 60GB database.

Tools like "mysqldump" and hiedi's dump feature is very usefully when you have small databases (less then 1GB). But when you data grows beyond this you'll have to use other tools as these methods of creating sql to recreate a dataset can be very time consuming especially during the restore phase.

One very useful tool relate to this is the binary log. See http://dev.mysql.com/doc/refman/5.5/en/backup-methods.html. This can be very handy to clone your dataset.

The one that works the best with my huge dataset is Percona Xtrabackup (http://www.percona.com/docs/wiki/percona-xtrabackup:start). This tool makes a hot copy of your mysql data directory for backup purposes.

With this tool I can backup and restore (on an other server) my 60GB database in less than 1 hour.

Please login to leave a reply, or register at first.