Selecting data into a file and table

You can save the output into a file using the SELECT INTO OUTFILE statement.

You can specify the column and line delimiters, and later you can import the data into other data platforms.

How to do it...

You can save the output destination as a file or a table.

Saving as a file

• To save the output into a file, you need the FILE privilege. FILE is a global privilege, which means you cannot restrict it for a particular database. However, you can restrict what the user selects:

mysql> GRANT SELECT ON employees.* TO 'user_ro_file'@'%' IDENTIFIED  WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> GRANT FILE ON *.* TO 'user_ro_file'@'%' IDENTIFIED  WITH mysql_native_password AS '*EBD9E3BFD1489CA1EB0D2B4F29F6665F321E8C18';
Query OK, 0 rows affected, 1 warning (0.00 sec)

• On Ubuntu, by default, MySQL will not allow you to write to file. You should set secure_file_priv in the config file...