MySQL database migration missing data

Question

I'm not sure if this should be posted in SuperUser or not since we're using the built in Migration Wizard in Workbench, please let me know if this question should be moved.

Objective
We're currently in the process of migrating a database from one server to another and since MySQL Workbench has a built in function called Migration Wizard we thought we would go on our merry way to migrate it. We have 16 different database schemas that needs to be migrated with varying sizes (smallest being 3 MB and the largest being 76 GB).

Problem
We started with trying to migrate one of the medium to large sized ones that sits on 14.7 GB and starts out fine but after it has successfully migrated half of the tables in that one we get the error "MySQL Server has gone away". After making sure the connection is stable and connected it with cable (maybe it was the wireless signal that dropped?) and ensured full privileges and used the root user for the migration we still get the "Server has gone away" error.

We then tried with the smaller databases which works fine, so we thought it could be a time-out issue. We tried removing the timeout settings but we still get the same error for the larger database. The real kicker is what we're currently experiencing.

When we tried this on the 150 MB database the Migration Wizard then correctly completes the migration without any errors or warnings. Just out of curiosity I ran the following code

SELECT table_name AS "Table", 
round(((data_length + index_length) / 1024 / 1024), 2) "Size in MB" 
FROM information_schema.TABLES 
WHERE table_schema = "TableName"
ORDER BY "Table";

To make sure that the table sizes are correct. To our surprise, the sum of the tables in our source database says 150 MB, but the sum in the target database says 94 MB.

Question(s)
What could some other reasons be as to why we get the "Server has gone away" error apart from timing out and privileges? Why would the Migration Wizard say that the migration went successful without warnings or errors but in reality only about 60% of the data was migrated? Can the Migration Wizard not be trusted or is it the table size query that shouldn't be trusted? Are we going about this completely wrong, i.e., do you recommend another more stable way of migrating a database?

I'm happy to provide more information if needed.

EDIT:
MySQL Version: 5.1.41 (Ubuntu)

We have also checked the number of rows in every table in every schema and while most are correct, some turn up wrong. This is where the data inconsistency comes into play. In the 150 MB / 94 MB example there are 25 tables. Of these 25 tables, 23 are correct and 2 are not. In the source, one of the tables have 2.57 million rows but only 1.5 million of these end up in the target table.

EDIT 2:
Running the same query again has now given me 94 of 150, 8 of 150, 24 of 150 and now the fourth time it migrated the whole database. I'm guessing that the issue is located somewhere else but can't for the life of me figure out why. It took 92 minutes to migrate 150 MB of data. Extrapolating that would give me roughly one month to migrate the 75 GB database - something is clearly not right.

Answer 1

I know its an old post, but for the benefit of others, you can use HeidiSQL, it does the job perfectly by exporting your database to another database of your choice:

• Go to your database (right click it)-> export DB as SQL.
• Choose the output target -> the Server you want to export your Database to (you need to have the database server already added to your list).