DBI, Part 3: How to install and configure MySQL

Dateline: 12/19/99

This is Part Three in a four part series on using DBI. In DBI, Part 1: Databases and drivers we learned about installing the correct database drivers. In DBI, Part2: How to program with databases, I showed you the basic information you'll need to know to use DBI. This week I'll show you how to install and configure MySQL.

Introduction
In the earlier parts of this series I have given you a lot of general information about DBI. Now I'll show you how to put it into action, using MySQL as an example. Although the example will be on a Linux system, I'll try to keep things as operating system independent as I can.

Downloading MySQL

The first thing you'll need to do is download MySQL. Although there are a lot of options available, you'll need to start with the latest version of the precompiled binaries for your operating system. The version numbers among the different operating systems are similar, although they may not match exactly. In this example, I'll be downloading to a directory called /tmp. The downloaded file has a name something like mysql-3.22.27-pc-linux-gnu-i686.tar.gz, depending on your operating system and the version you downloaded.

Installing MySQL

Now that the file is downloaded, you'll have to extract it into the working directory. If you have root access, you'll want to use something like /usr/local. If you don't have root privileges, you'll need to talk to your network administrator to find out what directory to use.

1. Change to the correct directory

$cd /usr/local

2. You'll need root privileges to continue the installation

$ su

3. Extract the files

# gzip -dc /tmp/mysql-3.22.27-pc-linux-gnu-i686.tar.gz | tar -xvf

Create a symlink

To make things easier, create a symlink to the new directory. This will not only make it more friendly, it will make it easier when you update, since all you'll need to do is change the symlink to the new directory created when you install the update. # ln -s mysql-3.22.27-pc-linux-gnu-i686 mysql

----------------------------------------------------------------------------

Create a new MySQL user account

OK, now we need to create a user account so that you can change the ownership of all the mySQL files, and to run the mySQL server daemon. Add a user as you normally would for your operating system. Because nobody should ever be logging into this account, you should disable logins by following the recommended procedure for your operating system.

Now that you have created a new account, its time to change the ownership of the files and directories to the new mysql user and the root group.

$ su

# cd /usr/local

# chown -R mysql mysql-3.22.27-pc-linux-gnu-i686 mysql

# chgrp -R root mysql-3.22.27-pc-linux-gnu-i686 mysql

Create the initial MySQL database

The initial MySQL database consists of a sample database named 'test', and the internal database it uses to keep track of users and their permissions. To create it, you'll need to log in as the new mysql user for the first and only time.

# su mysql

$ cd mysql

$ scripts/mysql_install_db

$ exit

Starting MySQL

To start MySQL, type
# /usr/local/mysql/support-files/mysql.server start

When it starts you should see something similar to

Starting mysqld daemon with databases from /usr/local/mysql/data

If you want to start MySQL automatically, be sure to make the startup script executable,

# chmod +x /usr/local/mysql/support-files/mysql.server

and then add it to the system startup script for your operating system. You'll need to check the documentation for instruction on how to do this.

----------------------------------------------------------------------------------

Testing MySQL

Now its time to start the MySQL client. The files you'll need are located in /usr/local/mysql/bin, and you'll want to add this to your path. Let's get started.

# mysql (or /usr/local/mysql/bin/mysql)

You'll see something like

Welcome to the MySQL monitor. Commands end with ; or \g.

Your MySQL connection is 1 to server version: 3.22.27

Type 'help' for help.

mysql>

Now let's make sure that the initial MySQL database shows up correctly. Type

mysql> show databases;

You should see:

+-------+
| Database |
+-------+
| mysql |
| test |
+-------+

2 rows in set (0.00 sec)

Cool! Now exit MySQL so we can change the Admin Password.

mysql> exit;
Bye
----------------------------------------------------------------------------
Change the Admin Password

Now that everything is running smoothly, you should change the administrator password. Run mysqladmin to do this, remembering to add the path if you need to.

mysqladmin -u root password new_password

Of course, you'll want to choose something other than new_password!

When you want to start the mysql client as the administrator, you'll have to do it like this:

# mysql -u root -p

Enter password: new_password
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 3.22.27

Type 'help' for help.

mysql>

Creating a new database

Now let's look at how to create a new database. Since I've already shown you how to write a guestbook that saves to a file, let me show you how to create a database for it instead. We'll start by creating the database, which I'll call 'guestbook'.

mysql> create database guestbook;
Query OK, 1 row affected (0.03 sec)

(Don't forget the semicolon after each command). mysql> use guestbook;
Database changed

Create a new table
OK, now that we have the database created, let's create the table that we're going to use. This is a very simple example, so we'll only be creating one table, which we'll call 'guests'. The table will consist of two columns: 'name' and 'email'. We'll be using the SQL CHAR(length) datatype, which is a fixed length character value. There are many other datatypes, of course, but let's keep this example simple. If you want to learn more, please read on. I'll include some suggested references at the end of this feature.

mysql> CREATE TABLE guests(
-> name CHAR(50),
-> email CHAR(50)
->);
Query OK, 0 rows affected (0.00 sec)

-------------------------------------------------------------------------
Populate the table

Now let's add some test data into the table. We'll be using the INSERT statement to do that.

mysql> INSERT INTO guests VALUES ("Elmer Fudd", "elmer@disney.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO guests VALUES ("Daffy Duck", "daffy@disney.com");
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO guests VALUES ("Bugs Bunny", "bbunny@disney.com");
Query OK, 1 row affected (0.00 sec)

Let's use SELECT to make sure everything is there.

mysql> SELECT * from guests;

+---------+---------------+
| name | email |
+---------+---------------+
| Elmer Fudd | elmer@disney.com |
| Daffy Duck | daffy@disney.com |
| Bugs Bunny | bbunny@disney.com |
+---------+---------------+
3 rows in set (0.00 sec)

Create the database user

So far so good. Now we need to create a database user account so that your guests can add their name to your guestbook.

mysql> GRANT usage
-> ON guestbook.*
-> TO newguest@www.GlobalComputerBusiness.com;
Query OK, 0 rows affected (0.0 sec)

This creates a new user called 'newguest' that can connect to guestbook from http://www.GlobalComputerBusiness.com The next step is to specify what operations this user can perform on the database.

mysql> GRANT select, insert
-> on guestbook.*
-> to newguest@www.GlobalComputerBusiness.com;
Query OK, 0 rows affected (0.0 sec)

This will allow the 'newguest' user to execute SELECT and INSERT queries on the guestbook database.

OK, now we're done with the mysql client, so now its time to exit.

mysql> exit
Bye
-------------------------------------------------------------------------
Connecting to the database with DBI

Now let's write a script that will access our guestbook database. First, let's open the connection. We covered the DBI basics last week in DBI, Part 2: How to program with databases, which you can use to review if you need it.

#======= Establish the connection, and proceed
my $db_handle = DBI->connect('DBI:mysql:guestbook', 'newguest');

If the call to connect succeeds, you will now have a valid handle to the database. If not, then connect will return undef, so you'll need to check the return value of $db_handle before you proceed.

if( $db_handle )
{
#======= The connection is OK, so proceed
}
else
{
#======= Oops, something went wrong...
print "Couldn 't establish the connection";
}
Once the connection is established, we'll construct a query that will get all the data from the guests table, then we'll prepare and execute it.

#======= Select all the rows/cols from the guests table
my $SQL = "select * from guests";
my $stmt_handle = $db_handle->prepare($SQL);
my $rows = $stmt_handle->execute;

Now that we have the data, let's print it back to the browser.
#======= Print the data row by row
my @row;
while(@row = $stmt_handle->fetchrow_array)
{
my $Name = $row[0];
my $Email = $row[1];
print "$Name";
print "

";
}

Great! Now all we have to do is clean up and close the connection.

#======= Clean up
$stmt_handle->finish();
$db_handle->disconnect();
-------------------------------------------------------------------------
Summary
Well, as you can see there's a lot of steps to getting things set up. However, taken one by one, the steps are not difficult. Once the SQL server and database is properly set up, using DBI is a snap.

The information here is really only the beginning. If you want to learn more about MySQL, I highly recommend the new book from O'Reilly: MySQL & mSQL, by Randy Jay Yarger, George Reese, & Tim King. It does fall short, though, in the step by step instructions to getting MySQL installed and properly configured, and that's why I wanted to take the time to show you how to do it. Other than that, I think that this book is an excellent reference for MySQL and mSQL. Not only will you have good information on how to use it with DBI, you'll also learn how to use it with PHP, C/C++, Python, and Java/JDBC.

DBI, Part 3: How to install and configure MySQL

Home » Articles » Databases » MySQL »

related web pages on net

Feel free to Contact us for your Outsourcing Software development Job Work from India

Our Portals : www.winentrance.com | www.emporiumonnet.com | www.schoolsoftwares.com | www.upscexam.com