2. Setup MySQL database server
Each assembly in an Ensembl site is stored in a separate MySQL database. GenomeHubs sites host these databases in a MySQL Docker container.
The EasyMirror container is used to configure users and password in the MySQL container and to mirror Ensembl databases from SQL dumps on the Ensembl/EnsemblGenomes ftp sites, making local copies ready to be hosted alongside newly imported data in a GenomeHubs site.
Create a
mysql/data
directory to allow the databases to be stored outside of the MySQL container:$ mkdir -p ~/genomehubs/mysql/data
Create a MySQL Docker container to host the Ensembl Databases for your GenomeHub:
- Docker sets up a default network bridge, but tosimplify connections between containers by using names rather than IP addresses, it is necessary to connect containers to the same named network bridge using
--network genomehubs-network
. MYSQL_ROOT_HOST='172.16.0.0/255.240.0.0'
will allow all other Docker containers on the same machine to connect to the mysql container as root, even if they are on a different Docker network. This is the simplest configuration as it does not matter which of the available subnets the Docker network has been created on, or which IP addresses are assigned to each of the containers. For more security you may wish to check the subnet of your bridge network and/or restrict access to the IP addresses of specific containers.
$ docker run -d \
--name genomehubs-mysql \
--network genomehubs-network \
-v ~/genomehubs/mysql/data:/var/lib/mysql \
-e MYSQL_ROOT_PASSWORD=CHANGEME \
-e MYSQL_ROOT_HOST='172.16.0.0/255.240.0.0' \
-p 3306:3306 \
mysql/mysql-server:5.5
Log in to MySQL inside the container to increase
max_allowed_packet
to allow import of large scaffolds:$ docker exec -it genomehubs-mysql mysql -u root -p
> set global max_allowed_packet=1073741824;
> exit
Edit the
database.ini
configuration file to set passwords for the database users:- the
DB_ROOT_PASSWORD
should match the value ofMYSQL_ROOT_PASSWORD
in thedocker run
command above - the
DB_HOST
must match the value of--name
in thedocker run
command above - to mirror existing Ensembl databases in your GenomeHub, add the appropriate database names to
SPECIES_DBS
as a space-separated list (a corresponding database dump must be available atSPECIES_DB_URL
) - even if you do not wish to mirror any existing databases, at least one database must be specified in
SPECIES_DBS
for use as a template when importing new data.
e93
e89
e85
$ nano ~/genomehubs/v1/ensembl/conf/database.ini
# (some lines omitted)
[DATABASE]
DB_SESSION_USER = ensrw
DB_SESSION_PASS = CHANGEME
DB_IMPORT_USER = importer
DB_IMPORT_PASSWORD = CHANGEME
DB_ROOT_USER = root
DB_ROOT_PASSWORD = CHANGEME
DB_PORT = 3306
DB_HOST = genomehubs-mysql
[DATA_SOURCE]
SPECIES_DB_URL = ftp://ftp.ensemblgenomes.org/pub/release-40/metazoa/mysql/
SPECIES_DBS = [ melitaea_cinxia_core_40_93_1 ]
MISC_DB_URL = ftp://ftp.ensembl.org/pub/release-93/mysql/
MISC_DBS = [ ensembl_accounts ]
$ nano ~/genomehubs/v1/ensembl/conf/database.ini
# (some lines omitted)
[DATABASE]
DB_SESSION_USER = ensrw
DB_SESSION_PASS = CHANGEME
DB_IMPORT_USER = importer
DB_IMPORT_PASSWORD = CHANGEME
DB_ROOT_USER = root
DB_ROOT_PASSWORD = CHANGEME
DB_PORT = 3306
DB_HOST = genomehubs-mysql
[DATA_SOURCE]
SPECIES_DB_URL = ftp://ftp.ensemblgenomes.org/pub/release-36/metazoa/mysql/
SPECIES_DBS = [ melitaea_cinxia_core_36_89_1 ]
MISC_DB_URL = ftp://ftp.ensembl.org/pub/release-89/mysql/
MISC_DBS = [ ensembl_accounts ]
$ nano ~/genomehubs/v1/ensembl/conf/database.ini
# (some lines omitted)
[DATABASE]
DB_SESSION_USER = ensrw
DB_SESSION_PASS = CHANGEME
DB_IMPORT_USER = importer
DB_IMPORT_PASSWORD = CHANGEME
DB_ROOT_USER = root
DB_ROOT_PASSWORD = CHANGEME
DB_PORT = 3306
DB_HOST = genomehubs-mysql
[DATA_SOURCE]
SPECIES_DB_URL = ftp://ftp.ensemblgenomes.org/pub/release-32/metazoa/mysql/
SPECIES_DBS = [ melitaea_cinxia_core_32_85_1 ]
MISC_DB_URL = ftp://ftp.ensembl.org/pub/release-79/mysql/
MISC_DBS = [ ensembl_accounts ]
Run the
database.sh
script in a genomehubs/easy-mirror
Docker container:- this script will set up database users and import databases into your MySQL container based on the information in the
database.ini
configuration file.
e93
e89
e85
$ docker run --rm \
--name genomehubs-ensembl \
--network genomehubs-network \
-v ~/genomehubs/v1/ensembl/conf:/ensembl/conf:ro \
genomehubs/easy-mirror:19.05 /ensembl/scripts/database.sh /ensembl/conf/database.ini
$ docker run --rm \
--name genomehubs-ensembl \
-v ~/genomehubs/v1/ensembl/conf:/ensembl/conf:ro \
--link genomehubs-mysql \
genomehubs/easy-mirror:17.06 /ensembl/scripts/database.sh /ensembl/conf/database.ini
$ docker run --rm \
--name genomehubs-ensembl \
-v ~/genomehubs/v1/ensembl/conf:/ensembl/conf:ro \
--link genomehubs-mysql \
genomehubs/easy-mirror:17.03 /ensembl/scripts/database.sh /ensembl/conf/database.ini
Last modified 2yr ago