Local UCSC database

If you wish to run a local version of the UCSC Genome Database, for example because queries to external databases are not allowed in your network, or because you want to use a specific version of the database, you can do so by following this guide.

First of all, to be able to run a local version of this database, MySQL is required on your machine. Install it by following the steps in https://dev.mysql.com/downloads/installer/.

Download database tables

Once MySQL is installed, you need to download the UCSC database tables refGene and snp150Common to your local machine. You can do so by running the following commands:

mysqldump -v --single-transaction -u genome -h genome-euro-mysql.soe.ucsc.edu hg38 snp150Common > snp150Common.sql

mysqldump -v --single-transaction -u genome -h genome-euro-mysql.soe.ucsc.edu hg38 refGene > refGene.sql

  • -v is to activate verbose mode (not strictly necessary)

  • --single-transaction is to avoid locking the tables

  • -u genome is to specify the user (genome)

  • -h genome-euro-mysql.soe.ucsc.edu is to specify the host

  • hg38 is the genome version

  • snp150Common and refGene are the tables to download

  • snp150Common.sql and refGene.sql are the output files

Create and populate local database

Access MySQL CLI

sudo mysql

Create a new database hg38

CREATE DATABASE hg38;

Review database

SHOW CREATE DATABASE hg38;

Select the database

USE hg38;

Copy the content of the downloaded files to the database

SOURCE refGene.sql

SOURCE snp150Common.sql

Create new user to access the database

Access MySQL

sudo mysql

Create a new user genome with password password

CREATE USER 'genome'@'localhost' IDENTIFIED BY 'password'

Grant privileges to the user genome

GRANT ALL PRIVILEGES ON hg38.* TO 'genome'@'localhost' WITH GRANT OPTION;