skip to primary navigationskip to content
 

MariaDB database (Debian 9)

Database provided with Debian 9 (Stretch)

MariaDB (version 10.1.26) is the database provided with Debian 9 (Stretch) as a drop-in replacement for MySQL.  The closest corresponding version is MySQL 5.7, but there are some differences. Debian have changed how they manage MySQL/MariaDB authentication, and this change is reflected in the MWS.

The database can be accessed from the command line using either of the client commands mysql or mariadb.

The siteadmin role on the MWS will essentially have "root" privileges in the database and will authenticate using PAM. Users who are listed as administrators, AND have an SSH public key configured, will be added to this role automatically, so they will be able to get passwordless access to the server and carry out administrative tasks. These access semantics are enforced by configuration management and any change to them will be overwritten upon reconfiguring the server.

[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. That means that only someone actually logged in as root will be able to connect to the server as root by default.]

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.

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

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

The  database on your MWS server is only directly accessible from that server. It listens on the normal Unix domain socket and on the TCP port localhost:3306. The TCP port can be used to establish an SSH tunnel, allowing administrators and ssh-only 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

will correctly connect to the MWS site database. You will probably need to supply a username and password before such a connection will actually succeed.