Administration of MySQL

Initially the MySQL server may not be running. Make sure it is running

Many of the following commands should be executed from within a mysql client.

You can start a mysql session using the unix command mysql -u root -p (the password is blank initially - just press Enter when prompted for a password)

You may need to review commands to manage users. Recall:

• Protect the root account by supplying the password 'secure'.
• Create a user scott with host localhost with password tiger.

• Create a database called number and a table called prime. The table prime should have exactly one field named i of type integer. You may use the following snippet to create the table:

  CREATE TABLE prime (i INTEGER);
  

• Set the permissions so that scott can read the table but cannot add or change the contents.

CREATE TABLE beatles(num INTEGER NOT NULL PRIMARY KEY, name VARCHAR(20));
INSERT INTO beatles VALUES (1,'John');
INSERT INTO beatles VALUES (2,'Paul');
INSERT INTO beatles VALUES (3,'George');
INSERT INTO beatles VALUES (4,'Ringo');

Start the web server.

The following perl program may be used to access the mysql database. Put the code in a file /var/www/cgi-bin/beatle.htm and test it by visiting the page http://host-1-19.linuxzoo.net/cgi-bin/beatle.htm - but substitue the actual address of your web server (you can find it from the "Server Administration" page).

You will need to make this file executable.

The sample shown does not include the functionality to delete beatles. You should do this yourself.

#!/usr/bin/perl
use strict;
use DBI;
use CGI qw(:standard);
import_names("Q");
print header;
my $dbh = DBI->connect("DBI:mysql:scott","scott","tiger")
  || print "Connection to mysql database failed: ". DBI->errstr;
print h1 "Beatles";
if ($Q::d){
  print "You asked to delete: $Q::d";
}
my $sth = $dbh->prepare("select * from beatles")
  || print "The statement could not be prepared." . DBI->errstr;
$sth->execute
  || print "The statement could not be executed." . DBI->errstr;
while (my ($number,$name) = $sth->fetchrow_array){
  print p "$number, $name " .
         a({-href=>"?d=$number"},"Delete this Beatle");
}