flexview-cdc

Flexview CDC Implementation

This CDC utility identifies row changes in the database, and takes few action on them like writing them to a log file table or file. In order to update views incrementally, Flexviews needs to have detailed information of each row where change is made in the database. FlexCDC reads MySql binary logs, which tracks those row changes .

1. FlexCDC triggers MySqlbinlog in an external procedure with the following commandline options ‘–base64-output=decode-rows -v’. This tells the utility to transform RBR base64 entries into readable SBR notation.

2. Then MySqlbinlog connects to database-MySql and queries for binary logs. The output from MySqlbinlog is stored by FlexCDC and proceeds.

3. FlexCDC gathers the changes into log tables rather than applying the actual changes. FlexCDC assigns a unique sequentially increasing transaction id to every set of changes made. FlexCDC inserts every set of changes into one table change logs or more than one table change logs (one per changed table) as a single transaction.

image

Figure 2.5 Flexview CDC Flowchart


Note: FlexCDC do not replace MySql replication. However there is an experimental class called FlexSBR which involves support for replication of SBR binary logs. It is mainly a proof-of-concept. It does not work with RBR.

Flexview : Materialized view implementation

Flexview : Materialized view implementation


Flexviews is basically materialized views implementation for MariaDB, MySQL and its forks. It consists of a SQL API that creates materialized views and refresh them.

The advantage of Flexviews is that the materialized views can be incrementally refreshed, that is, the views are updated efficiently by using special logs which record the changes to database tables. Flexviews creates and maintains these logs with the help of the tools in it. The views created by Flexviews supports  JOINs and all major aggregate functions.Flexviews works well with Shard-Query that can be used to shard the source data, making the refresh process faster.


 
Requirements for flexview implementation

1.Required MySQL settings

  • MySQL 5.1+
  • Row Level Binary logging (binlog_format = ROW in my.cnf)
  • server_id set to unique value (server_id = 999 in my.cnf)
  • SUPER privileges

 

2. Suggested MySQL 5.1 settings

 

  • transaction-isolation = READ-COMMITTED
  • sync_binlog=1
  • sync_frm=1
  • innodb_support_xa=1

 

3. Transaction isolation level: READ-COMMITTED

 

This transaction isolation level, when combined with row-based binary logging, will prevent locks from being held during

INSERT .. SELECT statements which Flexviews uses.


4.PHP Requirements

 

  • PHP 5.2+ required, 5.3+ is recommended
  • pcntl extension
  • MySQL extension
  • PHP is required for FlexCDC, the binary log reading change data capture tool included in Flexviews.

 
 
Reference:

–https://mariadb.com/kb/en/mariadb/flexviews/