1

So, I'm trying to export all the DB structure of my MySQL db.

Attached is my DB structure, I have more then this, but I want these tables only.

Basically Tools relate to Factsheets and Factsheets to Content and Images, then to everything else.

I need to export based on TOOL ID. Which is only defined on the Factsheets table as a FK and PK only on the Tools Table. DATABASE DESIGN

Any ideas?

I can do command line, mysql workbence or PHP-my admin.

UPDATE #1:

Ok to clear some stuff up, lets forget about glossary. A tool is a collection of factsheets, factsheets are collections of factsheetcontent (1 factsheet to many contents) and each piece of factsheetcontent is related to one factsheettitle.

Basically, I'm needing to query based on the TOOLID, ex the Citrus Pests tool with ID of 2, to get all the factsheets which then get all the content under them.

Relations:

Tool to Factsheet: 1 to Many

Factsheet to FactsheetContent: 1 to Many

FactsheetContent to FactsheetTitle: 1 to Many

Factsheet to FactsheetImages: 1 to many

FactsheetImages to image: 1 to 1

Starting from the tool I need to get factsheets, then all things under them. I need the Database structure. Otherwise I'd do a Select with a bunch of joins and export to a CSV

This is what I need, we can drop the glossary as thats not needed. This is what I'm after, using the command line mysqldump works great for the 2 table that has the toolid, after that it quickly fails and blows apart--starts to throw lots of errors.

Any ideas?

Error I get using RolandoMySQLDBA suggestion:

./getDbtablesBasedOnToolID

: command not foundnToolID: line 6:

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO)

ERROR 1045 (28000): Access denied for user '-p'@'localhost' (using password: NO) : command not foundnToolID: line 22: : command not foundnToolID: line 32: '@'localhost' (using password: NO) when trying to connect : command not foundnToolID: line 39: '@'localhost' (using password: NO) when trying to connect : command not foundnToolID: line 46: '@'localhost' (using password: NO) when trying to connect : command not foundnToolID: line 53: '@'localhost' (using password: NO) when trying to connect : command not foundnToolID: line 60: '@'localhost' (using password: NO) when trying to connect

2
  • You can use Workbench to export a database or just these 7 tables. What is the meaning of "I need to export based on TOOL ID"? Aug 1, 2012 at 22:52
  • I added some clarification.
    – Snow_Mac
    Aug 3, 2012 at 17:41

1 Answer 1

1

1) GUI option

You can use Toad for MySQL Freeware from here.

To take an export that creates inserts for a specified set of data, after creating a connection to your mysql database, you can choose Tools->Export->Export Wizard

Click Next, then Add Button

Choose query as the Export category and then type your SQL statement for one of the tables

SELECT col1, col2 ... 
  FROM table_name
 WHERE tool_id='xyz'

Then Click next - choose SQL SCRIPT any other relevant options

Choose file as the output

Repeat this for as many tables as required

Then execute it to create a set of insert scripts.

You can take a structure export either using TOAD or phpmyadmin and the combination should give you what you are looking for which i assume is a cut down set of data for a test environment.

2) Command line option

Use mysqldump at the command line and list all tables that have the tool_id column

  mysqldump -t -u [username] -p test mytable anothertable --where="tool_id = 1"

This should generate a set of insert statements and as above, if you load these scripts into an empty schema exported with phpmyadmin using structure only, it should give you what you need.

1
  • 1) won't work because of licensing issues with working for the USDA... (its taken 3 months of waiting to get Git). 2) tried that but failed after 2 tables, threw an error because only 2 tables have the tool id. Any ideas?
    – Snow_Mac
    Aug 3, 2012 at 17:44

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.