Database Best Practices
Exporting
Exporting
Basics of Exporting
The following is a basic overview of the export process. Please see the subsequent example for commands that are to be used. Complete documentation for the SELECT … INTO FILE command to be run within MariaDB may be found at https://mariadb.com/kb/en/library/select-into-outfile/.
The general process is as follows:
- (1) Prepare export folder such that it is accessible to everyone
- (2) Within MariaDB, export data
- (3) Change access rules to export folder
- (4) Copy your data out of the export folder
- (5) Remove export folder
Please note that you will be copying your data to a new file, not moving it. This ensures you are the owner of the file, as opposed to the MariaDB server.
Your exported data will NOT include column headers/names! Please feel free to contact RCS at research@hbs.edu to discuss how to append column headers, or for any other questions you may have regarding data export.
Example of Exporting
We will use the same account and database as above in Example of Importing to illustrate exporting:
- MariaDB username = jharvard
- MariaDB database = jharvard_database
- MariaDB database table = table_import
- Export filename = my_export.dat
Data:
Column_1 |
Column_2 |
Column_3 |
---|---|---|
25 |
Harvard Way |
Boston, MA |
To begin, prepare the export folder as described above in the section Prevent Others From Accessing Your Data. Be sure to see the note at the end of that section regarding temporary export folders.
mkdir /export/mdb_external/export/jharvard
chmod 777 /export/mdb_external/export/jharvard
Log into MariaDB
mysql -h HOSTNAME -u jharvard -p
Within MariaDB, export the data
select * from table_import into outfile ‘/export/mdb_external/export/jharvard/my_export.dat’
fields terminated by ‘|’ lines terminated by ‘\n’;

Please note our command has 3 sections:
(1) select * from table_import
specify what data you want export
(2) into outfile ‘/export/mdb_external/export/jharvard/my_export.dat’
specify the export file
(3) fields terminated by ‘|’lines terminated by ‘\n’
specify delimiters (Click here for more information)
Official documentation for this command may be found at https://mariadb.com/kb/en/mariadb/select-into-outfile.
Log out of MariaDB
exit;
Change access permissions to your export folder
chmod 700 /export/mdb_external/export/jharvard
Copy the data to ensure you have ownership as opposed to the MariaDB server
Opt 1: Copy data to your home dir
cp /export/mdb_external/export/jharvard/my_export.dat ~/
Opt 2: Copy data to your project space,
project_space
cp /export/mdb_external/export/jharvard/my_export.dat /export/projects/project_space
Once data has been copied, delete export folder
rm -rf /export/mdb_external/import/jharvard