The database credentials will be in a file named after the database, in a directory under the home directory of the web site (i.e. in the example above this would be <SITE>/home/database_details); it will contain the following information:
- database name
- database server
- database user name
- database password
We would advise you to use the database server name rather than looking up the IP address of the server.
For MySQL users the command line tool is mysql and can be run on access.nsms.ox.ac.uk using:
- mysql -h <database server> -u <username> -p
To back up the database, use mysqldump, e.g.
- mysqldump -u siteXXXX_YYYY -h database-server-goes-here -p database-name-goes-here > path-to-store/name-of-file.dump
We recommend storing database dumps in the site home directory rather than within the htdocs directory (unless protected by a suitable .htaccess file), as attackers routinely try to download likely backup file names.
If you prefer to use a GUI tool for database access then you can use port forwarding, e.g. from your desktop:
- Mac or Linux users can tunnel over SSH:
ssh -L 3306:<database-server-name>:3306 <your-sso>@access.nsms.ox.ac.uk
ssh -L 3306:nsmsblah.nsms.ox.ac.uk:3306 firstname.lastname@example.org
You could then run e.g. mysql-workbench, and connect to localhost which will be port-forwarded to the actual database host. Closing the ssh session will terminate the port forwarding.
Windows users can tunnel using PuTTY:
- Install putty from https://www.chiark.greenend.org.uk/~sgtatham/putty/
- The download is called e.g. putty-X.YZ-installer.msi. Install it, accepting the default options is fine
- Run PuTTY from the start menu
- In the "Host Name (or IP address)" box, type access.nsms.ox.ac.uk
- On the left menu, under "Connection" click "Data"
- In the "Auto-login username" box, type your NSMS AD username, which is the same as your SSO username if you have one
- On the left of the putty window, click the "+" next to "SSH" then click "Tunnels"
- In the "Source port" box, type 3306
- (if you are running a mysql / mariadb server on your desktop then port 3306 might not be available, in which case you'd need to pick another port)
- In the "Destination" box type the full name of your database server, followed by ":3306", e.g.: nsmsblah.nsms.ox.ac.uk: 3306
- Leave "Local" and "Auto" selected, and click "Add".
- The text box should then display something like:
- L3306 nsmsfoo.nsms.ox.ac.uk: 3306
- Scroll the left menu back to the top, then click "Session".
- In the "Saved Sessions" box give it a name, e.g. access-nsms
- Click "Save"
- Now that the connection details are saved, you can load them by clicking the name you gave, then clicking "Load"
- Click "Open"
- If you get a popup "PuTTY Security Alert", then check that the fingerprint given matches the one given in the SSH/SFTP FINGERPRINTS section below.
- If it does, click Yes
- Enter your NSMS AD password
- Run your choice of desktop SQL program, e.g. mysql-workbench or heidisql
- When creating a connection, use "localhost" or "127.0.0.1" (they're the same) for the database hostname, *instead* of the actual database hostname e.g. nsmsfoo.nsms.ox.ac.uk
- Use the database username and password that you were given when the site was set up
Closing the PuTTY session will terminate the port forwarding, so close your database program first.