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.


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?

  • 3
    What command are you issuing to import the database, and how are you doing it (phpmyadmin, command line, etc.)? – Ben Pilbrow Sep 5 '11 at 20:40
  • 2
    and what command did you use to import the dump into your database ? – user9517 Sep 5 '11 at 20:49
  • 3
    @dhananjai, what is the FULL command you specified for the dump? Telling us mysqldump without the parameters is of no use. – John Gardeniers Sep 5 '11 at 21:45
  • 3
    Use mysqldump to export, use mysql to import. – Zoredache Sep 6 '11 at 16:52
  • 3
    The problem is that you can't be bothered to read the detailed answer that someone kindly typed out for you, containing exactly what you need to do to restore from your dump. – MikeyB Sep 6 '11 at 17:09

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.

| improve this answer | |
  • I checked md5sum of the file and its fine. I also checked the structure, all the tables are in there. What else should I check for? – dhananjai Sep 6 '11 at 17:06
  • Thanks! Importing with mysql did it. – dhananjai Sep 6 '11 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.