Triggering Shell Scripts from MySQL

Overview

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).

Installing

  1. Become super-user: sudo -s
  2. Install mysql, compile tools and development libraries
    apt-get install mysql-server libmysqld-dev build-essential -y
  3. 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.
  4. In a temporary directory, uncompress the source code: tar xvzf lib_mysqludf_sys_0.0.3.tar.gz, compile and install: make
  5. Log into the MySQL console as root and issue the following command: CREATE FUNCTION sys_exec RETURNS INT SONAME ‘lib_mysqludf_sys.so’;

Using

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.

Leave a Reply