Momy is a simple cli tool for replicating MongoDB to MySQL in realtime.
- Enable SQL query on data in NoSQL database
- Enable to be accessed by Excel / Access
Install via npm:
$ npm install -g momyOr use docker:
$ docker run -it --rm -v $(pwd):/workdir cognitom/momyYou might want to create an alias, for example
$ echo 'alias momy="docker run -it --rm -v $(pwd):/workdir cognitom/momy"' >> ~/.bashrcSee more detail about Docker configurations below.
Momy uses Replica Set feature in MongoDB. But you don't have to replicate between MongoDB actually. Just follow the steps below.
Start a new mongo instance with no data:
$ mongod --replSet "rs0" --oplogSize 100Open another terminal, and go to MongoDB Shell:
$ mongo
....
> rs.initiate()rs.initiate() command prepare the collections that is needed for replication.
Launch MySQL instance, and create the new database to use. The tables will be created or updated when syncing. You'll see mongo_to_mysql, too. This is needed to store the information for syncing. (don't remove it)
Create a new momyfile.json file like this:
{
"src": "mongodb://localhost:27017/dbname",
"dist": "mysql://root:password@localhost:3306/dbname",
"prefix": "t_",
"case": "camel",
"collections": {
"collection1": {
"_id": "number",
"createdAt": "DATETIME",
"field1": "number",
"field2": "string",
"field3": "boolean",
"field4.subfield": "string"
},
"collection2": {
"_id": "string",
"createdAt": "DATETIME",
"field1": "number",
"field2": "string",
"field3": "boolean",
"field4": "TEXT"
}
}
}src: the URL of the MongoDB serverdist: the URL of the MySQL serverprefix: optional prefix for table name. The name of the table would bet_collection1in the example above.fieldCase: optional.snakeorcamel. See the section below.exclusions: optional. Chars or a range of chars to exclude:"\uFFFD"inclusions: optional. Chars or a range of chars to include:"\u0000-\u007F"collections: set the collections and fields to sync
_id field is required for each collection and should be string or number.
"<field_name>": "<field_tipe>"
or, field_name could be dot-concatenated:
"<field_name>.<sub_name>": "<field_tipe>"
For example, if you have { a: { b: { c: 'hey!' } } } then "a.b.c": "string"
Currently these native types are supported:
BIGINTTINYINTVARCHARDATEDATETIMETIMETEXT
There're also some aliases:
number=>BIGINTboolean=>TINYINTstring=>VARCHAR
Some system like Microsoft Access don't allow dot-concatenated field names, so address.street will cause an error. For such a case, use fieldCase:
snake:address.street-->address_streetcamel:address.street-->addressStreet
Note: if you set fieldCase value, the name of _id field will change into id without _, too.
At the first run, we need to import all the data from MongoDB:
$ momy --config momyfile.json --importThen start the daemon to streaming data:
$ momy --config momyfile.jsonor
$ forever momy --config momyfile.jsonFirst thing first, create a network for your containers:
$ docker network create my-netThen, launch database servers:
$ docker run \
--name my-mongod \
--detach --rm \
--network my-net \
--mount type=volume,source=my-mongo-store,target=/data/db \
mongo --replSet "rs0"
$ docker run \
--name my-mysqld \
--detach --rm \
--network my-net \
--mount type=volume,source=my-mysql-store,target=/var/lib/mysql \
--env MYSQL_ALLOW_EMPTY_PASSWORD=yes \
mysqlIf this is the first time to run the containers above, you need to initialize them:
$ docker exec my-mongod mongo --eval 'rs.initiate()'
$ docker exec my-mysqld mysql -e 'CREATE DATABASE momy;'Create momyfile.json like this:
{
"src": "mongodb://my-mongod:27017/momy",
"dist": "mysql://root@my-mysqld:3306/momy",
"collections": {...}
}Note: you must change username, password, port, ...etc. to fit your environment.
OK, let's run momy with --import option:
$ docker run \
--interactive --tty --rm \
--network my-net \
--mount type=bind,source=$(pwd),target=/workdir \
cognitom/momy --importEverything goes well? Then, stop the container (Ctrl + C). Now you can run it as a daemon:
$ docker run \
--detach --rm \
--restart unless-stopped \
--init \
--network my-net \
--mount type=bind,source=$(pwd),target=/workdir \
cognitom/momySee dev directory.
MIT
This library was originally made by @doubaokun as MongoDB-to-MySQL and rewritten by @cognitom.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent