3

Similar to: Only getting a friendlier mysqldump isn't possible.

My webhost kicked my database off so the latest backup I have is their mysqldump. In other words, --extended-insert=FALSE --complete-insert=TRUE isn't possible, I already have the mysqldump.

The issue I'm having is that re-importing that onto my webhost phpmyadmin times the poor guy out. Reasonably so. It can easily handle a 40,000 inserts, but not as a single insert of them all in a row.

Problem: I'm struggling with figuring out how to break these into separate queries.

I've already tried going through every single one and find-replacing, but, as expected, I made mistakes (and it cost me 10 hours) and I'm better off starting over again that trying to figure out where I messed up in this massive file.

The long lines look like this: 

 INSERT INTO `TABLE_UNICORNS` VALUES ('user',0),('user',1),('user',2),...,('user',20000);

I do have MSSQL management studio, but I'm queasy about importing a MySQL database into MSSQL and spitting back out MySQL. I can also get the MySQL dbms (I'm on windows). The only other solution I could rack my brain for is a programmatic solution, but I'm queasy about that, too. (I'm not disqualifying solutions, merely thinking aloud to demonstrate that I am trying on my own).

Community
  • 1
  • 1
rlb.usa
  • 14,942
  • 16
  • 80
  • 128
  • Also want to clarify that it's not the file size that's timing out phpmyadmin, it's the line length - eg, having 10,000 queries on one comma separated line. – rlb.usa Jun 04 '14 at 08:45
  • 2
    You could just avoid the whole mess if you have CLI access to the machine... the `mysql` command line program has no timeout, so it can suck in the dump without too much thought... – Charles Jun 04 '14 at 09:21

1 Answers1

1

@Charles was right on the money. But still, the queries were too big even for the command line!

They were too big to manage with any windows file viewer or unix file editor.

Once I realized that I was on the command line I remembered all my unix-foo. I used CAT and SPLIT to duplicate the queries into smaller files, and I used TOP to check the tops of the files, so I could see if they were split up correctly. From there things were a breeze.

Using the CLI instead of the PHPMyAdmin file upload saved a wealth of time, and it's hard to judge if the unix utility programs or the CLI was the bigger champ here.

rlb.usa
  • 14,942
  • 16
  • 80
  • 128