Navigation

mongosqld

Description

mongosqld

Note

The MongoDB Connector for BI and associated utilities are compatible only with MongoDB server version 3.2 or greater.

mongosqld accepts incoming requests from a SQL client and proxies those requests to a mongod or mongos instance.

Usage

New in version 2.3:

You can start mongosqld either with a schema file in .drdl format using the --schema option or by sampling data from a MongoDB instance to create the schema.

You can specify which namespace or namespaces to sample data from with the --sampleNamespaces option. If you don’t specify any namespaces or a schema file, mongosqld samples data from all databases in the target MongoDB instance except the admin and local databases.

You can specify a database in which to store schema information with the --sampleSource option. Otherwise, mongosqld holds the schema in memory.

Starting mongosqld with a Schema File

Use the --schema option to specify a schema file when starting mongosqld.

mongosqld --schema /path/to/schema-file.drdl

Use mongodrdl to create a schema file from a MongoDB instance.

Starting mongosqld with a Schema Database

Use the --sampleSource option to specify a database to store schema information.

mongosqld --sampleSource sampleDb

Starting mongosqld with Specified Namespaces

Use the --sampleNamespaces option to specify databases and collections for mongosqld to sample data from to create the schema.

mongosqld --sampleNamespaces contacts.addresses

See additional usage examples below.

Security Considerations

A mongosqld process which connects to a MongoDB instance with authentication enabled must use authentication and pass MongoDB user credentials, either through command line options or with a configuration file.

The user credentials you use with mongosqld must belong to a MongoDB user with the listDatabases and listCollections privileges and must have read permissions on all the namespaces you wish to sample. The MongoDB built-in All-Database Roles provide the necessary privileges.

The MongoDB credentials which BI Connector users provide to connect via an SQL client can be for a different MongoDB user with fewer permissions. SQL client users must have the listCollections privilege, which is included with the built-in Database User Roles

For more information on MongoDB users and roles, see Role-Based Access Control.

See the example below of mongosqld with authentication.

Command Line Options

Core Options

mongosqld
--help

Returns information on the options and use of mongosqld.

--addr

Default: 127.0.0.1:3307

Specifies the host address to listen on.

--version

Returns the mongosqld release number.

--config <path>

Specifies the path to a configuration file.

--mongo-uri <uri>

Default: mongodb://localhost:27017

Specifies a MongoDB connection string to connect to.

The --mongo-uri option only supports the following options within the connection string:

  • readPreference
  • readPreferenceTags
  • replicaSet

For more information on these URI options see Read Preference Options and Replica Set Option.

For options set in the Mongo URI not included in the list above, use the equivalent mongosqld option. For the complete list of mongosqld options, see Command Line Options.

Example

Instead of specifying a username and password in your connection string, run mongosqld with the --auth option to direct mongosqld to pass the authentication credentials provided by the MySQL client to the MongoDB server.

Similarly, instead of enabling ssl in the connection string, run mongosqld with --mongo-ssl.

URI options not in the list above nor in the list of supported mongosqld options are not supported.

--mongo-versionCompatibility <version-number>

Restricts mongosqld to using features that the specified version of MongoDB supports. Only necessary when used with replica sets in which members use different MongoDB versions. Requires MongoDB version 3.2 or later.

--maxVarcharLength <length>

New in version 2.2.

Specifies the maximum length, in characters, for all varchar fields. If mongosqld encounters a string that is longer than the maximum length, mongosqld truncates it to the maximum length and logs a warning.

--mongo-username <username>, -u <username>

New in version 2.3.

Specifies the authentication username to use for schema discovery. Only required if --auth is enabled. The user specified by --mongo-username must be a valid MongoDB user with the listDatabases privilege. See mongosqld Security Considerations.

--mongo-password <password>, -p <password>

New in version 2.3.

Specifies the authentication password to use for schema discovery. Only required if --auth is enabled. Use in conjunction with --mongo-username.

--mongo-authenticationSource <auth-db-name>

Default: admin

New in version 2.3.

Specifies the database that holds the credentials for the schema discovery user. Only available if --auth is enabled. Use in conjunction with credential options --mongo-username and --mongo-password.

--mongo-authenticationMechanism <[SCRAM-SHA-1|PLAIN]>

