Adding MySQL to my OpenBSD machine

My best friend while doing all of this was the MySQL Reference Manual :-).

The hard part was the fact that this and every thing else I want setup should have been done like 6 or 7 hours ago... But that's my family for ya....

Phase I: Install and Configure the MySQL database service:

My desktop has WAMP installed and a mother load of development tools, my PC-BSD
laptop has the most complete development environment I have access to, and is
where I do all of my real work ^_^.

In order to make some progress in a few bits of playful testing and work that I
do need to tinker with, I've elected to setup things on Vectra to avoid the more
transient nature of the Windows machine here...

This is the notes I've compiled during the process. Managing to sleep off the
headache and get this done before everyone else wakes up... Grr. The machines an
OpenBSD 4.3-Release system working off an old Pentium 3 500Mhz with 384MB of RAM
-- far from ideal for running MySQL but with just me to play with it, it's no
problemo.

ssh2v
 ...
su - root
Password:

pkg_add -iv mysql-server
less /usr/local/share/doc/mysql/README.OpenBSD # refer to the instructions
given

vi /etc/login.conf
 ... # let the login class for _mysql and rebuild the login database
cap_mkdb /etc/login.conf
/usr/local/bin/mysql_install_db
 ... # initialize the database files
vi /etc/rc.mysql
 ...  # quick script to launch mysql properly, listing 2A
vi /etc/rc.local
 ...  # start it during resource configuration, listing 2B
/etc/rc.mysql
/usr/local/bin/mysql_secure_installation
 ...  # secure the installation := -u root -p (pw=V1p3l2)
mysqladmin -u root -p status
 ...
mysql -u root -p -h localhost # set up our databases using the mysql client
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.0.51a-log OpenBSD port: mysql-server-5.0.51a

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER trowa IDENTIFIED BY '*********';                   
Query OK, 0 rows affected (0.03 sec)

mysql> CREATE DATABASE SpidersWeb
    -> ;
Query OK, 1 row affected (0.01 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema | 
| SpidersWeb         | 
| mysql              | 
+--------------------+
3 rows in set (0.00 sec)

mysql> GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, DELETE, DROP,
    -> EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, CREATE VIEW, SHOW VIEW,
    -> UPDATE ON SpidersWeb.* TO 'trowa'@'%';
Query OK, 0 rows affected 
mysql> exit
Bye

That basically allows the database user to do just about everything to the
specified database from anywhere. I considered restricting access further but am
not in the mood to screw with changing it later should it become necessary (and
I hate setting up replacement routers, which happens every now and then).

Since every thing in the mysql client ends up in ~/.mysql_history, including the
password used in the CREATE USER statement. I am also rather glad that OpenBSD
keeps everyones nose out of /root by default, I plan on shredding the file:

rm -P /root/.mysql_history

for safety.

Phase II: Verify it works!

To make sure every thing works out properly enough (considering the current
local time!). I opened another urxvt on my laptop and connected to the server
machine.

Terry@dixie$ mysql -h vectra -u trowa -p SpidersWeb                        5:44
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 5.0.51a-log OpenBSD port: mysql-server-5.0.51a

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SHOW TABLES
    -> ;
Empty set (0.01 sec)

mysql> exit
Bye
Terry@dixie$                                                               5:45

Various notes

I found the kern.maxfiles sysctl and standard issue /etc/my.cnf file suitable
for my needs (for now). So no need to screw with them tonight, later on I need
to work on setting up httpd and things... Already 0600Z and work is early
tomorrow so no time to sor that out

Rather then muck about, in case I need to stop and start mysqld I moved the
startup from /etc/rc.local to calling a shell script to run it properly. That
way OpenBSD should allow mysqld sufficant file handles and I can control things
via /etc/my.cnf if I wish to lower it.

Listing 1: /etc/login.conf

#
# This class is used when running MySQL from /etc/rc.local
# XXX: It will *N_O_T* be used when starting/stopping mysqld manually!!
#
_mysql:\
        :ignorenologin:\
        :datasize=infinity:\
        :maxproc=infinity:\
        :openfiles=3580:\       # I've set this to sysctl::kern.maxfiles
        :stacksize-cur=8M:\
        :localcipher=blowfish,8:\
        :tc=daemon:

Listing 2A: /etc/rc.mysql

#!/bin/sh
#
# A simple script to launch mysqld with the proper login privledges
#

su -c _mysql root -c '/usr/local/bin/mysqld_safe >/dev/null 2>&1 &'
echo -n ' mysql

Listing 2B: edits to /etc/rc.local

# launch the MySQL database server
if [ -x /usr/local/bin/mysqld_safe -a -x /etc/rc.mysql ]; then
        /etc/rc.mysql
fi