Using the UDF Repository for MySQL,you can access a lot of operating system functionality from MySQL. This has been tested using Ubuntu Server on the i386 architecture. It does not work on 64-bit systems at this time. This includes having a shell script triggered from MySQL on some database operation (for instance, call a shell script every time a new record is inserted).
- Become super-user: sudo -s
- Install mysql, compile tools and development libraries
apt-get install mysql-server libmysqld-dev build-essential -y
- Download the source code from the lib_mysqludf_sys website. A direct link to the latest (at the time of writing this) tarball is here and a cached copy is here.
- In a temporary directory, uncompress the source code: tar xvzf lib_mysqludf_sys_0.0.3.tar.gz, compile and install: make
- Log into the MySQL console as root and issue the following command: CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so’;
Once installed you can use sys_exec() from a MySQL trigger. Triggers are thoroughly documented here, but the way I found out how to get the sys_exec() to run was a little hacky. It can be illustrated with the following example:
delimiter ;; create trigger mytrigger1 after insert on my_example_table for each row begin set @stupid_var=sys_exec('/usr/local/bin/mysql_trigger'); end; ;; delimiter ;
Note the assignment was necessary to get the shell script to run from a trigger. Also, if you have AppArmor installed, you will need to add your executable (in my case /usr/local/bin/mysql_trigger) and any other program it will call to your AppArmor profile. It should be located in /etc/apparmor.d/usr.sbin.mysqld.