Remote OUTFILE

MySQL Remote Backups - No Access To FTP Or Remote Command Line
I have a mySQL database located on a remote host's server. I would
like to schedule a task on my local Windows computer to retrieve a
backup/dump of this remote database.

I have contacted my host, and they indicated that the server is only
open to the Internet via mySQL. Therefore, even if I did have access
to that server's command line (which I don't) to create dumps via
mysqldump, I would not be able to retrieve via FTP.

Does anybody have any ideas as to how I can generate backup files from
my local computer through a local command-line interface? I have done
it through a GUI (DBTools), but I would like to have it done via DOS
batch, so that I can schedule it. Does anyone know of any mysql backup
GUIs with command line switches? Or can somebody recommend an
alternate means of getting a local backup?

---

Outfile
Tried to write data from a query out to a file and get told permission denied if I use any directory paths - unix. If I jsut put a name.txt, it exports, but I can't find the file anywhere, yet if I re-export, I'm told the file already exists.
What am I missing?

---

OUTFILE
I am running a cron job that calls a script to export data from a single table on a The query in my script looks like this:Code:

SELECT * INTO OUTFILE "/var/www/path/to/webroot/htdocs/data/data.csv" FIELDS
TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY "
"
FROM tableName;

Is there anything that can be added to that query to allow "data.csv" to be overwritten every time it runs? Right now, I get an error saying it can't be overwritten.

If this cannot be done, I can run another script to delete the file before the next time the 1st script runs, but I was hoping to have it all taken care of in the SQL query.

---

Into Outfile
Im Using Mysql V3.22+ And Im Trying This Query Select ... Into Outfile... Inthe Mysql Control Center But It Cant Work! Does Any 1 Can Tell Me How To Write The Data In The Database Into A File In Client Pc

---

Into Outfile
I am using MySQL v4.1.20. I need to output records to a file the customer can then open with MS Excel. I could not get the PHP script to create this file so I went to PHPMyAdmin to craft the query. Here is what I am trying:
SELECT *
INTO OUTFILE 'd:siteshansenplus.csv'
FIELDS ESCAPED BY ''
ENCLOSED BY '"'
TERMINATED BY ','
FROM plusservices;
There are records in the table. The query runs but returns a null data set and no file is created.

I have tried many variations on the SQL syntax. This version is the only one that will run.

---

SELECT INTO OUTFILE
I was wondering if it were possible to make it so that if a REMOTE user connects to the server and issues a "SELECT INTO OUTFILE" type statement, the file created will be created locally on their machine rather than the server machine.

---

OUTFILE To Blob
i wonder if this is possible:

1) I would like to create a backup of data from a trigger - this backup should load all data regarding a user (from various tables) into blob columns in a other table

now - i know one can use outfile ...... and then load it back into a blob - however , my problem is that i will be using mysql on a shared hosting platform ... writing to file wil not be permitted... i will have to insert the blob directly from a varaible or a select query..

---

Select Into Outfile
Is there a way to have the field descriptions put into an outfile? When I do a Select * from data, the display shows the field names, however when I do the select * into outfile /tmp/test.txt the field names are not in the file.

---

Select Into Outfile
i have the following query :

(used in visual basic)

"select * into outfile ......where id=irand || id=irand2 || id=irand3 || id=irand4 || id=irand5 "

the problem is that in the file created the data in sorted ASC, i need the data to output like in the query, irand,irand2,irand3,irand4 and irand5,

i see order by 1,2,3,4,5 as a solution but it didnt worked.

---

Select Into Outfile
I'm using the comand select * into outfile. It´s working and I get a .txt-file. My problm is, that I need the headers of the columns and right now they are not included. Is there a comand, what tells Mysql to write them in the text-file? MySQL-version is 4.0.

---

Select Into Outfile
Is there a way to have the field descriptions put into an outfile?
When I do a Select * from data, the display shows the field names,
however when I do the select * into outfile /tmp/test.txt the field names
are not in the file.

---

INTO OUTFILE Permission
I have the MySQL server on a shared file server, and would like the internal network users to query the database and store the results in text or Excel format on their local drives (Or a mapped shared drive).

Currently in the test version of this system,i have Mysql running on local host and the scripts on the same system. All the files are traded locally under test.
But when i try to save the generated excel files to a shared location i get an error.

---

SELECT INTO OUTFILE
When I export a query in an .txt file using command SELECT ... INTO OUTFILE how can I also export fields name?

---

Add Headers To Outfile
I'm selecting records into an OUTFILE;

SELECT foo,bar INTO OUTFILE '/tmp/foo' FIELDS TERMINATED BY ',' FROM bar

I would like to add the headers foo and bar to the output file. How can I have mysql do that automatically.

---

