MySQL

16 min

	MySQL is an open-source relational database management system. With the MySQL app in Make, you can run your custom logic directly on your database server through stored procedures. Make loads the interface of input and output parameters and record sets dynamically so each parameter/value can be mapped individually.

Requirements

To use the MySQL app in Make, you must have a MySQL server connection and a configured MySQL server.

Connect MySQL and Make

To get started, you must first create a connection between MySQL and Make, allowing the two services to communicate.

To create the connection, you need to:

Configure your MySQL server

Create a connection in Make

Configure your MySQL server

Remote access must be allowed, and the CREATE ROUTINE privilege must be granted on the MySQL server.

Set the read access to MySQL tables

Make sure the account you are using to connect to your database has read access to the information_schema.tables, information_schema.columns, information_schema.routines, and information_schema.parameters tables.

Allow connections to and from Make IP addresses

Make will make connections to your database from the IP addresses listed in this article.

Our servers are set up with valid PTR records, so you can easily restrict access by allowing %.make.com.

Enable access on your firewall

Enable incoming TCP connections to port 3306 from source IP addresses.

Configure MySQL for remote access

Linux

Open the configuration file /etc/mysql/my.cnf

Comment outline bind-address = 127.0.0.1 (by adding a hash at the beginning of the line):

#bind-address = 127.0.0.1

Restart the MySQL daemon

Create and grant remote access to your MySQL account

Start the MySQL command-line interface:

mysql -u root -p

Execute these commands (with <database>, <user> and <password> replaced by actual values):

CREATE USER <user>@'%' IDENTIFIED BY <password> GRANT ALL PRIVILEGES ON <database>.* TO <user>@'%' IDENTIFIED BY <password> WITH GRANT OPTION; FLUSH PRIVILEGES;

Set the tolerance of non-interactive connection

Make processes scenarios transactionally. That's why all Make modules that support transactions have open transactions throughout the whole run of a scenario. Therefore, make sure that you have set a long enough time to tolerate non-interactive connections. This MySQL command will list the values:

SHOW VARIABLES LIKE 'wait_timeout';

See more information in the MySQL documentation on Server System Variables.

Create a connection in Make

Once you have configured your MySQL server, you're ready to create the connection in Make

To create the connection:

Optional: In the Connection name field, enter a name for the connection.

Complete the following fields:

Field	Description
Connection name	Enter the name of the new MySQL connection you want to establish.
Host	Enter the address of the MySQL server. The MySQL server must be publicly accessible from the internet. If you want to use the database hosted on your local server, you'll need to specify your public IP address, and set the port forwarding on your router. Do not enter "localhost," or "127.0.0.1" in this field, as it will not work.
Port	Enter the port number of the MySQL server.
Database	Enter the database name if needed. This is required for the Execute a Query (advanced) module.
User	Enter the MySQL server user name.
Password	Enter the password.
Allow insecure authentication	Enable this option to connect to MySQL instances that use the old authentication method (MySQL 4.0 and older). If needed, see additional information about Updating Old Authentication Protocol Passwords.
Charset (optional)	Enter the MySQL character set for the connection, e.g., utf8. In this case, the utf8_general_ci MySQL collation is used.
Self-signed certificate (optional)	Upload your certificate (P12, PFX, or PEM file) if you want to use TLS using your self-signed certificate. If you're using the client-side certificate authorization, you can enter your CA certificate here. Make does not retain or store any data (files, passwords) you provide. File and password are only used to extract a private key/certificate.
Client Private Key (optional)	Upload the private key to use the client-side certificate authorization. Make does not retain or store any data (files, passwords) you provide. File and password are only used to extract a private key/certificate.
Client Certificate (optional)	Upload your certificate if you want to use client-side certificate authorization. Make does not retain or store any data (files, passwords) you provide. File and password are only used to extract a private key/certificate.

Click Save.

If prompted, authenticate your account and confirm access.

You've successfully created the connection and can now use the MySQL app in your scenarios. If you want to make changes to your connection in the future, follow the steps here.

MySQL modules

After connecting to the MySQL app, you can use the following types of modules to build your scenarios.

Actions

Deletes a row according to a defined filter.

Field	Description
Connection	Establish a connection to the MySQL server.
Table	Select the table where you want to delete the row.
Filter	Set the filter for the row to be deleted. Set filter values. You can also use logical operators, AND/OR, to specify your selection.

Field	Description
Connection	Establish a connection to the MySQL server.
Query	Enter the desired MySQL query. Make sure that the Database field under Connection settings is filled. Variables used in the query are not sanitized. Make sure you sanitize variables properly to prevent SQL injection.
Continue the execution of the route even if the module returns no results	If enabled, the scenario will not be stopped by this module.

Only one SQL statement is allowed (one semicolon ; as a statement terminator per query).

Field	Description
Connection	Establish a connection to the MySQL server.
Stored procedure	Select the stored procedure you want to execute. The stored procedure must already be created. Here is a sample SQL statement that creates a stored procedure that simply inserts the values passed in the three parameters into a table: DELIMITER ;; CREATE PROCEDURE `insert_record_from_integromat`(IN _name text, IN _email text, IN _phone text) BEGIN INSERT INTO contacts (name, email, phone) VALUES (_name, _email, _phone); END;;
Fields	Contains the parameter fields from the stored procedure. E.g., enter values you want to insert into the table during the stored procedure execution.

Example

The following example adds 2 to the provided parameter (e.g., in the Execute a Stored Procedure module), and returns the result, together with the current date (can be further iterated using the Iterate Recordset of a Stored Procedure module).

CREATE PROCEDURE `test_procedure`(IN id integer, OUT outid integer)
BEGIN
 set outid = id + 2;
 select id as inid, now() as current_date;
END$$

Inserts a row with the desired values into a selected table.

Field	Description
Connection	Establish a connection to the MySQL server.
Table	Select the table where you want to add a row.
column name(s)	Enter the desired values that will be inserted into the new row.

To pass a NULL value, use an INSERT statement using the Execute a query (advanced) module. For more information, refer to Passing NULL values into a tablePassing NULL Values into a Table.

Updates rows (can be defined by the filter settings) in the selected table.

Field	Description
Connection	Establish a connection to the MySQL server.
Table	Select the table where you want to update a row.
Column name(s)	Enter the desired values that will be inserted into the rows.
Filter	Set the filter to define which rows will be updated.

To pass a NULL value, use an INSERT statement using the Execute a query (advanced) module. For more information, refer to Passing NULL values into a tablePassing NULL Values into a Table.

Searches

Selects a row from a table according to a defined filter.

Field	Description
Connection	Establish a connection to the MySQL server.
Table	Select the table you want to select a row from.
Filter	Set the filter for the row to be selected. Set filter values. You can also use logical operators, AND/OR, to specify your selection. In the following MySQL dialog, the row that contains number 1 or 2 in the "column1" column will be selected.
Continue the execution of the route even if the module returns no results	If enabled, the scenario will not be stopped by this module.

You may also use the existing basic text operators for comparing dates and datetimes. Just use text formatted date/datetime as the second operand:

YYYY-MM-DD for date type
YYYY-MM-DD HH:mm:ss for datetime type

Literal static datetime value example:

Formatted dynamic datetime value example:

MySQL

Requirements

Connect MySQL and Make