Linux MySQL編譯安裝相關(guān)命令
想知道到Linux MySQL系統(tǒng)的真相么，想知道Linux MySQL系統(tǒng)中藏有的內(nèi)在奧義么，只有我來(lái)給大家全面講解介紹Linux MySQL系統(tǒng)差不多在一年前，寫過(guò)一篇文章介紹將Linux MySQL數(shù)據(jù)映射到Memcached，當(dāng)時(shí)Linux MySQL和Memcached Functions for MySQL都還不夠成熟，時(shí)過(guò)一年，Memcached Functions for MySQL升級(jí)到了0.8版本，而MySQL也發(fā)布了GA版本，加上很多朋友反應(yīng)前一篇文章中的實(shí)現(xiàn)他們因種種原因沒(méi)能成功，于是便有了這篇文章，就當(dāng)是上一篇文章的升級(jí)版本吧。
測(cè)試環(huán)境在Linux MySQL下進(jìn)行，版本系統(tǒng)為CentOS5.以下為相關(guān)軟件，包括其版本和下載地址：
- mysql-5.1.30 下載
- memcached-1.2.6 下載
- libevent-1.4.7-stable 下載
- memcached_functions_mysql-0.8 下載
- libmemcached-0.26 下載
編譯安裝Linux MySQL,安裝因個(gè)人細(xì)好而定，省略許多與測(cè)試無(wú)關(guān)的編譯細(xì)節(jié)及參數(shù)。
- [root@localhost ~]#tar xzf mysql-5.1.30.tar_001.gz
- [root@localhost ~]#cd mysql-5.1.30
- [root@localhost ~]#./configure --prefix=/usr/local/mysql51
- [root@localhost ~]#make
- [root@localhost ~]#make install
- [root@localhost ~]#./scripts/mysql_install_db --user=mysql --skip-name-resolve
- [root@localhost ~]#/usr/local/mysql51/bin/mysqld_safe
省略列出安裝memcached和libevent的相關(guān)命令，具體可按照實(shí)際情況安裝，測(cè)試時(shí)我將libevent默認(rèn)安裝，memcached安裝于/usr/local/memcached目錄下。
啟動(dòng)memcached.
/usr/local/memcached/bin/memcached -d -m 50 -u root -p 11211編譯安裝libmemcache.
- [root@localhost ~]#tar xzf libmemcached-0.26.tar.gz
- [root@localhost ~]#cd libmemcached-0.26
- [root@localhost ~]#./configure --with-memcached=/usr/local/memcached/bin/memcached
- [root@localhost ~]# make && make install
編譯安裝Memcache UDFs for MySQL.
- [root@localhost ~]# tar xzf memcached_functions_mysql-0.8.tar.gz
- [root@localhost ~]# cd memcached_functions_mysql-0.8
- [root@localhost ~]# ./configure --with-mysql-config=/usr/local/mysql51/bin/mysql_config
- [root@localhost ~]# make && make install
編譯完成后將編譯好的庫(kù)文件復(fù)制到mysql的插件目錄下，以便于加載使用。cp /usr/local/lib/libmemcached_functions_mysql* /usr/local/mysql51/lib/mysql/plugin/進(jìn)入memcached_functions_mysql的源碼目錄，在目錄下有相關(guān)添加UDF的SQL文件用于初始化。[root@localhost ~]# mysql <sql/install_functions.sql注：如果對(duì)這些UDFs不熟悉或者不懂，可進(jìn)行源碼目錄參看README，里邊有相應(yīng)的說(shuō)明。
至此，相關(guān)軟件的編譯和安裝完成，進(jìn)行測(cè)試，我們要達(dá)到的目的是當(dāng)MySQL有新記錄插入時(shí)，同時(shí)插入到Memcached中，當(dāng)記錄更新時(shí)同步更新Memcached中的記錄，刪除時(shí)同時(shí)也刪除Memcached相關(guān)的記錄，為此創(chuàng)建三個(gè)觸發(fā)器來(lái)實(shí)現(xiàn)，如果對(duì)MySQL的觸發(fā)程序不熟悉可以參考MySQL手冊(cè)第21章，下面SQL中的memcached為需要操作的表名，SQL如下：
- #插入數(shù)據(jù)時(shí)插入Memcached
- create trigger mysqlmmci after insert on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
- #更新記錄時(shí)更新Memcached
- create trigger mysqlmmcu after update on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
- #刪除記錄時(shí)刪除Memcached相應(yīng)的記錄
- create trigger mysqlmmcd before delete on memcached for each row set @tmp = memc_delete(OLD.key);
以下為測(cè)試記錄，在對(duì)Linux MySQL操作的同時(shí)操作Memcached來(lái)查看情況，當(dāng)然你也可以在啟動(dòng)Memcached的時(shí)候帶-vv參數(shù)來(lái)查看相關(guān)信息.
Linux MySQL操作相關(guān)的記錄：
- [root@localhost ~]#mysql -S /tmp/mysql51.sock
- Welcome to the MySQL monitor. Commands end with ; or \g.
- Your MySQL connection id is 6
- Server version: 5.1.30 Source distribution
- Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
- mysql> use test;
- Database changed
- mysql> create table `memcached` (`key` varchar(10), `value` varchar(100));
- Query OK, 0 rows affected (0.00 sec)
- mysql> create trigger mysqlmmci after insert on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
- Query OK, 0 rows affected (0.00 sec)
- mysql> create trigger mysqlmmcu after update on memcached for each row set @tmp = memc_set(NEW.key, NEW.value);
- Query OK, 0 rows affected (0.00 sec)
- mysql> create trigger mysqlmmcd before delete on memcached for each row set @tmp = memc_delete(OLD.key);
- Query OK, 0 rows affected (0.00 sec)
- mysql> insert into memcached values("keyi", "valuei"),("keyu","valueu"),("keyd", "valued");
- Query OK, 3 rows affected (0.00 sec)
- Records: 3 Duplicates: 0 Warnings: 0
- mysql> update memcached set `value`="update" where `key`="keyu";
- Query OK, 1 row affected (0.00 sec)
- Rows matched: 1 Changed: 1 Warnings: 0
- mysql> delete from memcached where `key`="keyd";
- Query OK, 1 row affected (0.00 sec)
- mysql> quit
- Bye
- Memcache查看時(shí)的記錄：
- [root@localhost ~]#telnet 127.0.0.1 11211
- Trying 127.0.0.1...
- Connected to 127.0.0.1.
- Escape character is '^]'.
- get keyi
- VALUE keyi 0 6
- valuei
- END
- get keyu
- VALUE keyu 0 6
- valueu
- END
- get keyd
- VALUE keyd 0 6
- valued
- END
- get keyu
- VALUE keyu 0 6
- update
- END
- get keyd
- END
- quit
- Connection closed by foreign host.
至此，我們基本實(shí)現(xiàn)的將Linux MySQL的數(shù)據(jù)同步到Memcached中，性能暫時(shí)還沒(méi)有測(cè)試，當(dāng)然上面只是簡(jiǎn)單的實(shí)現(xiàn)的數(shù)據(jù)映射的功能，如果在實(shí)現(xiàn)的生產(chǎn)環(huán)境中，則需要考慮名字空間，高可靠性的問(wèn)題，這些都是可以通過(guò)數(shù)據(jù)庫(kù)名-表名-關(guān)鍵字的方面能達(dá)到KEY***的目的，而高可靠性則是一個(gè)比較大的問(wèn)題。
【編輯推薦】
