Skip to content

Database Setup Guide

Ethopy uses MySQL as its database backend, with DataJoint as the data management framework. This guide explains how to set up and manage the database for Ethopy.

The recommended way to run the database is using Docker with the official DataJoint MySQL image.

Prerequisites

  1. Docker installed and running
  2. Docker Compose (usually included with Docker Desktop)

Using the Built-in Setup Command

The easiest way to set up the database is using the provided command:

1
ethopy-setup-djdocker

This command will: 1. Check if Docker is running 2. Create a MySQL container named ethopy_sql_db 3. Set up the necessary volumes and configurations 4. Prompt for a root password 5. Start the container

The Docker container uses: - Image: datajoint/mysql:5.7 (https://github.com/datajoint/mysql-docker) - Port: 3306 (standard MySQL port) - Volume: ./data_ethopy_sql_db:/var/lib/mysql for persistent data storage

Manual Docker Setup

If you prefer to set up the container manually:

  1. Create a docker-compose.yaml file: 

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
    version: '2.4'
services:
  ethopy_sql_db:
    image: datajoint/mysql:5.7
    environment:
      - MYSQL_ROOT_PASSWORD=your_password
    ports:
      - '3306:3306'
    volumes:
      - ./data_ethopy_sql_db:/var/lib/mysql

  2. Start the container: 

    1
    docker compose up -d

Remote Access

To access the database from another computer:

  1. Update the Docker port mapping to allow external access: 

    1
2
    ports:
  - '0.0.0.0:3306:3306'

  2. In the computer that will run ethopy configure the section dj_local_conf in the local_conf.json, example: 

    1
2
3
4
5
6
7
8
    {
    "dj_local_conf": {
        "database.host": "database_ip",
        "database.user": "root",
        "database.password": "your_password",
        "database.port": 3306
    }
}

  3. Ensure the Docker host's firewall allows connections on port 3306

Verify port 3306 is available: 

1
netstat -an | grep 3306

Standalone MySQL Setup

If you prefer not to use Docker, you can install MySQL directly:

Ubuntu/Debian

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
# Install MySQL
sudo apt update
sudo apt install mysql-server

# Start MySQL service
sudo systemctl start mysql
sudo systemctl enable mysql

# Secure the installation
sudo mysql_secure_installation

# Create user and grant privileges
sudo mysql
CREATE USER 'ethopy'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'ethopy'@'localhost';
FLUSH PRIVILEGES;

macOS (using Homebrew)

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# Install MySQL
brew install mysql

# Start MySQL service
brew services start mysql

# Secure the installation
mysql_secure_installation

# Create user and grant privileges
mysql -u root -p
CREATE USER 'ethopy'@'localhost' IDENTIFIED BY 'your_password';
GRANT ALL PRIVILEGES ON *.* TO 'ethopy'@'localhost';
FLUSH PRIVILEGES;

Windows

  1. Download and install MySQL Community Server
  2. Follow the installation wizard
  3. Use MySQL Workbench or command line to create user and grant privileges

Database Schema Setup

After setting up the MySQL server (either via Docker or standalone), initialize the schemas:

1
2
3
4
5
# Verify database connection
ethopy-db-connection

# Create schemas
ethopy-setup-schema

This will create the following schemas: - lab_experiments - lab_behavior - lab_stimuli

Troubleshooting

Common Issues

  1. Connection Refused
  2. Check if MySQL service is running
  3. Verify port 3306 is not blocked by firewall

  4. Ensure correct host/IP in configuration

  5. Authentication Failed

  6. Verify username and password in local_conf.json

  7. Check user privileges in MySQL

  8. Docker Container Issues

  9. Check Docker logs: docker logs ethopy_sql_db
  10. Verify Docker daemon is running
  11. Check available disk space for volume

Useful Commands

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
# Check Docker container status
docker ps -a | grep ethopy_sql_db

# View Docker logs
docker logs ethopy_sql_db

# Restart Docker container
docker restart ethopy_sql_db

# Check MySQL status (standalone installation)
sudo systemctl status mysql

# Test MySQL connection
mysql -u root -p

Additional Resources