Forums

Hi, I'm trying to backup my tables and mysqldump returns a 'mysqldump: Got error: 1045: Access denied for user 'ehebel'@'%' (using password: YES) when executing 'SELECT INTO OUTFILE'. And I did make sure that all the data was correct. Is this option disabled in PythonAnywhere?

Thanks, Esteban

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

@Cartroo thanks for your answer. I had been struggling with a similar issue and now it makes sense

Hi there,

If you are too lazy to hack together a script to convert from mysqldump .sql files to csv, I just found a python github repo that converts sql dumps into csv files.

After downloading the repo:

git clone https://github.com/jamesmishra/mysqldump-to-csv.git

You just need a single command to download your data and back it up to csv.

mysqldump -u <username> -h <host-address> --password=<password> '<database>' | python mysqldump-to-csv/mysqldump_to_csv.py > "output$(date +%s).csv"

For the above command, you need to put in your username (eg: conrad), db host-address (eg: conrad.mysql.pythonanywhere-services.com), your mysql password that you had set in our databases tab, and also your database name (eg: conrad$default) which must be surrounded in single quotes.

There will be a timestamped output csv that you can download from the files tab.