Database Administration (MySQL & mSQL)

5.5. Database Administration

Now that your server is running 24 hours a day, 7 days a week, your next task is to get it to serve your database needs. mSQL provides a set of utilities that support easy server administration.

5.5.1. The msqladmin Utility

The msqladmin utility is your primary database administration tool. It supports creating, deleting, copying, renaming, and examining your mSQL databases. If you look back to our discussion of what a database is in Chapter 1, "Introduction to Relational Databases", you will remember that mSQL itself is not a database. Your databases are the collection of files under each directory in the msqldb directory. mSQL is the database engine. One engine can serve many databases simultaneously. The msqladmin utility lets you administer the databases under a particular server.

5.5.1.1. Database creation

The first thing you want to do with your new mSQL installation is create a database that serves some purpose for you. The syntax of creating a database is:

msqladmin create DATABASENAME

In this syntax, DATABASENAME is the name of the new database you wish to create. This command will create a new, blank database with the name you specify. As we mentioned earlier, a database in mSQL is simply a directory under the msqldb directory in the mSQL installation. mSQL places all the data associated with your new database in files underneath this directory. For example, if you create a database called "mydata" using a default mSQL installation, the directory /usr/local/Hughes/msqldb/mydata will appear.

5.5.1.2. Database destruction

During the process of developing a new database application, you will likely want to create several databases to support the development process. For example, it is common in database application development to have separate databases for development, testing, and production. When development is complete, it is time to get rid of the development and testing databases. The msqladmin utility provides the "drop" option to let you delete a database:

msqladmin drop DATABASENAME

As with the msqladmin create command, DATABASENAME is the name of the database you wish to destroy. mSQL will not let you accidentally drop the database. After issuing this command, it will warn you that dropping a database is potentially a very bad thing to do and ask you to confirm the drop. You can examine the msqldb directory after dropping the database to verify that the directory that once served as that database no longer exists.

5.5.1.3. Database renaming and copying

A convenient new feature of mSQL 2 is the ability to copy and rename databases. Under mSQL 1, you could drop to the file system and do a manual rename or copy of the database directory using the renaming and copying tools of your operating system. If you go that route, however, you also have to remember to restart the mSQL server and make sure you have not messed up any permissions. With mSQL 2, however, a rename is as simple as:

msqladmin move OLDNAME NEWNAME

For example, if you had created a database with the misspelled name "midata" and you wanted to rename it to the proper spelling, you would issue the command:

msqladmin move midata mydata

Copying is just as simple:

msqladmin copy mydata mynewdata

5.5.1.4. Server status

If you have been playing with MySQL, you will notice that the monitoring of server status is one area in which mSQL is decidedly lacking. The msqladmin utility is your interface into monitoring your servers. The msqladmin stats command under mSQL 2 will produce the following output:

Server Statistics
-----------------

Mini SQL Version 2.0.4.1 - Forge Alpha Build #9
Copyright (c) 1993-94 David J. Hughes
Copyright (c) 1995-98 Hughes Technologies Pty Ltd.
All rights reserved.

Config file      : c:\usr\local\hughes\msql.conf
Max connections  : 61
Cur connections  : 1
Running as user  : UID 500

Connection table :
  Sock  Username    Hostname    Database    Connect   Idle   Queries
 +-----+----------+---------- +------------+---------+------+--------+
 |   5 | randy    | Unix sock | No DB      |  0H  0M |    0 |      1 |
 |  13 | bob      | client.com| mydata     |  0H  5M |    2 |      4 |
 +-----+----------+------ ----+------------+---------+------+--------+

This output likely needs a little explanation:

Max connections: The maximum number of simultaneous connections that the server can handle.
Cur connections: The current number of connections to the database server.
Sock: The Internet socket number used by mSQL to identify each connection.
Username: The username of the person connected to the server.
Hostname: The hostname of the machine connected to the server. "Unix sock" is shown if the client is connecting from the local host via a Unix socket.
Database: The name of the database to which the user is currently connected. "No DB" means that the client has not chosen a database.
Connect: The total time the client has been connected to the server in hours and minutes.
Idle: The amount of time in minutes since the client's last query.
Queries: The total number of queries the client has sent using this connection.

