• 通过mysql触发器操作memcached

    mysql5.0以上版本可以利用用户的自定义函数(即UDFs),来访问memcache的写入或者读取
    mysql5.1版本开始支持触发器,可以在触发器中使用UDFs直接修改memcached中的数据,摆脱依靠程序要更新缓存。下面记录了实现这个功能的安装步骤

    系统环境 centos6.9
    1.安装 mysql5.6.36 和 memcached 1.4.37
    我这边为了方便,直接使用oneinstack安装了mysql和memcached,具体方法参考:oneinstack
    oneinstack一直在更新,比较稳定

    2.安装libmemcached-0.34
    Memcached Functions for MySQL Latest version is 1.1 只兼容0.34版本: https://launchpad.net/memcached-udfs/trunk

    1
    2
    3
    4
    5
    6
    7
    8
    cd /tmp
    mkdir -p /usr/local/libmemcached
    wget https://launchpad.net/libmemcached/1.0/0.34/+download/libmemcached-0.34.tar.gz
    tar zxvf libmemcached-0.34.tar.gz
    cd  libmemcached-0.34
    ./configure --prefix=/usr/local/libmemcached --with-memcached=/usr/local/webserver/memcached/bin/memcached
    make
    make install

    3.安装memcached_functions_mysql

    1
    2
    3
    4
    5
    6
    7
    cd /tmp
    wget https://launchpad.net/memcached-udfs/trunk/1.1/+download/memcached_functions_mysql-1.1.tar.gz
    tar xvf memcached_functions_mysql-1.1.tar.gz
    cd memcached_functions_mysql-1.1
    ./configure --prefix=/usr/local/memcached_functions_mysql --with-mysql=/usr/local/webserver/mysql/bin/mysql_config --with-libmemcached=/usr/local/libmemcached
    make
    make install

    4.将库文件拷贝至mysql的安装目录下

    1
    2
    3
    cp /usr/local/memcached_functions_mysql/lib/libmemcached_functions_mysql.* /usr/local/webserver/mysql/lib/plugin
    echo "/usr/local/lib" >> /etc/ld.so.conf
    ldconfig

    5.导入memcached的自定义函数

    1
    2
    MySQL [(none)]> source /tmp/memcached_functions_mysql-1.1/sql/install_functions.sql
    Query OK, 0 rows affected, 1 warning (0.00 sec)

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    Query OK, 0 rows affected, 1 warning (0.00 sec)

    检查是否成功

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    MySQL [(none)]> select name,dl from mysql.func;
    +------------------------------+---------------------------------+
    | name                         | dl                              |
    +------------------------------+---------------------------------+
    | memc_add                     | libmemcached_functions_mysql.so |
    | memc_add_by_key              | libmemcached_functions_mysql.so |
    | memc_servers_set             | libmemcached_functions_mysql.so |
    | memc_server_count            | libmemcached_functions_mysql.so |
    | memc_set                     | libmemcached_functions_mysql.so |
    | memc_set_by_key              | libmemcached_functions_mysql.so |
    | memc_cas                     | libmemcached_functions_mysql.so |
    | memc_cas_by_key              | libmemcached_functions_mysql.so |
    | memc_get                     | libmemcached_functions_mysql.so |
    | memc_get_by_key              | libmemcached_functions_mysql.so |
    | memc_get_cas                 | libmemcached_functions_mysql.so |
    | memc_get_cas_by_key          | libmemcached_functions_mysql.so |
    | memc_delete                  | libmemcached_functions_mysql.so |
    | memc_delete_by_key           | libmemcached_functions_mysql.so |
    | memc_append                  | libmemcached_functions_mysql.so |
    | memc_append_by_key           | libmemcached_functions_mysql.so |
    | memc_prepend                 | libmemcached_functions_mysql.so |
    | memc_prepend_by_key          | libmemcached_functions_mysql.so |
    | memc_increment               | libmemcached_functions_mysql.so |
    | memc_decrement               | libmemcached_functions_mysql.so |
    | memc_replace                 | libmemcached_functions_mysql.so |
    | memc_replace_by_key          | libmemcached_functions_mysql.so |
    | memc_servers_behavior_set    | libmemcached_functions_mysql.so |
    | memc_servers_behavior_get    | libmemcached_functions_mysql.so |
    | memc_behavior_set            | libmemcached_functions_mysql.so |
    | memc_behavior_get            | libmemcached_functions_mysql.so |
    | memc_list_behaviors          | libmemcached_functions_mysql.so |
    | memc_list_hash_types         | libmemcached_functions_mysql.so |
    | memc_list_distribution_types | libmemcached_functions_mysql.so |
    | memc_udf_version             | libmemcached_functions_mysql.so |
    | memc_libmemcached_version    | libmemcached_functions_mysql.so |
    | memc_stats                   | libmemcached_functions_mysql.so |
    | memc_stat_get_keys           | libmemcached_functions_mysql.so |
    | memc_stat_get_value          | libmemcached_functions_mysql.so |
    +------------------------------+---------------------------------+

    安装基本已经完成,接下来就测试用触发器操作memcached

    1.创建表
    新建两张表:domain_name和error,其中error是错误的记录

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    create database t1;
    use t1
    drop table if exists domain_name;
    CREATE TABLE `domain_name` (
    `id` int(10) NOT NULL,
    `url` varchar(255) NOT NULL DEFAULT '',
     PRIMARY KEY (`id`)
    );
    drop table if exists error;
    CREATE TABLE `error` (
    `id` int(10) NOT NULL,
    `error` varchar(255) NOT NULL DEFAULT 'error',
    `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`)
    );

    2.建立3个触发器(trigger)

    当向domain_name表中插入数据时,对Memcached执行set操作。trigger的代码如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    DELIMITER //
    DROP TRIGGER IF EXISTS url_mem_insert;
    CREATE TRIGGER url_mem_insert
    BEFORE INSERT ON domain_name
    FOR EACH ROW BEGIN
    set @mm = memc_set(NEW.id, NEW.url);
    if @mm <> 0 then
    insert into error(id) values(NEW.id);
    end if;
    END //
    DELIMITER ;

    当对domain_name表中的数据进行更新时,对Memcached执行replace操作。trigger代码如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    DELIMITER //
    DROP TRIGGER IF EXISTS url_mem_update;
    CREATE TRIGGER url_mem_update
    BEFORE UPDATE ON domain_name
    FOR EACH ROW BEGIN
    set @mm = memc_replace(OLD.id,NEW.url);
    if @mm <> 0 then
    insert into error(id) values(OLD.id);
    end if;
    END //
    DELIMITER ;

    当对domain_name表中的数据进行删除操作时,对Memcached执行delete操作。trigger代码如下:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    DELIMITER //
    DROP TRIGGER IF EXISTS url_mem_delete;
    CREATE TRIGGER url_mem_delete
    BEFORE DELETE ON domain_name
    FOR EACH ROW BEGIN
    set @mm = memc_delete(OLD.ID);
    if @mm <> 0 then
    insert into error(id) values(OLD.id);
    end if;
    END //
    DELIMITER ;

    3.设置memcache的参数(trigger)

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    MySQL [(none)]> SELECT memc_servers_set('192.168.0.99:11211');      
    +----------------------------------------+
    | memc_servers_set('192.168.0.99:11211') |
    +----------------------------------------+
    |                                      0 |
    +----------------------------------------+
    1 row in set (0.00 sec)

    MySQL [(none)]> select memc_server_count();
    +---------------------+
    | memc_server_count() |
    +---------------------+
    |                   1 |
    +---------------------+
    1 row in set (0.01 sec)

    MySQL [(none)]> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');
    +--------------------------------------------------------------+
    | memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1') |
    +--------------------------------------------------------------+
    |                                                            0 |
    +--------------------------------------------------------------+
    1 row in set (0.00 sec)

    MySQL [(none)]> select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');
    +-----------------------------------------------------------------+
    | memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1') |
    +-----------------------------------------------------------------+
    |                                                               0 |
    +-----------------------------------------------------------------+
    1 row in set (0.00 sec)

    简单的测试:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    MySQL [t1]> insert into domain_name (id,url) values (1, 'http://www.zzz.com');
    Query OK, 1 row affected (0.01 sec)
    MySQL [t1]> select memc_get('1');
    +--------------------+
    | memc_get('1')      |
    +--------------------+
    | http://www.zzz.com |
    +--------------------+
    1 row in set (0.01 sec)

    telnet  192.168.0.99 11211
    Trying 192.168.0.99...
    Connected to 192.168.0.99.
    Escape character is '^]'.
    get 1
    VALUE 1 0 18
    http://www.zzz.com
    END

    修改数据

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    MySQL [t1]> update t1.domain_name set url='http://www.xxx.com' where id=1;
    MySQL [t1]> Query OK, 1 row affected, 1 warning (0.00 sec)

    telnet  192.168.0.99 11211
    Trying 192.168.0.99...
    Connected to 192.168.0.99.
    Escape character is '^]'.
    get 1  
    VALUE 1 0 18
    http://www.xxx.com
    END

    删除数据:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    MySQL [t1]> delete from t1.domain_name where id=1;
    Query OK, 1 row affected (0.00 sec)

    telnet  192.168.0.99 11211
    Trying 192.168.0.99...
    Connected to 192.168.0.99.
    Escape character is '^]'.
    get 1
    END

发表评论

电子邮件地址不会被公开。 必填项已用*标注