By default, queries are returned \t (tab) delimited<strong>INTO OUTFILE</strong> (Server-side)The INTO OUTFILE command is a SQL statement executed by the database server process itself.Where it writes: The file is written to the database server’s file system. Requirements: This requires the database user to have the FILE permission and for the secure_file_priv system variable to be configured to allow writing to the target directory. The database’s system user must also have operating system permissions to write to that directory.Performance: It can be very fast for large datasets because the data doesn’t need to be sent over the network to a client. Use case: It’s ideal for server-side backups or exports where you want the file generated directly on the database machine. mariadb -e "..." > file (Client-side)Using the mariadb command-line client with output redirection is a function of the shell on your local machine.Where it writes: The file is written to the local file system where you execute the command. Requirements: This only requires your database user to have SELECT permissions. It does not require any special database permissions for file writing. The file system permissions are managed by the operating system user running the command.Performance: Performance depends on the network speed between the client and server, as the entire result set must be transferred over the connection. For very large datasets, INTO OUTFILE is often faster.Use case: This is a safe and reliable method for exporting data to your local machine without needing elevated database privileges. |
| # Export query to csv file mariadb -u [user] -p -D [mdb] < [input.sql] | sed ‘s/\t/,/g’ > [output.csv] |
mariadb --user=[user] --password [mdb] -B -e "[sql_query];" | sed "s/\t/,/" > [output.csv] |
| import mariadb try: conn = mariadb.connect( user=”your_user”, password=”your_password”, host=”your_host”, port=3306, database=”your_database” ) cursor = conn.cursor() except mariadb.Error as e: print(f”Error connecting to MariaDB Platform: {e}”) # Handle error # Assuming 'create_table_sql' is the extracted CREATE TABLE statement for your table temp_table_name = "temp_my_table" # Choose a unique name create_temp_table_sql = create_table_sql.replace("CREATE TABLE `my_table`", f"CREATE TEMPORARY TABLE `{temp_table_name}`") <br>cursor.execute(create_temp_table_sql)# Assuming 'insert_statements' is a list of extracted INSERT statements for insert_sql in insert_statements: modified_insert_sql = insert_sql.replace("INSERT INTO `my_table`", f"INSERT INTO `{temp_table_name}`") cursor.execute(modified_insert_sql) conn.commit()cursor.execute(f"SELECT * FROM `{temp_table_name}`") for row in cursor: print(row)cursor.close() conn.close() |
| SELECT column1, column2, … FROM your_table WHERE condition INTO OUTFILE ‘/path/to/your/file.csv’ FIELDS TERMINATED BY ‘,’ ENCLOSED BY ‘\”‘ LINES TERMINATED BY ‘\n’; |
| echo “SELECT @x := NOW(); SELECT @x+INTERVAL 23 HOUR;” | mariadb -uroot -p Enter password: @x := NOW() 2025-10-10 21:46:39 @x+INTERVAL 23 HOUR 2025-10-11 20:46:39 |
| MariaDB [wp_db]> select CONCAT_WS(‘,’,id,post_date,post_title) from wp_posts; +————————————————————————————————-+ | CONCAT_WS(‘,’,id,post_date,post_title) | +————————————————————————————————-+ | 3,2022-12-21 05:17:00,Privacy Policy | | 7,2022-12-23 19:24:33,Custom Styles | | 9,2022-12-29 16:39:51,Custom Styles | | 10,2022-12-29 16:40:24,Custom Styles | | 13,2023-01-11 05:22:54,Draft-Terms | | 14,2023-01-11 05:22:54, | | 15,2023-01-11 05:26:47,Disclosure | | 16,2023-01-11 05:26:47,Disclosure |