MySQL的原始碼安裝及使用UDFs進行資料自動更新的教程

MySQL的原始碼安裝
1. 安裝依賴元件

?

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

2. 安裝cmake

?

12345

# wget # tar zxvf cmake-2.8.12.tar.gz# cd cmake-2.8.12# ./bootstrap # make && make install

3. 安裝bison

?

12345

# wget # tar zxvf bison-3.0.2.tar.gz# cd bison-3.0.2# ./configure# make && make install

4. 建立使用者的相應目錄

?

12345

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

5. 獲取mysql安裝包並安裝

?

# wget # 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. 修改目錄許可權

?

# 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. 初始化資料庫

?

123	# 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/mysqldatadir=/data/mysql/data# chkconfig --add mysqld# service mysqld start

9. 完成配置

?

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

UDFs實現Memcached與Mysql的自動更新

UDFs是User Defined Functions的縮寫，表示MYSQL的使用者定義函式，應用程式可以利用這些函式從MYSQL5.0以上版本的資料庫中訪問Memcached寫入或者獲取的資料。此外，MYSQL從5.1版本開始支援觸發器，從而可以在觸發器中使用UDFs直接更新Memcached的內容，這種方式降低了應用程式設計和編寫的複雜性。
1.安裝

?

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

?

12345

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

?

123	make && make installmysql -uroot -pmysql /sql/install_functions.sqlmysql -uroot -pmysql -se "select name,dl from mysql.func"

?

select memc_servers_set('127.0.0.1:11211');//如果mysql restart，需要重新執行這句以建立與memcached之間的關係select memc_server_count();select memc_set('urls:sequence', 0); select memc_list_behaviors()G//修改memcached引數的行為 select memc_servers_behavior_set('MEMCACHED_BEHAVIOR_NO_BLOCK','1');selectmemc_servers_behavior_set('MEMCACHED_BEHAVIOR_TCP_NODELAY','1');

設定MEMCACHED_BEHAVIOR_NO_BLOCK為開啟狀態，這樣在memcached出現問題時（不能連線時）資料繼續插入到
mysql中，報錯提示，如果不設定此值，如果memcached失敗，mysql需要等到timeout才可以插入到表中。

2.測試：

?

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_insertBEFORE INSERT ON urlsFOR 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_updateBEFORE UPDATE ON urlsFOR EACH ROW BEGIN  SET @mm= memc_replace(OLD.id, NEW.url);END | DROP TRIGGER IF EXISTS url_mem_delete |CREATE TRIGGER url_mem_deleteBEFORE DELETE ON urlsFOR EACH ROW BEGIN  SET @mm= memc_delete(OLD.id);END | DELIMITER ; insert into urls (url) value ('');insert into urls (url) value ('');insert into urls (url) value ('');insert into urls (url) value ('');insert into urls (url) value('');insert into urls (url) value ('');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= '/sun' where url = '';select url from urls where url = '/sun';select memc_get('urls:6'); delete from urls where url = '';select * from urls where url='';select memc_get('urls:4');