256 Kilobytes

TL;DR: Run this command from your local machine:

  • mysql -u root -p yourdatabase -e "select * from table_name;" > file.csv

How to export a MySQL or MariaDB Query to a CSV

Write the Query

First, plan out your query. For example:

  • select * from table_name;

Step 2: Modify it to Export

  • select * from table_name INTO OUTFILE 'file.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n';

Step 3: Move the File to Your Local Machine

When you export from MySQL or MariaDB, the export goes to the remote server not to your local machine. This is true even if you are running a local MySQL/MariaDB instance.

To transfer the file from the remote host to your local machine:

If your database is running locally, run this to transfer the file to your home directory:

  • sudo mv /var/lib/mysql/yourdatabase/file.csv ~/file.csv

If this command doesn’t work, see the section “Where are local MySQL/MariaDB databases stored?”

Step 4: Change the CSV’s File Permissions

The output CSV file will likely/plausibly be created with locked-down file permissions. Once you’ve moved the file to your local machine/home directory, run this command to change the permissions (if needed):

  • sudo chmod 777 fuck*.csv

Make sure to run the chmod command with the “sudo” in it or you’ll get errors such as:

  • chmod: changing permissions of 'file.csv': Operation not permitted

Troubleshooting CSV Export Errors

Issues Writing to Directories

As mentioned, the export goes to the remote machine. If you try to run an export and are getting errors like these, it is likely because those directories are on your local machine, but not the remote machine (or the remote machine has no privileges).

  • MariaDB [yourdatabase]> select * from table_name INTO OUTFILE ‘~/file.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;
    • ERROR 1 (HY000): Can’t create/write to file ‘/nonexistent/file.csv’ (Errcode: 2 “No such file or directory”)
  • MariaDB [yourdatabase]> select * from table_name INTO OUTFILE ‘/file.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;
    • ERROR 1 (HY000): Can’t create/write to file ‘/file.csv’ (Errcode: 13 “Permission denied”)

File Already Exists

You may get this error if you attempt to export the file a second time without moving or deleting the previously created CSV:

  • MariaDB [yourdatabase]> select * from table_name INTO OUTFILE ‘file.csv’ FIELDS TERMINATED BY ‘,’ LINES TERMINATED BY ‘\n’;
    • ERROR 1086 (HY000): File ‘file.csv’ already exists

There is no direct way to disable this error message or to force an overwrite. From the documentation:

The SELECT … INTO OUTFILE ‘file_name’ form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed. The character_set_filesystem system variable controls the interpretation of the file name.
Source: MySQL 5.5 Reference Manual: SELECT … INTO Syntax

However, it is possible to duct tape together some bash/terminal script to handle this for you on the fly. You can run MySQL commands from the terminal directly like this and then redirect the standard output to a file, which will allow for an overwrite:

  • mysql -u root -p yourdatabase -e "select * from table_name;" > file.csv

This also lets you skip steps 3 and 4, so probably just do this instead.

Also, you would think that you could have to specify the “FIELDS TERMINATED BY” and “LINES TERMINATED BY” in MySQL (while simultaneously not having an outfile), but this seems to work fine without specifying them at all.

Where are local MySQL/MariaDB databases stored?

If you’re running MariaDB/MySQL from localhost, the export will go to the database’s installation folder. On Ubuntu, the file will (by default) be at:

  • /var/lib/mysql/yourdatabase/file.csv

If you need to find the file, run this command to search for files from the computer’s root directory:

  • sudo find / -name "file.csv"

Note that the sudo is required, since this command will need to search through directories that require administrator permissions. Otherwise, you’ll get errors like the following:

  • find: ‘/run/user/1000/gvfs’: Permission denied

And the same errors if you run ls without sudoing:

  • ls: cannot open directory '/var/lib/mysql/yourdatabase/': Permission denied
Download more RAM. 🐏 ⨉ 0 Posted by August R. Garcia 2 months ago 🕓 Posted at 08 March, 2019 16:39 PM PST

Profile Photo - August R. Garcia August R. Garcia LARPing as a Sysadmi... Portland, OR
🗎 135 🗨 706 🐏 164
Site Owner

Grahew Mattham

August Garcia is some guy who used to sell Viagra on the Internet. He made this website to LARP as a sysadmin while posting about garbage like user-agent spoofing, spintax, the only good keyboard, virtual assitants from Pakistan, links with the rel="nofollow" attributeproxies, regular expressions, HTML and CSSsin, the developer console, and probably some other trash.

Account created 5 months ago.
135 posts, 706 comments, and 164 RAMs.

Last active 1 day ago:
Posted thread Dear Websites -- Update Your "Powered by vBulletin® Version 3.7.3" Websites; The current version is 5.5.2

Post a New Comment

To leave a comment, login to your account or create an account.

Do you like having a good time?

Read Quality Articles

Read some quality articles. If you can manage to not get banned for like five minutes, you can even post your own articles.

View Articles →

Argue with People on the Internet

Use your account to explain why people are wrong on the Internet forum.

View Forum →

Vandalize the Wiki

Or don't. I'm not your dad.

View Wiki →

Ask and/or Answer Questions

If someone asks a terrible question, post a LMGTFY link.

View Answers →

Make Some Money

Hire freelancers and/or advertise your goods and/or services. Hire people directly. We're not a middleman or your dad. Manage your own business transactions.

Register an Account
You can also login to an existing account or recover your password. All use of this site is subject to terms outlined in the terms of service and privacy policy.