OpenBSD Tip: Installing MySQL Database Server

Installing the MySQL Database Server, in OpenBSD is needed for a number of different applications. Including Wordpress, which is what I have been playing with today.

Installing MySQL
First if you don't have PKG_PATH set then set this first, then install mysql-server. For a package server near you please see: FTP mirrors
# export PKG_PATH=ftp://ftp.openbsd.org/pub/OpenBSD/4.1/packages/i386/
# pkg_add mysql-server
mysql-server-5.0.33:mysql-client-5.0.33: complete
mysql-server-5.0.33:p5-Net-Daemon-0.39: complete
mysql-server-5.0.33:p5-PlRPC-0.2018p0: complete
mysql-server-5.0.33:p5-DBI-1.53: complete
mysql-server-5.0.33:p5-DBD-mysql-3.0008: complete
mysql-server-5.0.33: complete

Configuring MySQL
The mysql-server package doesn't initialize a default database. The following command will create one:
# /usr/local/bin/mysql_install_db

Now we need to temporarily start mysql to set the root access password for the database. Changing 'new-password' to a password of your choosing.
# /usr/local/bin/mysqld_safe &
# /usr/local/bin/mysqladmin -u root password 'new-password'
# /usr/local/bin/mysqladmin -u root -p -h Your-server.name.tld password 'new-password'

Verify the server is running by using the 'fstat' in the following example:
# fstat | grep "*:" | grep mysql
_mysql mysqld 29321 15* internet stream tcp 0xd6121af4 *:3306

To start MySQL from boot, edit /etc/rc.conf.local:
# vi /etc/rc.conf.local
adding:
mysql=YES
Then edit: /etc/rc.local:
# vi /etc/rc.local
After the 'starting local daemons' and before the following echo '.' Insert the following into the /etc/rc.local file:

if [ X"${mysql}" == X"YES" -a -x /usr/local/bin/mysqld_safe ]; then

echo -n " mysqld"; /usr/local/bin/mysqld_safe --user=_mysql --log --open-files-limit=256 &

for i in 1 2 3 4 5 6; do
if [ -S /var/run/mysql/mysql.sock ]; then
break
else
sleep 1
echo -n "."
fi
done
#
# Apache chroot Settings

mkdir -p /var/www/var/run/mysql
sleep 2
ln -f /var/run/mysql/mysql.sock /var/www/var/run/mysql/mysql.sock

#
# Postfix chroot Settings
if [ "X${postfix_flags}" != X"NO" ]; then
mkdir -p /var/spool/postfix/var/run/mysql
sleep 2
ln -f /var/run/mysql/mysql.sock /var/spool/postfix/var/run/mysql/mysql.sock
fi

fi
Now every time you restart; the machine will check to see whether you have enabled mysql in the rc.conf (rc.conf.local) file then start the mysql daemon. To disable mysql we can simply change mysql=YES to mysql=NO

After restarting verify the server is running again:
# fstat | grep "*:" | grep mysql
_mysql mysqld 29321 15* internet stream tcp 0xd6121af4 *:3306

Once the above startup script has worked you can change the mysqld_safe line to something like:
/usr/local/bin/mysqld_safe --user=_mysql --log --open-files-limit=1000 > /dev/null 3>&1 2>&1 &

For more information, and different configurations please see:

• MySQL
• http://nomoa.com/bsd/mysql.htm
• http://www.openbsdsupport.org/mysql.htm
• Tell me about this chroot(2) Apache?

Using MySQL

• Tutorial Introduction
• MySQL Databases and the SQL Language
• Getting Started with MySQL
• SQL Fundamentals
• MySQL command to show list of databases on server
• How to: Connect to my MySQL Database server using command line and php

MySQL security
Remove history
The MySQL history file ( /.mysql history), contains all executed SQL commands, even passwords, (which are stored as plain text). We can easily clear the contents of this file:
# cat /dev/null > /.mysql_history

Disable remote access
If you are only going to use MySQL on this server, (i.e. the database will be used only by locally installed applications) then you can disable remote access. MySQL listens on tcp port 3306, we can disable listening on the port by doing the following:

# vi /etc/my.cnf
add:
skip-networking

Improve local security
The next change is to disable the use of LOAD DATA LOCAL INFILE command, which will help to prevent against unauthorized reading from local files.
# vi /etc/my.cnf
add:
set-variable=local-infile=0

Change admin name
It is also recommended to change the default name of administrator’s account (root), to a different, and harder to guess one.

mysql> UPDATE USER SET user='dbadmin' WHERE user='root';
mysql> FLUSH PRIVILEGES;

Now you should have MySQL up and running on your OpenBSD server. Good luck, and have fun.