 | MySQL & mSQL |  |
mSQL is really the only database engine that is "install and go." In other words, you can install mSQL and start the server right up and be in business. The mSQL server process is called msql2d (for mSQL 1 servers, it is called msqld). This executable and all of the utilities that come with an mSQL distribution can be found in the bin directory. You can start up an mSQL instance simply by issuing the msql2d command with no command line options.
This default implementation will get you only so far. In most cases, you will want to edit a file in the mSQL distribution directory called msql.conf. This configuration file enables mSQL 2 users (it does not exist under mSQL 1) to specify a few common options. Configuring this file is generally necessary only when you are using some precompiled mSQL distribution.
The mSQL daemon process does not fork. This means that running it from the command line will leave the process running in your terminal unless you explicitly press CTRL-C to kill the process or put the process in the background. Under Unix, you can start the mSQL server from the command line in the background using the following command:
msqld2 &
The following script works to start up an mSQL server process at system start-up and to shut it down cleanly on system shutdown:
#!/bin/sh
if [ $1 = "start" ]; then
if [ -x /usr/local/Hughes/bin/msql2d ]; then
su msql -c '/usr/local/Hughes/bin/msql2d &'
fi
else
if [ $1 = "stop" ]; then
if [ -x /usr/local/Hughes/bin/msqladmin ]; then
su msql -c '/usr/local/Hughes/bin/msqladmin shutdown'
fi
fi
fiThis example script assumes that you want to run mSQL on Unix under the user ID "msql." You should naturally replace it with whatever user ID you wish to run mSQL under and replace /usr/local/Hughes with the directory where you installed mSQL.
While the Win32 does not yet run as an NT service, you can run mSQL from the StartUp folder simply by sticking a shortcut to the msql2d.exe file into the StartUp folder. (We will cover the msqladmin command later in the chapter.)
While mSQL is a very stable product, every once and a while things just happen and the server dies. In particular, earlier versions of mSQL did have stability issues that caused the server to crash unexpectedly. For those occasions, you want to have something running that will check on the status of the database server.
The following Unix script will perform a thorough check to see that the msql2d daemon is still running. If the daemon is inactive, it is restarted and a message is sent to the administrator of the machine.
#!/bin/sh
# Retrieve the process ID of the database daemon
# This is for a default mSQL 2 installation, for mSQL 1
# the line should be:
# PID=`cat /usr/local/Minerva/msqld.pid`
PID=`cat /usr/local/Hughes/msql2d.pid`
# This checks to see if the server process is running.
# Use this line for BSD systems (Linux)
ALIVE=`ps aux | grep $PID | grep -v grep | awk '{print $2}'`
# Uncomment this line for SYSV systems (Solaris)
#ALIVE=`ps -ef | grep $PID | grep -v grep | awk '{print $2}'`
if [ $ALIVE ]
then
REALLY_ALIVE='msqladmin version'
DATE='date'
# If the first word of the result is 'ERROR', or if
# there was no output at all, the msqladmin
# program was not able to connect to the database server
if ! echo $REALLY_ALIVE | grep "^ERROR"
then
exit
fi
if [ ! $REALLY_ALIVE ]
then exit; fi
else
# This should be /usr/local/Minerva/bin/msqld &
# for mSQL 1 installations
/usr/local/Hughes/bin/msql2d &
mail -s "mSQL daemon restarted" [email protected] <<EOM
The mSQL daemon died unexpectedly and was restarted
on $DATE.
Sincerely,
The mSQL Watchdog
EOM
fiOnce started, mSQL is ready to communicate with the outside world in two different ways. Communication with the Internet happens through a TCP/IP port. mSQL listens to port 1112 by default. mSQL 2 listens to port 1114. You can, however, change which port the server listens to either at compile time (mSQL 1) or in the configuration file (mSQL 2).
Internal communication happens through Unix sockets. Unix sockets appear as regular files in the file system of a Unix server. You can distinguish them from regular files because they are followed by the character when using the `-F' flag for the ls command. mSQL 1 uses the file /dev/msql while mSQL 2 uses the file /usr/local/Hughes/msql2.sock.
You may find it useful at times to run more than one database server at a time. Performance is the most common cause for you to want to do this. Because of mSQL's single-threaded nature, it handles all requests serially. If one of your databases -- or even one of your tables -- is responsible for a large percentage of your database hits, applications which hit other databases or tables may end up spending a lot of time waiting on queries that hit that database or table.
You will not encounter this problem with a multithreaded server like MySQL, but you can get around it in mSQL by running multiple mSQL processes. This solution is limited in that only one daemon may have access to a particular database. Without this limitation, two daemons could overwrite each other's changes. You can accomplish this by giving each running msql2d instance its own base directory under which its data is stored. You can do this in mSQL 2 via command line options and the configuration file. Under mSQL 1, you actually have to compile and install mSQL multiple times into multiple installation directories.
To set up the second directory for mSQL 2, use the following:
mkdir -p /usr/local/second_database/msqldb/.tmp cp /usr/local/Hughes/msql.conf /usr/local/Hughes/msql.acl\ /usr/local/second_database chown -R msql /usr/local/second_database
NOTE
Some Unix systems use the mkdirs command instead of mkdir -p while some may not have that option at all. If the option does not exist, you will have to create each directory separately. Also, you need to substitute the user ID under which you run msql2d if it is different from "msql." Finally, the steps are identical if you are working under Windows or OS/2 -- only the command names and the path separator change.
Once the directories are created and the files copied, you need to edit msql.conf in the new directory to change Inst_Dir to point to /usr/local/second_database and TCP_Port to some value that does not conflict with any other TCP service on the server. You can leave the Unix_Port option unchanged since the new socket will go in the new directory.
To launch the new daemon, run the old msql2d command with the following option:
msql2d -f /usr/local/second_database/msql.conf
If you are working with mSQL 1, life is a little more complicated. You specifically have to compile and install mSQL once for each server instance you wish to run. All of your utilities will work with any instance of your server.
We have touched on the mSQL configuration file in places, but we have not really gone into any detail on it. Under mSQL 1, everything except security was defined at compile time. mSQL 2 provides a configuration file that enables you to configure the runtime behavior of your mSQL server. A mSQL configuration file might look like this:
[general] Inst_Dir = c:\usr\local\Hughes mSQL_User = msql Admin_User = root Pid_File = %I\msql2d.pid TCP_Port = 1114 Unix_Port = %I\msql2.sock [system] Msync_Timer = 30 Host_Lookup = True Read_Only = False Remote_Access = True Local_Access = True [w3-msql] Auth_Host = NULL Footer = True Force_Private = False
Like a Windows INI file, the mSQL configuration script is divided into sections, each with its own set of key/value pairs. At this point, the only parts of the mSQL configuration file of interest to us are in the general section.
The mSQL installation directory. More specifically, this directory is where mSQL looks for your ACL file, the mSQL PID file, and the msqldb directory where the server's database directories are housed. Using this configuration key, you can support multiple mSQL servers with a single set of mSQL binaries.
The user ID under which the mSQL process is running.
The user ID allowed to execute administrative commands such as the msqladmin command discussed in the next section of this chapter.
The TCP/IP port to which this server will be listening. Under Unix, you can only choose a port number less than 1024 if the mSQL server is running as the root user.
The name of the Unix socket file. In this file, we used the %I% variable to stand for the value of Inst_Dir.
|  | |
| 5.3. Installing mSQL |  | 5.5. Database Administration |
Copyright © 2001 O'Reilly & Associates. All rights reserved.