Search code examples
mysqlgcloud

Unable to run SELECT INTO OUTFILE on Google MySQL and cannot Grant File to user root. Access denied

I have a Google MySQL database and I can login from my local MySQL workbench. I need to export the data from it using command SELECT... INTO OUTFILE.

I have tried this:

grant all privileges 
  on mydatabase.* 
  to 'root'@'%' 
  identified by 'mypassword';
flush privileges;

which works, but when I next ran this:

GRANT FILE ON *.* TO 'root'@'%' identified by 'mypassword';

I got this error and still cannot SELECT INTO OUTFILE

Error Code: 1045. Access denied for user 'root'@'%' (using password: YES)

Further check:

  • show user() gives me root@[my PC's IP address]
  • show current_user() gives me root@'%'

Please advise how I can run SELECT INTO OUTFILE

Solution

  • As you can see here, exporting in CSV format is equivalent to run SELECT... INTO OUTFILE query.

    On the other hand, in case you want to do it locally, once you are connected to the DB, as you can see here, you can use the "LOAD DATA LOCAL INFILE" statement in mysql client to loads a local file to the database.