In addition to the msqladmin stats command, you can monitor other, more static, server information via the msqladmin version command. Your output might look something like this:

Version Details :-

    msqladmin version       2.0.4.1 - Forge Alpha Build #9
    mSQL server version     2.0.4.1 - Forge Alpha Build #9
    mSQL protocol version   23
    mSQL connection         127.0.0.1 via TCP/IP
    Target platform         CYGWIN32_NT-4.0-i586

Configuration Details :-

    Default config file     c:\usr\local\hughes/msql.conf
    TCP socket              1114
    Unix socket             c:\usr\local\Hughes\msql2.sock
    mSQL user               msql
    Admin user              root
    Install directory       c:\usr\local\Hughes
    PID file location       c:\usr\local\Hughes\msql2d.pid
    Memory Sync Timer       30
    Hostname Lookup         False

Each of the values in the output of msqladmin version can be set via the mSQL 2 configuration file.

If mSQL has been compiled with debugging enabled, mSQL will place information about the running server process into the debug file chosen at compile time. mSQL provides no other logging facilities.

5.5.1.5. Server shutdown

You saw an example of how to shutdown mSQL earlier in the chapter in the example Unix startup/shutdown script. The command is:

msqladmin shutdown

This command will perform a clean shutdown of the mSQL server.

5.5.1.6. Reloading server changes

If you make any changes to the mSQL ACL, you will need to tell the server to reload those changes. The command to do this is:

msqladmin reload

We will cover the mSQL ACL later in the chapter.

5.5.1.7. Command line options for msqladmin

In all of the examples we have given so far, msqladmin has been used to manage the mSQL server on the local host with the default configuration file. You can use this tool to manage servers on other machines or that use different configuration files. The full syntax for the msqladmin utility is:

msqladmin [-h host] [-f conf] [-q] COMMAND

The options have the following meanings:

-h: The host of the server you wish to manage.
-f: The configuration file for the server you wish to manage. You will only likely ever use this option if you are running multiple mSQL instances as described earlier in the chapter.
-q

5.5.2. Backups

Good backups are a vital part of any administration scheme. Database corruption can happen and, if severe enough, can cripple all applications that depend on the database. As the saying goes, data is only as good as the most recent backup.

There are a couple of backup methods available when using mSQL. Like most of mSQL, they do not provide all of the bells and whistles but they do get the job done. The msqldump command is the most commonly used method for backing up mSQL databases. This command produces a full standard SQL dump of an entire database. You must issue a separate msqldump command for each database on the system. For example:

msqldump database1 > /usr/backups/database1.sql.daily
msqldump database2 > /usr/backups/database2.sql.daily
msqldump database3 > /usr/backups/database3.sql.daily

This example creates a dump of three different databases into the same directory.

You use the file extension daily to indicate that the backup files are replaced every day. How often you backup your data will depend on the importance of the data and the amount and type of backup storage you have available. Because only full dumps are available from mSQL, the size of the backups can grow large on systems that contain a great deal of data. If you have enough space, a good backup plan is to keep separate daily backups for each day of the week -- or for two weeks or even a month. At the end of the cycle, the tapes are reused as needed -- or the files are overwritten if backing up to hard disk. This way there is always one week of data available. If backing up to hard disk, you can possibly condense the individual daily backups into a single daily backup that is overwritten each day. In this case, you should also keep a separate weekly backup in order to recover accidentally deleted data that cannot be found on the most recent backup. Because of the lack of redundancy, this plan should only be used if you have a limited amount of backup space available.

The other method of backing up mSQL databases involves taking advantage of the simple nature of the mSQL data files. Unlike some database packages, mSQL keeps its data in regular files stored in the native operating system's file system. Thus is it possibly to act on these files as if they were any other type of file. Therefore a complete backup of an mSQL system can be obtained by shutting down the mSQL server and then creating a tar archive of the mSQL data directory. You must shut down the server first or the archive data files could be incomplete or corrupt.

