I'm not sure if this should be published in SuperUser or not, since we use the built-in migration wizard in Workbench, please let me know if this question should be moved.
goal
We are currently migrating the database from one server to another, and since the MySQL Workbench has the built-in “Migration Wizard” feature, we thought we would go on our fun path to migrate it. We have 16 different database schemas that need to be migrated with different sizes (the smallest is 3 MB and the largest is 76 GB).
Problem
We started by trying to migrate one of the medium and large sizes, which is located at 14.7 GB, and it starts fine, but after he successfully moved half the tables, we get the error "MySQL Server is gone." After making sure that the connection is stable and connected to the cable (maybe it was a disconnected wireless signal?), And provided full privileges and used the root user for migration, we still get the error "Server is gone."
Then we tried with smaller databases that work great, so we thought this might be a timeout problem. We tried to remove the timeout settings, but we still get the same error for a larger database. A real kicker is what we are experiencing right now.
When we tried this in a 150 MB database, the migration wizard then finishes the migration correctly 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 the table sizes are correct. To our surprise, the sum of the tables in our source database is 150 MB, but the sum in the target database says 94 MB.
Question (s)
What could be other reasons, why we get the error "Server is gone" due to lack of time and privileges? Why did the migration master say that the migration was successful without warnings or errors, but in fact only about 60% of the data was transferred? Can you trust the Migration Wizard or is it a table size query that should not be trusted? We are completely wrong about this, i.e. Do you recommend another more stable way to migrate the database?
I am pleased to provide additional information if necessary.
EDIT:
MySQL Version: 5.1.41 (Ubuntu)
We also checked the number of rows in each table in each schema, and while most of them are correct, some are erroneous. This is where data inconsistency comes into play. In the example with 150 MB / 94 MB, there are 25 tables. Of these 25 tables, 23 are correct, but 2 are not. In the source, one of the tables has 2.57 million rows, but only 1.5 million of them fall into the target table.
EDIT 2:
Running the same query again gave me 94 out of 150, 8 out of 150, 24 out of 150, and now is the fourth time that he has moved the entire database. I guess the problem is somewhere else, but it can't understand why for me. It took 92 minutes to transfer 150 MB of data. An extrapolation that would give me about one month to transfer the database to 75 GB is something clearly wrong.