Extracting table structure from a full MySQL dump

[pwaring]

I currently have a complete MySQL dump for all the tables and data in a database. I'm trying to port it to a different database structure (still MySQL, but different tables, names for columns etc.) so at the moment I'm only interested in the table structure definitions (i.e. CREATE TABLE (....)). Usually I'd just find them all and copy/paste them from the complete dump, but this DB is over 300Mb so that's not really a feasible option.

What I ideally want to do is run a simple script (under Linux, maybe in Perl) that will take all the table structure from the existing file and dump it to standard output, so I can redirect it to another file to read from. Does anyone have any idea how to go about this?

Thanks in advance.

[raf]

Welcome here !

I don't understand your problem. How different are the two designs? Can't you use a 'select into'? What do you normally copy and paste ?
What do you mean by that will take all the table structure from the existing file and dump it to standard output, so I can redirect it to another file to read from
Dump the structure to standard output? Redirect it to another file?

[pwaring]

The database designs are significantly different - so much so that I need to find out the structure of the old database so that I can see what maps to what in the new design.

I don't see how SELECT INTO would work - I've already got the dump of the database (from mysqldump) as a plain text file, but I don't have a copy of the database itself on disk and (given the huge size of the DB) I'm not too keen on installing MySQL on my machine and importing all the data and then exporting it again just with the table structures.

When I say I want the stuff to be sent to standard output, I mean I want the script to take the table structure, minus the data, and send it to standard output (i.e. print it on the console). That way, I can redirect it to a file rather than having a script that writes to a file for me because that just complicates things.

[raf]

Let me get this straight:
- you have a dumpfile of 300MB without create table commands in it --> else you could just see the original tables design
- you are not planning on executing the dumpfile to recreate the tables and then do a normal ETL proces
- you plan on writing a script that wil parse this 300MB textfile to transform it's insert-statements into other insert-statements that fit your new design?

