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

前端技术 2023/09/09 MYSQL

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\');

本文地址：https://www.stayed.cn/item/25554

转载请注明出处。

本站部分内容来源于网络,如侵犯到您的权益,请 联系我

我的博客

人生若只如初见，何事秋风悲画扇。

我的标签

随笔档案