If your research project requires database hosting, please contact HMDC Support at support@help.hmdc.harvard.edu.
HMDC runs a MariaDB database for our researchers. MariaDB is compatible with MySQL. For technical differences, please see: MariaDB versus MySQL.
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.
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.
Enter the following, replacing appropriate values:
[mysql]
host=mariadb-1.priv.hmdc.harvard.edu
user=your_username
password=your_password
database=your_database
Save and close the file.
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
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
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.
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