Online Databases with MySQL

In this series of articles, spanning a few months, watch out for setting up, configuring and managing a MySQL database on PCQ Linux 7.1 system (given in July 2001). You’ll also find graphical front-end clients for MySQL and setup server-side scripting languages on Apache Web server to interact with data stored in an MySQL database.

The MySQL database is becoming quite popular because it’s mostly free to use, though for some specific commercial uses, a licensing fee is required. You can check out its licensing structure at
www.mysql. com/documentation/mysql/bychapter/manual _Introduction.html#Licensing_policy

RDBMSs (Relational Database Management Systems) are powerful, ‘table-based’ databases, which can efficiently store voluminous data for quick retrieval. Some popular RDBMSs are Oracle, MS SQL Server, Informix, Sybase, Postgres SQL and MySQL. These databases understand a language called SQL (Structured Query Language), which is used to add, delete, and update the data stored in them. With the Internet boom, these databases have matured to interact with server-side scripting languages like ASP, Perl, PHP, and Java Server Pages to display, add and manipulate data through the World Wide Web.

To install MySQL you need the July 2001 issue of PCQuest along with its two CDs. If you are running a PCQLinux 7.1 system, from the following section you can understand the installation of MySQL on it. MySQL database consists of a database server called mysqld and a command-line client called
mysql.

Installation

Log in as root. Mount the second July 2001 CD (CD 2). Change to the directory RedHat/RPMS on the CD and install the RPMs named mysql-3.23.36-1.i386.rpm and mysql-server-3.23.36-1.i386. rpm. These are the RPMs for the MySQL database client and MySQL database server respectively. Use –nodeps option if you are shown a dependency error related to
libz.so.

Testing the installation

From the same machine, try connecting to the MySQL server using the “mysql” client as:

If you get a prompt similar to the following:

followed by a mysql prompt ( mysql> ), it means your MySQL server is up and running. To quit from the client, type “quit”. Instead, if you get an error message saying that “can’t connect to local MySQL server….”, then the MySQL server, mysqld, is not running. Check the status by issuing the following command at the Linux console:

If a message as “mysqld is stopped” is shown then start “mysqld” by issuing the following command:

To start the server automatically on reboots, issue the following command at the Linux command line or console:

Now from among the listed services select “mysqld”.

Understanding MySQL structure

We assume that you are comfortable with RDBMS concepts and know how a RDBMS presents data in the form of a table with columns and rows. In MySQL, all the data (including metadata) goes into files on secondary storage (hard disks). By metadata we mean information about the data itself. The RPM installation places this data related to MySQL in the directory /var/lib/mysql which is called the “datadir”. The RPM installation creates a user called “mysql”, who owns this directory as well as its subdirectories and files. This means only if you are logged in as “mysql” (or root, as root can access any file) or a process invoked as user “mysql” (or root) can you access this directory. When you run mysqld as root (as described above), effective user of the process switches to the user “mysql” as running a process as root is not safe.

Each subdirectory within the /var/lib/mysql represents a database. For example, the presence of subdirectory “mysql” and “test” indicates that there are already two existing databases installed during the MySQL installation. If you change to the mysql subdirectory, you can see six file names called columns_priv, db, func, host, tables_priv and user. There are three instances of each file, with extensions “frm”, “MYD” and “MYI”. The files with extension “frm” contain the structure of the tables, that is, the column names in the table, their types, etc. The files with extension MYI contain the table indexing information and the files with extension MYD contain the actual data in each table. The combination of these three types of files represents a table in the database. So for instance, the files db.frm, db.MYD, and db.MYI would represent one table named db in the mysql database.

Caution! Do not edit any file or change its permissions as this may corrupt the database or make these files inaccessible by the “mysqld” server. The information provided here is just to tell you how MySQL organizes the data.

The Metadata in “mysql” database

Understanding the pre-existing database “mysql” is all you need for user-administration of the MySQL database. Through this database you can add users who can access the database, specify the trusted hosts which can connect to the database, specify the level of access of each user per database, per table and per column within the table, etc.

Fixing some default glitches

By default any user (anonymous) can connect to the MySQL database server from the same machine (localhost) on which the server is running. Try the following. Just issue “mysql” command, which invokes the MySQL command line client. And voila, you can see the mysql prompt which means you have connected to the server. In this case you have connected as an anonymous user. But once connected, an anonymous user can only access a database with the name “test” or any database with its name starting with “test_”. Who is an anonymous user in MySQL lingo? A user whose exact user name or login name is not present in the “user” table of the “mysql” database is an anonymous user. Such users map to a blank ‘’ user name. Thus, even if you connect to the MySQL server using any username, say “shekhar”, as:

it would be considered an anonymous connection since there is no entry for username “shekhar” in the “user” table. Note that the —u option is used to specify the username. You cannot access the “mysql” database. Try issuing the command “use mysql;” at the mysql prompt and you will get an access denied message. The “use ” is an SQL query to change and use a specified . Note that a “;” (semi-colon) marks the end of an SQL query at the mysql prompt.

However, there is a username “root” specified by default in the “user” table. So if you login as user root (this username has nothing to do with the Linux user “root”) then it’s not considered an anonymous login. Now access the mysql database as user “root” using the following command:

You can now access “mysql” database by issuing “use mysql;” at the mysql prompt. Next, issue the following SQL query:

The above query will show you the “host” and “user” column of the “user” table. Do not confuse. There is a column named “user” in the “user” table of the “mysql” database. The output shown would be something as follows:

Here shekhar.pcqlabs.com is the hostname of the machine on which the MySQL server is running. In your case, the entry “shekhar.pcqlabs.com” would be substituted by the host name of your machine. Note the blank entries in the first and the third row in the “user” column. These blank entries together with the corresponding entries in the host column allow anonymous logins from the same machine. If you are security conscious you should immediately remove these entries by issuing the following SQL query:

Note that there is no space between ‘ and ’. Now quit from the mysql prompt and then type the following command.

This command makes the MySQL server aware of the new settings. This is essential as the MySQL server refers to the “user” table only once at its start-up to determine the access settings.

The next problem is, you can log in as user “root” without a password. This happens because again “password” column in the “user” table is again blank. To set a password for the “root” user in the “mysql” database, issue the following:

Here in place of “pcq” substitute your preferred password. You could have done this by using an SQL query but using “mysqladmin” is easier (as the former involves using a password encryption function in the query). Now you cannot connect to the MySQL server just by issuing:

You will get an access denied message. Instead to connect to the server you must use the following command:

You will be shown a “Enter password :” prompt where you enter the password you had set above which is “pcq” in our case.

The tables in the “mysql” database are called grant tables. We’ll look at them in detail next month.

Shekhar Govindarajan