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. Updating them to be something more unique is recommended.

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

Setup tables for annotation

1. Start mysql as the new user

mysql -u username -p dbname

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

  # run the script in the mysql prompt
  mysql> source scripts/db/create_annotation_tables.sql

3. 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

Specific tables

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

Restore

mysql < dump.sql