Novell is now a part of Micro Focus

An Introduction to MySQL for NetWare

Articles and Tips: article

Rob Lyon
Senior Engineer
Novell, Inc.
rlyon@novell.com

01 Oct 2002


This is the first in a series of articles that will explore using MySQL on NetWare. This article introduces MySQL for NetWare in a simple tutorial format. Future AppNotes will explore topics such as backup and recovery, logging, replication, management clients, MySQL in a Web application environment, and coding to MySQL with various programming languages (Java, PHP, C, and so on).


Topics

MySQL, databases, network application development

Products

MySQL for NetWare

Audience

developers

Level

beginning

Prerequisite Skills

basic knowledge of SQL

Operating System

n/a

Tools

none

Sample Code

no

Introduction

MySQL is a relational database management system (RDBMS) that touts itself as being "the world's most popular open source database." It is designed for speed, power, and precision in mission-critical applications and under heavy load use.

Note: If you want to sound knowledgeable around the water cooler, the correct pronunciation of MySQL is "my ess cue ell," not "my sequel."

MySQL has been ported to the NetWare operating system. This is great news to developers looking for a low-cost persistent storage solution on NetWare. The current port of MySQL on NetWare is based on the development version "4.0.1-alpha" of the MySQL source code.

As the basis for a tutorial to introduce you to MySQL for NetWare, suppose you have been given a consulting job to create a database with MySQL for NetWare to help the local city zoo manage their animals. In this AppNote you will find the steps to create a very simple version of this database. Most of the steps are valid with MySQL on any operating system; NetWare-specific information will be so noted.

Note: Throughout this AppNote, "SERVER:" will be used to represent your server's console prompt.

Installing MySQL on a NetWare Server

Your first step is to install MySQL for your NetWare server. MySQL is currently supported on NetWare 6 (SP1 or greater) with updated NetWare LibC libraries. This section is NetWare-specific.

Updating LibC

You need to complete the following steps to update your LibC libraries on your NetWare server.

Note: You can load "DOSFAT.NSS" on the server's command prompt to create a volume for the C drive. This will simplify steps 2 and 3.

  1. Download and install the LibC package onto your client machine. You can obtain the LibC package from the software "download" link at http://developer.novell.com/ndk/libc.htm.

  2. Copy all the "*.nlm" files from the LibC package to the "C:\NWSERVER" directory on your server.

  3. Copy all the "*.msg" files from the LibC package to the "C:\NWSERVER\NLS\4" directory on your server.

  4. Reboot your server.

Installing MySQL for NetWare

  1. Download the MySQL for NetWare distribution zip file. You can obtain the zip file from the "download" link next to "Leading Edge 167" on http://developer.novell.com/ndk/leadedge.htm. (Since the exact release date for this file was not known at the time this AppNote was written, keep checking this site to see when the file is available.)

    Note: The next steps install MySQL onto the "SYS:\" volume. You can install MySQL on any other volume by replacing "SYS:\" with your volume name in steps 2 and 3.

  2. Extract the zip file to the root of the "SYS:\" volume on your server.

  3. Add "SYS:\MYSQL\BIN" to the server's search path by typing the following command at the server console:

    SERVER: SEARCH ADD SYS:\MYSQL\BIN

    Note: This command should also be added to the AUTOEXEC.NCF file in the SYS:\SYSTEM directory.

  4. Enter the following command at the server console to create the required initial database:

    SERVER: mysql_install_db

    You may be prompted to "press any key" after the process has finished.

MySQL is now properly installed on your server. You are ready to start the MySQL Server.

Operating and Monitoring MySQL

You can now operate the MySQL Server and the MySQL Monitor. The monitor is a simple client program that you can use to send SQL commands to the server.

MySQL Server Operation

The preferred way to start the server is via the following command:

SERVER: mysqld_safe

Once the server is running, you can check its status with this command:

SERVER: mysqladmin status

Don't perform this next command at this point in the tutorial, but for your information the preferred method to stop the server is this:

SERVER: mysqladmin shutdown

Note: Many MySQL commands create their own screens on NetWare and may need to be closed by pressing any key. This behavior is specific to NetWare.

Monitor Operation

To start the MySQL monitor, enter this command:

SERVER: mysql

You should see the following displayed on the console screen:

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

Your MySQL connection id is 3 to server version: 4.0.1-alpha-debug

    

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.



mysql>

You can now send commands and SQL statements to the MySQL Server from the monitor's prompt. Note that the commands must end with a semicolon (;) or "\g". For example, to get the status of the MySQL Server, type:

mysql> status;

To exit the monitor, use this command:

mysql> quit;

Don't exit the MySQL monitor just yet, however. You need to be in the monitor to continue with the tutorial.

Creating a Database

At this point you can create the database for the zoo. Databases are easy to create in MySQL. You simply type a command like the following:

mysql> CREATE DATABASE zoo;

You can see a list of the databases currently hosted by your MySQL Server with this command:

mysql> SHOW DATABASES;

You will see three databases listed: mysql, test, and zoo. The "test" database is empty and is there for your convenience. The "mysql" database is where the MySQL Server stores its own information and settings. The "zoo" database is the database you just created.

You can switch to your new database with the USE command:

mysql> USE zoo;

At any time, you can use the following SQL statement to determine which is the current database:

mysql> SELECT DATABASE();

Don't perform this next command at this point in the tutorial, but for your information it is just as easy to get rid of a database as it was to create it. So after the funding runs out on your consulting job, you can delete (drop) the database as follows:

mysql> DROP DATABASE database;

Creating a Table

With your new database created, you can proceed to create your first table. Perhaps you'd like to create a table for animals in the zoo, which is accomplished as follows:

mysql> CREATE TABLE animal

        -> (name VARCHAR(20),

        -> species VARCHAR(20),

        -> sex CHAR(1),

        -> birth DATE);

Get all the attributes of your table by using this command:

mysql> DESCRIBE animal;

You can see all the tables in the current database with this command:

mysql> SHOW TABLES;

Don't perform this next command at this point in the tutorial, but for your information this command will delete (drop) a table that you don't need any more:

mysql> DROP TABLE table;

Populating a Table

You have several options to populate a table with data. This section covers two basic options.

The first option is to simply use an SQL insert statement, as shown here:

mysql> INSERT INTO animal

        -> VALUES ( Fluffy',  Elephant',

        ->  f',  1997-02-14');

The second method is to load a list of entries from a tab-delimited file, as shown here:

Pokey           Cheetah                     m   1995-04-21

Stumpy          Giraffe                     f   1999-07-04

Tiny            Lowland Gorilla             m   1989-10-31

Midnight        Snow Leopard                f   1985-12-01

Speedy          Radiated Tortoise           m   1972-01-17

This next command assumes that you have above data, tab delimited, in a file called "animal.dat" in the root directory of the "SYS:\" volume.

mysql> LOAD DATA LOCAL INFILE

        ->  sys:/animal.dat' INTO TABLE animal;

Querying a Table

The next two commands show simple SQL statements that query the data you have in the animal table.

The first command will display all of the records in the animal table.

mysql> SELECT * FROM animal;

+----------+-------------------+------+------------+ | name | species | sex | birth | +----------+-------------------+------+------------+ | Fluffy | African Elephant | f | 1997-02-14 | | Pokey | Cheetah | m | 1995-04-21 | | Stumpy | Giraffe | f | 1999-07-04 | | Tiny | Lowland Gorilla | m | 1989-10-31 | | Midnight | Snow Leopard | f | 1985-12-01 | | Speedy | Radiated Tortoise | m | 1972-01-17 | +----------+-------------------+------+-------------+

The next command will display the names of the animals who were born after 1 January 1995.

mysql> SELECT name FROM animal

        -> WHERE birth >=  1995-1-1';

+--------+ | name | +--------+ | Fluffy | | Pokey | | Stumpy | +--------+

Creating User Accounts

Now that you have a simple database for zoo, you want to make sure that only authorized people at the zoo can muck with the data. To do this, you will want to create user accounts and grant the users specific rights.

Initial User Setup

As mentioned above, the "mysql" database contains the MySQL Server's information and settings. The following two commands take you in for a quick look at some of that information:

mysql> USE mysql;

mysql> SELECT host, user, password FROM user;

+-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | root | | | localhost | | | +-----------+------+----------+

In MySQL, users are always associated with a host or segment of hosts. According to the first line of this table, you have the user "root" who can only connect from the "localhost" and who currently has no password.

Warning: If you don't change the root password, anyone that can get to your server's console can log in and do everything that you have been doing in this tutorial.

You can change the "root" user password with this command:

mysql> SET PASSWORD FOR root@'localhost' =

        -> PASSWORD( secret');

Now that you have a password associated with you "root" user, you will need to add a "-p" option to the starting of the monitor. This tells the monitor to prompt you for the password.

SERVER: mysql -p

The second line of the above table shows that you also have a "guest" account (a blank user name) that can also connect to the database. Any user name not found in the table is defaulted to the guest account, which only has the rights to see the names of the databases and poke around in the "test" database.

Your problem at this point is that you can only connect to the database from the localhost. To fix this, create another "root" account that can connect from anywhere:

mysql> GRANT ALL ON *.* TO root@'%'

        -> IDENTIFIED BY  secret'

        -> WITH GRANT OPTION;

Here you are granting all rights ( ALL ) on all databases and tables ( *.* ) to the user "root" coming from any host ( % ), with the password "secret", and with the ability to grant rights to other users ( GRANT OPTION ).

Creating the Zookeeper Account

You can now create a new user account for the zookeeper that has full rights, but only inside the "zoo" database:

mysql> GRANT ALL ON zoo.* TO keeper@'%'

        -> IDENTIFIED BY  secret';

Here you are granting all rights ( ALL ) on all the tables in the zoo database ( zoo.* ) to the user "keeper" coming from any host ( % ).

Unfortunately, you now have an interesting side effect. This side effect occurs if you try to connect to the MySQL Server from the localhost with the "keeper" user, as follows:

SERVER: mysql -u keeper -p

If you try to switch to the "zoo" database with the USE command, you will get a "permission denied" error. To understand what is happening here, you need to look at your updated user table.

+-----------+--------+------------------+ | host | user | password | +-----------+--------+------------------+ | localhost | root | 428567f408994404 | | localhost | | | | % | root | 428567f408994404 | | % | keeper | 428567f408994404 | +-----------+--------+------------------+

When MySQL searches for a user, it first finds the best match it can with host. Since there is a localhost with "guest" user (a blank user), MySQL will select the "guest" user before the "keeper@'%'" user when connecting from the localhost.

There are a couple of methods you can employ to work around this side effect.

The first is to create a "keeper@'localhost'" user that mirrors the "keeper@'%'" user:

mysql> GRANT ALL ON zoo.* TO keeper@'localhost'

        -> IDENTIFIED BY  secret';

The second is to remove the "guest" user from the database using the following two commands:

mysql> DELETE FROM user WHERE user= ';

    

mysql> FLUSH PRIVILEGES;

You can see the rights given to a specific user with this command:

mysql> SHOW GRANTS FOR keeper@'%';

Conclusion

In this tutorial you have explored some of the basics of MySQL and created a simple database with MySQL on NetWare. The next AppNote in this series will talk about backup and recovery, and logging.

For Additional Information

For additional information about MySQL, refer to the following resources:

* Originally published in Novell AppNotes

Disclaimer

The origin of this information may be internal or external to Novell. While Novell makes all reasonable efforts to verify this information, Novell does not make explicit or implied claims to its validity.

© Copyright Micro Focus or one of its affiliates