MySQL connector

Use the MySQL connector to index from a MySQL table without writing an indexing script. In this quickstart, you import a sample product dataset into MySQL, create a source for this database table, use an Algolia destination, and create an on-demand synchronization task.

Before you begin

Make sure you have:

An Algolia account. Create one for free if you don’t already have one.
A MySQL host that Algolia can reach. Use a hosted MySQL server or configure an SSH tunnel to your server. A server available only at 127.0.0.1 or localhost isn’t reachable from the hosted Algolia connector.
Your MySQL host’s port number. The default is 3306 but your provider may use a different port.
A MySQL database. Create or choose a database for the sample data and note its name. For more information, see Creating and selecting a database.
A MySQL username and password. The user must have CREATE, DROP, INSERT, and SELECT access to the target database. For more information, see GRANT Statement.

Set up the MySQL connector

Create a source, destination, transformation, and synchronization task for the sample product dataset.

The source tells the connector which MySQL database to read.
The destination tells the connector which Algolia index to write to.
The transformation converts MySQL JSON values and enriches the records.
The task controls which table to index and when the connector runs.

Download the sample SQL file

Command line

curl --fail --silent --show-error --location \
  --output apparel.sql \
  https://raw.githubusercontent.com/algolia/quickstarts/main/sample-data/apparel.sql

Import the sample data into MySQL

This script replaces any existing apparel table in your database.

Run the script:

Command line

mysql --host=MYSQL_HOST \
  --port=MYSQL_PORT \
  --user=MYSQL_USER \
  --password \
  --database=MYSQL_DATABASE \
  < apparel.sql

Replace MYSQL_HOST, MYSQL_PORT, MYSQL_USER and MYSQL_DATABASE with your MySQL connection details.When prompted, enter the password for MYSQL_USER.For more information, see Executing SQL statements from a text file.

Verify the import

Run:

Command line

mysql --host=MYSQL_HOST \
  --port=MYSQL_PORT \
  --user=MYSQL_USER \
  --password \
  --database=MYSQL_DATABASE \
  --execute="SELECT COUNT(*) AS records FROM apparel;"

When prompted, enter the password for MYSQL_USER.The SQL query should return 1000 records.

Choose the MySQL connector

Go to the Algolia dashboard and select your Algolia application. Open the Connectors page.Find MySQL, then select Connect.

Configure the MySQL source

Under Configure your data source, select Create a new source.Enter the connection details for the database table that contains the sample data:

Field	Value
Host	`MYSQL_HOST`
Port	`MYSQL_PORT`, usually `3306`
Database	`MYSQL_DATABASE`
User	`MYSQL_USER`
Password	`MYSQL_USER_PASSWORD`

Under SSH Tunnel Method, select No Tunnel if Algolia can connect directly to your database. Otherwise, configure the authentication for your SSH tunnel.

Create the MySQL source

In Connector name, enter Quickstart products MySQL source, then select Create source.

Transform the records

This transformation adds a price_range attribute to each record. After the connector indexes your records, you can display price_range or configure it as a facet.Select Transform using the code editor and replace the placeholder Transformation code with this function:

JavaScript

async function transform(record, helper) {
    const price = Number(record.price);
    if (!Number.isFinite(price)) {
        return record;
    }
    if (price < 25) {
        record.price_range = "Under $25";
    } else if (price < 50) {
        record.price_range = "$25 to $49";
    } else if (price < 100) {
        record.price_range = "$50 to $99";
    } else {
        record.price_range = "$100 and up";
    }
    return record;
}

Select Save.

Choose the destination index

Under Configure your destination, select Create a new destination.Under Search, enter quickstart-products as the index name.

If you enter the name of an existing index, the connector overwrites the records and settings in that index.

Create the destination

Under Index credentials, select Create one for me. To use an existing API key, choose one with the addObject, deleteIndex, and editSettings ACLs.In Name, enter MySQL quickstart products destination, then select Create destination.

Create and run an on-demand synchronization task

Under Configure your task, select On demand.Select the apparel table and keep all its columns selected. Confirm that the connector maps the table’s objectID primary key to each record’s Algolia objectID.Select Create task, then Run and wait for the task to finish.

Verify the indexed records

When the task completes, open the quickstart-products index in the Algolia dashboard. The index contains 1,000 product records with attributes such as title, description, product_type, price, price_range, and showcase_image.

You can use this index as the data source for Build your first search experience. Before you build the UI, configure product_type as an attribute for faceting in the quickstart-products index.

Index your own MySQL data

Use a dedicated read-only MySQL account with access only to the tables you want to index, and restrict network access to Algolia’s connector IP addresses.

Set up a dedicated MySQL connector user

Connect to MySQL with an account that can create users and grant privileges. Run the following statements in the MySQL client or your hosting provider’s SQL console. Create a dedicated connector user and grant it SELECT access to the table you want to index. For example:

