Setting up database

Setting up MySQL/PostgreSQL with administrative privilege

Note: The following instructions are for Ubuntu 14.04, but can be used as a guideline for other Linux flavors. Please replace 'YOURPASSWORD' with your own password for the database!

For MySQL:

sudo apt-get install mysql-server mysql-client
sudo apt-get install libmysqld-dev
mysql -u root -p
CREATE DATABASE BioQueue;
CREATE USER 'bioqueue'@'localhost' IDENTIFIED BY 'YOURPASSWORD';
GRANT ALL PRIVILEGES ON BioQueue . * TO 'bioqueue'@'localhost';

For PostgreSQL

# install postgresql
sudo apt-get install postgresql postgresql-contrib
# configure
sudo -i -u postgres
psql
# change password
alter user postgres with password 'YOURPASSWORD';
# exit psql
\q

Setting up MySQL/PostgreSQL without administrative privilege

For people who are using facilities managed by their institutions, the required root/administrative privilege may not be available. In this case, you can try to use package manager, conda, to install databases. The drawback of this strategy is that:

1. You'll need to restart the database manually every time when the machine gets rebooted;
2. You may need to assign the database to listen to a non-default port number.

Before install databases, let's create a conda environment (db) dedicated for this purpose:

conda create --name db;
# activate the environment
conda activate db;

For MySQL:

# install PostgreSQL
conda install -c conda-forge mysql

For PostgreSQL:

# install PostgreSQL
conda install -y -c conda-forge postgresql;
# Initiate a directory called bioq_db, which will be used to store tables later
initdb -D bioq_db
# now start the server of postgres
pg_ctl -D bioq_db -l logfile start
# create a user (bioq), you'll be asked about the password you want to use
createuser --encrypted --pwprompt bioq
# create a database named bioq
createdb --owner=bioq bioq

# if the machine gets rebooted or for any other reason that
# the database process got killed
# you can use the following command to get it running again:
conda activate db
pg_ctl -D bioq_db -l logfile start