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

数据运维 2023-04-26 张二河 手机阅读

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 ins

                        <p>MySQL的源码安装

  1. 安装依赖组件

  2. yum install gcc gcc-c++ ncurses-devel perl -y

2. 安装cmake

  1. wget http://www.cmake.org/files/v2.8/cmake-2.8.12.tar.gz
  2. tar zxvf cmake-2.8.12.tar.gz
  3. cd cmake-2.8.12
  4. ./bootstrap
  5. make && make install

3. 安装bison

  1. wget http://ftp.gnu.org/gnu/bison/bison-3.0.2.tar.gz
  2. tar zxvf bison-3.0.2.tar.gz
  3. cd bison-3.0.2
  4. ./configure
  5. make && make install

4. 创建用户的相应目录

  1. groupadd mysql
  2. useradd -g mysql mysql
  3. mkdir -p /data/mysql/
  4. mkdir -p /data/mysql/data/
  5. mkdir -p /data/mysql/log/

5. 获取mysql安装包并安装

  1. wget http://downloads.mysql.com/archives/mysql-5.5/mysql-5.5.24.tar.gz
  2. tar zxvf mysql-5.5.24.tar.gz
  3. cd mysql-5.5.24
  4. 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
  5. make && make install

6. 修改目录权限

  1. chmod +w /data/mysql/

  2. chown -R mysql:mysql /data/mysql/

  3. ln -s /data/mysql/lib/libmysqlclient.so.18 /usr/lib/libmysqlclient.so.18

  4. ln -s /data/mysql/mysql.sock /tmp/mysql.sock

7. 初始化数据库

  1. cp -rp /data/mysql/support-files/my-medium.cnf /etc/my.cnf
  2. cp -rp /data/mysql/support-files/mysql.server /etc/rc.d/init.d/mysqld
  3. /data/mysql/scripts/mysql_install_db --user=mysql --defaults-file=/etc/my.cnf --basedir=/data/mysql --datadir=/data/mysql/data

8. 启动mysql服务

  1. chmod +x /etc/init.d/mysqld
  2. vi /etc/init.d/mysqld --> basedir=/data/mysql datadir=/data/mysql/data
  3. chkconfig --add mysqld
  4. service mysqld start

9. 完成配置

  1. /data/mysql/bin/mysqladmin -uroot -p password 'PASSWD'
  2. echo "export PATH=/data/mysql/bin:$PATH" >> ~/.bash_profile
  3. 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');