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 synchronise it with Meilisearch.
This guide teaches you to use meilisync
to keep Meilisearch up to date with a MySQL database.
Requirements
- A command-line console, such as macOS's terminal or Window's Cygwin
- A MySQL database populated with data
- A Meilisearch instance, either self-hosted or via Meilisearch Cloud
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 the 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
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 Meiliearch 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
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.
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.