Go to homev1.7 Docs

    Syncing databases with MySQL and meilisync

    Though Meilisearch is a database, it is not recommended you use it as your primary data store. Instead, you should use an external database to store your data and periodically synchronize it with Meilisearch.

    This guide teaches you to use meilisync to keep Meilisearch up to date with a MySQL database.

    Requirements

    Install meilisync

    Install meilisync to the same location as your primary database. You can do this manually or with Docker:

    Docker

    First, create a docker-compose.yml file in your Docker project directory. Open it with a text editor and add the following content:

    services:
  meilisync:
    platform: linux/x86_64
    image: long2ice/meilisync
    volumes:
      - ./config.yml:/meilisync/config.yml

    If your project already has a docker-compose.yml, add the meilisync settings to the existing services field.

    When you are done, open your console, navigate to your Docker project directory, and run the following command:

    docker-compose pull

    After a few seconds, Docker will inform you it has successfully pulled meilisync's image.

    Connect meilisync to Meilisearch

    With meilisync installed, you now need to connect it to your Meilisearch instance.

    Create a config.yml file in your project's directory. Open it with a text editor and add the following configuration options depending on whether you are using Docker or not:

    meilisearch:
  api_url: http://host.docker.internal:7700/
  api_key: 'MEILISEARCH_API_KEY'
  insert_size: 1000
  insert_interval: 10

    Replace MEILISEARCH_API_KEY with an API key able to create, update, and delete documents and indexes. Replace MEILISEARCH_API_URL with your instance's API URL. If you are using Meilisearch Cloud, this URL should look similar to this: https://ms-4d85L33tC0d3-5041.fra.meilisearch.io.

    Configure MySQL

    After connecting meilisync to Meilisearch, you need to connect it to your MySQL database.

    Binary log format

    To use meilisync with MySQL, you must set your binary log format to ROW.

    Launch your MySQL server using the --binlog-format command-line option:

    mysql --binlog-format=ROW

    Alternatively, set the binary log format during runtime by running the following command in your MySQL server console:

    SET GLOBAL binlog_format = 'ROW';

    Connect meilisync and MySQL

    Open meilisync's config.yml once again and append the following settings to the bottom of the file:

    source:
  type: mysql
  host: 127.0.0.1 # assuming your MySQL server is running on the same machine as `meilisync`
  port: 3306
  database: MYSQL_DATABASE_NAME
  user: MYSQL_USERNAME
  password: MYSQL_PASSWORD

    Replace MYSQL_DATABASE_NAME, MYSQL_USERNAME, and MYSQL_PASSWORD with the name of your database, a user with read access to that database, and its credentials.

    Configure meilisync

    The last configuration step is specifying which tables in your database should be synced to which Meilisearch indexes.

    Open config.yml and append the following settings to the bottom of the file:

    sync:
  - table: MYSQL_TABLE_NAME_1
    index: MEILISEARCH_INDEX_NAME_1
    pk: id # Read the "Primary key inference" section below
    full: true
  - table: MYSQL_TABLE_NAME_2
    index: MEILISEARCH_INDEX_NAME_2
    full: true

    Replace MYSQL_TABLE_NAME_1 and MEILISEARCH_INDEX_NAME_1 with the names of your tables and indexes. As the above example demonstrates, you may sync multiple tables.

    Primary key inference

    If your table contains multiple columns with id in their name, meilisync will not be able to infer which column is the primary key. In this case, you must specify the primary key manually by adding a pk field to the table's configuration.

    Customizing `meilisync`

    This tutorial describes a fairly basic meilisync setup. For more customization options, consult meilisync's documentation.

    Run meilisync

    With the configuration done, open your command-line prompt once again and run meilisync with Docker or your manual install:

    docker-compose up

    meilisync should immediately start syncing your primary MySQL database with your Meilisearch instance.

    Once meilisync is done, use Meilisearch's search preview to perform a few test searches and confirm data has been successfully synced between your primary database and Meilisearch.

    Conclusion

    Congratulations—you have successfully set up meilisync.

    You may leave the process running permanently on the background so all changes to your primary database are automatically synced, or you may interrupt the process and only run it when you update your database again.