Mongodb BI Connector Installation guide

Make sure OpenSSL installed on your host

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew update
brew install openssl

Install the BI Connector

Download the MongoDB Connector for BI

tar -xvzf mongodb-bi-osx-x86_64-{version}.tgz

sudo install -m755 bin/mongo* /usr/local/bin/

Launch the BI Connector

Create schema file with mongodrdl

// mongodb without authentication
mongodrdl -d zly -o schema.drdl

// mongodb with authentication
sudo mongodrdl --host <host:port> -d <databaseName> -c <collectionName> -u opadmin -p 123456 --authenticationDatabase zlydata -o patientInfo.drdl

//--host    -h    //默认localhost
//--port     -p    //默认27017
//--db    -d    //指定database
//--collecion        -c        //指定集合，默认db中的所有集合
//--out            -o        //指定输出schema文件

//--username        -u        //用户名
//--password        -p        //密码
//--authenticationDatabase <dbname>    //指定用户创建的db
//--authenticationMechanism <name>    //指定认证机制，默认SCRAM-SHA-1

you can create lots of schema file for different collection

Starting mongosqld with a Schema File

mongosqld --schema schema.drdl

OR Starting mongosqld with a Config File

//mongosqld.config
mongodb:
  net:
    uri: dds-2ze665***********6-pub.mongodb.rds.aliyuncs.com:3717
    auth:
      username: "opadmin"
      password: "123456"
      mechanism: "SCRAM-SHA-1"
      source: "zlydata"
security:
  enabled: true
net:
  bindIp: 0.0.0.0
  port: 3307

schema:
  path: "/opt/mongo_bi/schema"

mongosqld --config mongosqld.config

OR Starting mongosqld with systemd on linux

mongosqld install --config mongosqld.config
service mongosql start

sorry, i didn’t start it successful, there’s a failure i didn’t konw why

Connect from MySQL shell

Install the C Authentication Plugin and MySQL client

1. Download the MySQL5.7.x installer
   and install the MySQL Community Server, which includes the MySQL shell
2. Download the C Authentication Plugin
3. Move the file mongosql_auth.so to mysql plugin directory./lib/plugin/,you can find it by running the following command:

   mysql_config --plugindir

Connect

// mongo without authentication
mysql --protocol tcp --port 3307

// mongodb with authentication
mysql --protocol tcp --host 172.0.0.1 --port 3307 '--user=opadmin?source=zlydata' --default-auth=mongosql_auth -p

Connect from Other Client

Install the JDBC Authentication Plugin

1. you can either download the plugin .jar file directly or use Maven
2. save it to a local directory, and select it as a jdbc driver to de client.

3. add the authentication plugin in the MySQL connection string:

   jdbc:mysql://127.0.0.1:3307?useSSL=false&authenticationPlugins=org.mongodb.mongosql.auth.plugin.MongoSqlAuthenticationPlugin

4. you may also specify the authentication mechanism and authentication source as a query parameter on the username, for example:

   {username}?mechanism=PLAIN
   {username}?source={dbname}