Friday, April 1, 2005

MySQL Tweaking

MySQLAfter setting up WordPress and another MySQL-based application on my OpenBSD 3.6 server and then referencing a link to my website on OpenBSD's misc @, MySQL kept dying. The biggest problem with that was: WordPress's database errors occurred prior to HTML headers being sent. So, I never saw any reference to any errors until using curl to download the page.

To fix the problem, I googled and searched the misc @ archives. I found several references to mysql problems and the solution finally came down to the following:

  • Editing the /etc/login.conf file, adding this section:

  • Then, I added an /etc/my.cnf (MySQL config file) with these contents:
    set-variable = max_allowed_packet=64M
    set-variable = max_connections=1000
    open-files = 2048

  • Finally, I changed the MySQL startup command in /etc/rc.local to:
    sudo -c mysql -u _mysql /usr/local/share/mysql/mysql.server start
    sudo -c mysql /usr/local/share/mysql/mysql.server start
    [Edit: (2005-04-04 @ 7:15 PM ET) It seems that starting MySQL from /etc/rc.local with sudo -u _mysql caused MySQL not to start, so I removed the "-u _mysql"]

To aid in starts, stops and re-starts, I made aliases in my /etc/profile:

mysql-restart='sudo sudo -c mysql -u _mysql /usr/local/share/mysql/mysql.server restart'

mysql-start='sudo sudo -c mysql -u _mysql /usr/local/share/mysql/mysql.server start'

mysql-stop='sudo sudo -c mysql -u _mysql /usr/local/share/mysql/mysql.server stop'

Note the double sudo. When I didn't run "sudo sudo ...", I got a message saying that only root could launch a command with sudo and "-c class". The double sudo fixed that. My user account is in the wheel group which, in the /etc/sudoers, is allowed to run any command as root (as long as members authenticate with their password).