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 是实现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);
很好，