~gearman-developers/gearman-mysql-udf/1.0 : revision 6

1

Better documentation coming soon, but for now...

2

3

Once the package is compiled and installed, load the modules into

4

MySQL with:

1

The Gearman MySQL user defined functions (UDFs) allow queries in MySQL

2

to act as a Gearman client. This gives you the ability to run functions

3

in you query not just on the local machine, but to any machine running

4

a Gearman worker. This can be useful for a number of reasons, including

5

processor or memory intensive functions that you wish to run on other

6

machines, functions that can run in the background, functions that

7

can run in parallel for aggregate operations, and also to trigger

8

jobs or other actions from a MySQL query. See the Gearman website

9

for information at:

10

11

http://www.gearmanproject.org/

12

13

The MySQL UDFs require the Gearman C server and library package to be

14

installed. You can find more information about how to do this through

15

the website above. Once this is installed, you can then compile the

16

Gearman MySQL UDF package. This can usually be done with the normal:

17

18

./configure

19

make

20

make install

21

22

Once the UDFs have been compiled and installed, you load them into

23

MySQL using the following queries:

5

24

6

25

CREATE FUNCTION gman_do RETURNS STRING

7

26

SONAME "libgearman_mysql_udf.so";

12

31

CREATE FUNCTION gman_servers_set RETURNS STRING

13

32

SONAME "libgearman_mysql_udf.so";

14

33

15

Once loaded, you'll need to add servers first:

34

Once loaded, you'll need to add servers for the clients to query

35

first. This can be done with:

16

36

17

37

SELECT gman_servers_set("127.0.0.1");

18

38

19

Then, you can run jobs:

39

You can also add a different set of servers for each function

40

type. For example:

41

42

SELECT gman_servers_set("192.168.1.1", "resize");

43

44

This would direct future queries to use 192.168.1.1 for all Gearman

45

functions calls to "resize". You can also specify multiple job servers

46

and port numbers in a single query using the following syntax:

47

48

SELECT gman_servers_set("192.168.1.3:7004,192.168.1.4:7004", "index");

49

50

Once your servers are setup, you can then run jobs from your queries:

20

51

21

52

SELECT gman_do("reverse", Host) AS test FROM mysql.user;

22

53

24

55

25

56

SELECT gman_do_background("reverse", Host) AS test FROM mysql.user;

26

57

27

This example requires gearmand running, and the examples/reverse_worker

28

program from libgearman.

58

These examples run a normal job, a high-priority job, or a background

59

job, respectively. The last job does not return a result, but instead

60

a job handle that you can later use to query for status. In order to

61

get the result from a background job, the worker would need to store

62

the result someplace a client can find it again later, such as back

63

into a MySQL table, into memcached, or even a file somewhere both

64

the client and worker can access.

65

66

If you would like to get the above examples working, you need to run

67

'gearmand' to start the job server, and then run 'reverse_worker'

68

from the examples directory in the C server and library package.

69

70

If you ever need to remove the functions from MySQL, you can run:

71

72

DROP FUNCTION gman_do;

73

DROP FUNCTION gman_do_high;

74

DROP FUNCTION gman_do_background;

75

DROP FUNCTION gman_servers_set;

76

77

Enjoy!

78

-Eric

79

80

eday@oddments.org

81

http://www.oddments.org/