Syntax For Outfile
I'm a kinda newbie at MySQL...
I can't seem to gather where my typo is for running a query for outfile into a file. I get confused to what's going on when I do see that it created a file name "Datalist.txt" in the c: drive but its filesize is 0kb -an empty file...What to do??

SELECT TABLE2.file1
lines terminated by '
'
FROM TABLE1, TABLE2
where (TABLE2.TABLE2_id=TABLE1.TABLE2_id) and (TABLE2.file='OutPutFile.txt') limit 100;

---

SELECT INTO OUTFILE
I have data in a table that I would like to select into an outfile.I can do this no problem but the problems lies with the fact I would also like to include the column names and if possible overwrite the file if it already exists.do you know if either of these options is possible?

---

Outfile Of Sql Data
Here the SQL Statement:
mysql> select c.company_id, c.name from company c, co_directory d where c.company_id=d.company_id and d.directory_id=10 into outfile "comp_dir10.txt";
I have executed the above statement in my mysql server. Where the output file will stored. ?

---

Select * Into Outfile
im trying to execute this sql statement

select * into outfile '/tmp/numbers.txt' from tableName where thisField like '%this%'

the statement executes with no errors, but the text file is not created.

when i create a file of the same name on the server, and execute, an error kicks up saying 'the file already exists'
what do you think is happening here, why is the txt file not being created?

---

Select Into Outfile And 2Gb Limit
"select into outfile" stops writing to file at 2Gb size... Where i can turn it off?

---

Variables In Select Into Outfile
I wan to use a variable for the outfile file name in a select into clause. I am
doing something like:

set @fileName="/tmp/result.text"
SELECT col1, col2 INTO OUTFILE @fileName
FIELDS TERMINATED BY ','
FROM test_table;

the interpreter dies at the @fileName...
if I exchange @filename with "/tmp/result.text" it runs fine.

---

SELECT INTO OUTFILE 'filename'
Im trying to create a stored procedure in mysql 5 to dump the result of a select statement into a file. When i give the actual file name, it works perfectly.

I want to give the filename as a variable that is parsed into the procedure, how do I do this.

This is the procedure i used, and it works...

CREATE PROCEDURE `genfile`()
BEGIN
SELECT * FROM vreg INTO OUTFILE 'TEST.TXT';
END

Please tel me how i can parse a filename as a parameter and use it as the filename.
When I do the below, i get an error,

CREATE PROCEDURE genfile(IN filename VARCHAR(100))
BEGIN
SELECT * FROM vreg INTO OUTFILE filename;
END

---

Variables In Select Into Outfile
I want to use a variable for the outfile file name in a select into clause. I am
doing something like:

set @fileName="/tmp/result.text"
SELECT col1, col2 INTO OUTFILE @fileName
FIELDS TERMINATED BY ','
FROM test_table;

the interpreter dies at the @fileName...
if I exchange @filename with "/tmp/result.text" it runs fine.

any suggestions?

---

Dump Data Outfile
is there a way to dump just data to a file similar to the way "load data
infile" reads data?

---

SELECT ... INTO OUTFILE Options
how to use the SELECT ... INTO OUTFILE command to create a csv file of query output. The problem i have is that the resulting file doesnot have column headers. Is there an option that i need to turn on/off to enable the output file have column headers.

---

Is It Possible To Use INTO OUTFILE To Amend A File?
I'm using multiple SELECT ... INTO OUTFILE commands to write the results to seperate text files at the moment. Would prefer if the results of each statement were written to the same file (with each statement amending, rather than replacing the file contents). Is this possible?

---

Variables In Select Into Outfile
I wan to use a variable for the outfile file name in a select into clause. I am
doing something like:

set @fileName="/tmp/result.text"
SELECT col1, col2 INTO OUTFILE @fileName
FIELDS TERMINATED BY ','
FROM test_table;

the interpreter dies at the @fileName...
if I exchange @filename with "/tmp/result.text" it runs fine.

any suggestions?

---

Outfile Name As Parameter In Proc
can I use the outfile_name as a parameter for my proc?

DELIMITER $$

