When you first install the mysql Cydia package, you need to go to Settings.app and turn mysql ON, then OFF and then ON again. This is needed only once.

After that, you need to SSH to your device and run:

ps ax | grep mysql

and check that the MySQL daemon is up and running. This should list something like the following:

 4962   ??  S      0:00.07 /bin/sh /usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/lib/mysql/mysqld.pid
 5013   ??  S      0:00.05 /usr/libexec/mysqld --basedir=/usr --datadir=/var/lib/mysql --user=daemon --pid-file=/var/lib/mysql/mysqld.pid --port=3306 --socket=/tmp/mysql.sock

If not, go back to Settings.app and try to turn it OFF and ON again till MySQL is running.

Next, in the SSH session, issue the following command to set-up the initial databases:

mysql_secure_installation

In case you get a mysql.sock error, then go back to Settings.app and using SSH make sure that MySQL is up and running. mysql_secure_installation will fail if MySQL is not already running.

this cannot be done automatically because it will ask you a series of questions, like setting a root password, whether the test databases should be wiped, etc…

After running mysql_secure_installation you can log-in to your mysql server on your iDevice and have fun:

mysql -u root -p -h DEVICE_IP

and you are done. The rest of the document describes some technical problems we've had as a reference to anybody that may find them useful.

It is important to mention that the way we have set-up the starting and stopping of the mysqld daemon, there is no considerable drain on resources such as battery power or CPU slowdowns. That is because when MySQL is turned off from Settings.app there is nothing that runs in the background. The mechanism uses WatchPaths which is managed internally and cached by the operating system. Similarly, MySQL is a rather stale daemon, even with MySQL running and with the memory limit set at a certain amount, there should be no major drainage.

mysql will be running in a memory and CPU-limited environment, so we compiled mysql using:

./configure --prefix=/usr --sysconfdir=/etc --localstatedir=/var --with-low-memory --without-man --without-bench --without-docs --without-innodb --without-geometry --without-query-cache --disable-largefile --with-mysqld-user=mobile --with-unix-socket-path=/var/mysql/mysql.sock --without-debug --disable-dependency-tracking

which should just cover the MyISAM engine, disable the documentation that takes up space and no largefile support.

Even so, the configuration should allow you to host even the most demanding Wordpress installation (along with lighttpd).

The mysql configuration file can be found at /etc/my.cnf and contains the following tweaked settings:

# Taken and adapted from: http://wiki.vpslink.com/Low_memory_MySQL_/_Apache_configurations

[mysqld]
user=daemon
datadir=/var/lib/mysql
pid-file=/var/lib/mysql/mysqld.pid
socket=/tmp/mysql.sock
log-error=/var/lib/mysql/mysql.err
port = 3306
skip-locking
key_buffer = 16K
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 131072
skip-bdb
skip-innodb

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_size = 8M

[mysqlhotcopy]
interactive-timeout

When we created the mysql package, we wanted to startlaunchd by monitoring a PreferenceLoader preference file using the WatchPaths directive. The problem is that mysql uses scripts to handle the daemonizing part (via mysqld_safe) rather than implementing a fork() call within the mysqld daemon.

This is similar to the lighttpd settings switch for the lighttpd daemon. In lighttpd's case, a process called lighttpd-angel executes a fork(), after which the parent lighttpd-angel returns while leaving the lighttpd daemon running.

launchd is thus supposed to run a single bash script that should start or stop the mysqld daemon. However, even on an iOS device, the mysqld_safe script, and even the mysql.server script run too fast for launchd 10 second timeout.

What happens is that although launchd manages to run our manager script, launchd will kill all spawned children if the manager script terminates before the 10 second timeout.

This can be avoided by making the manager script run permanently. The problem with that though, is that if the user toggles the button in Preferences.app, launchd refuses to start the script again because launchd is "still running" the bash script.

The solution is three-fold (although, we do not exclude other solutions): we first set launchd's ThrottleInterval to 0. Then we use a C script that forks and executes the /usr/share/mysql/mysql.start script. Finally, we make the bash script sleep utill the mysqld daemon has been launched.

One could ask why we did not start mysqld directly from the bash script, but that would not work because mysqld does not fork and would thus block the bash script.

org.grimore.mysql.plist

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple Computer//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
<dict>
  <key>Label</key>
  <string>org.grimore.mysql</string>
	<key>LowPriorityIO</key>
	<true/>
	<key>ProgramArguments</key>
	<array>
		<string>/usr/libexec/mysql/mysql_manager.sh</string>
	</array>
  <key>WatchPaths</key>
	<array>
		<string>/var/mobile/Library/Preferences/org.grimore.mysql.plist</string>
	</array>
	<key>RunAtLoad</key>
	<true/> 
  <key>ThrottleInterval</key>
  <integer>0</integer>
</dict>
</plist>

should be placed in /Library/LaunchDaemons/org.grimore.mysql.plist.

launcher.c

#include <stdio.h>
#include <stdlib.h>
int main(int argc, char **argv) {
  if(!strcmp(argv, "start", 5) || !strncmp(argv, "stop", 4))
    return 1;
  if(!fork()) {
     setsid();
     execvp("/usr/share/mysql/mysql.server", argv);
  }
  return 0;
}

should be placed in /usr/libexec/mysql/launcher.

mysql_manager.sh

#!/bin/bash
# Copyright (C) Wizardry and Steamworks.
##
#  Licensed to Wizardry and Steamworks under
# the GPLv3 GNU License which can be found at:
#    http://www.gnu.org/licenses/gpl.html
#
# http://grimore.org/ios:launchd_and_mysql
#
 
function throttleBlock() {
  MYSQLD=`ps ax | grep '[m]ysqld' | grep 'libexec' | awk '{ print $1 }'`
  while [[ (( "$1" == "off"  && "$MYSQLD" != "" ))  || (( "$1" == "on" && "$MYSQLD" == "" )) ]]; do
    sleep 0.1 # don't mess with blocking sleeps
    MYSQLD=`ps ax | grep '[m]ysqld' | grep 'libexec' | awk '{ print $1 }'`
  done
}
 
SETTING=`plutil -key mysqlEnabled /var/mobile/Library/Preferences/org.grimore.mysql.plist`
if [ $SETTING -eq 0 ]; then
  /usr/libexec/mysql/launcher stop
  throttleBlock off
else
  /usr/libexec/mysql/launcher start
  throttleBlock on
fi

the throttleBlock function delays the termination of the script until mysqld has been successfully launched by the C program launcher. Alternatively, the function could be replaced by sleep 1 because that exceeds the 0 throttle from launchd.

Similar to LAMP for desktop systems, the folks at iOS-WebStack have packaged lighttpd, PHP and MySQL for iOS together and offer a Cydia repo that will allow you to set-up all the packages as well as all required dependencies. You can find more information on their set-up page. This is essentially a complete solution that will allow you to run any web application on iOS.