mySQL dos batch file to import

We have to hack into a mySQL database. After i have created a number of powershell\sql scripts from the SQLDUMPfile, some to create tables and others to import data, we have to LOAD those script, which are in fact sql scripts. The dump file is 52GB, which would strangle the workbench, which is why the separation of tables and import.

The imports can fit into a single file and we can run it from the workbench, but SOME of the imports have too much data in them. There is also overhead for the workbench and one time the script was still too big and i lost the session.

So run it from the command line. I want to make it into a batch\DOS file so i can just execute it. When that works, i can cluster together a number of import scripts to get the bunches of smaller files together. Like all the files that have no data, and turn out to be 1KB in size?

I can start mySQL from the command line, but it took awhile to get the script to auto-login with the password. And THEN to recognize the right database. Turns out that that is best done by calling the import script when the SCRIPT contains the “use database;” syntax, and not in the batch. I could manually load the script from the mySQL prompt, as mySQL> -e “source importfile.sql”, but that’s no fun. I am sure there are other ways too.

What i found was this .bat file

“C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql.exe” -u rootie -p##### < d:\scripts\importscripts\forms2.sql

will run, start mySQL, use the password (notice no space) albeit with a warning, and load/run the script forms2.sql. That file looks like this:

use database;
LOCK TABLES `forms` WRITE;
/*!40000 ALTER TABLE `forms` DISABLE KEYS */;
/*!40000 ALTER TABLE `forms` ENABLE KEYS */;
UNLOCK TABLES;
LOCK TABLES `mastermachines` WRITE;
/*!40000 ALTER TABLE `mastermachines` DISABLE KEYS */;
/*!40000 ALTER TABLE `mastermachines` ENABLE KEYS */;
UNLOCK TABLES;
LOCK TABLES `mytable` WRITE;
/*!40000 ALTER TABLE `luprolinkraw` DISABLE KEYS */;
INSERT INTO `luprolinkraw` VALUES ('3ac160e5-1a41-4213-98e6-555a574238ac','…’,’2014-06-16 18:04:11′,1);
/*!40000 ALTER TABLE `mytable` ENABLE KEYS */;
UNLOCK TABLES;

There are three sql statements in this script – set keys on forms and mastermachines. These are actually empty tables, but the SQLDump wants to output sometime to put into the newly created table. The first statement updates the mytable with data – which is a soap document. There can be more lines of data – both in this insert statement and there can be more insert statements.

If you run it, you get a warning and nothing else. If you change the name of the tables, you get an error, so NO error is good. If you run it twice, you get an error on importing the GUID twice.

So – no news is good news.

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.