MySQL store global persistent user variables

Install MySQL Global User Variables UDF. Global User Variables UDF is a MySQL extension which helps storing global user defined variables persistently.

Go to URL: https://www.pureftpd.org/project/mysql-udf-global-user-variables/download

Click on appropriate method of download either HTTP or FTP.

wget https://download.pureftpd.org/mysql-udf-global-user-variables/global-user-variables-1.2.tar.gz

Extract downloaded tar file.

tar xzvf global-user-variables-1.2.tar.gz

Change directory to extracted directory and install it.

cd global-user-variables
make install

Now we can find udf_global_user_variables.so. We need to copy it to mysql plugin directory.

ll udf_global_user_variables.so

cp udf_global_user_variables.so /usr/lib64/mysql/plugin/

Now login to mysql and execute following command to create function.

mysql -u root -p

DROP FUNCTION IF EXISTS global_set;
CREATE FUNCTION global_set RETURNS INTEGER
  SONAME 'udf_global_user_variables.so';

DROP FUNCTION IF EXISTS global_get;
CREATE FUNCTION global_get RETURNS STRING
  SONAME 'udf_global_user_variables.so';

DROP FUNCTION IF EXISTS global_add;
CREATE FUNCTION global_add RETURNS INTEGER
  SONAME 'udf_global_user_variables.so';

Now we can set variables with following command. In case below VAR_GOLD_PRICE is a variable and 48000 is the value.

do global_set("VAR_GOLD_PRICE",48000);

To read the variable value we can use following command.

select global_get("VAR_GOLD_PRICE");

The value is readable globally and persistent till mysql server reboots.

We can set the string variables as below.

do global_set("VAR_MEMBER_NAME","Dilli Raj Maharjan");

select global_get("VAR_MEMBER_NAME");