Practical MySQL & MariaDB

How to Export CSV in MySQL: SELECT INTO OUTFILE, Version Differences, Errors, and Security Best Practices

SQL Basics
目次

1. Introduction

CSV (Comma Separated Values) is a widely used format for data export, migration, and backup. MySQL provides functionality to export data in CSV format, making it useful for efficient data management and analysis. This article explains in detail how to export data to CSV format using MySQL, including differences between versions, how to handle error messages, and important security considerations.

Execution Environment

This article is based on MySQL 8.0, but it also covers differences when using MySQL 5.x. Since behavior and configuration may vary by version, be sure to follow the appropriate procedures according to the version you are using.

2. Basic Steps to Export CSV in MySQL

To export data in CSV format in MySQL, use the SELECT INTO OUTFILE command. This command is the standard method for saving query results to a file in CSV format.

2.1 Basic Syntax

SELECT * FROM table_name INTO OUTFILE '/path/to/file.csv'
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

2.2 Command Details

  • SELECT *: Selects all data in the table. If you want to export specific columns, specify the column names instead.
  • INTO OUTFILE: Saves the query result as a file to the specified path. The path must be an absolute path.
  • FIELDS TERMINATED BY ',': Sets the delimiter between columns to a comma.
  • ENCLOSED BY '"': Encloses each field in double quotes. This ensures correct handling even if the data contains commas or line breaks.
  • LINES TERMINATED BY '\n': Separates each row with a newline character. In Windows environments, '\r\n' may be used instead.

3. Version-Dependent Differences

3.1 Differences Between MySQL 5.x and 8.x

There are several important differences between MySQL 5.x and 8.x. In particular, pay attention to the following points regarding encoding and file output features.

  • Handling of Encoding:
  • MySQL 5.x uses utf8 as the default encoding. However, it supports only up to 3-byte characters, meaning it cannot correctly handle emojis or certain special characters. Therefore, it is recommended to use utf8mb4, which supports up to 4-byte characters. However, support for this in 5.x is limited.
  • MySQL 8.x uses utf8mb4 as the default encoding, allowing proper handling of emojis and all multibyte characters.
  • secure_file_priv Enhancements:
  • In MySQL 8.x, security has been strengthened, and file writing is strictly controlled by secure_file_priv. An error occurs if you attempt to write a file outside the permitted directory.
  • Although similar settings exist in 5.x, they may be less strict depending on configuration, and proper setup may be required.

3.2 CSV Output Performance

MySQL 8.x includes performance improvements, particularly noticeable when exporting large datasets to CSV. While CSV export is also possible in 5.x, optimizations in 8.x enable faster and more efficient data output.

4. Important Notes When Exporting CSV

4.1 File Write Permissions and secure_file_priv

secure_file_priv is a setting that restricts the directories MySQL can access for file operations. If this is configured, writing outside the specified directory is not allowed. To check this setting, use the following command:

SHOW VARIABLES LIKE 'secure_file_priv';

This setting limits the directories where files can be safely written. If you do not specify an allowed directory, you will encounter an error message like the following.

4.2 Encoding Issues

When exporting data containing multibyte or special characters (such as Japanese text or emojis), encoding settings are critical. By using utf8mb4, all characters can be exported correctly. MySQL 5.x often uses utf8, but upgrading to 8.x makes it easier to avoid encoding-related issues.

5. Error Messages and Solutions

Various errors can occur during CSV export. Below are common error messages and their solutions.

5.1 secure_file_priv Error

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

This error occurs when attempting to write a file to a directory that is not permitted by the secure_file_priv setting. You must either export the file to an allowed directory or modify the configuration.

5.2 Write Permission Error

ERROR 13 (HY000): Can't get stat of '/path/to/file.csv' (Errcode: 13 - Permission denied)

This error occurs when write permissions are insufficient. To set appropriate permissions, use the following command:

sudo chmod 755 /path/to/directory

Security Note: Avoid using chmod 777. Granting write permissions to all users poses a security risk. It is recommended to apply the minimum necessary permissions.

6. Additional Security Considerations

6.1 Managing File Permissions

When exporting CSV files in MySQL, it is essential to pay attention to write permissions and configure appropriate permissions. Especially on public servers, granting excessive permissions creates security risks. It is recommended to apply minimal permissions such as chmod 755 and ensure that only administrators or specific users can access the files.

6.2 Using secure_file_priv

secure_file_priv restricts the directories where MySQL can read and write files, making it crucial for preventing data leaks and unauthorized access. This setting is managed in the MySQL configuration file (my.cnf or my.ini). Clearly specifying the permitted directory helps reduce security risks.

7. Summary

Exporting CSV files in MySQL is extremely useful for data migration and backup, but features and performance differ depending on the version. In particular, MySQL 8.x offers improved performance optimization and enhanced security. Handling of encoding and directory restrictions during CSV export has also been refined.

On the other hand, MySQL 5.x differs slightly in encoding configuration and the handling of secure_file_priv, so it is necessary to understand these differences and respond appropriately. It is recommended to use utf8mb4 and carefully manage security settings when exporting data.

Additionally, by properly configuring file permissions and using secure_file_priv to restrict file access, you can minimize the risks of data leaks and unauthorized access. Especially when working on public servers, apply minimal permission settings (for example, chmod 755) and ensure that only administrators or necessary users have access.

7.1 Key Practical Points

  • Understand Version Differences: Recognize the differences between MySQL 5.x and 8.x, especially in encoding and file output behavior.
  • Set Appropriate Permissions: Avoid excessive permissions. Configure file permissions to the minimum required level. In particular, avoid chmod 777 and use restrictions such as chmod 755.
  • Leverage secure_file_priv: Configure secure_file_priv to properly restrict directories accessible by MySQL and reduce security risks.
  • Verify Encoding: When exporting CSV files containing multibyte characters or emojis, it is recommended to use utf8mb4.

By keeping these points in mind, you can safely and efficiently utilize MySQL’s CSV export functionality.