Shell/Bash Commands Execution from MySQL Client or Stored Procedure/function

Today I am going to explain ways to execute shell or bash commands from mysql clients or stored procedure and function. There are basically 2 method to do so:

Method 1: Use MySQL Client inbuilt feature

 To run single command: \! command or system command. eg \! uptime or system command

 To get terminal \! bash or \! sh

Method 2: Deploy external plugin (lib_mysqludf_sys)

Step 1: Download lib_mysqludf_sys from github:

git clone https://github.com/mysqludf/lib_mysqludf_sys.git 

Step 2: Install libmysqlclient15-dev, for Ubuntu you can use:

apt-get install libmysqlclient15-dev 

Step 3: Note down o/p of:

mysql -uroot -pxxxx -e "select @@plugin_dir;" 

Step 4: Change directory to git clone dir.

cd lib_mysqludf_sys/

Step 5: Compile and put plugin in plugin dir

gcc -Wall -I/usr/include/mysql -I. -shared lib_mysqludf_sys.c -o {value from step 3}lib_mysqludf_sys.so -fPIC 

Step 6: Create necessary functions for lib_mysqludf_sys:

mysql -uroot -pxxx mysql -e "source lib_mysqludf_sys.sql" 

Step 7: library lib_mysqludf_sys contains a number of functions that allows one to interact with the operating system.

sys_eval - executes an arbitrary command, and returns it's output eg: select sys_eval("df -h")

sys_exec - executes an arbitrary command, and returns it's exit code eg: select sys_exec("df -h")

sys_get - gets the value of an environment variable.

sys_set - create or update an environment variable.

lib_mysqludf_sys_info - information about the currently installed version of lib_mysqludf_sys.