skip to content

Managed Web Service Help & Support

 

Database provided with Debian 9 (Stretch)

Overview

MariaDB (version 10.1.26) is the database provided with Debian 9 (Stretch). Note that for the general case MariaDB is a drop-in replacement for MySQL and no change to tooling is necessary. The database can be accessed from the command line using either of the client commands mysql or mariadb. The closest corresponding version of MySQL is 5.7, there are some differences that might affect users but this is very rare in practice.

Managing the database

Unlike in MySQL on Debian 8, there is no need to set a root password for the database, and the debian-sys-maint user is now obsolete. The root user is authenticated using PAM and does not have a password. Using the user `root`@`localhost` to access databases is thus not possible by default. Changing the properties of this user is not supported and any changes made will be overwritten upon a subsequent reconfiguration of the server.

Users who are listed as site administrators AND have an SSH public key configured via the control panel will be added automatically, so they will be able to get local access to the server without a password and carry out administrative tasks. These usernames will be assigned the siteadmin role and will essentially have "root" privileges over the database. They cannot and should not be used to connect a web application to a database, but they can create further users as required for web applications and remote access using standard MySQL CREATE USER or GRANT syntax.

Database backups are stored in the "mysqldumps" directory for each daily backup: /snapshots/mws-snapshot-<year>-<month><day>/mysqldumps

The MWS does not provide graphical tools such as PHPMyAdmin, though you could install this yourself.

Examples of how to create a database and database users

Here are a couple of examples of creating a database and users.
The MariaDB documentation contains many more details, and is
available at CREATE DATABASE, CREATE USER, and GRANT.

a)  Create a database and grant a user control of that particular database
(remember to replace mydata, myuser, and mypassword with your own choice
of details!).
Use the 'mysql' command to enter the database, then

CREATE DATABASE 'mydata';
GRANT ALL on 'mydata'.* to 'myuser'@'localhost' IDENTIFIED BY 'mypassword';
FLUSH PRIVILEGES;

This will create the user 'myuser' if it does not already exist.

b) Create a new database admin user (remember to replace  
myadmin and myadmin_pass with your own choice of details!):

CREATE USER 'myadmin'@'localhost' IDENTIFIED BY 'myadmin_pass';
GRANT ALL on *.* to 'myadmin'@'localhost' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

Accessing and using the database

The  database on your MWS server is only directly accessible from that server. It listens on the normal Unix domain socket /var/run/mysqld/mysqld.sock and on the TCP port 127.0.0.1:3306. MWS site administrators with users created automatically as above must use SSH socket forwarding if they wish to connect remotely:

$ ssh -o "StreamLocalBindUnlink yes" -L /tmp/my.sock:/var/run/mysqld/mysqld.sock -N mws-12345.mws3.csx.cam.ac.uk

This will establish an SSH connection and create a /tmp/my.sock file that can be used to connect to the database:

$ mysql -S /tmp/my.sock

There is no need to enter a password.

The TCP port can be used in a similar way to establish an SSH tunnel, allowing other database users to connect remotely to the site's MySQL server. For example after running

$ ssh mws-12345.mws3.csx.cam.ac.uk -L 3307:127.0.0.1:3306

the database port on the MWS site mws-12345.mws3.csx.cam.ac.uk will appear as port 3307 on the local machine and

$ mysql -h 127.0.0.1 -P 3307 -u someuser -p

will correctly connect to the MWS site database. A site administrator will need to have created a user with a password for this to work.

mysql_connect is no longer supported in PHP 7, but one can use MySQLi or PDO_MySQL instead. Choosing an API has further details.

If you are transferring a mysqldump of a database into the MWS from a MySQL database elsewhere, you may need to run  mysql_upgrade after importing it into your MariaDB database.