Using MySQL at NTNU

An introduction to creating and using MySQL-databases for students and employees.

Norsk versjon - Bruke MySQL ved NTNU

Looking for something else? Topic page about IT-support | Pages marked database

NTNU's servers run a MySQL database management system for students, and one for employees. MySQL is one of the most used and popular database servers, particularly for the web. If you are new to MySQL, we recommend you familiarize yourself with the core concepts before you start using the service.

Concepts #

User: A user is necessary to access a database. You can create multiple users, and the same user can access multiple databases. Your MySQL user must be the same user as your own NTNU username, or follow the following template: yourNTNUusername_something (f.ex. johndoe, johndoe_web, johndoe_calendar etc.). Please note that the maximum length of a MySQL username is 16 character, which means that if your username is johndoe (7 characters), you only have 8 characters left for the last section of the username.

Database: You can have multiple databases. Databases created can have the same name as your NTNU username, or follow the same format as MySQL usernames (with an added section, f.ex. johndoe, johndoe_recipes, johndoe_friends). As with MySQL usernames, a database can have a name length of maximum 16 characters.

Table: Databases can contain multiple tables. Tables can be named whatever you want, so long as no two tables in the same database are named the same. Tables contain the actual data in your database.

Permissions: MySQL users assigned to a database have permissions to execute various actions in a database.

Procedure #

Special permissions are required to create MySQL users and databases. On our servers, we run two small pieces of software which are used to create users, set passwords and permissions, and to create the database itself. This is Unix-software which you can access by visiting login.stud.ntnu.no for students and login.ansatt.ntnu.no for employees via SSH. Use your NTNU account information when logging in.

Create a MySQL user #

Say we are creating a database of our friends and their phone numbers. The first thing we do is create a MySQL user. We will call this user 'johndoe_demo'. Make sure the username adheres to the requirements mentioned earlier.

[johndoe@lynx (13) ~ ]$ mysql-useradm create johndoe_demo
[johndoe@lynx (14) ~ ]$ mysql-useradm show
User 'johndoe_demo': no password set.

Now we have to give the user a password. Note that passwords cannot be longer than 8 characters. The password should only be used for MySQL, especially if you want to use the new user for the web on NTNU. Files that are saved in your public_html-folder will be readable to all users on NTNU.

[johndoe@lynx (16) ~ ]$ mysql-useradm passwd johndoe_demo
New MySQL password for user 'johndoe_demo': *****
Retype new MySQL password for user 'johndoe_demo': *****
Password updated for user 'johndoe_demo'.

Create a database #

Now, we can create the database itself:

[johndoe@lynx (22) ~ ]$ mysql-dbadm create johndoe_friends
Database 'johndoe_friends' created.

Add user and grant permissions #

Before we can begin using the database, we have to add our user to it and grant the user permissions. First we check the permissions with the command:

[johndoe@lynx (25) ~ ]$ mysql-dbadm show johndoe_friends

The result should look similar to this:

Database 'johndoe_friends':
# User                Select  Insert  Update  Delete  Create   Drop   Alter   Index    Temp    Lock  References
# ----------------    ------  ------  ------  ------  ------   ----   -----   -----    ----    ----  ----------
# (no permissions currently granted to any users)

To add one and grant it permissions, do as follows:

[johndoe@lynx (30) ~ ]$ mysql-dbadm editperm johndoe_friends

An editor is started ($EDITOR). Enter the desired user and permissions. Type in the username of the user you are giving permissions, and use the Tab-key to navigate through the permissions. After doing so, exit and save (ctrl and X to exit, then Y for yes when prompted). Before you continue, double-check that the changes were saved by showing the database again with the command:

[johndoe@lynx (25) ~ ]$ mysql-dbadm show johndoe_friends

Note: if you are using the browser interface, the text will appear 'cut' on the right-hand side (it's all still there, though!). If you encounter problems, try using a different method of connecting via SSH.

A correct result may look like this:

Database 'johndoe_friends':
# User                Select  Insert  Update  Delete  Create   Drop   Alter   Index    Temp    Lock  References
# ----------------    ------  ------  ------  ------  ------   ----   -----   -----    ----    ----  ----------
  johndoe_demo           Y       Y       Y       N       Y       Y       Y       Y       Y       Y       Y

Add data #

Now, we can begin adding contents to our database.

[johndoe@lynx (33) ~ ]$ mysql -A -h mysql.stud.ntnu.no -u johndoe_demo -p johndoe_friends
Enter password: *****

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 217293 to server version: 3.22.22 Type 'help' for help. 
mysql> CREATE TABLE friends (
-> id int unsigned AUTO_INCREMENT NOT NULL,
-> firstname varchar(255),
-> surname varchar(255),
-> phone int(20),
-> PRIMARY KEY (id)
-> );
Query OK, 0 rows affected (1.27 sec) 
mysql> insert into venner (firstname, surname, phone) values ('John', 'Doe', 73591500);
Query OK, 1 row affected (1.72 sec) 
mysql> select * from venner;
+----+-----------+-----------+----------+
| id | firstname | surname   | phone    |
+----+-----------+-----------+----------+
|  1 | John      | Doe       | 73591500 |
+----+-----------+-----------+----------+
1 row in set (0.06 sec)

Database for student organization groups #

Student organization groups may only create databases and users for normal users, and databases whose names start with the name of the student organization group. In other words, if you want to use MySQL in connection with a student organization group, you can create a database named f.ex. dance_test, if you are a member of the dance group. Then, you can grant an existing (personal) MySQL user access by using mysql-dabadm editperm dance_test. All members of a student organization group may add their own users to a database connected to the group in this way.

Graphical user interface: phpMyAdmin #

If you want to use a graphical user interface to edit the database after it has been created, you can use NTNU's phpMyAdmin. Log in with the MySQL username and password you created earlier.

Tips and tricks #

How to use the database with PHP #

PHP has its own functions to access MySQL-databases. An example:

<?php
  $db = mysqli_connect("mysql.stud.ntnu.no", "johndoe_demo", "troll", "johndoe_friends");
  $result = mysqli_query($db, "SELECT * FROM friends");
  $row = mysqli_fetch_row($result);
  printf("First name: %s<br>\n", $row[0]);
  printf("Surname: %s<br>\n", $row[1]);
  printf("Phone: %s<br>\n", $row[2]);
?>

How to use the database with Perl #

Here is a small example on how to use the database with Perl:

#!/usr/bin/perl
use strict;
use DBI;

my $db='databasename';
my $host='mysql.stud.ntnu.no';
my $user='databaseusername';
my $password='databasepassword';

my $dbh = DBI->connect("DBI:mysql:$db:$host", "$user", "$password",{PrintError => 0}) || die $DBI::errstr;

$dbh->do("SELECT 1 FROM tablename") or print $DBI::errstr;

Two types of password encyption #

MySQL has two ways of encrypting your password. If you haven't changed the password for your database user in a long time, the password might have old style encryption. This is not supported in newer clients like PHP7, which is used on folk.ntnu.no and org.ntnu.no. If this is the case you have to set the password for your database user again. The new password will be encrypted with the modern encryption method.

Contact #

Orakel Support Services can help if you encounter difficulties. If you are an NTNU employee, consult your local IT Support.

0 Vedlegg
30274 Visninger
Gjennomsnitt (2 Stemmer)