Database Service

Before being able to access the database service, you will need to register to have a database user account created. You should do this by placing a general request with the IT Service Desk requesting access. Database user account names will normally match your ADF username however the password will be different. For security, we suggest that you do notset your database password to be the same as your ADF password - this is because you may need to put the password into scripts that you use.

If you are starting a new database, you will also need to request that you require a database to be created. Again, this should be done by placing a general request with the IT Service Desk. As part of the request process you will need to provide some information about your request, for example:

If you require access to another user's database, then you should contact that person to grant you permission once your database user account has been confirmed as created. We will not normally provide access to another user's database.

If you are planning to hold data which may be subject to the data protection act or may be highly confidential, you should contact us in advance to discuss this and help determine if the service is suitable for your needs.

You will need to register for access to the database service before you can connect.

Windows users may wish to use a graphical tool to interact with your database. There are many such tools available, however HeidiSQL provides a stable interface and includes the ability to take database dumps.

To connect to the service you will need a MySQL or MariaDB client. We are currently running version 5.5 of MariaDB and as such any 5.5 or greater client should connect without trouble.

When connecting to the database from campus, you should connect to bb2db.bham.ac.uk on port 3306. From the BlueBEAR cluster, simply connect to bb2db on port 3306. Using this name will connect to the high availability cluster providing fault-tolerant access to the database servers.

If you are experienced and are writing your own application, you may wish to do your own HA connections, you can connect to bb2db01.bham.ac.uk and bb2db02.bham.ac.uk, both on port 3306. For most users, we suggest you do notdo this.

Important note:It is only possible to connect to the database servers from the BlueBEAR cluster and from the University campus network. Connections to the database service from off-site are not permittedand will be discarded by the firewall, this helps to protect the integrity of the service and reduce the likelihood of an attack being made on the service and hence reduce the possibility of data loss.

There are a few important limitations and notes regarding the database service:

• We are currently only able to support InnoDB backed databases on the database service. This is normally the default for MySQL and so is unlikely to be an issue for most uses. The is because we provide a high-availability, clustered database service and the clustering software is unable to replicate other types of database backend.
• We do not impose size restrictions or connection limiting on databases, however as the service develops, we may review this and we may remove or limit access to databases where we feel usage is likely to cause detriment to other users of the service.
• Our database servers are connected to the BEAR infrastructure and should be considered at-risk during BlueBEAR maintenance periods. Whilst we would aim to maintain partial service during these windows, this cannot be guaranteed. Scheduled maintenance periods are published on the BEAR website.
• We do notbackup the contents of the databases we hold. Our servers have redundant storage arrays and we replicate the data between servers, however this would not prevent a user incident such as 'DROP TABLE'. We therefore suggest that you take your own regular backups of your databases. You may do this using mysqldump from a Linux machine, or a Windows client such as HeidiSQL as a point and click mechanism for managing tables and taking database dumps.
• As our database servers are clustered, if a fail-over action does occur, an active database connection may block for up to a minute as fail-over takes place. Normally the MySQL client will reconnect automatically, however you may wish to take account for this eventuality in your code.

You may wish to check both the MySQL 5.5 documentationand the MariaDB knowledgebase. A few common operational tasks are given below.

Changing your password

To change your password, connect to the service using a client and run the following SQL command:
SET PASSWORD = PASSWORD('mynewpassword');

Giving access to another user

If you are the owner of a database, you can give (or grant) access to another user once their database account has been created. You should refer to the documentation for full details, but an example to give all permissions to a user on all tables in the database would be:
GRANT ALL ON mydb.* TO 'someuser'@'%';