How to export MySQL or MariaDB table to CSV format

CSV (Comma Separated Value) files are a popular data format for data storage due to their compatibility and simplicity. These files can be opened and manipulated in text editors and spreadsheet programs, presenting data in columns and rows for easy analysis, which makes it convenient for data sharing and analysis.

SQL databases are excellent for data storage and analysis but are not easily shareable. Exporting data from a MySQL or MariaDB database as a CSV file is a practical data extraction and sharing solution. The required data can be selected using SQL queries and saved in CSV format.

Access to the table and the destination folder is required to export data from a MySQL or MariaDB database to a CSV file. The formatting of the CSV file must also be specified, including the terminator, encloser, and line terminator characters. This ensures the data in the CSV file is formatted correctly and readable by other applications.

1. Make sure that you have a appropriate access to the directory that you want to export the data to.
2. Log in to the MySQL or MariaDB server using the command-line interface or a graphical tool such as phpMyAdmin.

   $ sudo mysql -u root
   [sudo] password for user: 
   Welcome to the MySQL monitor.  Commands end with ; or \g.
   Your MySQL connection id is 8
   Server version: 8.0.32-0ubuntu0.22.10.2 (Ubuntu)
   
   Copyright (c) 2000, 2023, Oracle and/or its affiliates.
   
   Oracle is a registered trademark of Oracle Corporation and/or its
   affiliates. Other names may be trademarks of their respective
   owners.
   
   Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

3. Switch to the database that contains the table you want to export (optional).

   mysql> USE employees;
   Reading table information for completion of table and column names
   You can turn off this feature to get a quicker startup with -A
    
   Database changed

4. Check table structure (optional).

   mysql> describe salaries;
   +-----------+------+------+-----+---------+-------+
   | Field     | Type | Null | Key | Default | Extra |
   +-----------+------+------+-----+---------+-------+
   | emp_no    | int  | NO   | PRI | NULL    |       |
   | salary    | int  | NO   |     | NULL    |       |
   | from_date | date | NO   | PRI | NULL    |       |
   | to_date   | date | NO   |     | NULL    |       |
   +-----------+------+------+-----+---------+-------+
   4 rows in set (0.01 sec)

5. Query the data for export using SELECT statement.

   mysql> SELECT * FROM salaries;
   +--------+--------+------------+------------+
   | emp_no | salary | from_date  | to_date    |
   +--------+--------+------------+------------+
   |  10001 |  60117 | 1986-06-26 | 1987-06-26 |
   |  10001 |  62102 | 1987-06-26 | 1988-06-25 |
   |  10001 |  66074 | 1988-06-25 | 1989-06-25 |
   ##### snipped
   | 499999 |  70745 | 1999-11-30 | 2000-11-29 |
   | 499999 |  74327 | 2000-11-29 | 2001-11-29 |
   | 499999 |  77303 | 2001-11-29 | 9999-01-01 |
   +--------+--------+------------+------------+
   2844047 rows in set (0.46 sec)

6. Refine your query if necessary.

   mysql> SELECT * FROM salaries WHERE salary > 150000;
   +--------+--------+------------+------------+
   | emp_no | salary | from_date  | to_date    |
   +--------+--------+------------+------------+
   |  43624 | 151115 | 1998-03-23 | 1999-03-23 |
   |  43624 | 153166 | 1999-03-23 | 2000-03-22 |
   |  43624 | 153458 | 2000-03-22 | 2001-03-22 |
   ##### snipped
   | 493158 | 151565 | 2000-05-05 | 2001-05-05 |
   | 493158 | 152208 | 2001-05-05 | 2002-05-05 |
   | 493158 | 154376 | 2002-05-05 | 9999-01-01 |
   +--------+--------+------------+------------+
   36 rows in set (0.33 sec)

7. Check if secure_file_priv is enabled and the location where the export file can be saved to.

   mysql> SHOW VARIABLES LIKE "secure_file_priv";
   +------------------+-----------------------+
   | Variable_name    | Value                 |
   +------------------+-----------------------+
   | secure_file_priv | /var/lib/mysql-files/ |
   +------------------+-----------------------+
   1 row in set (0.02 sec)

8. Store the output of the qeury to a CSV file using the INTO OUTFILE clause.

   mysql> SELECT * INTO OUTFILE '/var/lib/mysql-files/myfile.csv'
       ->  FIELDS TERMINATED BY ',' 
       ->  ENCLOSED BY '"' 
       ->  LINES TERMINATED BY '\n' 
       ->  FROM salaries 
       ->  WHERE salary > 150000;
   Query OK, 36 rows affected (0.32 sec)

   In this example, the “FIELDS TERMINATED BY ','” clause specifies that the fields should be separated by commas, the “ENCLOSED BY '”'“ clause specifies that the fields should be enclosed in double quotes, and the “LINES TERMINATED BY '\n'” clause specifies that each row should be terminated by a newline character.

   Writing the file to inappropriate location will throw the following error:

   ERROR 1290 (HY000): The MySQL server is running with the %%--secure-file-priv%% option so it cannot execute this statement

9. View the file's content to verify that it was created correctly.

   $ sudo cat /var/lib/mysql-files/myfile.csv
   "43624","151115","1998-03-23","1999-03-23"
   "43624","153166","1999-03-23","2000-03-22"
   "43624","153458","2000-03-22","2001-03-22"
   ##### snipped
   "493158","151565","2000-05-05","2001-05-05"
   "493158","152208","2001-05-05","2002-05-05"
   "493158","154376","2002-05-05","9999-01-01"