Performing a SELECT INTO OUTFILE
requires the FILE
privilege since it creates an output file on the server machine. I'd expect this privilege to be disallowed because it's a potential security risk, and also I suspect that the MySQL service on PA doesn't share a filesystem with your user account so it wouldn't be particularly useful.
Are you using the --tab
option? This is the only reason of which I'm aware that mysqldump
would write a data file on the server, but it's not really a viable backup mechanism unless you're the administrator of that server. Personally I'm not a fan of it even then for various reasons, but it's a matter of preference.
In any case, you'll have to use mysqldump
to dump in SQL format instead, which doesn't require any special privileges beyond read access to the database and schema. See this page for details - the first two linked sections discuss dumping and restoring in SQL format, which is what you'll need, and the subsequent two describe using --tab
, which is presumably what you're attempting now and which won't work for reasons I just outlined.
EDIT
As an aside, I forgot to mention that mysqldump
allows use of the --xml
option to dump data in an XML format, which may be more useful to you than raw SQL statements if you want to parse the data yourself. This can be done on the client with no special privileges. Quite why the MySQL developers felt that XML format was useful on the client but CSV was not is something you'd have to take up with them. This will not be useful for backup and restore since the LOAD XML
command was added in MySQL 5.5 whereas the PA server is 5.1 - also, it requires the same FILE
privilege which standard users are not granted.
Also, if you don't mind dumping one table at a time, you can also use the batch mode of the standard mysql
client to get data in tab-separated format:
mysql -B -h mysql.server -u username -p -e 'SELECT * FROM testTable'
To omit the column titles, add -N
:
mysql -BN -h mysql.server -u username -p -e 'SELECT * FROM testTable'
Just as with mysqldump
, if you're running these from script, you'll need --password=xxx
instead of -p
to avoid the interactive prompt or, better yet, create a .my.cnf
file in your home directory like this:
[client]
user=username
password=xxx
... and chmod 0600 ~/.my.cnf
.
Again, not very useful for backup because you can't restore it easily. For backup, just dump the SQL - it's the most commonly used backup mechanism for MySQL databases (until they become really massive).