MySQL

1. Connect to MySQL

1. Go to the Resources page and click the Add Resource button.

2. Select MySQL under the Database tab.

3. Enter your database credentials.

• Host

• Port

• Database

• User

• Password

• Replication Method

  • Standard

  • CDC: Learn more here.

Note: remember to add Acho's IP address, 35.239.14.244, to your whitelist before starting the connection.

4. Choose tables that you want to import and click Finish Setup.

5. You will see a icon next to your MySQL resource. It may take a few minutes to several hours depending on the data size and the number of your tables. Once your APP's data is ready, you will receive a notification email.

2. Change Data Capture (CDC)

• If you need a record of deletions and can accept the limitations posted below, you should be able to use CDC for MySQL.

• If your data set is small, and you just want a snapshot of your table in the destination, consider using Standard replication for your table instead of CDC.

• If the limitations prevent you from using CDC and your goal is to maintain a snapshot of your table in the destination, consider using non-CDC incremental and occasionally reset the data and re-sync.

• If your table has a primary key but doesn't have a reasonable cursor field for incremental syncing (i.e. updated_at), CDC allows you to sync your table incrementally.

CDC Limitations

• Make sure to read our CDC docs to see limitations that impact all databases using CDC replication.

• Our CDC implementation uses at least one delivery for all change records.

Steps

1. Enable binary logging

You must enable binary logging for MySQL replication. The binary logs record transaction updates for replication tools to propagate changes. You can configure your MySQL server configuration file with the following properties, which are described below:

server-id         = 223344
log_bin           = mysql-bin
binlog_format     = ROW
binlog_row_image  = FULL
expire_logs_days  = 10

• server-id : The value for the server-id must be unique for each server and replication client in the MySQL cluster. The server-id should be a non-zero value. If the server-id is already set to a non-zero value, you don't need to make any change. You can set the server-id to any value between 1 and 4294967295.

• log_bin : The value of log_bin is the base name of the sequence of binlog files. If the log_bin is already set, you don't need to make any change.

• binlog_format : The binlog_format must be set to ROW.

• binlog_row_image : The binlog_row_image must be set to FULL. It determines how row images are written to the binary log.

• expire_logs_days : This is the number of days for automatic binlog file removal. We recommend 10 days so that in case of a failure in sync or if the sync is paused, we still have some bandwidth to start from the last point in incremental sync. We also recommend setting frequent syncs for CDC.

2. Enable GTIDs (Optional)

Global transaction identifiers (GTIDs) uniquely identify transactions that occur on a server within a cluster. Though not required for a MySQL connector, using GTIDs simplifies replication and enables you to more easily confirm if primary and replica servers are consistent.

• Enable gtid_mode : Boolean that specifies whether GTID mode of the MySQL server is enabled or not. Enable it via mysql> gtid_mode=ON

• Enable enforce_gtid_consistency : Boolean that specifies whether the server enforces GTID consistency by allowing the execution of statements that can be logged in a transactionally safe manner. Required when using GTIDs. Enable it via mysql> enforce_gtid_consistency=ON

3. Set up Data Sync?

Acho supports creating schedulers to retrieve the latest data from your database at a certain time interval. To see more details, you can visit here.

4. How to add Acho's IP into your whitelist?

You should whitelist our service IP address 34.71.232.211 . This way we can make a connection to your database and import the tables to Acho Studio. Here is how you can whitelist Acho's service IP address for your database instance.

Example 1: Whitelist for Cloud SQL on GCP (Google Cloud Platform)

1. Click on your database instance and then navigate to the Connections page

2. Check the Public IP checkbox.

3. Click Add network , and enter 34.71.232.211(If you need to input an IP range, just enter 34.71.232.211/32).

4. Click Done and Save.

img

Example 2: Whitelist for RDS or MySQL on AWS (Amazon Web Services)

1. Go to Network & Securtiy and make sure your database instance can connect to our service as shown below.

img

2. Click on the Security Groups and add an Inbound Rule to allow our service to connect to your database as shown below.