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

export1

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’;

export2

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