Loading database dump from another system

So last week Zhewei and I were working on some old data – we decide to wrangle it a little bit to improve our data quality, that is when I got into this problem of loading data MySQL dump across systems.

I didn’t give it too much thought at first, after all, how hard could a one-liner going to be? Turns out that when you dump data from one platform (Linux here) to another, there could be a lot more to consider.

The one line:

mysql -u [username_without_brackets] --password=[password_without_brackets] [the_database_to_be_populated] < [dump_file.sql]

And then we got:

ERROR at line 114: Unknown command '\'

Why? My first guess is “could it be broken because I damaged the file when unzipping it from a tar ball?”. Since 7-zip did spit out something while I’m unzipping, this did caught my attention. After a closer look at the file, I found that some tables did get populated and the error did not happen at the end of the file, so that was ruled out pretty fast. What could the work around be then?

A brief list of things that didn’t work:

  • Load the dump on NCSU’s VCL then access it remotely
    • It did load under Ubuntu √
    • Turns out that 3306 is not listening to anything but local host
    • Tried to bind-address to all (0.0.0.0) in mysql.cnf and the DB service won’t restart
    • Force 3306 to be opened to public? Maybe not such a good idea
    • Tunnel through SSH then access the database
      • This somehow doesn’t work on DataGrip, keep getting the “packet sent but nothing received” message.
  • What about just copy all database files from Linux to Windows?
    • It did take a few extra step to gain control though chmod 777 database_folder/* and get them onto my Windows environment, but they’re here √
    • Once again MySQL service won’t start:
      • Could this be some where there’s a DB structure/configuration file I didn’t copy over?
      • Or could it be a file privilege thing that I can’t access it?
        • No, I did run as admin for mysqld, it still won’t start
      • Or user root cannot access it?
        • Not sure, but note that both DB have user root and the same password.

The a post on StackOverflow caught my attention, and this time it lead to the right direction: Encoding

You’d imagine that at current date everything would be under utf-8, for some reason this might be the case.

  • Initial attempt is to cast encoding to utf-8 from presumably some other encoding xxx-16-2 or else
    • But when loaded in notepad++, it’s already in utf-8
      • Saving it and loading the dump doesn’t work
      • Casting it to other things doesn’t work

What worked:

  • Ironically, what we really need to do is just specifying what the character set is on loading:

mysql -u [username_without_brackets] --password=[password_without_brackets] --default-character-set=utf8 [the_database_to_be_populated] < [dump_file.sql]

Done