SQL

CREATE USER 'algolia_connector'@'%' IDENTIFIED BY 'MYSQL_USER_PASSWORD';
GRANT SELECT ON MYSQL_DATABASE.TABLE_NAME TO 'algolia_connector'@'%';

Replace MYSQL_USER_PASSWORD and MYSQL_DATABASE.TABLE_NAME with your connector user’s password, database, and table name. If the user already exists, run only the GRANT statement.

Allow the Algolia connector to reach your MySQL server

The % host permits remote connections.

For production databases, restrict inbound connections to Algolia’s connector IP addresses or use an SSH tunnel. instead of allowing connections from all IP addresses.

Configure the connector for your data

Follow the steps to configure the MySQL source and create a synchronization task: replace the sample connection details and apparel table with your own database details and tables.
Use the dedicated connector user you created.
Review the transformation and destination index to make sure they’re appropriate for your data.

Secure the connection

Configure connection security (SSL modes) in your MySQL source. SSL encrypts the connection to your MySQL server and can verify the server’s identity. An SSH tunnel lets the connector reach a database that isn’t directly accessible from Algolia. Depending on your setup, you can use SSL, an SSH tunnel, or both.

Configure SSL

Choose an SSL mode that matches your MySQL server’s TLS configuration:

Preferred. The connector uses SSL if the MySQL server supports it. Otherwise, it connects without SSL.
Required requires an encrypted connection but doesn’t verify the server certificate.
Verify CA requires encryption and verifies the server certificate against a trusted certificate authority. Add the CA certificate supplied by your database provider to CA certificate.
Verify Identity verifies that the certificate identifies the host. Add the provider’s CA certificate.

Leave Client certificate and Client key empty unless your MySQL server requires mutual TLS authentication. If it does, add the client certificate supplied by your database provider to Client certificate and its corresponding private key to Client key.

Connect through an SSH tunnel

If Algolia can’t connect directly to your MySQL database, connect through an SSH host. Select an authentication method:

SSH Key Authentication
Password Authentication

To authenticate with an RSA private key:

Generate an RSA key pair in PEM format:

Command line

ssh-keygen -t rsa -m PEM -f algolia_mysql_rsa

Add algolia_mysql_rsa.pub to the SSH user’s authorized keys on the bastion host.
In the MySQL source configuration, select SSH Key Authentication under SSH Tunnel Method.
Enter the SSH Tunnel Jump Server Host, SSH port, and SSH Login Username.
Add the contents of algolia_mysql_rsa to SSH Private Key.

MySQL data types

The connector maps MySQL values to JSON-compatible Algolia attributes. In particular:

DECIMAL values become numbers.
TINYINT(1) values become booleans by default.
Native MySQL JSON values become serialized JSON strings.
TEXT and LONGTEXT columns remain strings, including columns that contain JSON-encoded data.

For JSON-encoded strings, use a transformation to convert them into arrays or objects before indexing.

Transformations

Use transformations to add computed attributes or change values before the connector indexes your records. To use a transformed attribute for search, faceting, or ranking, update the relevant index settings, such as searchableAttributes, attributesForFaceting, or customRanking.

Test transformations with repeated task runs. Transformations that read from and write to the same attribute, or extract a value and then delete the source attribute, can produce different results when the task runs again.

Synchronization schedule

After you create a task, you can edit it in the Algolia dashboard to choose when the connector runs:

On demand. Run the connector manually.
Scheduled. Select a predefined schedule or enter a custom cron expression.

Indexing strategy

You can choose how the connector updates your index. For information about full reindexing, full record updates, and partial record updates, see Data synchronization strategies.

Limitations

This connector is subject to the following limitations:

Search and Discovery platform

Optimization and Personalization

AI-powered experiences

Production and scale

MySQL connector

Before you begin

Set up the MySQL connector

Index your own MySQL data

Set up a dedicated MySQL connector user

Allow the Algolia connector to reach your MySQL server

Configure the connector for your data

Secure the connection

Configure SSL

Connect through an SSH tunnel

MySQL data types

Transformations

Synchronization schedule

Indexing strategy

Limitations

See also

​Before you begin

​Set up the MySQL connector

​Index your own MySQL data

​Set up a dedicated MySQL connector user

​Allow the Algolia connector to reach your MySQL server

​Configure the connector for your data

​Secure the connection

​Configure SSL

​Connect through an SSH tunnel

​MySQL data types

​Transformations

​Synchronization schedule

​Indexing strategy

​Limitations

​See also

Before you begin

Set up the MySQL connector

Index your own MySQL data

Set up a dedicated MySQL connector user

Allow the Algolia connector to reach your MySQL server

Configure the connector for your data

Secure the connection

Configure SSL

Connect through an SSH tunnel

MySQL data types

Transformations

Synchronization schedule

Indexing strategy

Limitations

See also