MySQL Data Sync
This comprehensive document will walk you through the step-by-step configuration process for MySQL data sync and address specific use cases tailored to MySQL .
Standard Data Sync functionality
Please refer to Data Sync - Getting Started for the standard data sync functionality. The getting started document covers usages of data sync features valid for all integrations.
Configuring PostgreSQL Data Sync
1. Click on 'Create Sync'
2. Pick the app that you want to sync with MySQL
3. Select the MySQL from the dropdown
4. Select the schema from the dropdown
5. Click on 'Next' button
6. Delete any tables that you don't want to sync. If the table you want to sync is not displayed, add it using "Add table" button
7. Select the 'Last updated column' from dropdown
8. Click on 'Next' button
9. Mark any of the field as unique
10. Click on 'Confirm' button
11. Click on 'Save' button
12. Click on 'Start Sync' button
Required MySQL Columns
Auto increment Primary key
You need to have an auto-increment primary key to work with Byteline data sync. It can be created using the below alter statement.
alter table <table name> add <column name> INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
For example: alter table products add ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY;
Last Updated Column
Timestamp data type for the last updated column.The "last updated" column is required for Byteline data sync and other sync tools. So, ensure your data sync works seamlessly by setting up the essential "last updated" column. This column is crucial for identifying modified records that need to be synced. You will configure this column when setting up the data sync, as shown in the below screenshot.
You can use below type on the last updated column
timestamp default now() on update now()
Alter table SQL to add the last updated column
Below is an example SQL to add last updated column
alter table products add last_updated timestamp default now() on update now();
Syncing Data from localhost Using Ngrok
If your database is hosted locally and you want to sync data to or from it using Byteline, Ngrok can help expose your local database to the internet securely.
Steps to Access a Local Database Using Ngrok
1. Install Ngrok
Download and install Ngrok from https://ngrok.com/download. Follow the installation instructions provided for your operating system.
2. Expose Your Local Database Using Ngrok
Ngrok can expose a TCP connection for your local database. Use the following command to expose your database:
ngrok tcp <port>
Replace <port> with the port your database is running on (e.g., 3306).
Example for a MySQL database running on port 3306:
ngrok tcp 3306
Ngrok will generate a public TCP address (e.g., 0.tcp.ngrok.io:12345) that tunnels to your local database.
3. Update Database Configuration in Byteline
In the Byteline data sync configuration, use the Ngrok TCP address as the database host. For example:
Host: 0.tcp.ngrok.io
Port: 12345
Username: Your database username
Password: Your database password
Database Name: The name of your database
4. Test the Connection
Use the "Test Connection" feature in Byteline to ensure the platform can connect to your local database through the Ngrok TCP tunnel.
5. Run the Sync
After successfully testing the connection, initiate the data sync as you normally would. Byteline will access your local database through the Ngrok tunnel.
