Running mSQL (MySQL & mSQL)

5.4. Running mSQL

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 &

#!/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
fi

This 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.

#!/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" root@yourmachine.com <<EOM The mSQL daemon died unexpectedly and was restarted on $DATE. Sincerely, The mSQL Watchdog EOM fi

5.4.1. Running Multiple Daemons

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.

[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

5.4. Running mSQL

5.4.1. Running Multiple Daemons

NOTE

5.4.2. The mSQL Configuration File