Restore of 10 GB-Database takes days

1

I need to backup 5 InnoDB databases on my client’s Windows based live server, and restore them on a dev server so that that could replace the live server temporarily, should the normal live server run into some serious problem.
Doing the backup is fast (25 minutes) and easy and can be automatized with SQLBackupAndFTP.
Doing the restore - well. One of the databases has > 70 million rows and a size of more than 10 GB, and the restore takes days. Something tells me that MySQLDump is not the best suited way to do backup/restore. Can anyone give me an idea what I could do instead? (do the indexes take so long, for example? is there a way to disable them during the restore, and recreate them afterwards? the table has 5 fields: one primary key, one int, one datetime, two numeric double fields, each of those with a non-unique index on it)

2

Hi Michael_Peters,

Please give us some time to check it, we’ll keep you updated.

Sorry for the inconvenience.

3

Hi Michael_Peters,

Thank you for your waiting.

  1. We have checked the issue and can confirm that everything works smoothly. With the extended insert option on our test models, a single insert command inserts multiple rows at once. However, there is a limit to the number of records that can be placed in one insert command. Most likely your insert command contains many inserts, but the insert commands themselves are also many since you have 70 million lines.

  2. Lengthy recovery process is a common problem with logical backups. You can try to restore manually via mysql.exe by disabling various checks. There is a lot of information on this topic on the Internet about what and how to disable it. For example, take a look at the following post: backup - How can I speed up a MySQL restore from a dump file? - Server Fault

If you have any other questions, please let us know.

Thank you!

4

hi Alexander, I have found pages that recommended to extend the innodb_buffer_pool_size to 1 GB. This has sped up the restore from several days to about 13 hours! now it is possible to restore the zipped databases from the other server every day

5

Hi Michael_Peters,

Thank you for your reply.

Good news! Please feel free to contact us on any issues or concerns.