I'm working with a server that I'm helping someone with to extract data from their database. The database server is no a shared hosting plan as is very very slow to run queries. Query that takes 3 seconds on my machine takes 5-7 hours on the server. So, I devised a plan of exporting the data to a CSV file using the INTO OUTFILE syntax.

The now, the problem is that it seems only the mysql user has access the directly /var/lib/mysql-files, rightly so.

Seeing as root the SSH-ed in user is not a sudoer, how can I copy this file to the current user's home directory? Or is there any other way of exporting the data to CSV, to a directory that I could hope to give mysql write access to?

NOTE: phpMyAdmin times out because the query takes too long. I'd really like to look for a solution first before re-running the query because 5-7 is a lot of time.

  • Someone will have to ask the hosting company for a copy of that file, since only mysql and root can access it. Can you access the database remotely? Dec 15, 2021 at 10:36
  • @GerardH.Pille No. I cannot access the DB remotely. I would have used Workbench in that case. Also, weirdly enough, the custodians of the server tell me that they can no longer get support from the provider. I think they are lying. But I'm just thinking of creating a script to do the export for us. Dec 15, 2021 at 11:35
  • People are slow in discovering the joys of the cloud. In your case I would develop a web api allowing to fetch his data. Better slow than never? Dec 15, 2021 at 12:14
  • Have you tried specifying a different destination path, e.g. ... INTO OUTFILE '/tmp/somefile.csv'?
    – mustaccio
    Dec 15, 2021 at 13:17
  • @GerardH.Pille There's an API on the server. Everything on the server is so broken. The reason why we have opted for CSV is to get around the API throwing errors. Sadly, the I.T team there is so clueless that I have to leave my job from the company I work for and practically work for these guys to come up with a solution so the team on our side can move on. I just decided I'll create a PHP script to save the CSV to the user's home directory. Dec 15, 2021 at 14:14


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.