Default: SCRAM-SHA-1

New in version 2.3.

Specifies the authentication mechanism to use for schema discovery. Only available if --auth is enabled. Use in conjunction with credential options --mongo-username and --mongo-password.

Schema Options

--schema <filename>

Specifies the path to a schema file or the schema directory.

--schemaDirectory <directoryname>

Deprecated since version 2.2.

Use --schema instead.

--sampleNamespaces <db.collection>

New in version 2.3.

--sampleNamespaces specifies a database and collection to sample data from to create the schema. It is also possible to specify multiple collections from a single databases, or multiple collections from multiple databases. See examples below.

If you do not use the --sampleNamespaces option or the --schema option, mongosqld samples data from all available MongoDB databases and collections except the admin and local databases.

--mongo-versionCompatibility <version-number>

Restricts mongosqld to using features that the specified version of MongoDB supports. Only necessary when used with replica sets in which members use different MongoDB versions. Requires MongoDB version 3.2 or later.

--sampleSource <db-name>

New in version 2.3.

Specifies a database to store schema information. If you do not specify either a schema file with the --schema option or a database with the --sampleSource option, mongosqld holds its schema in memory.

--sampleSize <number>

Default: 1000

New in version 2.3.

The number of documents per database to sample when gathering schema information.

Set --sampleSize to 0 to include all documents in the specified namespace when building the schema. If you do not specify a namespace, setting --sampleSize to 0 causes mongosqld to consider all documents in all databases (excluding local, admin, and system) when building the schema. See an example below.

--sampleSource <db-name>

New in version 2.3.

Specifies a database to store schema information. If you do not specify either a schema file with the --schema option or a database with the --sampleSource option, mongosqld holds its schema in memory.

--sampleRefreshIntervalSecs <number>

Default: 0

New in version 2.3.

The interval in seconds at which mongosqld re-samples data to create its schema. The default value is 0, which means that after the initial sampling no re-sampling occurs for the duration of the connection. The specified value must be a positive integer.

If you specify a non-zero value for --sampleRefreshIntervalSecs and a schema database with the --sampleSource option, you must also set the --sampleMode option to write. Otherwise, mongosqld halts with an error.

To force a one-time update of the schema, use the FLUSH SAMPLE command from your SQL client.

--uuidSubtype3Encoding <old|csharp|java>, -b <old|csharp|java>

Specify the encoding used to generate UUID binary subtype 3. Choose one of the following values:

  • old: Old BSON binary subtype representation
  • csharp: The C#/.NET legacy UUID representation
  • java: The Java legacy UUID representation

Log Options

--logAppend

Appends new logging output to an existing log file specified by --logPath.

Requires --logRotate.

--logPath <filename>

Default: stderr

Specifies a path to a log file for storing logging output.

--logRotate reopen | rename

Default: rename

Specifies that you want to rotate logs and how they should be rotated.

When this option is set, the logs rotate when you issue a FLUSH LOGS command to the MongoDB Connector for BI or when you restart mongosqld.

If you set --logRotate to rename:
The existing log file is closed. An RFC3339-formatted timestamp is appended to the closed log file. A new log file is created.
If you set --logRotate to reopen:
The existing log file is closed and reopened.
--verbose, -v

Specifies that mongosqld should provide more detailed log output.

The following table describes the information provided at each log level:

Letter Option Log Level Message Content
--quiet None Log nothing.
No flag Default Log messages that notify user of basic mongosqld events and state changes.
-v Administrator Log messages that provie information to mongosqld administrators.
-vv Developer Log messages useful to MongoDB support and development.
--quiet

Hides all log output.

MongoDB TLS/SSL Options

--mongo-ssl

Default: False

Instructs mongosqld to use TLS/SSL when connecting to a MongoDB instance.

--mongo-sslPEMKeyFile <filename>

Specifies the .pem file containing both the TLS/SSL certificate and key for the MongoDB instance. Specify the file name of the .pem file using relative or absolute paths.

This option is required when using the --mongo-ssl option to connect to a mongod or mongos that has CAFile enabled without allowConnectionsWithoutCertificates.

--mongo-sslPEMKeyPassword <password>

Specifies the path to a file containing the certificate and private key for connecting to MongoDB.

--mongo-sslAllowInvalidHostnames

Permits mongosqld to connect to a MongoDB server whose hostname differs from the hostname on its TLS/SSL certificate.

--mongo-sslAllowInvalidCertificates

Permits the MongoDB instance to present an invalid server SSL/TLS certificate. When using the allowInvalidCertificates setting, MongoDB logs the use of the invalid certificate as a warning.

--mongo-sslCAFile <filename>

Specifies the MongoDB instance’s .pem file containing the root certificate chain from the Certificate Authority. Specify the file name of the .pem file using relative or absolute paths.

Warning

For SSL connections (--ssl) to mongod and mongos, if the mongosqld runs without the --mongo-sslCAFile, mongosqld will not attempt to validate the server certificates. This creates a vulnerability to expired mongod and mongos certificates as well as to foreign processes posing as valid mongod or mongos instances. Ensure that you always specify the CA file to validate the server certificates in cases where intrusion is a possibility.

--mongo-sslCRLFile <filename>

Specifies the MongoDB instance’s .pem file containing the certificate revocation list.

--mongo-sslFIPSMode

Enables FIPS mode in the installed OpenSSL library.

Client TLS/SSL Options

--sslMode <mode>

Default: disabled

New in version 2.3.

Enable or disable TLS/SSL for connections to mongosqld. The argument to the sslMode option can be one of the following:

Value Description
disabled mongosqld cannot accept connections secured using TLS/SSL.
allowSSL mongosqld can accept connections secured using TLS/SSL.
requireSSL mongosqld can only accept connections secured using TLS/SSL.
--sslPEMKeyFile <filename>

Specifies the .pem file containing both the TLS/SSL certificate and key for MySQL clients. Specify the file name of the .pem file using relative or absolute paths.

--sslPEMKeyPassword <password>

Specifies the password used to decrypt the private key specified by --sslPEMKeyFile.

--sslAllowInvalidCertificates

Permits MySQL clients to present invalid client TLS/SSL certificates.

--sslAllowInvalidHostnames

Permits mongosqld to connect to a MySQL server whose hostname differs from the hostname on its TLS/SSL certificate.

--sslCAFile <filename>

Specifies the MySQL client’s .pem file containing the root certificate chain from the Certificate Authority. Specify the file name of the .pem file using relative or absolute paths.

--sslCRLFile <filename>

Specifies the MySQL client’s .pem file containing the certificate revocation list.

--auth

Requires authentication for incoming client requests.

--defaultAuthSource <authSource>

Default: admin

Specifies the default MongoDB authentication source. Set this value to specify a default source that mongosqld uses when authenticating with a MongoDB database. The authentication mechanisms GSSAPI and PLAIN use the $external source, while SCRAM-SHA-1 uses a MongoDB database as its source.

If no value is given for this option it defaults to the the MongoDB admin database.

The $external authentication source stores a reference to system users in a MongoDB database called $external, but the credentials are stored in an external, non-MongoDB system, such as an LDAP server.

Any connection which uses the default value can omit the source parameter from its MySQL or Tableau username.

--defaultAuthMechanism <authMechanism>

Default: SCRAM-SHA-1

Specifies the default authentication mechanism. Set this value to specify a default mechanism for connecting to mongosqld. Any connection which uses this specified default value can omit the mechanism value from its MySQL or Tableau username.

Service Options

--serviceName <service-name>

Name of the system service which runs mongosqld.

--serviceDisplayName <service-name>

Display name of the system service which runs mongosqld.

--serviceDescription <service-name>

Description of the system service which runs mongosqld.

Socket Options

--filePermissions <mode>

Default: 448

Specify the permissions for the Unix domain socket file.

--noUnixSocket

Disable listening on Unix domain sockets.

--unixSocketPrefix <path>

Default: /tmp

Specifies an alternative directory for the mongosqld Unix domain socket.

mongosqld will create a socket file called mysql.sock underneath this path. If you do not specify --unixSocketPrefix, the socket will exist at /tmp/mysql.sock.

Configuration File

You may configure mongosqld using a YAML configuration file. This file may contain the settings listed in the following sections.

Logging Options

systemLog:
  logAppend: <boolean>
  logRotate: "rename"|"reopen"
  path: <string>
  quiet: <boolean>
  verbosity: <integer>
Name Type Corresponds to
systemLog.logAppend
boolean --logAppend
systemLog.logRotate
string --logRotate
systemLog.path
string --logPath
systemLog.quiet
boolean --quiet
systemLog.verbosity
integer --verbose

Schema Options

schema:
  path: <string>
  maxVarcharLength: <integer>
Name Type Corresponds to
schema.path
string --schema
schema.maxVarcharLength
integer --maxVarcharLength

Data Sampling Options

schema:
  sample:
    mode: <[read|write]>
    source: <string>
    size: <integer>
    namespaces: <array of strings>
    refreshIntervalSecs: <integer>
    uuidSubtype3Encoding: <[old|csharp|java]>
Name Type Corresponds to
schema.sample.mode
string --sampleMode
schema.sample.source
string --sampleSource
schema.sample.size
integer --sampleSize
schema.sample.namespaces
string or array of strings --sampleNamespaces
schema.sample.refreshIntervalSecs
integer --sampleRefreshIntervalSecs
schema.sample.uuidSubtype3Encoding
string --uuidSubtype3Encoding

Runtime Options

runtime:
  memory:
    maxPerStage: <integer>
runtime.memory.maxPerStage

Type: integer

Default: unlimited

Specifies the maximum amount of memory in bytes that a query execution stage may use.

Network Options

net:
  bindIp: <string>
  port: <integer>
  unixDomainSocket:
    enabled: <boolean>
    pathPrefix: <string>
    filePermissions: <string>
  ssl:
    mode: <string>
    allowInvalidCertificates: <boolean>
    PEMKeyFile: <string>
    PEMKeyPassword: <string>
    CAFile: <string>
Name Type Corresponds to
net.bindIp
string

The hostname component of --addr

Changed in version 2.2: To bind to multiple IP addresses, enter a list of comma separated values.

Example

“72.198.41.200,72.198.41.201,72.198.41.202”

net.port
integer The port component of --addr
net.unixDomainSocket.enabled
boolean Inverse of --noUnixSocket
net.unixDomainSocket.pathPrefix
string --unixSocketPrefix
net.unixDomainSocket.filePermissions
string --filePermissions
net.ssl.allowInvalidCertificates
boolean --sslAllowInvalidCertificates
net.ssl.mode
string --sslMode
net.ssl.PEMKeyFile
string --sslPEMKeyFile
net.ssl.PEMKeyPassword
string --sslPEMKeyPassword
net.ssl.CAFile
string --sslCAFile

Security Options

security:
  enabled: <boolean>
  defaultMechanism: <string>
  defaultSource: <string>
Name Type Corresponds to
security.enabled
boolean --auth
security.defaultMechanism
string --defaultAuthMechanism
security.defaultSource
string --defaultAuthSource

MongoDB Host Options

mongodb:
  versionCompatibility: <string>
  net:
    uri: <string>
    ssl:
      enabled: <boolean>
      allowInvalidCertificates: <boolean>
      allowInvalidHostnames: <boolean>
      PEMKeyFile: <string>
      PEMKeyPassword: <string>
      CAFile: <string>
      CRLFile: <string>
      FIPSMode: <boolean>
    auth:
      username: <username>
      password: <password>
      source: <auth-db-name>
      mechanism: <auth-mechanism>
Name Type Corresponds to
mongodb.versionCompatibility
string --mongo-versionCompatibility
mongodb.net.uri
string --mongo-uri
mongodb.net.ssl.enabled
boolean --mongo-ssl
mongodb.net.ssl.allowInvalidCertificates
boolean --mongo-sslAllowInvalidCertificates
mongodb.net.ssl.allowInvalidHostnames
boolean --mongo-sslAllowInvalidHostnames
mongodb.net.ssl.PEMKeyFile
string --mongo-sslPEMKeyFile
mongodb.net.ssl.PEMKeyPassword
string --mongo-sslPEMKeyPassword
mongodb.net.ssl.CAFile
string --mongo-sslCAFile
mongodb.net.ssl.CRLFile
string --mongo-sslCRLFile
mongodb.net.ssl.FIPSMode
boolean --mongo-sslFIPSMode
mongodb.net.auth.username
boolean --mongo-username
mongodb.net.auth.password
boolean --mongo-password
mongodb.net.auth.source
boolean --mongo-authenticationSource
mongodb.net.auth.mechanism
boolean --mongo-authenticationMechanism

