MySQL的源码安装及使用UDFs进行数据自动更新的教程
MySQL的源码安装
1.安装依赖组件
#yuminstallgccgcc-c++ncurses-develperl-y
2.安装cmake
#wgethttp://www.cmake.org/files/v2.8/cmake-2.8.12.tar.gz #tarzxvfcmake-2.8.12.tar.gz #cdcmake-2.8.12 #./bootstrap #make&&makeinstall
3.安装bison
#wgethttp://ftp.gnu.org/gnu/bison/bison-3.0.2.tar.gz #tarzxvfbison-3.0.2.tar.gz #cdbison-3.0.2 #./configure #make&&makeinstall
4.创建用户的相应目录
#groupaddmysql #useradd-gmysqlmysql #mkdir-p/data/mysql/ #mkdir-p/data/mysql/data/ #mkdir-p/data/mysql/log/
5.获取mysql安装包并安装
#wgethttp://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.24.tar.gz #tarzxvfmysql-5.5.24.tar.gz #cdmysql-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&&makeinstall
6.修改目录权限
#chmod+w/data/mysql/ #chown-Rmysql: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.初始化数据库
#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.启动mysql服务
#chmod+x/etc/init.d/mysqld #vi/etc/init.d/mysqld--> basedir=/data/mysql datadir=/data/mysql/data #chkconfig--addmysqld #servicemysqldstart
9.完成配置
#/data/mysql/bin/mysqladmin-uroot-ppassword'PASSWD' #echo"exportPATH=/data/mysql/bin:$PATH">>~/.bash_profile #source~/.bash_profile
UDFs实现Memcached与Mysql的自动更新
UDFs是UserDefinedFunctions的缩写，表示MYSQL的用户定义函数，应用程序可以利用这些函数从MYSQL5.0以上版本的数据库中访问Memcached写入或者获取的数据。此外，MYSQL从5.1版本开始支持触发器，从而可以在触发器中使用UDFs直接更新Memcached的内容，这种方式降低了应用程序设计和编写的复杂性。
1.安装
wgethttps://launchpad.net/libmemcached/1.0/0.34/+download/libmemcached-0.34.tar.gz yuminstallgcc44gcc44-c++libstdc++44-devel exportCC=/usr/bin/gcc44 exportCXX=/usr/bin/g++44 ./configure--prefix=/soft/libmemcached-disable-64bitCFLAGS="-O3-march=i686"\ --with-memcached=/root/libmemcached-1.0.7/memcached make&&makeinstall
wgethttps://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&&makeinstall mysql-uroot-pmysql</sql/install_functions.sql mysql-uroot-pmysql-se"selectname,dlfrommysql.func"
selectmemc_servers_set('127.0.0.1:11211');//如果mysqlrestart，需要重新运行这句以建立与memcached之间的关系
selectmemc_server_count();
selectmemc_set('urls:sequence',0);
selectmemc_list_behaviors()\G//修改memcached参数的行为
selectmemc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');
select
memc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');
设置MEMCACHED_BEHAVIOR_NO_BLOCK为打开状态，这样在memcached出现问题时（不能连接时）数据继续插入到
mysql中，报错提示，如果不设置此值，如果memcached失败，mysql需要等到timeout才可以插入到表中。
2.测试：
droptableifexistsurls;
createtableurls(
idint(3)notnullauto_increment,
urlvarchar(64)notnulldefault'',
primarykey(id)
);
selectmemc_servers_set('localhost:11211');
selectmemc_set('urls:sequence',0);
DELIMITER|
DROPTRIGGERIFEXISTSurl_mem_insert|
CREATETRIGGERurl_mem_insert
BEFOREINSERTONurls
FOREACHROWBEGIN
SETNEW.id=memc_increment('urls:sequence');
SET@mm=memc_set(NEW.id,NEW.url);
END|
DROPTRIGGERIFEXISTSurl_mem_update|
CREATETRIGGERurl_mem_update
BEFOREUPDATEONurls
FOREACHROWBEGIN
SET@mm=memc_replace(OLD.id,NEW.url);
END|
DROPTRIGGERIFEXISTSurl_mem_delete|
CREATETRIGGERurl_mem_delete
BEFOREDELETEONurls
FOREACHROWBEGIN
SET@mm=memc_delete(OLD.id);
END|
DELIMITER;
insertintourls(url)value('http://google.com');
insertintourls(url)value('http://lycos.com/');
insertintourls(url)value('http://tripod.com/');
insertintourls(url)value('http://microsoft.com/');
insertintourls(url)value('http://slashdot.org');
insertintourls(url)value('http://mysql.com');
select*fromurls;
selectmemc_get('urls:1');
selectmemc_get('urls:2');
selectmemc_get('urls:3');
selectmemc_get('urls:4');
selectmemc_get('urls:5');
selectmemc_get('urls:6');
updateurlsseturl='http://mysql.com/sun'whereurl='http://mysql.com';
selecturlfromurlswhereurl='http://mysql.com/sun';
selectmemc_get('urls:6');
deletefromurlswhereurl='http://microsoft.com/';
select*fromurlswhereurl='http://microsoft.com/';
selectmemc_get('urls:4');