Prerequisites

  1. Admin/Owner permissions

  2. Database with version 8.0 or earlier.

Connecting MySQL with Automate.io

To connect your MySQL server with any other app, you would need to authenticate your MySQL server with Automate.io first. Below are the things you would need while following the authentication process.

1. Host

The IP address or hostname (db.automate.io) of your database instance.

2. Port

Port number used to connect to your database. In case you are unsure about this, the default port of a MySQL database is usually 3306.

3. Database

Name of the database to read data out of.

4. Username

Username with access to the database. We recommend creating a brand new Automate.io-specific user with highly limited permission scopes.

Note: please ensure that the User specified in this field has sufficient privileges. You can grant the privileges to the User using the following command.

GRANT CREATE,INSERT,SELECT,UPDATE ON DATABASE_NAME.* TO 'USERNAME'@'HOST';

* USERNAME: The user which you are using for Authentication.

* HOST: The host from which this user can be accessed i.e. Automate.io's IP address (34.206.38.217) or '%' if want this user to be accessible from anywhere.

5. Password

Similar to the username, make this a unique and strong password.

6. SSL Client Certificate and SSL Private Key

You can use these fields if you'd like to use MySQL certificate authorization instead of username/password authorization. Please note that you still need to provide a username since it is required.

Note: Please provide the SSL private key as-is without modifying it.

7. SSL Certificate Authority

This is the field you can use to provide the name of the SSL certificate authority as-is.

Note: all the fields are stored encrypted in our database. Of course, we have the encryption keys on hand but they are stored separately to minimize risk. Nonetheless, we recommend not authenticating a production database to Automate.io. Instead, set up an intermediary database to connect with Automate.io then dump data back to your main production database using some out-of-band process.


Triggers

⚡️ New Row

Description: This will trigger the bot whenever a new row is added to the specified table. You'd need to specify the below.

  1. The table name in which the Bot will look for new rows.

  2. Order by value. You'd select either auto-increment or date type of fields here. This field is used to identify and trigger the Bot when new rows are added. If you do not have such a field, please create an auto-increment type of field to make this work.

Limitations: Maximum of 100 new rows in a single data check interval. For example, if you are on the Startup plan, the data check time is 2 minutes. A maximum of 100 new rows every 2 minutes would be fetched. If there are more than 100 new rows received in 2 minutes, they would be fetched in the next data check interval.

⚡️ Updated Row

Description: This will trigger the bot whenever an existing row is updated in the specified table. You'd need to specify the below.

  1. The table name in which the Bot will look for modified rows.

  2. Order by value. You'd select the "updated_at" or "last_modified" type of fields here. This field is used to identify and trigger the Bot when existing rows are modified. If you do not have such a field, please create one of such type to make this work.

Limitations: Maximum of 100 updated rows in a single data check interval. For example, if you are on the Startup plan, the data check time is 2 minutes. A maximum of 100 updated rows every 2 minutes would be fetched. If there are more than 100 updated rows received in 2 minutes, they would be fetched in the next data check interval.


Actions

✅ Add Row

Description: This action is used to add a new row of data to the specified table.

✅ Update Row

Description: This action is used to update an existing row of data in the specified table. You would need to provide the primary key value of the row (which needs to be updated).


Common issues with MySQL integration

❗️ I am unable to authenticate or connect my MySQL server with Automate.io.

Sometimes, database connections might have strict requirements. Please ensure the below things.

  1. You are not using a database with version 8.0+. We only support databases with version 8.0 or earlier.

  2. Your database is not IP restricted.

  3. You are not trying to authenticate a locally hosted database. We do not support locally hosted databases.

  4. There are no typos in the username, password, SSL related fields.

  5. SSL certificates are active and in place.

❗️ Error: Invalid Credentials. Access Denied for user 'XYZ'.

This error occurs when you are using the wrong host or using localhost. Please make sure you are providing the correct host.

❗️ Error: Invalid Credentials. Access Denied for user 'XYZ'.

This error occurs when you are using the database name. Please make sure you are providing the correct name.

Did this answer your question?