> ## Documentation Index
> Fetch the complete documentation index at: https://www.algolia.com/llms.txt
> Use this file to discover all available pages before exploring further.

# MySQL connector

> Use the MySQL connector to index data from a MySQL database.

export const Records = () => <Tooltip tip="A record is a searchable object in an Algolia index. Each record consists of named attributes." cta="Algolia records" href="/doc/guides/sending-and-managing-data/prepare-your-data#algolia-records">
    records
  </Tooltip>;

export const Index = () => <Tooltip tip="An Algolia index is a searchable dataset that consists of records and configuration settings. These settings define how the records are searched and ranked.">
    index
  </Tooltip>;

Use the MySQL connector to index <Records /> 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 <Index /> destination, and create an on-demand synchronization task.

## Before you begin

Make sure you have:

* **An Algolia account**.
  [Create one for free](https://www.algolia.com/users/sign_up) if you don't already have one.
* **A MySQL host that Algolia can reach**.
  Use a hosted MySQL server or configure an [SSH tunnel](https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-connection-ssh.html) 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](https://dev.mysql.com/doc/refman/8.4/en/creating-database.html).
* **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](https://dev.mysql.com/doc/refman/8.4/en/grant.html).

## 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.

<Steps>
  <Step title="Download the sample SQL file">
    ```sh Command line icon=square-terminal theme={"system"}
    curl --fail --silent --show-error --location \
      --output apparel.sql \
      https://raw.githubusercontent.com/algolia/quickstarts/main/sample-data/apparel.sql
    ```
  </Step>

  <Step title="Import the sample data into MySQL">
    <Warning>
      This script replaces any existing `apparel` table in your database.
    </Warning>

    Run the script:

    ```sh Command line icon=square-terminal theme={"system"}
    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](https://dev.mysql.com/doc/refman/8.4/en/mysql-batch-commands.html).
  </Step>

  <Step title="Verify the import">
    Run:

    ```sh Command line icon=square-terminal theme={"system"}
    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.
  </Step>

  <Step title="Choose the MySQL connector">
    Go to the Algolia dashboard and select your Algolia application.
    Open the [**Connectors**](https://dashboard.algolia.com/connectors) page.

    Find **MySQL**, then select **Connect**.
  </Step>

  <Step title="Configure the MySQL source" id="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.
  </Step>

  <Step title="Create the MySQL source">
    In **Connector name**, enter `Quickstart products MySQL source`, then select **Create source**.
  </Step>

  <Step title="Transform the records" id="transform">
    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:

    ```js JavaScript icon=code expandable theme={"system"}
    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**.
  </Step>

  <Step title="Choose the destination index">
    Under **Configure your destination**, select **Create a new destination**.

    Under **Search**, enter `quickstart-products` as the index name.

    <Warning>
      If you enter the name of an existing index, the connector overwrites the records and settings in that index.
    </Warning>
  </Step>

  <Step title="Create the destination" id="destination">
    Under **Index credentials**, select **Create one for me**.

    To use an existing API key, choose one with the `addObject`, `deleteIndex`, and `editSettings`
    [ACLs](/doc/guides/security/api-keys#access-control-list-acl).

    In **Name**, enter `MySQL quickstart products destination`, then select **Create destination**.
  </Step>

  <Step title="Create and run an on-demand synchronization task" id="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.
  </Step>

  <Step title="Verify the indexed records">
    When the task completes, open the
    [`quickstart-products` index in the Algolia dashboard](https://dashboard.algolia.com/explorer/browse/quickstart-products).
    The index contains 1,000 product records with attributes such as `title`, `description`, `product_type`,
    `price`, `price_range`, and `showcase_image`.
  </Step>
</Steps>

<Info>
  You can use this index as the data source for
  [Build your first search experience](/doc/guides/get-started/quickstart).

  Before you build the UI,
  [configure `product_type` as an attribute for faceting](/doc/guides/managing-results/refine-results/faceting/how-to/declaring-attributes-for-faceting-with-dashboard)
  in the `quickstart-products` index.
</Info>

## 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 SQL icon="database" theme={"system"}
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](https://dev.mysql.com/doc/refman/8.4/en/grant.html).

### Allow the Algolia connector to reach your MySQL server

The `%` host permits remote connections.

<Warning>
  For production databases,
  restrict inbound connections to [Algolia's connector IP addresses](/doc/guides/sending-and-managing-data/send-and-update-your-data/connectors#allow-algolia-ip-addresses) or use an SSH tunnel.
  instead of allowing connections from all IP addresses.
</Warning>

### Configure the connector for your data

1. Follow the steps to [configure the MySQL source](#source) and create a [synchronization task](#task): replace the sample connection details and `apparel` table with your own database details and tables.
2. Use the dedicated connector user you created.
3. Review the [transformation](#transform) and [destination index](#destination) to make sure they're appropriate for your data.

### Secure the connection

Configure connection security (**SSL modes**) in your [MySQL source](https://dashboard.algolia.com/connectors/sources).
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:

<Tabs>
  <Tab title="SSH Key Authentication">
    To authenticate with an RSA private key:

    1. Generate an RSA key pair in PEM format:

       ```sh Command line icon=square-terminal theme={"system"}
       ssh-keygen -t rsa -m PEM -f algolia_mysql_rsa
       ```

    2. Add `algolia_mysql_rsa.pub` to the SSH user's authorized keys on the bastion host.

    3. In the MySQL source configuration, select **SSH Key Authentication** under **SSH Tunnel Method**.

    4. Enter the **SSH Tunnel Jump Server Host**, SSH port, and **SSH Login Username**.

    5. Add the contents of `algolia_mysql_rsa` to **SSH Private Key**.
  </Tab>

  <Tab title="Password Authentication">
    To authenticate with a password:

    1. In the MySQL source configuration, select **Password Authentication** under **SSH Tunnel Method**.
    2. Enter the **SSH Tunnel Jump Server Host**, SSH port, and **SSH Login Username**.
    3. Enter the SSH user's password.

    Use the credentials for the SSH host, not the MySQL username and password.
  </Tab>
</Tabs>

### 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](/doc/api-reference/settings-api-parameters),
such as `searchableAttributes`, `attributesForFaceting`, or `customRanking`.

<Note>
  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.
</Note>

### Synchronization schedule

After you create a task,
you can [edit it in the Algolia dashboard](https://dashboard.algolia.com/connectors/tasks) to choose when the connector runs:

* **On demand**. Run the connector manually.
* **Scheduled**. Select a predefined schedule or enter a custom [cron expression](https://crontab.guru/).

### 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](/doc/guides/sending-and-managing-data/send-and-update-your-data/in-depth/the-different-synchronization-strategies).

## Limitations

This connector is subject to the following limitations:

* [Connectors limits](/doc/guides/scaling/algolia-service-limits/#connectors-limits)
* [Transformation limits](/doc/guides/scaling/algolia-service-limits/#data-transformation-and-fetch-limits)

## See also

* [Connector overview](/doc/guides/sending-and-managing-data/send-and-update-your-data/connectors)
* [Prepare your records for indexing](/doc/guides/sending-and-managing-data/prepare-your-data)
* [Transform your data with code](/doc/guides/sending-and-managing-data/send-and-update-your-data/how-to/transform-your-data-with-code)