You are here

MySQL

MySQL server

MySQL server runs on one of our database machines called "db3". Users who have accounts on the MySQL server are able to connect only through the bugaboo headnode, compute nodes and joffre machine. For security users cannot make an SSH connection to db3.

MySQL account and connection

If you need a MySQL account please send your request to support@westgrid.ca. Once the account is created all required information for connection to the MySQL server (db3) will be stored in a file called .my.cnf located at the user's home directory. The information includes the MySQL username which is the same as westgrid username, password which is a 16 digit random ASCII characters (it is not your westgrid password) and name of the machine which runs MySQL server (db3). Please do not delete this file as it contains your mysql password. The file is confidential and readable only by the user. To connect to the mysql server run the following command line type on bugaboo:

# mysql

Reminder: users are not allowed to run any production job on the Bugaboo head node. Therefore the above command should be run though an interactive job submission.

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. To connect to a mysql server from a Perl module, for example, from one of 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 ....

where [database name] is the name of the database which should be already created by the user.

Rules to create database

You can create multiple databases. To create a database, the name of the database is arbitrary but it must starts with [username]_ . E.g. for username "david" the name of the database MUST start with "david_" and the MySQL command to create a database called "david_db1" would be:

CREATE DATABASE david_db1;

The created database will automatically be accessible from the bugaboo 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:

GRANT SELECT ON [database name].* TO '[username 2]'@'172.%';

[username 2] is the user who takes that grant. In order to connect to MySQL from gbrowse, the corresponding line to connect to MySQL should contains:
 
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.

For More Information

System Bugaboo
Version 5.5.34