5

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.

Improve this question
8
  • This looks like a timing issue, what is the current value for interactive timout and wait timeout in that session or in my.cnf? Did you test to set both to 0 (infinite)? Commented Sep 2, 2014 at 13:54
  • We looked in the my.cnf but could not find any parameters that looks like interactive or wait timeout. What specific parameters are we looking for? Thank you for taking your time with this. Commented Sep 2, 2014 at 14:11
  • interactive_timeout defaults to 28800 which is 8 hours. Commented Sep 2, 2014 at 14:14
  • 1
    That might indeed be caused due to activity on the sending client (unloading the stuff first) and inactivity on the receiving server. Commented Sep 2, 2014 at 15:03
  • 1
    I recommend to use mysqldump direct on the server, transfer the files and use mysql client to import that on the other server directly as well - taking into account the size of the tables. Commented Sep 2, 2014 at 17:32

2 Answers 2

Reset to default
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).

SQL export to another database

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Thanks for posting even on this old post :) I'm no longer part of the organisation or using MySQL, but it looks like this is the best solution, especially considering that it looks just like SQL Server Management Studio and how you export/import databases there. Thanks for this!
@MagneTism I see. I was just coming across this issue myself and found your question, after finding a solution.. i thought of including it here since there was no mention to it. You are welcome.
1
  1. Sizes in Databases can vary, even if it contains the same data. While migrating, you also defragment the data
  2. I would not trust a migration wizard with that much data. MySQL does not handle large data well and inserts and other stuff can fail silently or only with warnings if MySQL is not running in strict mode.
  3. If you are migrating everything (also user and metadata), just copy/rsync the whole mysql_data folder while both DBs are down.
Improve this answer

1 Comment

Apologies for not getting back to you on this as it received low priority here at work :S But now I'm back! :) I checked the data as a number of row-count and it seems that on 3.12 million rows I lose 20 000 rows during a mysqldump migration, so I'm definitely losing some data. Do you know what this could be the cause of?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.