MySQL on OpenBSD 4.3 using the Apache Chroot

I’m back with some more tech geek goodness for anyone who uses MySQL and OpenBSD.

OpenBSD by default apache runs in a chroot jail, thereby making it more secure in case the www server is compromised. I have talked about this before.

However if you use wordpress or some other web application that needs MySQL (and I’m talking where apache and MySQL reside on the same host), then the chroot jail will not allow your webapp access to the mysql socket file which is by default located in /var/run/mysql/

There are various solutions available online to wait x number of seconds and then to create symlinks, etc - but this was not always working for me - with the result the server would sometimes be up without database availability.

Here is my solution (thanks to NoMoa for the idea) - very simple, no postfix symlink but there is a symlink to allow other applications to access MySQL locally via the default socket location.

First I create the run dirs in the apache jail:

mkdir -p /var/www/var/run/mysql
chown _mysql:_mysql /var/www/var/run/mysql
chmod 711 /var/www/var/run/mysql

Then I add this to the bottom of /etc/rc.local on the webhost.

#mySQL
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 --socket=/var/www/var/run/mysql/mysql.sock &
        if [ ! -L /var/run/mysql/mysql.sock ]; then
                mkdir -p /var/run/mysql
                chown _mysql:_mysql /var/run/mysql
                chmod 711 /var/run/mysql
                ln -fs /var/www/var/run/mysql/mysql.sock /var/run/mysql/mysql.sock
        fi
fi

Lastly I add this to my /etc/rc.conf.local

mysql=YES

Note: you may need to change the open-files-limit to suit, but the above paths are as per a standard OpenBSD 4.3 install using the supplied MySQL pkgs.

I tested this all out on OpenBSD 4.3 and it now works fine for me. Whenever the server comes up I never get the dreaded word press DB failure screen. Good luck

Server Migration complete

I’ve had an on/off outage for most of the past 4 days, so to avoid hassles I put the site into maintenance mode.

I’ve actually had to decommission my 19″ server rack and the multiple servers I had for Tech Debug. There was a DNS server, kerberos, Database and web server. All the separate functions are now hosted on one OpenBSD VM that I’ve built recently. It’s running on the only remaining “non-laptop” left in my residence, which runs 24×7.

The only delay in getting back online in the last 48 hours was the MySQL data export and import. It’s a bit convoluted, and not documented in entirely one place on the MySQL documentation site (backup here, restore here), however once done once it will be easy to repeat. I still like the simplicity of PostgreSQL administration. Specifically the backup and restore:

Backup:
pg_dumpall > savefile.dump
Restore:
psql -f savefile.dump postgres

Well, there are some prep steps you should do; but it is all in one place on their documentation page.

Let me know how the site performs on the VM.

Twitter Wordpress plugin - Autolink to Username

Seeing as it is International Wordpress Day, I’ve written a new Wordpress plugin (tested on 2.3) that auto links a twitter at username (like @lantrix) in a post automatically.

Most of the other plugins out there either did this only in their “badge” or “widget” or where too full featured. This is a simple and quick plugin to get links in place to twitter people for the lazy blogger.

Visit my wordpress plugins page, give it a try and let me know what you think.

Coral Content Distributed network

Have you ever had your website hit on a topic that people find conflicting? When the site gets viewed by the masses, you need to be prepared. If you have access to modify your web server .htaccess file then go and have a read of the Coral CDN Overview
For those interested in the techie bits, here is my .htaccess for news site flood protection, and to allow CDN to serve up all my site images - thus offloading from my puny connection the bandwidth burden for images.

<ifmodule mod_rewrite.c>
RewriteEngine On
#prevent slashdot effect
RewriteCond %{HTTP_USER_AGENT} !^CoralWebPrx
RewriteCond %{HTTP_REFERER} blogspot\.com [NC,OR]
RewriteCond %{HTTP_REFERER} reddit\.com [NC,OR]
RewriteCond %{HTTP_REFERER} digg\.com [NC,OR]
RewriteCond %{HTTP_REFERER} news\.slashdot\.org [NC,OR]
RewriteCond %{HTTP_REFERER} slashdot\.org
RewriteRule ^(.*)$ http://techdebug.com.nyud.net/$1 [R,L]
#Rewrite images to allow CDN to serve them
RewriteCond %{HTTP_USER_AGENT} !^CoralWebPrx
RewriteRule ^(.*)/(.*\.(gif|png|jpe?g))$ http://techdebug.com.nyud.net/$1/$2 [R,L]
#Wordpress rewites
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]
</ifmodule>

mod_rewrite is your friend.

Joomla migration of Google Maps API

It looks like I may have successfully written an alpha “migrator plug-in” that may work for migrating your com_google_maps component from Joomla 1.0.x to 1.5.

Once I have tested this on the internal testbed migration sites for one of my customers, I will write it up here.
This will be useful since the developer of the Google Maps API for joomla has no current migration plugins.

Stay tuned….