Flexviews-MySQL物化视图


大家知道,MySQL支持视图,但是是逻辑上的视图,查询还是会去基表上查,下面介绍的这个工具可以创建物化视图,一起来看看如何配置吧


运行Flexviews环境:

[root@node153 consumer]# php --version
PHP 5.6.36 (cli) (built: Nov 18 2019 10:31:25)
将禁用函数注释掉:
vim /usr/local/php/etc/php.ini
302 ; http://php.net/disable-functions
303 ;disable_functions = passthru,exec,system,chroot,chgrp,chown,shell_exec,proc_open,proc_get_status,popen,ini_alter,     ini_restore,dl,openlog,syslog,readlink,symlink,popepassthru,stream_socket_server


重启PHP-FPM服务
service php-fpm restart


Mysql:5.7.18
○ Requires PHP 5.3+
○ ROW based binary log (not MIXED or
STATEMENT!)
○ Full binary log images (5.6)
○ READ-COMMITTED tx_isolation (recommended)






物化视图


物化视图实际上是预先计算并且存储在磁盘上的表,可以通过各种各样的策略刷新和更新


Flexviews
Flexviews 是实现MySQL物化视图的开源工具。按照物化视图的定义,它应由以下部分组成:


1.变更数据抓取(CDC)功能,可以读取服务器的二进制日志并且解析相关行的变更
2.一系列可以帮助创建和管理视图的工具
3.一些可以应用变更到数据的物化视图的工具
下载


下载zip:
https://github.com/greenlion/swanhart-tools/archive/master.zip
git下载:
https://github.com/greenlion/swanhart-tools.git


unzip /root/master.zip
mv /root/swanhart-tools-master/flexviews  /usr/local/


说明:flexviews已经是二进制文件,无需编译安装

MySQL配置
根据上文得到Flexviews的组成部分,应该启用或是修改MySQL相关功能或变量。


1.开启MySQL二进制日志:log-bin=mysql-bin  #mysqlbin为二进制文件名
2.设定MySQL二进制格式:binlog-format=row     #row/statement/mixed
3.开启MySQL的定时事件:event_scheduler = 1   #以便执行实现定时刷新脚本
4.配置MySQL服务器标识:server-id = 1         #记录日志为哪台服务器所写?
5.创建flexviews数据库:mysql -e "create database flexviews;"
6.创建flexviews用户:mysql -e "grant replication client,replication slave on *.* to flexviews@'%' identified by 'Flexviews1223.';"
7.关闭密码审计:
[mysqld]
validate_password_policy=LOW


Flexviews配置


flexviews官方给出的安装步骤有误,网上资料又很少。特此记录:


1.拷贝配置文件
cp /usr/local/flexviews/consumer/consumer.ini.example /usr/local/flexviews/consumer/consumer.ini
swanhart tools下的flexviews为主要目录.以下皆在flexviews中修改或执行. 2.修改flexviews\consumer目录中的consumer.ini (first copy consumer.ini.example to consumer.ini)
生效的配置文件:
[root@node153 consumer]# cat consumer.ini | grep -v ^$| grep -vE "^;"


[flexcdc]


mysqlbinlog=/usr/bin/mysqlbinlog


database=flexviews


error_log=flexcdc.err


binlog_consumer_status=binlog_consumer_status


mvlogs=mvlogs


mview_uow=mview_uow


log_retention_interval="10 DAY"


skip_before_update=false


mark_updates=false


auto_changelog=true


raise_warnings=false


bulk_insert=true


sleep_increment=.25


sleep_maximum=5


failure_sleep_time=30


failure_log_file=flex_cdc_log.log


failure_email_address=root@localhost


[source]


user=root


host=127.0.0.1


port=3306


password=123456


[dest]


user=root


host=127.0.0.1


port=3306


password=123456
初始化物化系统


创建flexviews库和三张flexviews基础表
    php /usr/local/flexviews/consumer/setup_flexcdc.php
运行完之后会在数据库flexviews下创建以下表:


(root@153_master)[flexviews]> show tables;


+------------------------+


| Tables_in_flexviews    |


+------------------------+


| binlog_consumer_status |


| mview_uow              |


| mvlogs                 |


+------------------------+


3 rows in set (0.00 sec)
错误提示:require_once(Console/Getopt.php): failed to open stream
解决:这是PHP没有pear相关插件导致错误
    下载:curl -o go-pear.php http://pear.php.net/go-pear.phar
    执行:php go-pear.php
