I've inherited a website I didn't originally build. The database includes a users and a bookings table both of which are relatively large (innodb) tables.

I'm trying to download a copy of the database to use locally but despite the mysqldump command seemingly working when I run the dump file against my local database server I am missing the majority of rows in these large innodb tables.

The mysqldump command I've used is:

mysqldump --compress --add-drop-table --routines --events  --comments --extended-insert  -u root -p dbname > dbdump.sql

And I've attempted to restore this using simply:

mysql dbname -u root -p < dbdump.sql 

Both the dump and import commands work without errors.

The imported bookings table contains only 4722 of the 41477 records that should be there.

I've found some suggestion it could have been a problem with the max_allowed_packet setting on the server but increasing this to 16777216 and re-running mysqldump made no difference.

Any suggestions?

  • Can you "eyeball" the imported records and see if anything stands out? Is dbname empty when you do the import? p.s. welcome to the forum! :-) – Vérace Nov 1 '19 at 0:43
  • Thanks for the reply and welcome. I've re-run the import first ensuring all tables had been dropped in my local database - same effect. Eyeballing the dump file it appears the records in the dump file for the 'booking' table start at ID 67209 - the live database has a few gaps in it's records but not a drastic number so it appears it's the dump itself is not exporting all the records in certain tables. – steve Nov 1 '19 at 6:22
  • Where did 4722 and 41477 come from? Don't trust SHOW TABLE STATUS; do trust SELECT COUNT(*). – Rick James Nov 2 '19 at 1:19
  • Did you restart the MySQL service after change max_allowed_packet? – Jesus Uzcanga Jan 29 at 16:42

Your Answer

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

Browse other questions tagged or ask your own question.