MySQL

The MySQL connector turns approved relational rows into searchable documents. It supports two indexing modes:

• Automatic discovery reads accessible base tables in the selected database, lets administrators select tables and columns, and maps rows into documents.
• Custom SQL runs a controlled SELECT or WITH query and maps returned columns into document ID, title, searchable content, metadata, and update time.

Use a dedicated read-only account and expose only curated tables or views. Do not connect a broad production account to a database that contains unapproved personal data, credentials, tokens, or operational secrets.

What You Need

Create a Read-Only MySQL Account

Run grants from a database administrator account. Adjust host pattern, database, table, and view names to match your environment.

1
CREATE USER 'aivis_reader'@'10.0.%'
2
  IDENTIFIED BY '<generated-password>'
3
  WITH MAX_USER_CONNECTIONS 3;
4
5
GRANT SELECT ON knowledge.product_catalog_view TO 'aivis_reader'@'10.0.%';
6
GRANT SELECT ON knowledge.faq_article_view TO 'aivis_reader'@'10.0.%';
7
8
SHOW GRANTS FOR 'aivis_reader'@'10.0.%';

MySQL supports account-level TLS requirements such as REQUIRE SSL. Enable them for this account only when your AIvis deployment path provides compatible MySQL TLS handling, for example through an approved private database proxy.

Create views when the source tables contain sensitive columns:

1
CREATE VIEW knowledge.product_catalog_view AS
2
SELECT
3
  id,
4
  name,
5
  status,
6
  public_summary,
7
  category,
8
  updated_at
9
FROM product_catalog
10
WHERE searchable = 1;
11
12
GRANT SELECT ON knowledge.product_catalog_view TO 'aivis_reader'@'10.0.%';

Use database-level grants only for a database that is already curated for AIvis indexing:

1
GRANT SELECT ON aivis_public.* TO 'aivis_reader'@'10.0.%';

Create the Credential

The connector uses utf8mb4 when reading MySQL rows and sets a 10-second connection timeout.

Choose an Indexing Mode

Automatic Discovery

Automatic discovery reads accessible base tables in the selected database and ignores MySQL system schemas such as information_schema, mysql, performance_schema, and sys.

In the schema tree:

• Select only tables and fields that are approved for indexing.
• Binary, geometry, and blob-like columns are not indexed.
• AIvis uses primary keys first, then non-null unique indexes, then a row hash when no stable key exists.
• Tables without a stable key can create new document IDs when selected values change; prefer adding a primary key or using Custom SQL with an explicit ID.
• The default title field is inferred from columns such as title, name, subject, or label; you can override it in table settings.
• The update field is inferred from datetime columns such as updated_at, updated, modified_at, modified, or last_modified; no update field means the table requires full scans.

Custom SQL Requirements

The connector wraps your query as a subquery and validates returned columns. Keep the SQL read-only and deterministic.

Example:

1
SELECT
2
  CAST(p.id AS CHAR) AS doc_id,
3
  p.name AS title,
4
  CONCAT_WS(
5
    '\n',
6
    CONCAT('Status: ', p.status),
7
    CONCAT('Category: ', p.category),
8
    CONCAT('Summary: ', p.public_summary)
9
  ) AS body,
10
  p.category,
11
  p.updated_at
12
FROM knowledge.product_catalog_view AS p
13
WHERE p.searchable = 1

For this query, set:

Production Safety

The connector opens MySQL reads in a read-only transaction and sets max_execution_time during reads. That protects AIvis from writing data, but it does not replace database-side controls.

Before production:

• Use a replica, analytics database, or curated view layer when possible.
• Add indexes that support your Custom SQL filters and update window.
• Avoid SELECT * in Custom SQL; explicitly return only approved columns.
• Avoid long-running joins against hot OLTP tables.
• Keep batch size modest until query plans and sync duration are measured.
• Restrict the MySQL account host pattern to the AIvis network path rather than %.
• Do not enforce account-level MySQL TLS requirements until the deployed connection path supports them; use private networking or an approved proxy for protected routes.
• Verify that query plans do not create heavy locks, full scans on large tables, or excessive temporary tables.

Verification

1. Confirm the credential connects with the read-only account.
2. In Automatic discovery, confirm the expected database tables and fields appear.
3. In Custom SQL, validate that the query returns every configured column.
4. Run an initial sync and inspect row count, indexed document count, and failures.
5. Search for representative records by title and content.
6. Confirm unselected tables, fields, and sensitive columns are absent from search.
7. Update one test row and confirm the same document updates when an update field is configured.
8. Test as a user outside the connector audience and confirm restricted documents do not appear.

Troubleshooting

Related Official Documentation

• MySQL CREATE USER
• MySQL GRANT
• MySQL encrypted connections