I realy don't get your explanation
When I say I want the stuff to be sent to standard output, I mean I want the script to take the table structure, minus the data, and send it to standard output (i.e. print it on the console). That way, I can redirect it to a file rather than having a script that writes to a file for me because that just complicates things.
Apart from that i completely don't understand the 'print to console and redirect to file' part, i don't quite see how this would help you to eventually get the data in your new db.
There is only one way to load the data into another table, and that is reordering the destinationstable so that the columns are in the right order (+ making sure they have the right columntype and valuerange etc) (extra column need to be removed since dumpfiles don't include a fieldlist inside the inserts, so they expect the same number of colmuns as there are values, and the column need to be in the right order.
--> dumpfiles aren't realy a datafile, they are just a bunch of sql-statement. You probably better created a csv or so.

I'd suggest installing mysql and executing the dumpfile and having your computer do the work while you drink a cup of coffee.

[pwaring]

Let me get this straight:
- you have a dumpfile of 300MB without create table commands in it --> else you could just see the original tables design

No, I *do* have the CREATE TABLE commands (otherwise how could I reconstruct the database structure?) but they're hidden amongst huge amounts of data and I just want to extract them automatically - i.e. have a script that goes through until it finds a CREATE TABLE command, then copies all the text from there until the end of the command.

- you are not planning on executing the dumpfile to recreate the tables and then do a normal ETL proces


- you plan on writing a script that wil parse this 300MB textfile to transform it's insert-statements into other insert-statements that fit your new design?

Possibly, either that or I'll write a script that pulls out each record from the old DB and places it in the new one with the correct data in the new fields.

Apart from that i completely don't understand the 'print to console and redirect to file' part, i don't quite see how this would help you to eventually get the data in your new db.

Because once I know the structure of the old DB I can figure out which fields need to be carried across to the new ones, where the data needs to go etc and *then* I can write a script to port the data across.

There is only one way to load the data into another table, and that is reordering the destinationstable so that the columns are in the right order (+ making sure they have the right columntype and valuerange etc) (extra column need to be removed since dumpfiles don't include a fieldlist inside the inserts, so they expect the same number of colmuns as there are values, and the column need to be in the right order.

I know that the field numbers and data match up. However, that is *not* the only way to port the data across - what happens if your database design is significantly different? You can still copy the data across without re-ordering things around.

--> dumpfiles aren't realy a datafile, they are just a bunch of sql-statement. You probably better created a csv or so.

Well, they hold the data from the database (in this case), so I'd say they were a data file. I didn't create a CSV - I know what a MySQL dump looks like.

I'd suggest installing mysql and executing the dumpfile and having your computer do the work while you drink a cup of coffee.

Have you any idea how long that would take with a 300Mb MySQL dump? I'd be waiting most of the day to import the data, assuming the server didn't crash under the load - I'm only using a normal PC to do this.

[raf]

A dumpfile is NOT a datafile. It is a textfile that contains sql-commands to create db's and/or tables and/or insert records. This is not a datafile. It is just as much a datafile as an html-table is a datafile (although it to contains the data)

Executing the dumpfile will indeed take a while, but if you do a few experiments then you'll learn that parsing the file and rewriting the sql-statements will take waaaaaaaaaaaaaaay longer and most likely timeout. (don't let me stop you. best order some extra coffee in case it times out a few times and you need to start creating a new dumpfile with only the statements that aren't included yet)

So even if you get the create statements, it wount help you much because rewriting the sql-command and executing them then one by one is simply the least efficient and most riskee approach. and while you're experimenting and writing your code and testing it etc, i'd probably already have executed the dumpfile, and dumped it again into a csv or txt or inserted the records directly in the new db with a 'select into'.

If you ever executed a large dumpfile, then you would also know that there is nothing stopping you to split it up over a few files and execute them one after the other (so you can minimalise the timewaste if one run goes bad.

I know that the field numbers and data match up. However, that is *not* the only way to port the data across - what happens if your database design is significantly different? You can still copy the data across without re-ordering things around.
Hmm. Try it. I just did. it will only be possible if you have complete insertstatements, (so including the fieldlists) but since you also dumpted the tablestructure, that isn't likely.
Have you any idea how long that would take with a 300Mb MySQL dump? I'd be waiting most of the day to import the data, assuming the server didn't crash under the load - I'm only using a normal PC to do this.
In fact, i have. And it will largely depend on the number and type of indexes that need to be build. And it would probably already have been finished if i started when you created this thread. It's not like you have the biggest dumpfile that was ever executed.

But i'll leave it alone now since i don't understand why you don't just use the most obvious sollution and you're just getting frustrated for whatever reason. Maybe someone else will share your opinion or has ever done anything else with a dumpfile then execute it.

[pwaring]

Well, thanks for your "help". I'm in exactly the same situation now as when I started.

*crosses codingforums.com off list of places to ask for help in future*

[raf]

Nice attitude.

You probably better take a step back and review the situation:
- for starters, you only need a dumpfile for the structure but you dump both structure and data and then want to filter out the structure.Usually I'd just find them all and copy/paste them from the complete dump, but this DB is over 300Mb so that's not really a feasible option. tells me this ins't your first, how should i call it, 'possible learning experience'
- then you find yourself faced with this problem, that can easely be solved. It will indeed take more time then you'd like, but it is an easy sollution none the less.
- but instead of seeing that you need to first get back to your startingpoint(recreating the db) and then export the design or data you realy need, in a format you can realy use, your planning on doing i don't know what.
- and instead of getting on with it, you start arguing about that it is possible to invent other non-conventional ways to solve this. and your obviously annoyed that you've gotten yourself into trouble and you persist in not taking the obvious way out.
- and instead of recognising the problem and getting on with the obvious way out, you now start blaming me that it didn't get solved? that you don't wan't to follow my advice is my fault? and because you don't want to accept the logical consequences of your past actions (which i have nothing to do with --> you've created this problem) you're gonna shoot the messanger?
"My tooth hurts" "Go to the dentist" "NoNo. There must be another way. If i close my eayes it will go away" .... "My tooth still hurts, your just useless with your advice to go to the dentist. That doesn't cure my pain.I think i'll ask someone else to do some magic."

maybe indeed try it on a few other forums and waste another few hours on finding another way out, and then another few hours executing your miracle-script to solve this. i'm sure you'll save a lott of time :rolleyes:

But don't get your facts wrong about who created this problem and what the most logical and safe way out is and why your problem still isn't solved.

[pwaring]

Why on earth would I want to import the entire database and then export just the parts I need, when some form of regular expression (which was all I was looking for) would give me what I want a darn sight faster? As I said, usually I would just scan the dump quickly and manually copy/paste the bits I wanted, but when you have a 300Mb text file (with binary data in as well, just to make things even harder to read) it's not feasible. All I wanted to do was to copy out all the CREATE TABLE statements without having to trawl through the file myself.

As for your dentist analogy, that is nothing like what my problem is. I asked a specific question and you just kept telling me to just import the data back, which would take forever on my machine. Yes it might be the "obvious" way out, but it's certainly not very efficient if all I want is the database structure.

for starters, you only need a dumpfile for the structure but you dump both structure and data and then want to filter out the structure.

I didn't dump both the structure and the data - the dump file I have is all I have to work with - as I said before I don't have the original database otherwise I'd obviously just dump the structure on its own. Having the DB was *not* the point that I started at.

[raf]

Yes. I'm sorry. I'm not the smartest one around here.

Getting the createstatement looks like a useless step to me, but then again, you'll know best what you need and how this will help you getting your data in the table with another design.

Best of luck.

[pwaring]

Getting the CREATE statements isn't a useless step - I need to know what the table structure was so that I can port the data across. Using your method of chopping columns only works if you know which columns to chop, which I can't decide without knowing the structure...

[raf]

Using your method of chopping columns only works if you know which columns to chop, which I can't decide without knowing the structure...
? My method of chopping columns ? Nonono. The only reason i commented on reordering the destinationtable and possebly removing column, or to rewrite the inserts, is because i was scanning the possible options if you want to persist on not running the dumpfile and use a standard ETL method. Which would exactly be my method.

But like i said, the best of luck with however you're planning on solving this.