How to export data from MySQL or MariaDB to CSV format

CSV (Comma Separated Value) is a plain-text format for representing tabular data. It is widely supported by spreadsheet applications, text editors, and various data analysis tools. Exporting a table from a MySQL or MariaDB database to CSV allows the data to be used outside the database system. This makes it easier to share, analyze, or import into other tools.

When exporting from MySQL or MariaDB to a CSV file, the data can be selected using SQL queries. The output must follow the correct structure with properly defined field and line terminators to ensure compatibility with other systems. It's important to use the correct syntax and have access to the required directories for the export.

System settings like secure_file_priv and proper file permissions need to be configured before performing an export. Failure to do so can result in errors or restricted access to specific directories. Ensuring proper configuration allows for a smooth and correct export of data from the database to the desired file location.

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.

   $ 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. Select the database that contains the table.

   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

   Make sure to switch to the correct database before proceeding with the export.

4. View the structure of the table if necessary.

   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)

   The table structure will help you ensure you're exporting the correct data.

5. Run the SELECT query to choose the data for export.

   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)

   Refine your query to export only the relevant data.

6. Check the secure_file_priv setting to confirm the export directory.

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

   If secure_file_priv is enabled, only the specified directory will be allowed for export. Ensure you have write access to this directory.

7. Export the data 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

8. Verify the contents of the exported CSV file.

   $ 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"