Restoring data from mSQL data backups is as simple as creating the backups. Dumps created with msqldump are in standard SQL that can be fed to the msql monitor. These dumps contain the instructions to create the tables as well as the data, so you should either remove the existing table, if it exists, or remove the CREATE TABLE statement from the backup file. If you are restoring only specific rows of data, you can simply copy them out of the backup file and feed them into the msql monitor.

When restoring data from a tar archive of the mSQL data directory, it is only possible to do a full restore to the backed up state. You cannot restore only parts of the data, and any data that has been added to the database since the backup will be lost. To perform this restore, simply shutdown the mSQL server then enter the backup file into the mSQL data directory. When you restart the server, it will be in exactly the state it was in when the backup was performed -- except that any new databases added will still be there, untouched.

Which method you choose to use depends on your needs. Creating a binary archive is simple and complete and allows for a very fast recovery time. However, it is not possible to do a partial recovery and any data you have added since the backup will be lost. The SQL dump method on the other hand can be slow, but it allows for partial recovery, albeit with a little work. In addition, the SQL dump method can be performed at any time, while a binary archive required the server to be shut down, which can be a deciding factor in a busy installation.

One final concern to consider is portability. Unlike a binary backup, a SQL dump consists entirely of plain ASCII SQL commands. With little modification -- mainly weeding out any of the mSQL specific SQL -- a mSQL SQL dump can be imported into any standards compliant SQL server. This is a very handy way to transport your data if you ever need to switch SQL servers.

5.5.3. Security

Depending on your point of view, the mSQL security scheme is either one of its advantages or one of its disadvantages. On the one hand, mSQL's security is easier to manage than any other server-based relational database engine available. It accomplishes this ease of maintenance through simplicity. Unfortunately, this simplicity is insufficient for even moderately complex database applications.

mSQL manages security through a file called msql.acl stored in the mSQL installation directory. The .acl extension refers to "Access Control List," a very flexible form of authorization that has been in use on several operating systems and applications for some time. The format of the msql.acl file looks like this:

database=mydata
read=*
write=*
host=*
access=local,remote

database=mynewdata
read=*
wriite=admin,root
host=*
access=local

Each database has a stanza of options. The read and write lines indicate which users you want to give read (SELECT) or write (INSERT,UPDATE,DELETE) access to the database. The host line specifies which hosts can connect remotely to the database. If the access line contains "local," local Unix socket connections are allowed. Similarly, if the access line contains "remote," remote TCP connections are allowed.

The ACL file allows a "*" wildcard entry in the read, write, and host fields. You could therefore have the following ACL:

database=mynewdata
read=*
write=msql*
host=*.client.com,*isp.com
access=local,remote

Under this ACL, anyone from any host at client.com or anyone from any host at any domain ending in isp.com -- for example, wisp.com and lisp.com -- can connect to the database. These users can read from the database, but only user names beginning with "msql" can modify the database.

By default, everything is excluded. Thus, if you leave out a write entry, nobody can modify the database. Specific users and hosts may be excluded by prefixing their entries with a "-". Consider the following:

database=moredata
read=-bob,*
write=jane
host=-junk.isp.com,*.isp.com
access=local,remote

This ACL enables all machines from the isp.com domain to connect, except for junk.isp.com. In addition, everyone except "bob" can read from the database. Only "jane" can write to the database. Because rejection is mSQL's default, having specific rejection entries like "bob," is meaningless unless the line also contains a wildcard entry.

mSQL acts on the first match it encounters. If, for example, the wildcard in the read entry came before "-bob," the wildcard would have matched "bob" and "bob" would have read access.

The msqladmin reload command, as noted earlier in the chapter, reloads an ACL after you have made any changes. If you fail to issue the msqladmin reload command, your changes will not be seen until the server shuts down and starts back up.