Tuesday, July 10, 2007

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
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
sleep 1
echo -n "."
# 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


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:

Using MySQL
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

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

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';

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


Paddy said...

Nice tip. I've bookmarked it in our MySQL section.

Kris said...

Thanks, I'm glad you found it useful.
I've been looking around your site, very interesting. There are some awesome, very helpful links: TekTag.com.
Thank you!

PacoBell said...

Are you sure the syntax to change the admin name is accurate? It failed for me with:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TABLE SET user='dbadmin' WHERE user='root'' at line 1

And, according to the MySQL manual, the UPDATE directive looks much different from what you demonstrated here. Can you please clarify this for me? Thanks.

Kris said...

Yes you are absolutely right Pacobell, there is an error in my syntax, it should be:
mysql> UPDATE USER SET user="dbadmin" WHERE user="root";

I have edited it in the post above, thank you for pointing this out.

iqbal said...

thank you very useful for newbie like me .. keep good posting ...

if dont mind, in my blog, i added your link

from indonesia

Anonymous said...

Very Thanks, After searching hundreds of Tutorials and POSTS I failed to Install MySQL to My OpenBSD5.3 Box, But now Its running fine.

I appreciate it, thanks again,