Database servers

This article is a draft

This is not a complete article: This is a draft, a work in progress that is intended to be published into an article, which may or may not be ready for inclusion in the main wiki. It should not necessarily be considered factual or authoritative.

Database servers available for researchers

Compute Canada offers access to MySQL and Postgres database servers for researchers:

• Cedar MySQL server
  • Description: General purpose server for the researcher wanting to set up SQL tables in MySQL and issue SQL commands against them.
  • Server name: cedar-mysql-vm.int.cedar.computecanada.ca
  • Short server name: cedar-mysql-vm (can be used instead of long name on most compute nodes)
  • Version: MariaDB version 10.2 Community Edition
  • Documentation: http://www.mariadb.com
• Cedar Postgres server
  • Description: General purpose server for the researcher wanting to set up SQL tables in MySQL and issue SQL commands against them. Includes a PostGIS extension available for those needing to do geocoding.
  • Server name: cedar-pgsql-vm.int.cedar.computecanada.ca
  • Short server name: cedar-pgsql-vm (can be used instead of long name on most compute nodes)
  • Version: PostgreSQL version 10.1, PostGIS version 2.4 extension available
  • Documentation: https://www.postgresql.org and https://postgis.net/documentation

Cedar MySQL server

The Cedar MySQL server runs as a VM called "cedar-mysql-vm" (full name: cedar-mysql-vm.int.cedar.computecanada.ca) on a database machine. Users who have accounts on the MySQL server are able to connect only through the Cedar headnode (cedar.computecanada.ca), Cedar compute nodes and joffre machine.

For security, users cannot make an SSH connection to the database server directly.

MySQL account and connection

If you need a MySQL account on the Cedar MySQL server please send your request to support@computecanada.ca . (With a MySQL account, you will have the privileges to create your own database)

To get a MySQL account on the Cedar MySQL server, please send a request to support@computecanada.ca with the following information:

• Name
• Compute Canada account
• Amount of database space needed for your project

Once the account is created, all required information for connection to the MySQL server will be stored in a file called .my.cnf located at your home directory on a Cedar node .

The information includes the MySQL username which is the same as Compute Canada username, password which is a 16 digit random ASCII characters (it is not your Compute Canada password) and name of the machine which runs MySQL server. Please do not delete this file as it contains your mysql password. The file is confidential and readable only by the user.

For interactive connection to the mysql server, you will want to run the latest version of the mysql client which is available for you to load to your account because by default the version on the server is older and will not offer all the latest features available on the server. Here are the steps to load the latest mysql client tool.

• step 1
• step 2

Here is how to run the client

[name@server ~]$ mysql

Please DO NOT use the -p option as an argument in running mysql. The required password will be automatically taken from your .my.cnf file if you don't use -p option.

It is alright to submit a long-running SQL command from the Cedar head node, as the work is being done on the database server. However, if you are issuing accessing the server via a script which is also doing a lot of work, then it needs to be submitted as a job to be run. Click here for details https://docs.computecanada.ca/wiki/Running_jobs

Rules to create a MySQL database

In order to be able to set up MySQL tables and query them, you need to create your own database. You can create multiple MySQL databases. To create a database, the name of the database is arbitrary but it must start with

[username]_

E.g. for username "david" the name of the database MUST start with "david_" and the commands to create a database called "david_db1" would be:

[name@server ~]$ mysql
 mysql> CREATE DATABASE david_db1;
 mysql> quit

Here is an example of how to work with your new database and create a table in it, populate it, and query it:

[name@server ~]$ {{{1}}}

The created database will automatically be accessible from the Cedar headnode, computenodes and joffre, so you do not need to do any other grant. However, if you want another user with a MySQL account on the server to view tables in your database you can issue this MySQL command:

[name@server ~]$ mysql
 mysql> GRANT SELECT ON [database name].* TO '[username 2]'@'172.%';
 mysql> quit

where [username 2] is the MySQL user who takes that grant.

=== MySQL connectivity for BioPerl

To connect to a mysql server from a Perl module, for example, from one of the BioPerl modules which is usually used to upload data in to the database the command line of a BioPerl command should contain the -d option as following:

-d [database name]:mysql_read_default_file=.my.cnf ....

MySQL connectivity for GBrowse

GBrowse is a combination of database and interactive web pages for manipulating and displaying annotations on genomes.

Documentation: http://gmod.org/wiki/GBrowse

In order to connect to MySQL from gbrowse, the corresponding line to connect to MySQL should contain:

db_args       =     -adaptor DBI::mysql
                    -dsn [database name];mysql_read_default_file=/home/[username]/.my.cnf
                    -user [username]

where [username] is the corresponding user name and [database name] is the name of the database.

Cedar PostgreSQL server

The Cedar PostgreSQL server runs as a VM called "cedar-pgsql-vm" (full name: cedar-pgsql-vm.int.cedar.computecanada.ca) on a database machine. Users who have accounts on the PostgreSQL server are able to connect only through the Cedar headnode (cedar.computecanada.ca), Cedar compute nodes and joffre machine.

For security, users cannot make an SSH connection to the database server directly.

To get an account and database on the Cedar PostgreSQL server, send a request to support@computecanada.ca with the following information:

• Name
• Compute Canada account
• Amount of database space needed for your project
• PostGIS extension: indicate if needed to be added to your database

PostgreSQL account and connection

PostgreSQL uses IDENT authentication for connection from compute nodes which means a password does not need to be supplied to your PostgreSQL account. When access is from the head node, however, PostgreSQL uses PAM authentication which means that you will be prompted for your Compute Canada password during an interactive session. Example:

[name@server ~]$ psql -h cedar-pgsql-vm -d db_[username]

where db_[username] is the name of the database that was set up for you with your PostgreSQL account. (If you require more databases to be set up for your PostgreSQL account to use, please send a request to support@computecanada.ca).

The example above runs an older version of the psql Postgres interactive client that by default is installed on all the nodes:

[name@server ~]$ psql --version
psql (PostgreSQL) 9.6.2

You can load a more recent version of the psql Postgres client available as a module. Here's how:

[name@server ~]$ module avail postgres
-------------------------------------------
postgresql/9.6.6    postgresql/10.2 (D)

[name@server ~]$ module load postgresql/10.2

[name@server ~]$ psql --version
psql (PostgreSQL) 10.2

PostgreSQL connectivity for BioPerl

The Bioperl Project is an international association of users & developers of open source Perl tools for bioinformatics, genomics and life science.

Documentation: bioperl.org

To connect to postgres from a perl module, for example from one of BioPerl modules, the command line should contains "-a" and "-d" options as following:

-a DBI::Pg  -d dbi:Pg:dbname=[database name] ....

where [database name] is the name of your database.

PostgreSQL connectivity for gbrowse

GBrowse is a combination of database and interactive web pages for manipulating and displaying annotations on genomes.

Documentation: http://gmod.org/wiki/GBrowse

In order to connect to postres from gbrowse, the corresponding line in the configuration file should contain:

db_args       =    -dsn dbi:Pg:dbname=[database name ]
                   -user [username]