Database servers: Difference between revisions

From Alliance Doc
Jump to navigation Jump to search
No edit summary
(Marked this version for translation)
 
(101 intermediate revisions by 12 users not shown)
Line 1: Line 1:
{{draft}}
<languages />
== 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


<translate>


== Cedar MySQL server ==
== Database servers available for researchers == <!--T:1-->
The Alliance offers access to MySQL and Postgres database servers for researchers on both Cedar and Graham:


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.
<!--T:84-->
Users who have accounts on the MySQL server are able to connect only through the Cedar headnode
{| class="wikitable"
(cedar.computecanada.ca), Cedar compute nodes and joffre machine.
|+ Database servers
|-
! Information !! Cedar - MySQL !! Cedar - Postgres !! Graham - MySQL
|-
| Description || General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them. || General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them. Includes a PostGIS extension available for those needing to do geocoding. || General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them.
|-
| Server name || cedar-mysql-vm.int.cedar.computecanada.ca || cedar-pgsql-vm.int.cedar.computecanada.ca || cc-gra-dbaas1.sharcnet.ca <br> IP: 199.241.163.99
|-
| Short server name (can be used instead of long name) || cedar-mysql-vm || cedar-pgsql-vm || N/A
|-
| Latest version || [https://mariadb.com/kb/en/mariadb-server-11-5/ MariaDB version 11.5] || [https://www.postgresql.org/docs/release/16.0/ PostgreSQL version 16], PostGIS version 3.3 extension || [https://mariadb.com/kb/en/mariadb-server-11-5/ MariaDB version 11.5]
|-
| Documentation || [https://www.mariadb.com MariaDB website] || [https://www.postgresql.org Postgres website], [https://postgis.net/documentation PostGIS documentation] || [https://www.mariadb.com MariaDB website]
|}


== Cedar MySQL server == <!--T:2-->
<!--T:3-->
The Cedar MySQL server offers MariaDB which is compatible with other flavours of MySQL. For information on compatibility, see [https://mariadb.com/kb/en/library/mariadb-vs-mysql-compatibility/ MariaDB versus MySQL Compatibility].
<!--T:4-->
The MariaDB server runs as a VM called "cedar-mysql-vm" (full name: cedar-mysql-vm.int.cedar.computecanada.ca).
Users who have accounts on the MySQL server are able to connect only through the Cedar head node
(cedar.computecanada.ca), Cedar compute nodes and [https://gateway.cedar.computecanada.ca Cedar Portal].
<!--T:5-->
For security, users cannot make an SSH connection to the database server directly.
For security, users cannot make an SSH connection to the database server directly.


=== MySQL account and connection ===
=== MySQL account and connection === <!--T:6-->


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)
<!--T:7-->
If you need the privileges to create your own database, you will need a MySQL account. To get a MySQL account on the Cedar MySQL server, please send a request to our [[Technical support]] with the following information:


To get a MySQL account on the Cedar MySQL server, please send a request to support@computecanada.ca with the following information:
<!--T:8-->
* Name
* Your Alliance username
* Compute Canada account
* Amount of database space needed for your project
* Amount of database space needed for your project


Once the account is created, all required information for connection to the MySQL server will
<!--T:9-->
be stored in a file called .my.cnf located at your home directory on a Cedar node .
We will then create an MySQL account with the same username as your Alliance username and a 16 digit random string password. The username, password, database server name, and other information required to make a MySQL connection will be stored in a file called <code>.my.cnf</code> in your home directory. This file is confidential. You cannot change its contents but you can read it or delete it. If the file is deleted, you will lose access to your database.


The information includes the MySQL username which is the same as Compute Canada username,
<!--T:10-->
password which is a 16 digit random ASCII characters (it is not your Compute Canada password)
Run the "mysql" client to connect to the MySQL server. An older version of the client may be available without loading a [[Utiliser des modules/en|module]], but it will not give you access the latest features on the MySQL server. We recommend issuing the following commands to load a more recent version of the client:
and name of the machine which runs MySQL server. Please do not delete this file as it
[name@server ~]$ module load mariadb
contains your mysql password. The file is confidential and readable only by the user.
[name@server ~]$ mariadb --version


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.
<!--T:60-->
Here are the steps to load the latest mysql client tool.
You can use the following commands to test that your MySQL database account is set up correctly:
* step 1
{{Command|mysql
* step 2
MariaDB [(none)]> show databases;
MariaDB [(none)]> quit}}


Here is how to run the client
<!--T:61-->
'''Do not''' use the <code>-p</code> or <code>-h</code> options as arguments when running <code>mysql</code>. The required password and server name are taken automatically from your <code>.my.cnf</code> file.


{{Command|mysql}}
<!--T:11-->
It is acceptable to submit a long-running SQL command from the head node, as most of the CPU usage is taken from the database server side. However, if you run a script which issues SQL commands and uses lots of CPU, then it needs to be submitted as a job to the scheduler. See [[Running jobs]] for details.


Please DO NOT use the -p option as an argument in running mysql. The required password will be automatically taken
=== Set up your MySQL database === <!--T:12-->
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
<!--T:13-->
In order to be able to set up MySQL tables and query them, you need to create your own database. To create a database, the name of the database is arbitrary but it must start with "''username_''"
where "''username''" is your MySQL username. For example, if your username were "david" the name of the database '''must''' start with "david_" and the commands to create a database called "david_db1", for example, would be:


=== Rules to create a MySQL database ===
<!--T:15-->
{{Command|mysql
MariaDB [(none)]> CREATE DATABASE david_db1;
MariaDB [(none)]> quit
}}


In order to be able to set up MySQL tables and query them, you need to create your own database.
<!--T:16-->
You can create multiple MySQL databases. To create a database, the name of the database is arbitrary but it must start with
You may create multiple MySQL databases as long as they all start with "''username_''".
[username]_
The created databases will automatically be accessible only to you from the Cedar head node, Cedar compute nodes, and [https://gateway.cedar.computecanada.ca Cedar Portal]. You will have full privileges to create SQL objects such as tables, views, etc.


E.g. for username "david" the name of the database MUST start with "david_" and the commands to create
=== Work with your MySQL database === <!--T:17-->
a database called "david_db1" would be:


<!--T:18-->
Suppose you have account "david" and have created a database called "david_db1" and want to use it. Here's how:
<!--T:19-->
{{Command|mysql
{{Command|mysql
  mysql> CREATE DATABASE david_db1;
  MariaDB [(none)]> -- List available databases. Confirm david_db1 is in the list
  mysql> quit
MariaDB [(none)]> SHOW DATABASES;
MariaDB [(none)]> -- Get into the database
MariaDB [(none)]> USE david_db1;
  MariaDB [(none)]> ... Issue SQL commands. See below for information.
MariaDB [(none)]> quit
}}
}}


Here is an example of how to work with your new database and create a table in it, populate it, and query it:
<!--T:20-->
Resources for using MariaDB:
* [https://mariadb.com/kb/en/ MariaDB Knowledgebase]
* [https://mariadb.com/kb/en/library/training-tutorials/ MariaDB Training and Tutorials]
* [https://mariadb.com/kb/en/library/sql-statements-structure/ MariaDB SQL Statement Structure]
* [https://mariadb.com/kb/en/library/optimization-and-indexes/ MariaDB Optimization and Indexes]


{{Command|mysql
=== Share your MySQL data === <!--T:69-->
mysql> USE david_db1;
 
mysql> CREATE TABLE fubar (age integer, id varchar(10));
<!--T:70-->
mysql> INSERT INTO fubar VALUES (34, '1122');
All MySQL account holders on Cedar can share their own databases. To share a table of your database with other users:
mysql> INSERT INTO fubar VALUES (22, '2233');
 
mysql> SELECT * FROM fubar WHERE age > 30;
<!--T:85-->
mysql> SELECT age FROM fubar WHERE id = '1122';
# Run the command <code>mysql</code> to connect to MySQL.
mysql> quit
# Run the command <code>USE ''database'';</code>
}}
#* "''database''" is the name of the database that you would like to share a table from.
# Run the command <code>GRANT ''priv_type'' ON ''mytable'' TO &prime;''user''&prime;@'172.%';</code>
#* "''priv_type''" is the type of privilege you would like to grant.
#* "''mytable''" is the name of the table.
#* "''user''" is the username you would like to share your table with.
 
==== MySQL sharing example ==== <!--T:83-->


The created database will automatically be accessible from the Cedar headnode, computenodes and joffre, so you do not
<!--T:86-->
need to do any other grant. However, if you want another user with a MySQL account on the server to view tables in
Username "david" would like to share his table, "mytable" from database, "david_db" with username "john" for reading only. Here are commands he needs to run:
your database you can issue this MySQL command:


<!--T:71-->
{{Command|mysql
{{Command|mysql
  mysql> GRANT SELECT ON [database name].* TO '[username 2]'@'172.%';
  MariaDB [(none)]> USE david_db;
  mysql> quit
MariaDB [(none)]> GRANT SELECT on mytable to 'john'@'172.%';
  MariaDB [(none)]> quit;
}}
}}


where [username 2] is the MySQL user who takes that grant.
== Cedar PostgreSQL server == <!--T:30-->


=== MySQL connectivity for BioPerl
<!--T:31-->
The Cedar Postgres server has [https://www.postgresql.org/ Postgres] along with the [https://postgis.net/ PostGIS] extension.


To connect to a mysql server from a Perl module, for example, from
<!--T:32-->
one of the BioPerl modules which is usually used to upload data in to the database the command line of a BioPerl command
The Cedar PostgreSQL server runs as a VM called "cedar-pgsql-vm" (full name: cedar-pgsql-vm.int.cedar.computecanada.ca).
should contain the -d option as following:
Users who have accounts on the PostgreSQL server are able to connect through the main Cedar head node
(cedar.computecanada.ca), Cedar compute nodes, and [https://gateway.cedar.computecanada.ca Cedar Portal].


-d [database name]:mysql_read_default_file=.my.cnf ....
<!--T:33-->
For security, users cannot make an SSH connection to the database server directly.


=== MySQL connectivity for GBrowse ===
<!--T:34-->
To get an account and database on the Cedar PostgreSQL server, send a request to our [[Technical support]] with the following information:
* Your Alliance username
* Amount of database space needed for your project
* If you need the PostGIS extension for the database


GBrowse is a combination of database and interactive web pages for manipulating and displaying annotations on genomes.
=== PostgreSQL account and connection === <!--T:35-->


Documentation: http://gmod.org/wiki/GBrowse
<!--T:36-->
The PostgreSQL account we create for you will have the same username as your Alliance username. You will be given a database. The name of the database will typically be "<username>_db" where <username> is your Alliance username. You cannot create a database yourself. If you need more than one database, please send a request to [[Technical support]].  


In order to connect to MySQL from gbrowse, the corresponding line to connect to MySQL should contain:
<!--T:62-->
You do not need to supply a password to access your PostgreSQL account. For security reasons your Alliance password must NEVER be required or used in a script. This also means that one user cannot access another user's database directly.
Run the "psql" client to connect to the PostgreSQL server. An older version of the client may be available without loading a [[Utiliser des modules/en|module]], but it will not give you access to the latest features of PostgreSQL. We recommend loading the following module to use a more recent version of the client:


db_args      =    -adaptor DBI::mysql
<!--T:63-->
                    -dsn [database name];mysql_read_default_file=/home/[username]/.my.cnf
[name@server ~]$ module load postgresql
                    -user [username]
[name@server ~]$ psql --version


where [username] is the corresponding user name and [database name] is the name of the database.
=== Work with your PostgreSQL database === <!--T:43-->


== Cedar PostgreSQL server ==
<!--T:44-->
Suppose you have account "david" and have been assigned a database called "david_db". Here is an example of how to use it from the Cedar head node:


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.
<!--T:45-->
Users who have accounts on the PostgreSQL server are able to connect only through the Cedar headnode
{{Command|psql -h cedar-pgsql-vm -d david_db
(cedar.computecanada.ca), Cedar compute nodes and joffre machine.
david_db{{=}}> -- List names of tables in your database
david_db{{=}}> \dt
david_db{{=}}> ... Issue SQL commands. See below for more information.
david_db{{=}}> -- Quit
david_db{{=}}> \q
}}


For security, users cannot make an SSH connection to the database server directly.
<!--T:46-->
Resources for using PostgreSQL:
* [https://www.postgresql.org/docs/current/static/tutorial.html PostgreSQL tutorials]
* [https://www.postgresql.org/docs/ PostgreSQL manuals]
* [https://www.postgresql.org/docs/release/ PostgreSQL release notes]


To get an account and database on the Cedar PostgreSQL server, send a request to support@computecanada.ca with the following information:
=== Share your PostgreSQL data === <!--T:64-->
* Name
You can share your data in your PostgreSQL database with others. This is how:
* Compute Canada account
* The person with whom you want to share access must have a Postgres account on the system. They should apply for one as described above.
* Amount of database space needed for your project
* You will have to give the person <code>connect</code> access to your database.
* PostGIS extension required for the database?
* For each table or view to be shared, you give the person one or more of <code>select, update, insert</code>, and <code>delete</code> access to it.
* You can also revoke access to a table, view, or database.


=== PostgreSQL account and connection ===
<!--T:65-->
Here is an example of user 'david' sharing a table with user 'kim':
{{Command|psql -h cedar-pgsql-vm -d david_db
david_db{{=}}> -- Give kim connect access to the database
david_db{{=}}> grant connect on database david_db to kim;
david_db{{=}}> -- Give kim select-only access to a table called mytable
david_db{{=}}> grant select on mytable to kim;
david_db{{=}}> -- Quit
david_db{{=}}> \q
}}


PostgreSQL uses IDENT authentication for connection from compute nodes which means a password does not need to be
<!--T:66-->
supplied to your PostgreSQL account.
Here is an example of user 'kim' accessing the shared table:
When access is from the head node, however, PostgreSQL uses PAM authentication which means that you will be prompted
{{Command|psql -h cedar-pgsql-vm -d kim_db
for your Compute Canada password during an interactive session. Example:
kim_db{{=}}> -- Connect to the database containing the table to be accessed
kim_db{{=}}> \c david_db
david_db{{=}}> -- Display the rows in the shared table
david_db{{=}}> select * from mytable;
david_db{{=}}> -- Quit
david_db{{=}}> \q
}}


{{Command|psql -h cedar-pgsql-vm -d db_[username]}}
<!--T:67-->
where db_[username] is the name of the database that was set up for you with your PostgreSQL account. (If you require
Here is an example of user 'david' revoking access to 'kim':
more databases to be set up for your PostgreSQL account to use, please send a request to support@computecanada.ca).
{{Command|psql -h cedar-pgsql-vm -d david_db
 
david_db{{=}}> -- Revoke kim's select-only access to a table called mytable
The example above runs an older version of the psql Postgres interactive client that by default is installed on all the nodes:
david_db{{=}}> revoke select on mytable from kim;
 
david_db{{=}}> -- Revoke kim's connect access to the database
{{Command|psql --version
david_db{{=}}> revoke connect on database david_db from kim;
psql (PostgreSQL) 9.6.2}}
david_db{{=}}> -- Quit
david_db{{=}}> \q
}}


You can load a more recent version of the psql Postgres client available as a module. Here's how:
== Graham MySQL server == <!--T:68-->
The steps for obtaining and using an account on the Graham MySQL server are similar to [[#Cedar_MySQL_server|those described above for Cedar]].


{{Command|module avail postgres
== Cloud-based database servers == <!--T:72-->
-------------------------------------------
=== Database as a Service (DBaaS) ===
postgresql/9.6.6    postgresql/10.2 (D)}}
If a VM is not sufficient to run a database load, a managed database can be used instead, the current offering includes MySQL/MariaDB and Postgres on a physical system.
The database systems as well as all databases are being backed up once a day. The backups are archived for 3 months.
To request access, please contact [[Technical support]].


{{Command|module load postgresql/10.2}}
<!--T:73-->
'''Please provide in your request the client network or IP address you will access the database from.'''


{{Command|psql --version
<!--T:74-->
psql (PostgreSQL) 10.2}}
{| class="wikitable"
|-
! Type !! Hostname !! TCP port
|-
| mysql || dbaas101.arbutus.cloud.computecanada.ca || 3306
|-
| pgsql || dbaas101.arbutus.cloud.computecanada.ca || 5432
|}


=== PostgreSQL connectivity for BioPerl ===
<!--T:75-->
The Bioperl Project is an international association of users & developers of open source Perl tools for bioinformatics, genomics and life
The CA certificate which is used to sign the host certificate for the service, is available for download [https://docs.computecanada.ca/mediawiki/images/5/58/Dbaas-ca.pem.zip here].
science.


Documentation: bioperl.org
=== PostgreSQL database === <!--T:76-->
Your instance will use an ssl connection to connect to the DBaaS host.
The example below connects to the DBaaS host, as '''''user01''''' and uses the database '''''dbinstance''''' via an ssl connection.


To connect to postgres from a perl module, for example from one of BioPerl modules,
<!--T:77-->
the command line should contains "-a" and "-d" options as following:
<pre>
psql --set "sslmode=require" -h dbaas101.arbutus.cloud.computecanada.ca -U user01 -d dbinstance
Password for user user01:
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dbinstance=> \l dbinstance
                              List of databases
    Name    | Owner  | Encoding |  Collate  |    Ctype    | Access privileges
------------+--------+----------+-------------+-------------+-------------------
dbinstance | user01 | UTF8    | en_US.UTF-8 | en_US.UTF-8 | user01=CTc/user01
(1 row)
</pre>


-a DBI::Pg  -d dbi:Pg:dbname=[database name] ....
<!--T:78-->
The ssl connection is enforced and plain text connections fail.


where [database name] is the name of your database.
=== MariaDB/MySQL database === <!--T:79-->
Your instance will use an ssl connection to connect to the DBaaS host.
The example below connects to the DBaaS host, as '''''user01''''' and uses the database '''''dbinstance''''' via an ssl connection.


=== PostgreSQL connectivity for gbrowse ===
<!--T:80-->
GBrowse is a combination of database and interactive web pages for manipulating and displaying annotations on genomes.
<pre>
mysql --ssl -h dbaas101.arbutus.cloud.computecanada.ca -u user01 -p dbinstance
Enter password:
MariaDB [dbinstance]> show databases;
+--------------------+
| Database          |
+--------------------+
| dbinstance        |
| information_schema |
+--------------------+
2 rows in set (0.001 sec)
</pre>


Documentation: http://gmod.org/wiki/GBrowse
<!--T:81-->
If you try to use a plain text connection, your authentication will fail.


In order to connect to postres from gbrowse, the corresponding line in the configuration
<!--T:82-->
file should contain:
<pre>
mysql -h dbaas101.arbutus.cloud.computecanada.ca -u user01 -p dbinstance
Enter password:  
ERROR 1045 (28000): Access denied for user 'user01'@'client.arbutus' (using password: YES)
</pre>


db_args      =    -dsn dbi:Pg:dbname=[database name ]
</translate>
                    -user [username]
[[Category:Cloud]]

Latest revision as of 16:15, 31 May 2024

Other languages:


Database servers available for researchers

The Alliance offers access to MySQL and Postgres database servers for researchers on both Cedar and Graham:

Database servers
Information Cedar - MySQL Cedar - Postgres Graham - MySQL
Description General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them. General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them. Includes a PostGIS extension available for those needing to do geocoding. General purpose server for the researcher wanting to set up SQL tables and issue SQL commands against them.
Server name cedar-mysql-vm.int.cedar.computecanada.ca cedar-pgsql-vm.int.cedar.computecanada.ca cc-gra-dbaas1.sharcnet.ca
IP: 199.241.163.99
Short server name (can be used instead of long name) cedar-mysql-vm cedar-pgsql-vm N/A
Latest version MariaDB version 11.5 PostgreSQL version 16, PostGIS version 3.3 extension MariaDB version 11.5
Documentation MariaDB website Postgres website, PostGIS documentation MariaDB website

Cedar MySQL server

The Cedar MySQL server offers MariaDB which is compatible with other flavours of MySQL. For information on compatibility, see MariaDB versus MySQL Compatibility.

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

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

MySQL account and connection

If you need the privileges to create your own database, you will need a MySQL account. To get a MySQL account on the Cedar MySQL server, please send a request to our Technical support with the following information:

  • Your Alliance username
  • Amount of database space needed for your project

We will then create an MySQL account with the same username as your Alliance username and a 16 digit random string password. The username, password, database server name, and other information required to make a MySQL connection will be stored in a file called .my.cnf in your home directory. This file is confidential. You cannot change its contents but you can read it or delete it. If the file is deleted, you will lose access to your database.

Run the "mysql" client to connect to the MySQL server. An older version of the client may be available without loading a module, but it will not give you access the latest features on the MySQL server. We recommend issuing the following commands to load a more recent version of the client: 

[name@server ~]$ module load mariadb
[name@server ~]$ mariadb --version

You can use the following commands to test that your MySQL database account is set up correctly:

Question.png 
[name@server ~]$ mysql
MariaDB [(none)]> show databases;
MariaDB [(none)]> quit

Do not use the -p or -h options as arguments when running mysql. The required password and server name are taken automatically from your .my.cnf file.

It is acceptable to submit a long-running SQL command from the head node, as most of the CPU usage is taken from the database server side. However, if you run a script which issues SQL commands and uses lots of CPU, then it needs to be submitted as a job to the scheduler. See Running jobs for details.

Set up your MySQL database

In order to be able to set up MySQL tables and query them, you need to create your own database. To create a database, the name of the database is arbitrary but it must start with "username_" where "username" is your MySQL username. For example, if your username were "david" the name of the database must start with "david_" and the commands to create a database called "david_db1", for example, would be:

Question.png 
[name@server ~]$ mysql
 MariaDB [(none)]> CREATE DATABASE david_db1;
 MariaDB [(none)]> quit

You may create multiple MySQL databases as long as they all start with "username_". The created databases will automatically be accessible only to you from the Cedar head node, Cedar compute nodes, and Cedar Portal. You will have full privileges to create SQL objects such as tables, views, etc.

Work with your MySQL database

Suppose you have account "david" and have created a database called "david_db1" and want to use it. Here's how:

Question.png 
[name@server ~]$ mysql
 MariaDB [(none)]> -- List available databases. Confirm david_db1 is in the list
 MariaDB [(none)]> SHOW DATABASES;
 MariaDB [(none)]> -- Get into the database
 MariaDB [(none)]> USE david_db1;
 MariaDB [(none)]> ... Issue SQL commands. See below for information.
 MariaDB [(none)]> quit

Resources for using MariaDB:

Share your MySQL data

All MySQL account holders on Cedar can share their own databases. To share a table of your database with other users:

  1. Run the command mysql to connect to MySQL.
  2. Run the command USE database;
    • "database" is the name of the database that you would like to share a table from.
  3. Run the command GRANT priv_type ON mytable TO ′user′@'172.%';
    • "priv_type" is the type of privilege you would like to grant.
    • "mytable" is the name of the table.
    • "user" is the username you would like to share your table with.

MySQL sharing example

Username "david" would like to share his table, "mytable" from database, "david_db" with username "john" for reading only. Here are commands he needs to run:

Question.png 
[name@server ~]$ mysql
 MariaDB [(none)]> USE david_db;
 MariaDB [(none)]> GRANT SELECT on mytable to 'john'@'172.%';
 MariaDB [(none)]> quit;

Cedar PostgreSQL server

The Cedar Postgres server has Postgres along with the PostGIS extension.

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

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 our Technical support with the following information:

  • Your Alliance username
  • Amount of database space needed for your project
  • If you need the PostGIS extension for the database

PostgreSQL account and connection

The PostgreSQL account we create for you will have the same username as your Alliance username. You will be given a database. The name of the database will typically be "<username>_db" where <username> is your Alliance username. You cannot create a database yourself. If you need more than one database, please send a request to Technical support.

You do not need to supply a password to access your PostgreSQL account. For security reasons your Alliance password must NEVER be required or used in a script. This also means that one user cannot access another user's database directly.

Run the "psql" client to connect to the PostgreSQL server. An older version of the client may be available without loading a module, but it will not give you access to the latest features of PostgreSQL. We recommend loading the following module to use a more recent version of the client: 

[name@server ~]$ module load postgresql
[name@server ~]$ psql --version

Work with your PostgreSQL database

Suppose you have account "david" and have been assigned a database called "david_db". Here is an example of how to use it from the Cedar head node:

Question.png 
[name@server ~]$ psql -h cedar-pgsql-vm -d david_db
 david_db=> -- List names of tables in your database
 david_db=> \dt
 david_db=> ... Issue SQL commands. See below for more information.
 david_db=> -- Quit
 david_db=> \q

Resources for using PostgreSQL:

Share your PostgreSQL data

You can share your data in your PostgreSQL database with others. This is how:

  • The person with whom you want to share access must have a Postgres account on the system. They should apply for one as described above.
  • You will have to give the person connect access to your database.
  • For each table or view to be shared, you give the person one or more of select, update, insert, and delete access to it.
  • You can also revoke access to a table, view, or database.

Here is an example of user 'david' sharing a table with user 'kim':

Question.png 
[name@server ~]$ psql -h cedar-pgsql-vm -d david_db
 david_db=> -- Give kim connect access to the database
 david_db=> grant connect on database david_db to kim;
 david_db=> -- Give kim select-only access to a table called mytable
 david_db=> grant select on mytable to kim;
 david_db=> -- Quit
 david_db=> \q

Here is an example of user 'kim' accessing the shared table:

Question.png 
[name@server ~]$ psql -h cedar-pgsql-vm -d kim_db
 kim_db=> -- Connect to the database containing the table to be accessed
 kim_db=> \c david_db
 david_db=> -- Display the rows in the shared table
 david_db=> select * from mytable;
 david_db=> -- Quit
 david_db=> \q

Here is an example of user 'david' revoking access to 'kim':

Question.png 
[name@server ~]$ psql -h cedar-pgsql-vm -d david_db
 david_db=> -- Revoke kim's select-only access to a table called mytable
 david_db=> revoke select on mytable from kim;
 david_db=> -- Revoke kim's connect access to the database
 david_db=> revoke connect on database david_db from kim;
 david_db=> -- Quit
 david_db=> \q

Graham MySQL server

The steps for obtaining and using an account on the Graham MySQL server are similar to those described above for Cedar.

Cloud-based database servers

Database as a Service (DBaaS)

If a VM is not sufficient to run a database load, a managed database can be used instead, the current offering includes MySQL/MariaDB and Postgres on a physical system. The database systems as well as all databases are being backed up once a day. The backups are archived for 3 months. To request access, please contact Technical support.

Please provide in your request the client network or IP address you will access the database from.

Type Hostname TCP port
mysql dbaas101.arbutus.cloud.computecanada.ca 3306
pgsql dbaas101.arbutus.cloud.computecanada.ca 5432

The CA certificate which is used to sign the host certificate for the service, is available for download here.

PostgreSQL database

Your instance will use an ssl connection to connect to the DBaaS host. The example below connects to the DBaaS host, as user01 and uses the database dbinstance via an ssl connection. 

psql --set "sslmode=require" -h dbaas101.arbutus.cloud.computecanada.ca -U user01 -d dbinstance
Password for user user01: 
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)
dbinstance=> \l dbinstance
                               List of databases
    Name    | Owner  | Encoding |   Collate   |    Ctype    | Access privileges 
------------+--------+----------+-------------+-------------+-------------------
 dbinstance | user01 | UTF8     | en_US.UTF-8 | en_US.UTF-8 | user01=CTc/user01
(1 row)

The ssl connection is enforced and plain text connections fail.

MariaDB/MySQL database

Your instance will use an ssl connection to connect to the DBaaS host. The example below connects to the DBaaS host, as user01 and uses the database dbinstance via an ssl connection. 

mysql --ssl -h dbaas101.arbutus.cloud.computecanada.ca -u user01 -p dbinstance
Enter password: 
MariaDB [dbinstance]> show databases;
+--------------------+
| Database           |
+--------------------+
| dbinstance         |
| information_schema |
+--------------------+
2 rows in set (0.001 sec)

If you try to use a plain text connection, your authentication will fail. 

mysql -h dbaas101.arbutus.cloud.computecanada.ca -u user01 -p dbinstance
Enter password: 
ERROR 1045 (28000): Access denied for user 'user01'@'client.arbutus' (using password: YES)
Retrieved from "https://docs.alliancecan.ca/mediawiki/index.php?title=Database_servers&oldid=154745"