Welcome to an introduction for the beginner to the basic manipulation of the MySQL database with free software. The purpose of this article is to show how universally straightforward it is to get started with installing and applying a high-grade enterprise ready database like MySQL, and to learn how to manipulate it via numerous free software approaches.

I will explain how to setup MySQL and a few client helper tools to enter data; I will also cover PhpMyAdmin, a well known and highly deployed administration tool for MySQL. Finally, I will look at the Perl programming language as an easily accessible vehicle to database manipulation.

One obvious warning: installing server software has inherent risks such as security and accidental deletion of files. A security related example is that the MySQL database sometimes comes cleanly installed with accounts with blank passwords, which are obvious targets for even the laziest hunter. Personally, I have an experimental machine that I reformat and cleanly install periodically and doesn’t hold any information valuable to the outside world.

Database installation

Both version 4 and 5 of MySQL are highly popular. Version 5 has extra features including views, triggers and stored procedures. These features enhance the databases potential when compared to the slightly older and arguably more proven version 4.

Sometimes life is easy by design. To install MySQL from the command line I’ll use the excellent package manager tool apt. Assuming you have Ubuntu 7.04 installed on your development machine, then enter the following command:

  sudo apt-get install mysql-server-5.0 

During installation, many words will whiz by past you at a rapid pace from the command line. Focusing in on the relevant, if all goes well, you will see the following text generated:

The following NEW packages will be installed: 
  libdbd-mysql-perl libdbi-perl libnet-daemon-perl libplrpc-perl mysql-client-5.0 mysql-server-5.0

Note from the text that MySQL wasn’t the only program that got installed: as part of the installation, apt updated the Perl interpreter with modules that enable seamless communication from Perl scripts with the MySQL database.

The default binding address of the database is localhost; this implies that the package maintainers have thoughtfully secured the database so that it is not connected to the internet and is only directly contactable from users on the same machine. Yes, a good solid default for single user play around computers, such as my own desktop.

As standard, the root account has no password. Obviously, an interesting feature meant for ease of use but definitely not a secure long-term stance. Our initial actions are first to check which accounts need changing and then actually change the password of those accounts.

To log on to the database with a just installed client tool, try the following from the command line:

mysql -u root

The tool connects you locally to the database. Now to find which accounts already exist try inputting the following:

SELECT Host, User FROM mysql.user; 

+-----------+------------------+ 
| Host      | User             | 
+-----------+------------------+ 
| 127.0.0.1 | root             | 
| alans     | root             | 
| localhost | debian-sys-maint | 
| localhost | root             | 
+-----------+------------------+ 

The debian-sys-maint account is exactly that: an account used by administrators and for scripted MySQL maintenance. Changing the password of this account is not necessary—in fact, it’s even potentially dangerous. However, for the daredevils among you, if you really have to fiddle then you will find the information about this account mirrored in the startup configuration /etc/mysql/debian.cnf—remember that you’ll need to change that file as well.

To change the root passwords used to log on from localhost and from alans (alans being the hostname of my overloaded, abused, battered, sandwich stained, beaten, trodden on, dropped and bounced computer), then copy the following commands into the MySQL client window. Before doing so please change the mentioned password changeit to suite your own security policy (you do have one, right?).

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('changeit'); 
SET PASSWORD FOR 'root'@'alans' = PASSWORD('changeit'); 
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('changeit'); 
flush privileges;

The flush privileges command is just a reflection of my over cautiousness nature, forcing me to make sure that the privileges have been correctly updated. Next, I am going to create a new database called freesoftware, then show that the database actually exists and then exit gracefully from the MySQL client shell.

create database freesoftware; show databases; exit 

To connect again I need to input the new password and, of course, use the freesoftware database.

mysql -u root -p freesoftware password: changeit

For the sake of simplicity, I will create a rather simple table with only two fields, PK_ITEM (the primary key) and Name (a normal field):

create table items 
    (PK_ITEM INTEGER NOT NULL, 
     NAME CHAR(15) NOT NULL, 
         PRIMARY KEY (PK_ITEM) ); 

describe items; 

+---------+----------+------+-----+---------+-------+ 
| Field   | Type     | Null | Key | Default | Extra | 
+---------+----------+------+-----+---------+-------+ 
| PK_ITEM | int(11)  | NO   | PRI |         |       | 
| NAME    | char(15) | NO   |     |         |       | 
+---------+----------+------+-----+---------+-------+