MySQL

Introduction

Stud is running a MySQL database. MySQL is a popular database server, and is particularly suited for use in web contexts. For students at NTNU a MySQL-server is available at mysql.stud.ntnu.no. Before you start using MySQL we recommend you familiarise yourself with the following terms:

Basically it takes special rights to create users and databases. Therefore there are two small programs you must use to create users, set passwords and rights aswell as to create the database itself. This is unix software and is located on login.stud.ntnu.no and solarislogin.stud.ntnu.no.

An example

Let us say that I want to make a database over my friends and their phone numbers. The first thing I must do is to make a database user. I will call this 'knutpett_demo'. Be sure to follow the naming conventions as described in the previous section.

	[knutpett@jeeves (13) ~ ]$ mysql-useradm create knutpett_demo
	[knutpett@jeeves (14) ~ ]$ mysql-useradm show
	User 'knutpett_demo': no password set.

Then I must set the password for the user. Note that passwords cannot be longer than 8 characters. The password should only be used for MySQL, especially if you plan to use the user in web applications hosted at NTNU. Files readable by the webserver will also be readable for every other user at NTNU, which means your MySQL login-details will be readable by all users at ntnu .

	[knutpett@jeeves (16) ~ ]$ mysql-useradm passwd knutpett_demo
	New MySQL password for user 'knutpett_demo':
	Retype new MySQL password for user 'knutpett_demo':
	Password updated for user 'knutpett_demo'.

Now I can make the database:

	[knutpett@jeeves (22) ~ ]$ mysql-dbadm create knutpett_friends
	Database 'knutpett_friends' created.

Before I can start using the databse, I must remember to set the appropriate rights.

	[knutpett@jeeves (25) ~ ]$ mysql-dbadm show knutpett_friends
	Database 'knutpett_friends':
# User                Select  Insert  Update  Delete  Create   Drop   Alter  Index  Temp Lock
# ----------------    ------  ------  ------  ------  ------  ------  -----  -----  ---- -----
# (no permissions currently granted to any users)

Here we see that no rights have been granted.

	[knutpett@jeeves (30) ~ ]$ mysql-dbadm editperm knutpett_friends

This starts your favourite editor (or whatever the system variable $EDITOR is set to) and you fill in the appropriate rights. My result was this:

	Database 'knutpett_friends':
# User                Select  Insert  Update  Delete  Create   Drop   Alter  Index  Temp  Lock
# ----------------    ------  ------  ------  ------  ------  ------  -----  -----  ----  ----
knutpett_demo         Y       Y       Y       Y       Y       Y       Y      Y      Y     Y 

We are now ready to create the tables:

	[knutpett@jeeves (33) ~ ]$ mysql -A -h mysql -u knutpett_demo -p knutpett_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 friends (firstname,surname,phone) values ('John','Doe',73591500);
	Query OK, 1 row affected (1.72 sec)
        mysql> select * from friends;
	+----+---------+-----------+----------+
	| id | firstname | surname | phone    |
	+----+---------+-----------+----------+
	|  1 | John    | Doe       | 73591500 |
	+----+---------+-----------+----------+
	1 row in set (0.06 sec)

How to use the database with PHP

PHP has built-in functionsr to access MySQL databases. Here is an example:

<?php
  $db = mysql_connect("mysql.stud.ntnu.no", "knutpett_demo","troll");

  mysql_select_db("knutpett_venner",$db);
  $result = mysql_query("SELECT * FROM venner",$db);

  printf("Fornavn: %s<br>\n", mysql_result($result,0,"fornavn"));
  printf("Etternavn: %s<br>\n", mysql_result($result,0,"etternavn"));
  printf("Tlf: %s<br>\n", mysql_result($result,0,"tlf"));
?>

How to access the database from Perl

Here is a small example:

	#!/store/bin/perl -w

	use Mysql;
	use strict;
	use POSIX qw(locale_h);
	
	my $dbh = Mysql->connect("mysql", "knutpett_friends","knutpett_demo","troll");
	my $sth = $dbh->query("select * from friends");

	my $i=0;
	while (my @arr = $sth->fetchrow) {
	  my ($id,$firstname,$surname,$phone) = @arr;
	  print "$id - $firstname - $surname - $phone\n";
	}

Employees

Employees can use mysql in the same way as the students, except that they have to use mysql.ansatt.ntnu.no instead of mysql.stud.ntnu.no.

studorgs

You can only create databases and users for regular users, in addition to databases with names starting with the name of the studorg. For example, if you are member of the studorg dans you can create the database dans_test using the mysql-dbadm create dans_test. Then you need to give an existing mysql user permission to access the database, using the mysql-dbadm editperm dans_test command.