[SOLVED] mySQL Export Statement?

[blackcell]

I am wanting to be able to export a table via the sql statement.  Within phpmyadmin, it works great for showing the sql statement for every other move you make within the program but when I export it basically gives me the sql query to recreate the table through sql.  I want to know how to initiate the export because I want to pull the sql command it currently gives me into a text file.

[blackcell]

Or is phpmyadmin hiding very intensive data processing through graphical interface?

[fenway]

Export a table into what?

[blackcell]

I need to know if there is a sql command to basically give me the sql statement to duplicate a table containing information.  I want to use it to build a light interface to backup selective tables.  I know programs exist to do such things but I want to create my own.  I just don't know if the manner in which myphpadmin's export function to give the user the sql statement to replicate the table is an intensive background operation.

 

Does that make sense?  Let me know if it doesn't I shall try to re-word.

[blackcell]

Oh if it helps and your fuzzy on myphpadmins process of backing tables, then look here:

 

Click [Export] > Select sql as output > Copy the sql statement to reproduce the table.

[blackcell]

After some more on mysql I have found that it has to do with the view dump. Cant find any information on where to go from there.

[fenway]

You can use SELECT ... INTO OUTFILE.

[blackcell]

I have looked around for some stuff on 'SELECT INTO OUTFILE' and I'm not understanding it completely or am looking at wrong information.  Could you give me an example of how to use this within php to achieve my desired result.  Nothing complicated, just an example sql.

[fenway]

It's just like any other select, example you store the result set in a file.

[blackcell]

I think I got it from here.  Thanks fenway.

[blackcell]

Maybe I don't have it from here!!!!

 

Here is my mysql:

 

SELECT * FROM `test` INTO DUMPFILE '//PC001/itdep/dump1.txt' LIMIT 0 , 30 

 

and I get this error:

 

#1064 - You have an error in your SQL syntax; check the manual that 
corresponds to your MySQL server version for the right syntax to use 
near 'LIMIT 0 , 30' at line 4 

 

I know the answer to my problem is right in front of me but I just started getting deeper into mysql.  Until now I never needed to use much of it.

 

Thanks in advance.

[fenway]

DUMPFILE? shouldn't it be OUTFILE?

[blackcell]

From my reading, I think that there are two options (DUMPFILE and OUTFILE) that act similar with the exception that one has a slightly different way of acting with more paramaters.

[fenway]

Then it might be a file path issue, don't think I've ever used it with UNC paths... did you try something local?

[beebum]

Limit needs to come before Into.

[blackcell]

I found a method of backing up the table(s) via hard sql files located in the mysql directory.  I don't know if that can cause issues later on or how unstable it is.  Seems to work and retain information, although the files are slightly bigger than expected.

[fenway]

Works for MyISAM... but make sure the tables aren't being accessed.

[blackcell]

Whats the worst case scenario if table is being accessed while exporting the file? I know it can't be good and remembering that you can't do this basically kills my selective automatic backup method I had.

[fenway]

Whats the worst case scenario if table is being accessed while exporting the file? I know it can't be good and remembering that you can't do this basically kills my selective automatic backup method I had.

Not sure... I don't remember how the files are locked... but in principle, you may get inconsistent tables.  A simple LOCK should remedy this.