MySQL PXC

TapData Cloud offers you cloud services that are suitable for scenarios requiring rapid deployment and low initial investment, helping you focus more on business development rather than infrastructure management. Free trial with TapData Cloud.TapData Enterprise can be deployed in your local data center, making it suitable for scenarios with strict requirements on data sensitivity or network isolation. It can serve to build real-time data warehouses, enable real-time data exchange, data migration, and more.TapData Community is an open-source data integration platform that provides basic data synchronization and transformation capabilities. This helps you quickly explore and implement data integration projects. As your project or business grows, you can seamlessly upgrade to TapData Cloud or TapData Enterprise to access more advanced features and service support.

Please follow the instructions below to successfully add and use MySQL PXC databases in TapData.

Supported Versions

MySQL PXC 5.0, 5.1, 5.5, 5.6, 5.7, 8.x

Prerequisites (As a Source)

Enable Binlog

• MySQL PXC's binlog must be enabled for TapData to perform synchronization properly.
• Cascade deletes, which are generated by the database, are not recorded in the binlog and are therefore not supported. Modify $MYSQL_HOME/mysql.cnf, for example:

server_id         = 223344
log_bin           = mysql-bin
expire_logs_days  = 1
binlog_format     = row
binlog_row_image  = full

Explanation of the configuration: server-id: Must be unique for each server and replication client in MySQL PXC. binlog_format: Must be set to row or ROW. binlog_row_image: Must be set to full. expire_logs_days: Number of days to retain binary log files; they will be automatically deleted when they expire. log_bin: Base name of the binary log sequence files.

Restart MySQL PXC

/etc/init.d/mysqld restart

To verify that binlog is enabled, execute the following command in the MySQL shell:

show variables like 'binlog_format';

The output's format value should be "ROW".

To verify if the binlog_row_image parameter's value is "full":

show variables like 'binlog_row_image';

The output's binlog_row_image value should be "FULL".

Create MySQL PXC Account

For MySQL 8 and later versions, the encryption method for passwords is different. Make sure to use the corresponding method for your version to set the password; otherwise, it will result in inability to perform incremental synchronization.

For 5.x Versions (3.3.1)

create user 'username'@'localhost' identified by 'password';

For 8.x Versions (3.3.2)

-- Create user
create user 'username'@'localhost' identified with mysql_native_password by 'password';
-- Change password
alter user 'username'@'localhost' identified with mysql_native_password by 'password';

Grant Permissions

Grant SELECT permission for a specific database:

GRANT SELECT, SHOW VIEW, CREATE ROUTINE, LOCK TABLES ON <DATABASE_NAME>.<TABLE_NAME> TO 'tapdata' IDENTIFIED BY 'password';

Grant global permissions:

GRANT RELOAD, SHOW DATABASES, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'tapdata' IDENTIFIED BY 'password';

Constraints Note

When synchronizing from MySQL PXC to other heterogeneous databases, if the source MySQL PXC has table-level cascade settings, data updates and deletions triggered by those cascades will not be propagated to the target. If you need to build cascade processing capabilities on the target side, you can use triggers or other means to achieve this type of data synchronization.