欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

MySQL的源码安装及使用UDFs进行数据自动更新的教程

程序员文章站 2024-02-16 16:18:28
mysql的源码安装 1. 安装依赖组件 # yum install gcc gcc-c++ ncurses-devel perl -y 2. 安...

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. 创建用户的相应目录

# 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. 修改目录权限

# 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. 初始化数据库

# 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 --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实现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.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,需要重新运行这句以建立与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');
select
memc_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_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');

上一篇: mysql的安装与设置记录

下一篇: