Installing the Ensembl Data

The Ensembl data is provided on the Ensembl FTP site in the form of tab-delimited text files for importing into MySQL. Go to the appropriate release directory and then to 'mysql'.

e.g. https://ftp.ensembl.org/pub/grch37/current/mysql

The MySQL data

Each database directory contains a data file for each table in that database an SQL file that contains the SQL commands necessary to build that database's table structure and a checksum file (using a UNIX "sum" utility) so you can verify that the data has downloaded correctly.

Regardless of which species you choose to install, you will also need ensembl_ontology_113, ensembl_website_113 and an empty copy of ensembl_accounts - even if you do not use logins, this latter db is needed for session data, e.g. image configurations.

You may optionally want to install the comparative genomics databases as well: ensembl_compara_113 and ensembl_ancestral_113

NB: The FTP site will ideally be laid out as described. If, however, for reasons of space or maintainability, files are not located as described then check the ftp site for README files which should explain where the data can be found.

To install the Ensembl Data:

Download the directory in https://ftp.ensembl.org/pub/grch37/current/mysql for whatever organism you want to install.
Each table file is gzipped so unpack the data into working directories, keeping separate directories for each database.
For each database you have downloaded, cd into the database directory and perform steps 3-5. For illustration, we will use homo_sapiens_core_113_37 as the database - you need to change this appropriately for each database you install. Remember, you also need to download and install the multi-species databases.
Start a MySQL console session (see the Installing MySQL section above if necessary) and issue the command:
```
create database homo_sapiens_core_113_37;
```
Exit the console session, and issue the following command to run the ensembl SQL file, which should be in the directory where you unpacked the downloaded data. This creates the schema for the empty database you created in step 3.
Note that we are using the example MySQL settings of /data/mysql as the install directory, and mysqldba as the database user. Note that here mysqldba is a MySQL account with file access to the database, which is not the same as a system user. See the MySQL documentation for instructions on creating/administering users.
```
/data/mysql/bin/mysql -u mysqldba homo_sapiens_core_113_37 < homo_sapiens_core_113_37.sql
```
Load the data into the database structure you have just built with the following command.
```
/data/mysql/bin/mysqlimport -u mysqldba --fields-terminated-by='\t' --fields-escaped-by=\\ homo_sapiens_core_113_37 -L *.txt
```
Note that owing to the nature of some of the data in Ensembl it has been necessary to escape the table fields when dumping the MySQL text files. Hence to import successfully, a field escape parameter needs to be specified when using mysqlimport

You have now created and loaded the core Ensembl database for human.

Note that all the databases except the ensembl_accounts database only require read access for the website to work. The ensembl_accounts requires a MySQL user with delete/insert/update permissions. Also note that because its the only database that the website writes data into, the ensembl_accounts has no .table (data) files to download.

NB MySQL needs quite a lot of temporary space to load the databases. It is quite possible that your / tmp directory (which MySQL uses by default) is too small, in which case you might see an Error 28 (use the MySQL tool perror to see what these error numbers mean). Fortunately, you can force MySQL to write temporary files to another location. See the MySQL docs for details: http://dev.mysql.com/doc/refman/5.1/en/temporary-files.html. The simplest solution is to start mysqld with the argument --tmpdir my_spacious_tmp_location.

GO data

The Ensembl ftp site now includes a copy of the GO database as ensembl_go_113. Install this if you want local GO information.