Process Management Options

processManagement:
  service:
    name: <string>
    displayName: <string>
    description: <string>
Name Type Corresponds to
processManagement.service.name
string --serviceName
processManagement.service.displayName
string --serviceDisplayName
processManagement.service.description
string --serviceDescription

Example Configuration File

Note

The paths used in this example configuration file are Linux-specific. Check your local system documentation to determine the correct paths for your system.

systemLog:
  logAppend: false
  path: "/var/log/mongosqld/mongosqld.log"
  verbosity: vv

security:
  enabled: true

mongodb:
  net:
    uri: "mongo.example.com:27017"
    auth:
      username: "root"
      password: "changeme"

net:
  bindIp: 192.0.2.14
  port: 3307
  ssl:
    mode: "allowSSL"
    PEMKeyFile: "/vagrant/certificates/mongosqld-server.pem"
    CAFile: "/vagrant/certificates/ca.crt"

schema:
  sample:
    namespaces: "inventory.*"

processManagement:
  service:
    name: mongosqld
    displayName: mongosqld
    description: "BI Connector SQL proxy server"

Usage Examples

mongosqld with a Schema File

Use the --schema option to specify a schema file for mongosqld to use.

mongosqld --schema /path/to/schema-file.drdl

Specify a Collection to Sample

Use the --sampleNamespaces option to specify a database and collection for mongosqld to sample data from. If you start mongosqld without the --sampleNamespaces option, it samples data from all available databases except the admin and local databases.

The following example samples data from a collection named addresses in a database named contacts:

mongosqld --sampleNamespaces contacts.addresses

Specify Multiple Collections to Sample

To sample data from all the collections in a database, use the --sampleNamespaces option with a wildcard (*).

The following example samples data from all the collections in a database named inventory:

mongosqld --sampleNamespaces 'inventory.*'

You can repeat the --sampleNamespaces at the command line to sample data from multiple collections within a database, or from multiple collections across several databases.

The following example samples data from all the collections in databases named cars and boats:

mongosqld --sampleNamespaces 'cars.*' \
          --sampleNamespaces 'boats.*' \

You can mix and match wildcards and specific collections with multiple --sampleNamespaces options.

The following example samples data from all collections in a database named orders, as well as the FY2015 and FY2016 collections from the expenses database:

mongosqld --sampleNamespaces 'orders.*' \
          --sampleNamespaces expenses.FY2015 \
          --sampleNamespaces expenses.FY2016 \

Note

It is only necessary to enclose the namespace in single quotes when you use a wildcard.

Sample All Documents in a Collection

If you have a collection in which some documents have different fields from others and you want to ensure that the schema takes them all into consideration, set the --sampleSize option to 0. This causes mongosqld to examine all documents in the specified namespace when it builds its schema.

The following example instructs mongosqld to build the schema based on all documents in the inventory.electronics namespace.

mongosqld --sampleNamespaces inventory.electronics \
          --sampleSize 0

Specify a Database to Persist a Schema

Use the --sampleSource option to specify a database in which to store schema information. Use the --sampleMode option to specify whether mongosqld can write to the schema database or only read from it.

The following example uses the sampleDb database to store schema information and sets --sampleMode to write.

mongosqld --sampleSource sampleDb \
          --sampleMode write

Specify a Data Resampling Interval

Use the --sampleRefreshIntervalSecs option to specify an interval in seconds for mongosqld to resample data and regenerate the schema. The default value for this option is 0, which means that mongosqld never resamples data.

If you set a resampling interval with --sampleRefreshIntervalSecs and you specify a schema database with --sampleSource, you cannot set --sampleMode to read.

The following example does not specify a schema database or a schema file, so it holds its schema in memory. It uses --sampleRefreshIntervalSecs to specify a data resampling interval of 3600 seconds.

mongosqld --sampleRefreshIntervalSecs 3600

mongosqld with Authentication Enabled

To connect mongosqld to a MongoDB instance running with authentication enabled, use the --auth option and provide MongoDB user credentials.

The following example starts mongosqld with credentials for a user named reportsUser with password reportsPass, who has access to the reports.inventory namespace.

