MySQL的源碼安裝及使用UDFs進(jìn)行數(shù)據(jù)自動(dòng)更新的教程

更新時(shí)間：2016年07月04日 15:22:30 作者：lucifercn

UDFs即是MySQL的用戶自定義函數(shù)的縮寫(xiě),配合觸發(fā)器可以自動(dòng)更新Memcached與MySql的數(shù)據(jù),這里我們就來(lái)總結(jié)一下MySQL的源碼安裝及使用UDFs進(jìn)行數(shù)據(jù)自動(dòng)更新的教程:

MySQL的源碼安裝
1. 安裝依賴組件

# yum install gcc gcc-c++ ncurses-devel perl -y

2. 安裝cmake

# wget http://www.cmake.org/files/v2.8/cmake-2.8.12.tar.gz
# tar zxvf cmake-2.8.12.tar.gz
# cd cmake-2.8.12
# ./bootstrap 
# make && make install

3. 安裝bison

# wget http://ftp.gnu.org/gnu/bison/bison-3.0.2.tar.gz
# tar zxvf bison-3.0.2.tar.gz
# cd bison-3.0.2
# ./configure
# make && make install

4. 創(chuàng)建用戶的相應(yīng)目錄

# groupadd mysql
# useradd -g mysql mysql
# mkdir -p /data/mysql/
# mkdir -p /data/mysql/data/
# mkdir -p /data/mysql/log/

5. 獲取mysql安裝包并安裝

# wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.24.tar.gz
# tar zxvf mysql-5.5.24.tar.gz
# cd mysql-5.5.24
# cmake \
-DCMAKE_INSTALL_PREFIX=/data/mysql \
-DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/data/mysql/data \
-DMYSQL_TCP_PORT=3306 \
-DENABLE_DOWNLOADS=1
# make && make install

6. 修改目錄權(quán)限

# chmod +w /data/mysql/
# chown -R mysql:mysql /data/mysql/

# ln -s /data/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18
# ln -s /data/mysql/mysql.sock /tmp/mysql.sock

7. 初始化數(shù)據(jù)庫(kù)

# cp -rp /data/mysql/support-files/my-medium.cnf /etc/my.cnf
# cp -rp /data/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
# /data/mysql/scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data

8. 啟動(dòng)mysql服務(wù)

# chmod +x /etc/init.d/mysqld
# vi /etc/init.d/mysqld -->
basedir=/data/mysql
datadir=/data/mysql/data
# chkconfig --add mysqld
# service mysqld start

9. 完成配置

# /data/mysql/bin/mysqladmin -uroot -p password 'PASSWD'
# echo "export PATH=/data/mysql/bin:$PATH" >> ~/.bash_profile 
# source ~/.bash_profile

UDFs實(shí)現(xiàn)Memcached與Mysql的自動(dòng)更新

UDFs是User Defined Functions的縮寫(xiě)，表示MYSQL的用戶定義函數(shù)，應(yīng)用程序可以利用這些函數(shù)從MYSQL5.0以上版本的數(shù)據(jù)庫(kù)中訪問(wèn)Memcached寫(xiě)入或者獲取的數(shù)據(jù)。此外，MYSQL從5.1版本開(kāi)始支持觸發(fā)器，從而可以在觸發(fā)器中使用UDFs直接更新Memcached的內(nèi)容，這種方式降低了應(yīng)用程序設(shè)計(jì)和編寫(xiě)的復(fù)雜性。
1.安裝

wget https://launchpad.net/libmemcached/1.0/0.34/+download/libmemcached-0.34.tar.gz
yum install gcc44 gcc44-c++ libstdc++44-devel
export CC=/usr/bin/gcc44
export CXX=/usr/bin/g++44
./configure --prefix=/soft/libmemcached -disable-64bit CFLAGS="-O3 -march=i686" \
--with-memcached=/root/libmemcached-1.0.7/memcached
make && make install

wget https://launchpad.net/memcached-udfs/trunk/1.1/+download/memcached_functions_mysql-1.1.tar.gz
./configure --prefix=/soft/UDFS/memcache_mysql \
--with-mysql=/soft/mysql/bin/mysql_config \
--libdir=/soft/mysql/lib/plugin \
--with-libmemcached=/soft/UDFS/libmemcached

make && make install
mysql -uroot -pmysql < /sql/install_functions.sql
mysql -uroot -pmysql -se "select name,dl from mysql.func"

select memc_servers_set('127.0.0.1:11211');//如果mysql restart，需要重新運(yùn)行這句以建立與memcached之間的關(guān)系
select memc_server_count();
select memc_set('urls:sequence', 0);

select memc_list_behaviors()\G//修改memcached參數(shù)的行為

select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');
select
memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');

設(shè)置MEMCACHED_BEHAVIOR_NO_BLOCK為打開(kāi)狀態(tài)，這樣在memcached出現(xiàn)問(wèn)題時(shí)（不能連接時(shí)）數(shù)據(jù)繼續(xù)插入到
mysql中，報(bào)錯(cuò)提示，如果不設(shè)置此值，如果memcached失敗，mysql需要等到timeout才可以插入到表中。

2.測(cè)試：

drop table if exists urls;
create table urls (
 id int(3) not null auto_increment,
 url varchar(64) not null default '',
 primary key (id)
 );

select memc_servers_set('localhost:11211');
select memc_set('urls:sequence', 0);

DELIMITER |

DROP TRIGGER IF EXISTS url_mem_insert |
CREATE TRIGGER url_mem_insert
BEFORE INSERT ON urls
FOR EACH ROW BEGIN
  SET NEW.id= memc_increment('urls:sequence'); 
  SET @mm= memc_set(NEW.id, NEW.url);
END |

DROP TRIGGER IF EXISTS url_mem_update |
CREATE TRIGGER url_mem_update
BEFORE UPDATE ON urls
FOR EACH ROW BEGIN
  SET @mm= memc_replace(OLD.id, NEW.url);
END |

DROP TRIGGER IF EXISTS url_mem_delete |
CREATE TRIGGER url_mem_delete
BEFORE DELETE ON urls
FOR EACH ROW BEGIN
  SET @mm= memc_delete(OLD.id);
END |

DELIMITER ;

insert into urls (url) value ('http://google.com');
insert into urls (url) value ('http://lycos.com/');
insert into urls (url) value ('http://tripod.com/');
insert into urls (url) value ('http://microsoft.com/');
insert into urls (url) value('http://slashdot.org');
insert into urls (url) value ('http://mysql.com');
select * from urls;

select memc_get('urls:1');
select memc_get('urls:2');
select memc_get('urls:3');
select memc_get('urls:4');
select memc_get('urls:5');
select memc_get('urls:6');

update urls set url= 'http://mysql.com/sun' where url = 'http://mysql.com';
select url from urls where url = 'http://mysql.com/sun';
select memc_get('urls:6');

delete from urls where url = 'http://microsoft.com/';
select * from urls where url='http://microsoft.com/';
select memc_get('urls:4');

您可能感興趣的文章: