mysql dump to text file

marakkesh

Could anyone suggest how to create a .txt mysql dump? Since phpadmin's csv output is generally correct as far format, would modifying it to change the extension from csv to txt pose any serious problems? Ideally though would be something much compacter that could do the job -

regards,

Gordon

jburfield

Just do a SELECT into outfile ....

SELECT * from TABLE into outfile '/home/user/filename.txt';

That will give you a tabbed text file of your database.

-- Jason

marakkesh

Hi Jason - thanks for your response.

I have formatted the query so:

$sql = "select * into outfile 'text.txt' from my_table" or die("yikes");

$result=mysql_query($sql);

Everything passes without a hitch - however no file is written. I have read that the server's php.ini has to have a file setting set to true - but if it weren't, wouldn't I receive an error?

Thanks

Gordon

jburfield

$sql = "select * into outfile 'text.txt' from >my_table" or die("yikes");

$result=mysql_query($sql);

Should be like this:

$sql = "SELECT * FROM table_name_here into outfile '/path/to/file'";

if(!$result = mysql_query($sql, $db_connection)) {
/ ERROR /
print("did not work");
}

You need the database connection passed in to your mysql_query() as well as the query itself. In my example above, it would be something like:

$db_connection = mysql_connect("localhost", "username", "password) or die (did not connect);

That should help get you started in the right direction...

-- Jason

[deleted]

Remember that 'SELECT INTO OUTFILE' can only write on the server's disks, not on the client where you issue the query from.

Also remember that CSV is not meant top be used for backups, if you want to make a backup of your data, use mysqldump.

rcbasie

Hi
Why not use mysqldump? It's a command-line tool that is used to extract all or part of the data from a remote database to a local machine, for example:

mysqldump -hfoobar.com -utwit -putwit > dump.sql

This command will connect to foobar.com with the specified password and user and extract all of the information from all of the tables to the file dump.sql. The default for mysqldump is to dump all of the table structure as 'CREATE TABLE' statements, and all of the data as 'INSERT INTO' statements, but i'm sure you can dump it to other formats as well. If you run the program without any parameters it will output all of the switch options.
Hope this helps!

Robin

marakkesh

Basically I have a client who needs the data in text format to pop into a foxpro app.

He wants to periodically generate these text reports, and there will be different options (i.e only select certain names, or names after a date, etc....)

I tried the running the script, and kept gettign an error message - when I ran query in php admin I got this:

SELECT * FROM zip_codes INTO OUTFILE 'zip.txt'
MySQL said: Access denied for user: 'spidata@localhost' (Using password: YES)

My sys admin is telling me I have full privileges set - what's up with that? I also don't see this 'Access denied' message when I run the php script - just the 'error' message.

Thanks

Gordon

jburfield

My bad...I typed that in wrong...try this:

SELECT * INTO outfile '/path/to/zip.txt' FROM zip_codes;

-- Jason