[OpenBSDsupport]

Install and run MySQL 5.x from a package on a OpenBSD 4.0 and later



Install

To install MySQL 5.0.24a (i386) on OpenBSD 4.0 perform the following actions.

# export PKG_PATH=ftp://ftp.openbsd.org/pub/OpenBSD/4.0/packages/`machine -a`/
# pkg_add mysql-server
mysql-server-5.0.24a:mysql-client-5.0.24a: complete
mysql-server-5.0.24a:p5-Net-Daemon-0.39: complete
mysql-server-5.0.24a:p5-PlRPC-0.2018: complete
mysql-server-5.0.24a:p5-DBI-1.51: complete
mysql-server-5.0.24a:p5-DBD-mysql-2.9004: complete
mysql-server-5.0.24a: complete
--- mysql-server-5.0.24a -------------------
Installing all prepared tables
Fill help tables
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/local/bin/mysqladmin -u root password 'new-password'
/usr/local/bin/mysqladmin -u root -h your.host.tld password 'new-password'
See the manual for more instructions.

Please report any problems with the /usr/local/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com

Now the software is installed and you can see this too

# pkg_info
mysql-client-5.0.24a multithreaded SQL database (client)
mysql-server-5.0.24a multithreaded SQL database (server)
p5-DBD-mysql-2.9004 MySQL drivers for the Perl DBI
p5-DBI-1.51 unified perl interface for database access
p5-Net-Daemon-0.39 extension for portable daemons
p5-PlRPC-0.2018 module for writing rpc servers and clients

Password

Then make sure you change the password for your installation. You did just that, right?

Limits

On misc@ there's way too many users saying they have ERROR #9, lock up, unstable systems, etc. when running MySQL on OpenBSD. This is completely unnecessary! If your servers have more than 29 tables and you try to open them all, then you will hit the default limits, so just change them. Usual limits reached by various installations are:

the number of connections
the number of tables (files) open
the kernel limits to protect your system
etc.
So, be wise and change what you need to change for your setup! But only what you need to absolutely change. Don't go nuts and start turning knobs left and right. That may well be what you need to do on some other Unix, or variations of... But on OpenBSD the default setup is really good and is done as such to protect youself. The bottom line is: don't change what you don't need to change and know what you do and why!

Here is a very simple way to test if you reach some of these limits for the number of tables you have as an example. Just do a mysqlcheck on them and if you reach the limits on the max files as an example, you will see it right away.

mysqlcheck -m -A -uYourUsers -pYourPassword
I only use -m here for it to be faster if you have a lots of data. You can use -e to do a full test, or do a quick one.
Refer to man 1 mysqlcheck for more details.

/etc/sysctl.conf

The first you might see are the limits on open tables. So, you need to increase this in mysql configuration, but also in the system as well. So, I do a simple change in my sysctl.conf file to start.

The default value are good for most, but may be you have more tables. So, check for it!

# sysctl kern.maxfiles kern.maxfiles=1772

Add in /etc/sysctl.conf

# Increase for MySQL
kern.maxfiles=4096

Also make it active. No need to reboot here. Just use the sysctl utility for it.
man 8 sysctl

# sysctl kern.maxfiles=4096
kern.maxfiles: 1772 -> 4096

Check that kern.maxfiles was indeed changed.

# sysctl kern.maxfiles
kern.maxfiles=4096

/etc/login.conf

Next is the default class your server will run under. On OpenBSD, MySQL runs under the user and class _mysql. These were created at the installation time when you did pkg_add above. So, we will have to use that class. However, if you simply add a startup in your rc.local, you will use the default class of your login.conf section, not _mysql. Classes are applied on login and in this MySQL setup, _mysql doesn't login and doesn't even have a login shell. Remember, MySQL is a daemon and as such you need to tell it to use the class _mysql when you start or restart the daemon, or it uses the default class. Many users either keep forgetting that fact or don't know about it to begin with! Refer to:

man 1 login
man 5 login.conf

So, what I do here is simple aswell. I add a section to my /etc/login.conf. Again, only make changes as you see fit for your setup. Don't just cut and paste without knowing what it does and why. Use values that fit your needs, not some crazy values just to be on the safe side.

So, in /etc/login.conf

# Setting used by MySQL daemon
_mysql:\
        :datasize=infinity:\
        :maxproc=infinity:\
        :openfiles-cur=2048:\
        :openfiles-max=4096:\
        :stacksize-cur=8M:\
        :localcipher=blowfish,8:\
        :tc=default:

/etc/rc.local

Now we have to put all this to use. If you simply start MySQL from your rc.local script, it will use the default class, not the one you added. So, make it your default.

To do this, I simply add this to rc.local. Note that you will get error in your mysql.err file if you put this in rc.conf.local instead. Refer to:

man 8 rc

In /etc/rc.local

# Start MySQL server
if [ -x /usr/local/bin/mysqld_safe ] ; then
        su -c _mysql root -c '/usr/local/bin/mysqld_safe &' > /dev/null & echo -n ' mysql'
fi

Alternatively, you might put this in /etc/rc.mysql and make sure root can execute that file. That way, when stopping and starting MySQL becomes an easier task:

mysqladmin -u root -p shutdown
Enter password: [your password here]
sudo /etc/rc.mysql

This will start MySQL and use the class you created. Refer to:

man 1 su
Remember, if you don't do this, it will use the default class! Same if you restart MySQL manually! Class are read and use on login

/etc/my.cnf

This file, you need to change for your particular needs, of course, and the table type you use. In my case, I use myisam only. So, this obviously reflects it and I also log all changes to my tables as I run slaves too. Again, do as you need. This also runs on a 2GB ram server, so if you have more or less memory, adjust the value for your needs.
Note the part about open files and max connections.
These are very often the case of trouble and that was the point of all the changes we did above: to be able to make use of them properly.

So, /etc/my.cnf could look something like this, but doesn't need to be like this. Again, read the documentations and modify for your needs. I run this:

[client]
port = 3306
socket = /var/run/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
skip-innodb
skip-bdb
skip-locking
safe-user-create
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 1M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 16M
open_files_limit=2048
max_connections = 512
thread_concurrency = 8
log-bin=sql1-bin
server-id = 1

[mysqldump]
quick
max_allowed_packet = 16M

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Starting

Now, you just need to start it and you are done

# su -c _mysql root -c '/usr/local/bin/mysqld_safe &' > /dev/null &
# ps aux
USER PID %CPU %MEM VSZ RSS TT STAT STARTED TIME COMMAND


root 12050 0.0 0.0 596 492 p0 I 4:32PM 0:00.02 /bin/sh /usr/local/bin/mysqld_safe
_mysql 21088 0.5 14.1 294796 294116 p0 S 4:32PM 3:41.97 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/v

Or, depending on your particular choices:

# /etc/rc.mysql
or
$ sudo /etc/rc.mysql

Updates

When you need to update MySQL when upgrading OpenBSD to a new release, you use pkg_add -u and you are home free. Refer to:

man 1 pkg_add