Database setup

MySQL

So far, we have used MySQL 5.7.

General Setup

1. Following MySQL installation to installing MySQL for your platform. Alternatively, you can also use MariaDB

Ubuntu 14.04, 16.04, 18.04

   sudo apt install mysql-server
   sudo mysql_secure_installation

MacOS

To install mysql:

   brew install mysql

Alternative, to install mariadb:

   brew install mariadb

2. Setup an account with username/password and database that you will use for the annotations (substitute appropriate name for dbname, username and password. The default in server/config/index.js (see config.annDb) uses stk for all three.

  sudo mysql -u root -p
  CREATE DATABASE dbname;
  CREATE USER 'username'@'localhost' IDENTIFIED BY 'password';  
  GRANT ALL PRIVILEGES ON dbname.* TO 'username'@'localhost' IDENTIFIED BY 'password';

Setup tables for annotation

1. Run scripts/db/create_annotation_tables.sql to create tables for annotation

  # in mysql prompt
  source scripts/db/create_annotation_tables.sql

2. Adjust configuration for your MySQL instance in server/config/index.js (see config.annDb)

Backing and restoring mysql data

mysqldump -u [username] -p --databases [database] > [database]_20190506.sql

Single table

mysqldump [database] [t1] [t2] [t3] > dump.sql

Restore

mysql < dump.sql