导入示例数据库


mysql -uxxx -pxxx -e 'source /usr/local/flexviews/flexviews_demo/demo.sql;';
导入存储过程


修改密码策略:
mysql -e "set global validate_password_policy=LOW"                #MySQL 5.7


导入存储过程
[root@node153 flexviews]# mysql </usr/local/flexviews/install.sql 
message
If you see no errors, then installation was successful.


错误提示:unknown sys storage_engine出错信息
解决:这是flexviews本身脚本错误。
将以下脚本删除:
 19 set @_storage_engine = @storage_engine;
 21 set storage_engine = coalesce( (select engine from information_schema.engines where engine='InnoDB'), (select engin    e from information_schema.engines where engine='TokuDB'), (select engine from information_schema.engines where engi    ne='MyISAM') ) ;
143 set @storage_engine = @_storage_engine;


添加在文件开头添加以下内容:
     set default_storage_engine = InnoDB;
运行Flexviews


测试:
[root@node153 consumer]# /usr/bin/php /usr/local/flexviews/consumer/run_consumer.php
6


mysqlbinlog=/usr/bin/mysqlbinlog


database=flexviews


error_log=flexcdc.err


binlog_consumer_status=binlog_consumer_status


mvlogs=mvlogs


mview_uow=mview_uow


log_retention_interval=10 DAY


skip_before_update=


mark_updates=


auto_changelog=1


raise_warnings=


bulk_insert=1


sleep_increment=.25


sleep_maximum=5


failure_sleep_time=30


failure_log_file=flex_cdc_log.log


failure_email_address=root@localhost


-- /usr/bin/mysqlbinlog --user=root --host=127.0.0.1 --port=3306 --password=Hexin123. --base64-output=decode-rows -v -R --start-position=2387 --stop-never mysqlbin-log.000001 2>&1


加入后台进程:
[root@node153 consumer]#cd /usr/local/flexviews/consumer
[root@node153 consumer]#nohup /bin/bash  consumer_safe.sh --ini=consumer.ini &


到此Flexviews安装配置完成

官方链接:

https://github.com/greenlion/swanhart-tools
http://flexviews.sourceforge.net/flexcdc.html
https://www.percona.com/blog/2011/03/23/using-flexviews-part-one-introduction-to-materialized-views/


应用配置:

一、创建增量更新的物化视图:
//第一步:创建物化视图


call flexviews.CREATE('demo',
                      'mv_company_sales_items_monthly',
                      'INCREMENTAL'
                      );


----------数据库、视图名称、刷新类型(增量刷新)
//获取视图id
select @mvid := last_insert_id();


  //第二步:增加表,where条件用到


call flexviews.add_table(@mvid, 'demo', 'orders', 'o', 'USING(CustomerId)');


-----视图id,数据库名称、表名称、表别名,条件
v_mview_id - The materialized view id (see flexviews.get_id)
v_table_schema - The schema which contains the table to add
v_table_name - The name of the table to add
v_table_alias - The table alias to use in the view. All tables MUST have an alias.
v_join_clause - Every table after the first must have a NOT-NULL join clause


//第三部:物化视图
call flexviews.enable(@mvid);
注:增量更新的物化视图,如果基表数据有更新,物化视图的表会时时更新




二、创建全量更新的物化视图:
(root@153_master)[(none)]> call flexviews.create('demo','ord_cus','complete');
Query OK, 0 rows affected (0.00 sec)


定义物化视图的关系:
(root@153_master)[(none)]> call flexviews.set_definition(
    -> flexviews.get_id('demo','ord_cus'),'select o.orderid,c.CompanyName from demo.orders o,  demo.customers c where o.CustomerID=c.CustomerID');
Query OK, 1 row affected (0.00 sec)
在数据库中创建物化视图基表:
(root@153_master)[(none)]> call flexviews.enable(flexviews.get_id('demo','ord_cus'));
Query OK, 2 rows affected (0.03 sec)


定期增量刷新视图:
更新基表后,物化视图不能刷新,需要运行以下语句才能刷新:
(root@153_master)[demo]> call flexviews.refresh(flexviews.get_id('demo','ord_cus'),'BOTH',NULL);



分割线
打赏
YWSOS.COM 平台代运维解决方案
 评论
 发表评论
姓   名:

Powered by AKCMS