MYSQL UDF calls external programs and system commands

1.mysql use a MySQL plugin from mysqludf to implement calling external programs and system commands

Download Lib_mysqludf_sys Program: Https://github.com/mysqludf/lib_mysqludf_sys

2. Installation instructions: 2.1 Query MySQL plugin path:

In MySQL, query the path to the MySQL plugin directory: Show variables like "Plugin_dir";

2.2 Decompression Source:

Unzip the downloaded plugin (lib_mysqludf_sys-master.zip) and copy it into the/tmp directory

#cd//ib/mysqludf_sys/lib_mysqludf_sys-master/#lsMakefile                        lib_mysqludf_ sys.html           install. SH                       lib_mysqludf_sys.solib_mysqludf_sys.c              lib_mysqludf_sys.sql

2.3 Compiling the source code:

Modify the makefile file to depend on your system's MySQL environment

#CatMakefilelibdir=/usr/LibInstall:        GCC-wall-i/usr/include/mysql-i.-shared Lib_mysqludf_sys.c-o $ (libdir)/lib_mysqludf_sys.so# Modified, according to your own system environment decision #CatMakefilelibdir=/usr/lib64/mysql/pluginInstall:        GCC-fpic-wall-i/usr/local/include/mysql-i.-shared Lib_mysqludf_sys.c-o $ (libdir)/lib_mysqludf_sys.so
# Make
Gcc-wall-i/usr/local/include/mysql-i.-shared Lib_mysqludf_sys.c-o/usr/lib64/mysql/plugin/lib_mysqludf_sys.so
#说明: I'm not sure I need to perform this step. Some tutorials say, some don't.
#chcon-T texrel_shlib_t mysql/lib/mysql/plugin/lib_mysqludf_sys.so

#备注:
#下面是另一个教程的编译方法, not tested, do not know can not, above is my own method of compiling, suggest the above method (if using the previous compilation method, you do not need to perform the following compilation)
#gcc-dmysql_dynamic_plugin-fpic-wall-i/usr/include/mysql-i.-shared lib_mysqludf_sys.c-o $ (LIBDIR)/lib_mysqludf_ Sys.so

The. So file has been compiled properly here, and you can see if there are lib_mysqludf_sys.so files in the/usr/lib64/mysql/plugin directory. If there is a problem during the compilation process, please Google

2.4 Creating a function

Execute the following command in MySQL

DROP function if EXISTS lib_mysqludf_sys_info;drop function if EXISTS sys_get;drop function if EXISTS sys_set;drop functio N if EXISTS sys_exec;drop FUNCTION IF EXISTS sys_eval; CREATE FUNCTION lib_mysqludf_sys_info RETURNS string SONAME ' lib_mysqludf_sys.so '; CREATE FUNCTION sys_get RETURNS string SONAME ' lib_mysqludf_sys.so '; CREATE FUNCTION sys_set RETURNS int SONAME ' lib_mysqludf_sys.so '; CREATE FUNCTION sys_exec RETURNS int SONAME ' lib_mysqludf_sys.so '; CREATE FUNCTION sys_eval RETURNS string SONAME ' lib_mysqludf_sys.so ';

2.5 Test 2.5.1 Verification Step 1:

Executing commands in MySQL

Mysql> SELECTSys_exec ('Touch/var/lib/mysql/test.txt');+---------------------------------------------+|Sys_exec ('Touch/var/lib/mysql/test.txt')|+---------------------------------------------+| 32512 |+---------------------------------------------+1Rowinch Set(0.00sec) MySQL> SELECTSys_eval ('ID');+----------------+|Sys_eval ('ID')|+----------------+| NULL           |+----------------+1Rowinch Set(0.40sec) MySQL> SELECTSys_eval ('cp/home/cassiano/desktop/index.html/home/cassiano/');+------------------------------------------------------------------+|Sys_eval ('cp/home/cassiano/desktop/index.html/home/cassiano/')|+------------------------------------------------------------------+|                                                                  |+------------------------------------------------------------------+1Rowinch Set(0.02Sec

As seen from the above execution results, all executions fail, due to apparmor (for specific reasons unknown), execute the following command at the shell command line

#sudoln -s/etc/apparmor.d/usr.sbin.mysqld/etc/apparmor.d/disable/#sudo Apparmor_parser-r/etc/apparmor.d/usr.sbin.mysql

2.5.2 Verification Step 2:

Mysql> SELECTSys_eval ('ID');+-------------------------------------------------+|Sys_eval ('ID')|+-------------------------------------------------+|Uid= the(MySQL) GID= the(MySQL) groups= the(MySQL)|+-------------------------------------------------+1Rowinch Set(0.09sec) MySQL> SELECTSys_eval ('pwd');+-----------------+|Sys_eval ('pwd')|+-----------------+| /var/Lib/Mysql|+-----------------+1Rowinch Set(0.01sec) But ..... The following operation failed ...-_-MySQL> SELECTSys_eval ('cp/home/cassiano/desktop/index.html/home/cassiano/');+------------------------------------------------------------------+|Sys_eval ('cp/home/cassiano/desktop/index.html/home/cassiano/')|+------------------------------------------------------------------+| NULL                                                             |+------------------------------------------------------------------+1Rowinch Set(0.02sec) MySQL> SELECTSys_exec ('cp/home/cassiano/desktop/index.html/home/cassiano/');+------------------------------------------------------------------+|Sys_exec ('cp/home/cassiano/desktop/index.html/home/cassiano/')|+------------------------------------------------------------------+|                                                               the |+------------------------------------------------------------------+1Rowinch Set(0.00Sec

Reason unknown, workaround, execute the following command in the shell:

#sudochown mysql:mysql/home/cassiano/teste/-R

2.5.3 Verify STEP3:

Write a small script to test it:

#cd/tmp#vi test. SH     #/bin/sh    Date > testlog.txt    0#chmod +x./test.  SH

Test in MySQL:

#mysqlmysql>SelectSys_exec ("/tmp/test.sh");+--------------------------+| Sys_exec ("/tmp/test.sh") |+--------------------------+|0|+--------------------------+1RowinchSet (0.03sec) MySQL>SelectSys_exec ("/tmp/test.sh");+--------------------------+| Sys_exec ("/tmp/test.sh") |+--------------------------+|0|+--------------------------+1RowinchSet (0.02sec) MySQL>SelectSys_exec ("/tmp/test.sh");+--------------------------+| Sys_exec ("/tmp/test.sh") |+--------------------------+|0|+--------------------------+1RowinchSet (0.02sec) MySQL>SelectSys_exec ("/tmp/test.sh");+--------------------------+| Sys_exec ("/tmp/test.sh") |+--------------------------+|0|+--------------------------+1RowinchSet (0.02sec) MySQL> exit;

Verify that the success is performed:

#CatTest.txttue May the  -: -: toCst theTue May the  -: -: toCst theTue May the  -: -: .Cst theTue May the  -: -: .Cst the# pwd/tmp# Test completed ... Success... 

3. Reference Articles

MySQL calls System program using UDF
MySQL executes local commands and external programs through functions MySQL UDF execute external command
MySQL UDF encounters a problem answer
AppArmor

Last note: Technical issues, Baidu search results are too few, suggest Google ...

MYSQL UDF calls external programs and system commands