I've sucessfully installed mSQL on Linux, FreeBSD, NetBSD, OpenBSD, SCO OpenServer and Solaris. I've had to compile it from source on all platforms except SCO OpenServer. mSQL is available from the mSQL home page. It compiles cleanly on all platforms, though you will need yacc (or bison from GNU binutils). By default, mSQL installs itself in /usr/local/Hughes. Note that you should add /usr/local/Hughes/bin to your PATH environment variable.
When compiling from source, you should create a user named msql, assign it a password, then compile and install mSQL as that user.
SCO OpenServerFor SCO OpenServer, mSQL packages are available on the Skunkware CD or from the SCO OpenServer support section of the Caldera website. SCO OpenServer packages are often called VOL's because they come as a set of files named VOL.000.000, VOL.000.001, etc. These VOLS can be installed using the Software Manager (custom). On SCO OpenServer, when the package is installed, it creates a msql user and sets proper permissions for everything.
Configuring mSQLOnce mSQL is installed, you should examine it's configuration file /usr/local/Hughes/msql.conf. Make sure that the mSQL_User parameter is set to msql and the Admin_User is set to root. Also make sure that the directory identified by the Inst_Dir paremater and /usr/local/Hughes/msqldb are writable by msql. This should be the case if the msql user installed the software or if it was installed from a package.
By default, mSQL is configured to allow access from clients on the local machine only. To allow access to remote clients, set the Remote_Access parameter to True. You should use caution when doing this though, mSQL is exceptionally insecure when remote access is enabled.
Starting the Database at Boot TimeThe package distribution of mSQL installs a script which starts the database at boot time. If you compiled from source, you'll need to install a script like the following to start the database at boot time.
#!/bin/sh
case "$1" in
start)
su -l msql - "/usr/local/Hughes/bin/msql2d > /dev/null 2> /dev/null &"
;;
stop)
kill `ps -efa | grep msql2d | grep -v grep | awk '{print $2}'`
;;
*)
echo $"Usage: $0 {start|stop}"
exit 1
esac
exit 0
Install this script and run it with the "start" option to start up the database. Running it with the "stop" option shuts the database down. To access a database, it must be running.
Creating a DatabaseAfter installation, mSQL is ready to use but to do any useful work, you'll have to create a database.
The following command creates a database named testdb. This command must be run as the Admin_User as defined in msql.conf, usually root.
msqladmin create testdb
You can log into your new database as follows.
msql testdb
Once you're logged in, you can create, drop and modify tables and run queries in your database.
By default, mSQL is quite insecure. It allows any user to perform any operation they like. When remote access is enabled, mSQL is exceptionally insecure. To secure an mSQL database, create an Access Control List file called /usr/local/Hughes/msql.acl with the following contents.
database=testdb
read=*
write=testuser
access=local
host=*
This example defines permissions for the testdb database. It allows any user to read the database but only the testuser unix user to write to it. Also, access is restricted to clients running on the same machine as the database. Each block of parameters beginning with "database=" controls access to that database, so access control can be defined for multiple databases from a single /usr/local/Hughes/msql.acl file.
If you want to drop the database, you can do so with the following command. This command must be run as the Admin_User as defined in msql.conf, usually root.
msqladmin drop testdb
This should be enough to get you started. To set up more complex configurations, consult the mSQL online documentation.
Accessing a DatabaseAccessing a mSQL database using the msql client tool is simple. For example, to access a database called testdb on the local machine, use the following command.
msql testdb
If you want to access a database on a remote machine, say on testhost, use the -h option as follows.
msql -htesthost testdb
Note that in both cases, you'll have to be logged in as a user that is allowed to access the database.
Once you're connected to the database, the msql client prompts you to enter a query. Queries may be split across multiple lines. To run a query, enter a \g on a line by itself. To exit, type \q.
A sample msql session follows.
[testuser@localhost testuser]$ msql testdb
Welcome to the miniSQL monitor. Type \h for help.
mSQL > create table testtable (
-> col1 char(40),
-> col2 integer
-> )
-> \g
Query OK. 1 row(s) modified or retrieved.
mSQL > insert into testtable values ('hello',50)
-> \g
Query OK. 1 row(s) modified or retrieved.
mSQL > insert into testtable values ('hi',60)
-> \g
Query OK. 1 row(s) modified or retrieved.
mSQL > insert into testtable values ('bye',70)
-> \g
Query OK. 1 row(s) modified or retrieved.
mSQL > select * from testtable
-> \g
Query OK. 3 row(s) modified or retrieved.
+------------------------------------------+----------+
| col1 | col2 |
+------------------------------------------+----------+
| hello | 50 |
| hi | 60 |
| bye | 70 |
+------------------------------------------+----------+
mSQL > update testtable set col2=0 where col1='hi'
-> \g
Query OK. 1 row(s) modified or retrieved.
mSQL > select * from testtable
-> \g
Query OK. 3 row(s) modified or retrieved.
+------------------------------------------+----------+
| col1 | col2 |
+------------------------------------------+----------+
| hello | 50 |
| hi | 0 |
| bye | 70 |
+------------------------------------------+----------+
mSQL > delete from testtable where col2=50
-> \g
Query OK. 1 row(s) modified or retrieved.
mSQL > select * from testtable
-> \g
Query OK. 2 row(s) modified or retrieved.
+------------------------------------------+----------+
| col1 | col2 |
+------------------------------------------+----------+
| hi | 0 |
| bye | 70 |
+------------------------------------------+----------+
mSQL > drop table testtable
-> \g
Query OK. 1 row(s) modified or retrieved.
mSQL > \q
Bye!
Accessing a Database With SQL RelayAccessing mSQL from SQL Relay requires an instance entry in your sqlrelay.conf file for the database that you want to access. Here is an example sqlrelay.conf which defines an SQL Relay instance called msqltest. This instance connects to the testdb database on the local machine. Note how the runasuser and runasgroup attributes are set to testuser, a user which is allowed to access testdb.
<?xml version="1.0"?>
<!DOCTYPE instances SYSTEM "sqlrelay.dtd">
<instances>
<instance id="msqltest" port="9000" socket="/tmp/msqltest.socket" dbase="msql" connections="3" maxconnections="5" maxqueuelength="0" growby="1" ttl="60" endofsession="commit" sessiontimeout="600" runasuser="testuser" runasgroup="testuser" cursors="5" authtier="listener" handoff="pass">
<users>
<user user="msqltest" password="msqltest"/>
</users>
<connections>
<connection connectionid="msqltest" string="db=testdb" metric="1"/>
</connections>
</instance>
</instances>
If you want to connect to a database on a remote machine, say on testhost, you would need to add host=testhost; to the string attribute of the connection tag.
Now you can start up this instance with the following command.
sqlr-start -id msqltest
To connect to the instance and run queries, use the following command.
sqlrsh -id msqltest
The following command shuts down the SQL Relay instance.
sqlr-stop msqltest