DROP PROCEDURE IF EXISTS `shema`.`sp_name` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_name`(in outfile_name varchar(100))
BEGIN

SELECT * FROM manufacturers
limit 11
INTO OUTFILE outfile_name
FIELDS TERMINATED BY ' '
LINES TERMINATED BY '
' ;

END $$

DELIMITER ;

I get an error

please correct my e-mail address to Robertsmargalit@yahoo.com

---

Syntax Error Using INTO OUTFILE Without FROM
This works:

SELECT "x" INTO OUTFILE "/path/xxx" FROM table-name;

This fails with a syntax error:

SELECT "x" INTO OUTFILE "/path/xxx";

Shouldn't this work?

I want to use this syntax in a UNION to add a constant value row to the results of the first SELECT, and the final SELECT is the one that has to have the INTO OUTFILE. No FROM is required, but the syntax fails.

Is there some requirement for FROM with INTO OUTFILE? Or is this a bug?

---

Korean File Name For Outfile
select * into outfile 'filename' from table;

When I supply a korean/japanese file name in place of 'filename' in the above query, file is getting generated but with non readable krean/japanese file names.

I am running MySQL 4.1.12 with multi lingual support. utf8 as default character set in both client and server.

---

Escaping Backslash For Outfile
I am inserting windows newline charachers into a field with the intention that when i "select into outfile" the field data will be written on several newlines.

It seems problem is that the default behaviour is to escape
the backslashes when writing the outfile so i end up with the literal charachers in the outfile.

example:
Field1
------
data
more data
some more data
------

mysql> select Field1 into outfile "test.txt" from Table;

returns:
data
more data
some more data

instead of:
data
more data
some more data

i have played with the "FIELDS ESCAPED BY ''" option with no luck.

Can this be done?
Or is there a better way?

---

Select Data Into Outfile
Thought I'd post that info here in case someone was looking for it.

Guelphdad - I found this thread:
http://lists.mysql.com/mysql/163185
I understand you may get what you want using batch mode.

Now I ran it directly from my command prompt and saved the file in the current directory (on WinXP, sure the same works for Linux)

echo SELECT * from animal | mysql -udavemysql -p***** testdb > mysqloutfile.txt

I found I had to name the database i was using or of course got the no db selected error message.

---

How To Get MY SQL Query Messages Into A Single Outfile
I want to run set of queries and want to get the messages after executing them into one outfile.

e.g when I run query "Select * from accounts", when the query is successfully executed, it gives message "1000 rows in set (0.01) sec".

I want to get all such messages from different query sets into one outfile.

Can anybody guide me on how to get the same?

---

MySQL Outfile Escaping Characters
SELECT people_id, people_fname, people_sname FROM people WHERE people_id IN (1, 2, 3) INTO OUTFILE '/home/test.csv' FIELDS ESCAPED BY '' OPTIONALLY ENCLOSED BY '"' FIELDS TERMINATED BY ',' LINES TERMINATED BY '
'
When executing this statement in SQL Yog i get this error...

Error Code : 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 'FIELDS TERMINATED BY ',' LINES TERMINATED BY '
'' at line 1

This is being used for a java service to export results to a csv file. I honestly can't see what is wrong with this. I want to use a backslash to escape chars that need escaping and a double quote to enclose fields. Both of those characters for the query need to be escaped i would think.

---

Accent Problem With Outfile Command
I'm using mysql 4.1.
When I insert data with special french character with accent é, à...
and that I use select command to see the line, I can see the
characters correctly.
But when I use the outfile command the characters are not in the
correct format.

I tried several charset and collation, but it seems that it's not the
problem.

---

Select ... Outfile ... Fixed Length?
I have query that I export into a file using the "outfile" syntax. I can
use the following lines of code to create a delimited file but I want to
know if I can create a fixed length file? Any ideas?

delimited file:
Select GivenName, Surname, Address, City, State, ZipCode
into outfile 'xx.csv' fields terminated by ',' lines terminated by '
'
from kbm
where zipcode = '12345'

---

SELECT INTO OUTFILE Access Denied
I am trying to make a big text file from an old forum version I use on my virtual LAMP server.

For this I am using the following statement:

SELECT threads INTO OUTFILE '/home/me/threads.txt' FROM forums;

I get the following error:

ERROR 1045 (28000): Access denied for user 'me'@'ip-adres' (using password: YES)

It seems I don't have acces to write to a file via mysql. My ISP covered that perfectly, but is there a work arround?

If I do:

SELECT threads FROM forums;

All the threads scroll by on my terminal. But they are to big to fit in the terminal for a simple copy paste.........

---

SELECT INTO OUTFILE With Column Headers
I've just written a stored procedure that uses the SELECT INTO OUTFILE command. I'd like the file it writes to contain the column headers similar to the way that they show up from a command line call.

---

How Can I Print The Results Of A Consult Into An Outfile?
I've been trying to print the results of my consults to the database I created. Although I've seen that the query it's OK, I can't find the file where are my results. Do you know how can I print my results to one outfile or where I could find it?

---

Running SELECT INTO OUTFILE As A CRON Job
I need to run the "SELECT * INTO OUTFILE" command as a CRON job. I can do this via command line and it works fine, but when I add it to my CRON file it does not run.

My problem is I dont know how to create a CRON job or script that logs into mySQL and then runs the "SELECT * INTO OUTFILE" command.

I am currently running "mySQL DUMP" as a CRON job without any problem.

---

MySql OUTFILE, ERROR 1 (HY000): Can't Create/write To File
I have problems in exporting tables to a file in MySql using OUTFILE option. MySql machine is a remote one. I got the error

ERROR 1 (HY000): Can't create/write to file '/tmp/scshekaran/bcp_table.txt' (Err 2)

---

"select Into Outfile " Issue
I am running MYSQL 5.0 ON WINXP pro.

I have the following procedure:

---

Remote Connection
How do i connect to a remote (webserver) Mysql from the windows command line?

---

Remote Control
I have MySQL running on a desktop computer that I log into through logmein.com and this is not the best solution. How can I connect remotely to use the MySQL Command Line Client on my desktop so I don't have to wait so long for each line to appear? I should also point out that this computer is on my LAN, so I shouldn't have to configure the router.

One problem I am running into right now is that when I try to make a new table, I keep getting error 1064 (42000) and I'm not sure why because everything looks correct. Code:

---

Remote Access
I have a MySQL server running on my iMAC and am able to access my databases locally. What steps do I have to do to allow access from a remote PC thru TCP/IP ? The manuals say it can be done but don't specify what is involved.

---

Accessing Remote DB
i want to inser data into a remote dB. I cant for the life of me get it to connect. The mysql_connect syntax is straightforward. Ive tried the ip number and domain name, the user name and pass are correct. No firewall probs.

Always an error lilke this:
"Can't connect to MySQL server on '130.220.33.14' (10054)"

I use phpdev to run the files at home. Is there maybe a config I have to change on it to access remotely?

---

Remote Mysql
I'm having a weird problem and hours of googling could not help me solve this, so maybe someone here can help me out? I'm by no means experienced with mysql, it just worked all the time so I didnt have to bother looking into it too much :)

We have 2 servers for our forum, one "powerful" server used as webserver where phpBB runs on, and one server that just holds the database (we got that after we found that code & database on 1 server caused overload).

So far, everything went fine for nearly a year now and I had nothing to do with all of it, all I had to do back then was edit a variable on my.cnf on the database-server I think, and then it worked.

Now the webserver's power unit broke, and after that the database had all these little errors on several tables, so I went to phpMyAdmin, marked them all and clicked "repair" - probably/like/surely a stupid move [my excuse: i was tired & in a rush as I did this from work] as the tables were rather big in size (several hundred mb). As expected, the forums didnt work during that, all I got was the typical phpBB "could not connect to database"-message. This went on for ages without anything happening, the tables just stayed "in use" (I could still connect to phpMyAdmin afterall).

After it wouldnt work, I restarted mysql, i restarted the server - those tables still stayed in use, so I flushed the whole database and installed a backup into another database, changed forum-configuration to read from the new db and thought things would be fine, but they weren't.

It just won't connect to mysql it seems, at first I thought some table must be still corrupted, but after some trying out, I realized that the database/forums DO work when being addressed as "localhost" from phpBB-files running on the database-server. When I change the configuration to connect via the IP of the server instead, it will give me the "Could not connect"-error just as it does when I try to connect from the webserver.

I checked netstat on the db-server to see if the the mysql-port was open and being listened to, and got this:
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
tcp 0 0 0.0.0.0:3306 0.0.0.0:* LISTEN

this does mean it's theoretically working, right?

I also tried to grant all priviledges again on mysql, but didnt change anything either - do you have any idea what I could do? This is driving me nuts (especially as I can see the forums running fine when connected to by localhost).

As I said - this happened suddenly and has been working before for nearly a year, that's what puzzles me most, I didnt change anything on that server mysql runs on!

---

Remote Connection
How do i connect to a remote (webserver) Mysql from the windows command line?

---

Remote SQL Connection
I am trying to access my database on the server with a MYSQL front-end
client on my computer (not the server). I have the correct username,
password and database name. I tried every possible hostname, even its ip
addy, but each time, I am rejected by the server, which says something
like - access denied. I can access the database in my php code with
'localhost', but that's of course because the code is actually running on
the server. Is there anything I have to specify on the MYSQL server in order
to gain access to it remotely?

---

Remote MySql
I have make a php application and i want to use mysql for the database in different computer, the computer ip is 10.20.37.77 and mysql port 3306, the password = "root"

here is the code

$MySQL_Username = "root";
$MySQL_Host = "10.20.37.77";
$MySQL_Passwd = "root" ;
$MySQL_DB = "squid";
$db = mysql_connect( $MySQL_Host , $MySQL_Username , $MySQL_Passwd ) ;

the error :
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'root'@'xeon' (using password: NO) in D:Program

if the php application use mysql in localhost it work
$MySQL_Username = "root";
$MySQL_Host = "localhost";
$MySQL_Passwd = "" ;
$MySQL_DB = "squid";
$db = mysql_connect( $MySQL_Host , $MySQL_Username , $MySQL_Passwd ) ;

---