mongosqld --auth \
          --mongo-username reportsUser \
          --mongo-password reportsPass \
          --sampleNamespaces reports.inventory

This example does not specify an authenticating database with the --mongo-authenticationSource option, so it defaults to admin. It does not specify an authentication mechanism with the --mongo-authenticationMechanism option, so it defaults to SCRAM-SHA-1.

mongosqld with a Configuration File

If you wish to specify a configuration file which saves logs to /var/log/mongosqld.log and loads a schema from /var/schema.drdl, you may save a file such as the following to /etc/mongosqld.conf:

Warning

All paths specified in the configuration file must be absolute, e.g. they must begin with /.

systemLog:
  path: /var/log/mongosqld.log
schema:
  path: /var/schema.drdl

You may then start mongosqld with the --config option:

mongosqld --config /etc/mongosqld.conf

For more information on starting mongosqld as a system service, see the BI Connector installation reference.

Usage with Atlas

Atlas is a cloud service for running, monitoring, and maintaining MongoDB deployments, including the provisioning of dedicated servers for the MongoDB instances.

Atlas uses TLS/SSL to encrypt connections and enforces authentication by default.

Note

With the MongoDB Atlas free tier, BI Connector cannot run aggregation pipelines using the allowDiskUse option. This option allows aggregation stages to write data as temporary files to disk. To review the MongoDB Atlas free tier constraints, see the MongoDB Atlas manual.

To connect the MongoDB Connector for BI to an Atlas cluster, you must provide mongosqld with a .pem key file.

mongosqld can use any valid TLS certificate issued by a certificate authority, or a self-signed certificate. If you use a self-signed certificate, although the communications channel will be encrypted, there will be no validation of server identity. Although such a situation will prevent eavesdropping on the connection, it leaves you vulnerable to a man-in-the-middle attack. Using a certificate signed by a trusted certificate authority will permit your MySQL client to verify the server’s identity.

For testing purposes, you can create a .pem key file named test.pem using the openssl tool:

openssl req -nodes -newkey rsa:2048 -keyout test.key -out test.crt -x509 -days 365 -subj "/C=US/ST=test/L=test/O=test Security/OU=IT Department/CN=test.com"
cat test.crt test.key > test.pem

Start mongosqld with the --auth and --sslPEMKeyFile options.

If you named your key file test.pem as in the previous example, you can run the following, substituting your schema file and cluster URI:

Note

Do not specify a username and password in --mongo-uri. The connection string should only contain the list of servers.

mongosqld --mongo-ssl --auth --sslPEMKeyFile test.pem --schema <schema.drdl> --mongo-uri <uri>

Pass your username, password, and authentication database to your SQL client. For example, using mysql without verifying your mongosqld server certificate:

mysql --host <mongosqld-host> --port <mongosqld-port> -u <username>?source=admin -p --ssl-mode required --enable-cleartext-plugin

Refer to Connect from the MySQL Client for more details on using the mysql client with the BI Connector.

Sampling Mode Reference Chart

The chart below describes types of mongosqld startup configurations and the options used with each.

Mode mongosqld Options Behavior
Standalone Reader

Standalone mongod instance. mongosqld samples data upon startup. If --sampleRefreshIntervalSecs > 0, resample at specified interval.

Example

mongosqld --sampleMode read
Clustered Reader

In a replica set or sharded cluster environment, sample data only at startup. Read schema data from specified schema database on the primary.

Example

mongosqld --sampleMode read \
          --sampleSource schemaDb
Clustered Writer

Read and persist schema data in a user-specified schema database.

Example

mongosqld --sampleMode write \
          --sampleSource schemaDb \
          --sampleRefreshIntervalSecs 3600

Invalid Configurations

The following mongosqld option configurations are invalid and cause an error at startup.

Mode mongosqld Options Behavior
Standalone Writer (invalid) This configuration is invalid for a standalone mongod instance because in write mode there must be a writeable database specified.
Clustered Sampling Reader (invalid) When used in a MongoDB replica set or sharded cluster, this configuration is invalid. When a database is specified for storing schema data with --sampleSource, --sampleMode must be set to write so that the schema may be updated from the primary to maintain cluster-wide consistency.