Beginners guide to database administration tools

MySQL, Ubuntu and a drop of PHP and Perl

Download the whole article as PDF

Short URL: http://fsmsh.com/2456

Write a full post in response to this!


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   |     |         |       | 
+---------+----------+------+-----+---------+-------+ 
Don't miss out on the other pages!
1234next ›last »

Write a full post in response to this!

0

Do you like this post?
Vote for it!

Copyright information

This article is made available under the "Attribution-NonCommercial-Sharealike" Creative Commons License 3.0 available from http://creativecommons.org/licenses/by-nc-sa/3.0/.

Biography

Alan Berg:

Alan Berg Bsc. MSc. PGCE, has been a lead developer at the Central Computer Services at the University of Amsterdam for the last eight years. In his spare time, he writes computer articles. He has a degree, two masters and a teaching qualification. In previous incarnations, he was a technical writer, an Internet/Linux course writer, and a science teacher. He likes to get his hands dirty with the building and gluing of systems. He remains agile by playing computer games with his kids who (sadly) consistently beat him physically, mentally and morally.

You may contact him at reply.to.berg At chello.nl

oh2bamonkey's picture

missing semi-colon

Submitted by oh2bamonkey (not verified) on Sun, 2007-10-07 22:56.

Vote!
0

when creating the first table, line 4 should be like this: PRIMARY KEY (PK_ITEM) );

Anonymous visitor's picture

thanks so much,Alan

Submitted by Anonymous visitor (not verified) on Thu, 2007-12-06 08:33.

Vote!
0

thanks so much,Alan Berg
Your article is so useful to me. I am certainly a newer to Linux and the softwares. But your article helps me a lot.
thank you.

abshandra's picture

This is really beginner

Submitted by abshandra on Sat, 2009-11-28 21:11.

Vote!
0

This is really beginner guide. You teaches us from the basic. I like it. However, if I’m not installing Ubuntu, but I do install SuSE or fedora, is it still the same? Thanks in advance. [Spam link edited out - Ed]



CariNet: Cloud computing is a reality.