mysqldump Not Exporting All Rows or mysql Not Importing
Sometimes this can occur because of a combination of a certain sequence of characters and the character set of the MySQL client at the time of the import.
You could have trapped such errors without additional tools by doing the following
mysql -u dbuser -p -f -D thedb < importfile.sql 2>errors_encountered.txt
This would have recorded your trouble spots into errors_encountered.txt
What could have been done with the mysqldump to begin with ???
SIMPLIFY EXPORT CONTENTS
You could have used the --hex-blob options to convert the characters into a hexadecimal representation. This could have eased any character set misinterpretations or misunderstandings on the part of the MySQL client program.
DUMP ONE ROW AT A TIME
Even when using --force for the MySQL client, this can still result in hundreds or thousands of rows not being inserted. Why ?
By default, mysqldump has --opt enabled. This sets the following
--opt Same as --add-drop-table, --add-locks, --create-options,
--quick, --extended-insert, --lock-tables, --set-charset,
and --disable-keys. Enabled by default, disable with
--skip-opt.
Notice that one of the options is --extended-insert. This causes mysqldump to set up inserts in chunks of hundreds or thousands of rows at a time.
If just one row in a chunk of rows had an issue with being imported and you are using --force, the entire chunk of rows is not inserted.
How do you get a hold of all the good parts of a chunk. For a mysqldump created with --extended-insert, there is no simple way. What can you do when this happens ? I have good news and bad news.
GOOD NEWS: You have to launch a new mysqldump with --skip-extended-insert. That forces a mysqldump to create an INSERT for every row. That way, when using --force during import, an invalid INSERT due to whenever circumstance will not affect surrounding rows.
BAD NEWS #1 : This makes the resulting mysqldump file much larger.
BAD NEWS #2 : Importing the resulting mysqldump file takes much, much longer.
This is something I recommended Aug 09, 2013 (Backup / Export data from MySQL 5.5 attachments table keeps failing!)
SUGGESTION
To nullify the size problem this creates, you could do the following
gzip the mysqldump while doing mysqldump
nohup mysqldump ... | gzip > importfile.sql.gz &
ungzip the mysqldump and pipe it to mysql for import
gzip -d < importfile.sql.gz | mysql -u dbuser -p -f -D thedb