Materialized MySQL

Materialized MySQL replicates the MySQL data into a materialized table in ByteHouse by reading the binlog of the MySQL database. This means that data will arrive in seconds, hence supporting change data capture (CDC) and real-time data analysis.

Implementation

ByteHouse will create a database with MaterializedMySQL engine, and it will work as a replica of the corresponding database of MySQL. The first dump will read all data. Subsequently, any changes to the database will be read to ByteHouse.

Tables in ByteHouse are created by rewriting DDL into CNCHMergeTree. There is a background thread for each table in the MaterializedMySQL engine, which will schedule tasks.

SQL syntax

Create MaterializedMySQL engine

Optional settings

• materialized_mysql_tables_list - By default, the engine will sync all the tables in the database. You can also define specific tables to sync in the settings.

• cnch_vw_write - By default, we use an internal virtual warehouse for free. However, the free-tier limits performance. For better performance, we advise you to set a separate virtual warehouse for managing the data ingestion.

• read_only_replica_info - Used for AWS RDS MySQL, which needs to provide a read replica for the first synchronization. e.g '{"host_name":"qa-global-mat-5-com-read.c1ozupm9r3vl.ap-southeast-1.rds.amazonaws.com","port":"3306","user_name":"xxx","password":"xxx"}'

CREATE DATABASE db_mysql_sync_naga
ENGINE = CNCHMaterializeMySQL('192.0.0.123:3300', 'mysql_db_name', 'root', 'password')
[SETTINGS materialized_mysql_tables_list ='table,table2', 
cnch_vw_write='vw123'
...]             

Update MaterializedMySQL database

When you add or drop a table, you can use an update command, otherwise it may take up to 10 minutes for the change to sync.

UPDATE MATERIALIZED_MYSQL_TABLES FOR db_mysql_sync_naga    

Stop/start sync in SQL

• You can pause the sync using "stop" and resume it using "start". Sync will resume from where it was left off.

system stop MATERIALIZEDMYSQL {materializedmysql_database_name}
system start MATERIALIZEDMYSQL {materializedmysql_database_name}

Drop the MaterializedMySQL engine

• This will delete the synced data as well as the connection information.

drop database db_mysql_sync

User interface

The MaterializedMySQL user interface allows you to see the status of the database.

• If MaterializedMySQL is running, it will show "Synchronizing" status.
• When you stop the MaterializedMySQL engine via SQL, it will show "Stopped" status.
• If the table sync threads are broken, it will show "Failed" status.

Troubleshooting

Go to Database --> Exception

• This will return the last exception message which can either be a warning or a failure message. A warning message means that there are problems with the engine but it is still syncing. A failure message means that the sync threads are broken.
• Note that MaterializedMySQL engine will continue to retry even in failed state unless it is "stopped".

Technical specifications

• Only supports MySQL 5.7 and 8.0
• Only syncs tables. Does not sync views and materialized views from MySQL

Note: AWS RDS MySQL needs to be prepared using the steps here AWS MySQL Replication.