Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-17
11
Medium Priority
?
1,521 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 20

Accepted Solution

by:
virmaior earned 2000 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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
 
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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

722 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