Research Database Hosting

Requesting

If your research project requires database hosting, please contact HMDC Support at support@help.hmdc.harvard.edu.

Hosting

HMDC runs a MariaDB database for our researchers. MariaDB is compatible with MySQL. For technical differences, please see: MariaDB versus MySQL.

Connecting

Once your database and account has been setup, you may connect via RCE:

[user@rce ~] mysql -h mariadb-1.priv.hmdc.harvard.edu -u username -p database

Make sure to replace username and database. Your database will be assigned a host, so it may be different than what's here. You will be prompted to enter your password before you're connected.

If you don't want to type out the entire command each time you connect, you can create a config file with your credentials. If you do the following, do not skip changing the file permissions, which ensure security.

  1. Create a file named .my.cnf in your home directory; note that it's a hidden file. You can do this from a terminal or using an application such as gedit.

  2. Enter the following, replacing appropriate values:
    [mysql]
    host=mariadb-1.priv.hmdc.harvard.edu
    user=your_username
    password=your_password
    database=your_database

  3. Save and close the file.

  4. Make the permissions '400'; from a terminal, execute: chmod 400 ~/.my.cnf

Once you have created this file with the proper permissions, you can connect to your database by using the command: mysql

Data Manipulation

Importing

If you have a .sql file, upload it to your project directory on the RCE (or your home directory). The following instructions assume you have a .my.cnf file, otherwise you'll need to use the full command (see above) to connect to your database.

[user@rce ~] mysql < /path/to/my_data.sql

Exporting

Saving Query Results

We do not grant FILE permissions to our users, so if you're attempting to use SELECT INTO OUTFILE, this won't work. There are however multiple ways to export data or collect query results. (Note: these instructions assume you have a .my.cnf file.)

Replace the query with your own in this example:

[user@rce ~] mysql -Be 'SELECT * FROM table_name;' > /path/to/results.tsv

This creates a tab deliminated file. If you want a csv, you can pipe it through sed:

[user@rce ~] mysql -Be 'SELECT * FROM table_name;' | sed 's/\t/,/g' > /path/to/results.csv

This has some caveats, especially if your data already has commas. If that's the case, you should export it as tsv and manipulate the deliminators appropriate to the system processing your data.

Full Database Dump

You can use mysqldump to export your entire database at once.

[user@rce ~] mysqldump -h mariadb-1.priv.hmdc.harvard.edu -u username -p database > /path/to/dump.sql

Make sure to replace username and database. as well as host, if applicable.

You can use your .my.cnf for this functionality as well. Enter these lines after the [mysql] section. Note that database is not defined in this section.

[mysqldump]
host=mariadb-1.priv.hmdc.harvard.edu
user=your_username
password=your_password

Now your command will look like this: [user@rce ~] mysqldump your_database > /path/to/dump.sql