Gearman + MySQL to achieve persistence operation example

This article uses the gearman+mysql method to implement persistence operations. Share with you for your reference, the details are as follows:

1. Why persistence?

The work queue in gearman's job server is stored in memory. Once the server is restarted or crashes when there are unprocessed tasks, these tasks will be lost.
Persistent storage queues allow you to add background tasks and store them in an external persistent queue (such as a MySQL database).

2. For articles about gearman persistence, it is recommended to read the official documentation

http://gearman.org/manual/job_server/#persistent_queues

3. Create a database and table for persistence

CREATE DATABASE gearman;

CREATE TABLE `gearman_queue` (
`unique_key` varchar(64) NOT NULL,
`function_name` varchar(255) NOT NULL,
`priority` int(11) NOT NULL,
`data` longblob NOT NULL,
`when_to_run` int(11),
PRIMARY KEY (`unique_key`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

4. Create gearman user

> create user 'gearman'@'%' IDENTIFIED BY '123456';
> grant all on gearman.* TO 'gearman'@'%';
> flush privileges;

5. Specify persistence parameters when starting gearmand

> gearmand -q libdrizzle \
--libdrizzle-host=192.168.1.100 \
--libdrizzle-port=3306 \
--libdrizzle-user=gearman \
--libdrizzle-password=123456 \
--libdrizzle-db=gearman \
--libdrizzle-table=gearman_queue \
--libdrizzle-mysql

Or use as follows

> gearmand -q mysql \
--mysql-host=192.168.1.100 \
--mysql-port=3306 \
--mysql-user=gearman \
--mysql-password=123456 \
--mysql-db=gearman \
--mysql-table=gearman_queue

If the following problem occurs, it means that you did not install libdrizzle when compiling and installing gearman.

gearmand: unrecognised option '--libdrizzle-host=192.168.1.100'

Download libdrizzle from the following URL

https://launchpad.net/libdrizzle/+download

For example: libdrizzle-5.1.4.tar.gz

Install libdrizzle

> tar xf libdrizzle-5.1.4.tar.gz
>cd libdrizzle-5.1.4

It is best not to specify --prefix here, because if you specify other directories, gearman may not be able to find the relevant header files and link libraries when compiling below, and you need to manually add soft links

> ./configure
> make && make install

Then we recompile and install gearman

> tar xf gearmand-1.1.12.tar.gz 
>cd gearmand-1.1.12

If you are not sure about the parameters of configure, you can use the following command to check

> ./configure --help

You need to install mysql-devel here so that gearman supports mysql persistence

> yum install mysql-server mysql-devel

Because I installed gearman earlier, I didn't specify --prefix, so I didn't specify it here. You can specify it yourself if you need it.

> ./configure
> make && make install

The last message displayed after configure completes

* LIBS: 
* LDFLAGS Flags: 
* Assertions enabled: no
* Debug enabled: no
* Warnings as failure: no
* Building with libsqlite3 no
* Building with libdrizzle yes
* Building with libmemcached not found
* Building with libpq no
* Building with Tokyo Office Building No.
* Building with libmysql yes
* SSL enabled: no
* cyassl found: no
* openssl found: yes
* make -j: 2
* VCS checkout: no
* sphinx-build: :

Finally, you can see that libdrizzle and libmysql show yes

Check if it is installed

> gearmand --help

If the following error occurs

gearmand: error while loading shared libraries: libdrizzle.so.9: cannot open shared object file: No such file or directory

Please open and modify /etc/ld.so.conf

> vi /etc/ld.so.conf

Add the following sentence

/usr/local/lib

Run ldconfig

>ldconfig

Run the above gearmand --help again. If the following message appears, the installation is successful.

builtin:

libdrizzle:
--libdrizzle-host arg (=localhost) Host of server.
--libdrizzle-port arg (=3306) Port of server. (by default Drizzle)
--libdrizzle-uds arg Unix domain socket for server.
--libdrizzle-user arg (=root) User name for authentication.
--libdrizzle-password arg Password for authentication.
--libdrizzle-db arg (=gearman) Database to use.
--libdrizzle-table arg (=queue) Table to use.
--libdrizzle-mysql Use MySQL protocol.

MySQL:
--mysql-host arg (=localhost) MySQL host.
--mysql-port arg (=3306) Port of server. (by default 3306)
--mysql-user arg MySQL user.
--mysql-password arg MySQL user password.
--mysql-db arg MySQL database.
--mysql-table arg (=gearman_queue) MySQL table name.

If the following problems occur when starting gearmand through libdrizzle

gearmand: Error while initializing the queue : libdrizzle

And the record in the log is like this

ERROR 2017-02-22 07:51:02.536574 [ main ] Failed to initialize libdrizzle: 
initialize(QUEUE_ERROR) -> libgearman-server/queue.cc:246

I don't know if it's because the MySQL version is too high or for other reasons. If you try it and it doesn't work, try another method. I tested the other method successfully.

Create a background job

> gearman -f test -b 123456

View the database as follows:

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.