Solved

Easily view and extract data from MySQL dump files...

Posted on 2008-06-17
11
1,478 Views
Last Modified: 2011-10-19
I have been given a bunch of .sql files. They are basically text files that contain data that was "dumped" from a MySQL database. I know this because the beginning of the files contains the following:
-- phpMyAdmin SQL Dump
-- version 2.9.0
-- http://www.phpmyadmin.net

I need some hand-holding here. I think that my ideal result will be to somehow get this data in an Access database and from there be able to pick and choose what to export - probably to an Excel spreadsheet. I do not have access to the original databases. I have only the .sql files and MS access to work with. I do not have any database experience, so please keep it simple. I will be happy to use whatever other software products are needed, as long as they are "free"and hopefully have a "GUI" and not a lot of command line action.

Thanks
0
Comment
Question by:techsedge
  • 6
  • 5
11 Comments
 
LVL 20

Accepted Solution

by:
virmaior earned 500 total points
ID: 21803139
(1) Depending on the format of the data, it might  be possible just to load it up into Access.  This is rather unlikely.  To fix this, you would have to modify a large number of the queries/

(2) In lieu of this, I would recommend installing MySQL on the computer where you want to look at this:
   (a) install MySQL from mysql.com
   (b) after everything is setup, you will have to suffer the command line for a little while so that you can import  the queries
   (c) install MySQL's ODBC driver ( http://dev.mysql.com/downloads/connector/odbc/5.1.html )
   (d) go into Control Panel -> Administrative -> "Connections" and add a connection to the correct DB in the MySQL install.
   (e) open access, add new tables that are "linked tables" pointing to the corresponding data in the MySQL DB.

Obviously, this is far from trivial to do...
0
 
LVL 1

Author Comment

by:techsedge
ID: 21803323
OK - so you are saying there is no way to "Easily view and extract data from MySQL dump files..."?

I can install some software, but in your example, step "b" will blow me away almost certainly. Am willing to give it a shot if I must.
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21803392
the difficulty is that they are by definition SQL files, which means they are designed to be loaded into a database.

If you can get them to send the files as CSV, then you could open them in excel or import them into Access without any issues.

If you just have the SQL, then you either need to work with the SQL in a database or just start manipulating them down to the equivalent of CSV files.  (e.g. go through and strip out all of the statements like "INSERT" etc).

but even CSV files may not be helpful.  Data in a database is structured in a way that is not an option for a flat file like CSV.  By this, I mean that a row might not appear to have meaningful data if you just look at it in excel or something like  that.  (this of course depends on what the data are).
0
 
LVL 1

Author Comment

by:techsedge
ID: 21803741
I have no choice - the dump files are all I get - the original data is long gone.

So - from the mysql page, I see 2 choices.
MySQL Community Server or MySQL Enterprise - Will the "community" version be any less painful than the Enterprise trial?

Thanks
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21804136
just run with community, it's what everyone is using.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:techsedge
ID: 21804483
I went ahead and installed it, did the "Standard" configuration, and now have MySQL running. I have also downloaded and installed the MySQL ODBC driver as outlined in step c -  

Looks like I still need to import the sql dumps somehow. I see that it points to a bunch of files under C:\Program Files\MySQL\MySQL Server 5.0\data\
as its "Path to dataset root". I can't seem to figure out further than this what to do next? The GUI doesn't offer any right click options or other help for importing.. When trying to run the MySQL Administrator program, I am being asked for "connection information" as shown in the attached file..

Thanks for the help thus far!


connect.png
0
 
LVL 20

Expert Comment

by:virmaior
ID: 21804542
it should have asked you to set a password when  you installed MySQL

enter the following:

Server Host: localhost
Username: root
Password: (whatever you picked during install)

Then click OK.

just leave the pw blank if you did not set one.
0
 
LVL 1

Author Comment

by:techsedge
ID: 21804931
OK - I am in... Have been messing with the options a bit, I can't get it to "Restore" any .sql file. The error presented is:

"The selected file was generated by mysqldump and cannot be restored by this application."


0
 
LVL 20

Expert Comment

by:virmaior
ID: 21805239
interesting error message.

have you tried just running the query rather than "restoring" the query?
0
 
LVL 1

Author Comment

by:techsedge
ID: 21806273
virmaior,
Using the "MySQL Query Browser", I have been able to do what I need, exporting from there direct to Excel -

Much thanks for the help...
0
 
LVL 1

Author Closing Comment

by:techsedge
ID: 31467950
Grade "A". Great having your help today.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Cannot convert sql query result in interger 3 45
php mysql if then statement syntax 4 36
Clean text to insert in database 9 52
sql_mode 1 19
Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now