How do I restore a dump taken with mysqldump?

Question

I took a backup from a Linode VPS using the mysqldump command, but then when I try to restore that dump into my localhost, it shows the dump was successfully imported but actually nothing gets imported into the database.

I suspect it to be a server issue - that my Linode server is not generating the correct dump file although it looks OK when I open it in an editor. The reason I suspect it to be a server issue is that in earlier attempts the database couldn't be downloaded, the download will always interrupt near its completion.

Commands:

For exporting the database:

mysqldump -u root -p dbname > db.sql

For importing the database:

mysqldump -u root -p dbname < db.sql

I even checked the integrity of the downloaded file using md5sum & its fine.

What could the problem be?

Answer 1

You don't say how you're importing the dump into your local MySQL server, but anything over a few MB should be done on the command line as opposed to a tool like phpMyAdmin. Things like phpMyAdmin are great for quick queries and edits, but since it's running on a web server there will be some limitations on the size of uploads which can be impractical to change just so you can import your 100MB database using phpMyAdmin.

From personal experience, phpMyAdmin can also be finicky about importing dumps, and report they were successful when in fact they were either only half imported or not imported at all.

---

If I were you, I'd try to break your problem down into smaller chunks and build it up to see if it's falling over somewhere down the line. In all the commands below, obviously substitute -u ben -p"password" with whatever's appropriate for you. I'm running it on Windows, but it shouldn't be much (if any) different for Linux. Since you have a VPS, I'm also assuming you have root on it.

First thing's first, ensure all of the tables are actually being created with the correct structure. Use mysqldump --no-data db_name -u ben -p"password" > structure.sql to do a "structure only" dump. Open this with a text editor on the server and check all tables have been dumped.

Next, do a full dump of the data with mysqldump db_name -u ben -p"password" > full-dump.sql. Again, verify with the text editor on the server that the last line of the file is the last row in the last table of the database.

Then transfer the file to your computer using your preferred method. If this is where you think the data is being corrupted or truncated, get the checksum of the file on the server and compare it to when you download it.

Import the dump into your local MySQL server using mysql -u ben -p"password" db_name < full-dump.sql. If you're feeling extra paranoid, compare the number of rows in each table on your newly imported data with the server copy.