Example of exporting MariaDB table into CSV

Here is an example of exporting a table to CSV file with a timestamp.

The query is built dynamically or else the OUTFILE will not work.

That can be used to archive old data that should no longer appear in the DB.

Notice, that the path of the output file is on the DB server.

SET @TS = DATE_FORMAT(DATE_SUB(CURDATE(), INTERVAL 3 MONTH),'%Y_%m_%d');

SET @FILENAME = CONCAT('C:\\\\Audit\\\\audit_', @TS, '.csv');

SET @CMD = CONCAT(

	"SELECT ra.Id, ra.OperationTime,ra.ClientName,ru.UserName ",

		"INTO OUTFILE", " '", @FILENAME, "' ",

		"FIELDS TERMINATED BY ',' ",

		"OPTIONALLY ENCLOSED BY '""' ",

		"ESCAPED BY '' ",

		"FROM RaTable ra LEFT JOIN RuTable ru ON ra.UserId=ru.Id ",

		"WHERE ra.OperationTime < DATE_SUB(CURDATE(), INTERVAL 3 MONTH) ");

PREPARE statement FROM @CMD;

EXECUTE statement;

We can also run it from CLI like this:

mysql --host=127.0.0.1 --user=username --password=somepassword DBName < script.sql