mongodb-cheatsheet.md

connect

mongo --hostname <hostname> \
	--port <port> \
	-u <username> \
	-p <password> \
	--athenticationDatabase <database>

disconnect

exit
quit()

clear screen

<ctrl + l>

show version

version()

show status

hostInfo()
stats()
serverStatus()
getLastError()

shutdown server

shutdownServer()

Show All Databases

show dbs

Show Current Database

db

Create Or Switch Database

use test

Drop

db.dropDatabase()

rename database

db.copyDatabase('old_name', 'new_name'); # Copy the database to a new database
use <old_name> # Switch to the database you wish to delete
db.dropDatabase(); # Destroys the selected database

Handy command

db.serverStatus().mem
db.serverStatus().storageEngine
db.serverStatus().wiredTiger

use admin
db.createUser({"user": "root", "pwd": passwordPrompt(), "roles": ["root"]})
db.dropUser("root")
db.auth( "user", passwordPrompt() )

use test
db.getSiblingDB("dbname")
db.currentOp()
db.killOp(123) // opid

db.fsyncLock()
db.fsyncUnlock()

db.getCollectionNames()
db.getCollectionInfos()
db.printCollectionStats()
db.stats()

db.getReplicationInfo()
db.printReplicationInfo()
db.isMaster()
db.hostInfo()
db.printShardingStatus()
db.shutdownServer()
db.serverStatus()

db.setSlaveOk()
db.getSlaveOk()

db.getProfilingLevel()
db.getProfilingStatus()
db.setProfilingLevel(1, 200) // 0 == OFF, 1 == ON with slowms, 2 == ON

db.enableFreeMonitoring()
db.disableFreeMonitoring()
db.getFreeMonitoringStatus()

db.createView("viewName", "sourceColl", [{$project:{department: 1}}])

Mongo replicaset

# mongo --host <ipadress>
> rs.initiate()
> rs.add("mongo_1_2")
> rs.add("mongo_1_3")
> rs.addArb("mongo_1_A")
> rs.status()

create a registered user to protect the collection agains public access

The following command must be executed on the master of the cluster. Not a slave

# mongo --host <ipadress>
> use admin 
> db.createUser(
{  user: "adminUser",
   pwd: "adminPassword",
   roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
})

Create a keyfile for the inter-cluster authentication

# openssl rand -base64 741 > /opt/mongo/mongodb.key
# chmod 600 /opt/mongo/mongodb.key
# chown mongod:mongod /opt/mongo/mongodb.key

Then copy this keyfile on all the cluster members.

Edit /etc/mongod.conf and activate security by adding the flowing lines

#security:
security:
  authorization: enabled
  keyFile: /opt/mongo/mongodb.key

Then restart mongod :

# systemctl restart mongod

Log in mongo and add the clusterAdmin right to the user

# mongo --host <ipadress>
> use admin
> db.auth("adminUser", "adminPassword" )
> db.grantRolesToUser("adminUser",["clusterAdmin"])

You can verify your user setting with the following command

> db.system.users.find()

Now we can create a collection in the cluster and add a user able to access it

> use myNewCollection
> db.createUser({user:"newUser", pwd:"newUserPassword", 
    roles:[{role:"readWrite", db:"myNewCollection"}] })

You can quit and try to login on the different cluster node

> use myNewCollection
> db.auth("newUser", "newUserPassword" )

The last step is to connect to your application. As an exemple with SpringBoot, if you want to setup a mongoDB cluster you can set the propertie file like this:

spring.data.mongodb.uri=mongodb://newUser:NewUserPassword@<ipadress1>:27017,<ipadress2>:27017,<ipadress3>:27017/newCollection

The hosts executing the springboot application need to have /etc/hosts file knowing the mongodb cluster members:

<ipadress1> mongo_1_1 
<ipadress2> mongo_1_2 
<ipadress3> mongo_1_3

Mongo replicaset 2

rs.status()
rs.initiate({"_id": "replicaTest",
  members: [
    { _id: 0, host: "127.0.0.1:27017" },
    { _id: 1, host: "127.0.0.1:27018" },
    { _id: 2, host: "127.0.0.1:27019", arbiterOnly:true }]
})
rs.add("mongodbd1.example.net:27017")
rs.addArb("mongodbd2.example.net:27017")
rs.remove("mongodbd1.example.net:27017")
rs.conf()
rs.isMaster()
rs.printReplicationInfo()
rs.printSlaveReplicationInfo()
rs.reconfig(<valid_conf>)
rs.slaveOk()
rs.stepDown(20, 5) // (stepDownSecs, secondaryCatchUpPeriodSecs)

Mongo Sharding

sh.status()
sh.addShard("rs1/mongodbd1.example.net:27017")
sh.shardCollection("mydb.coll", {zipcode: 1})

sh.moveChunk("mydb.coll", { zipcode: "53187" }, "shard0019")
sh.splitAt("mydb.coll", {x: 70})
sh.splitFind("mydb.coll", {x: 70})
sh.disableAutoSplit()
sh.enableAutoSplit()

sh.startBalancer()
sh.stopBalancer()
sh.disableBalancing("mydb.coll")
sh.enableBalancing("mydb.coll")
sh.getBalancerState()
sh.setBalancerState(true/false)
sh.isBalancerRunning()

sh.addTagRange("mydb.coll", {state: "TN", zip: MinKey }, { state: "TN", zip: MaxKey }, "TN")
sh.removeTagRange("mydb.coll", {state: "TN", zip: MinKey }, { state: "TN", zip: MaxKey }, "TN")
sh.addShardTag("shard0000", "TN")
sh.removeShardTag("shard0000", "TN")

sh.addShardToZone("shard0000", "JO")
sh.removeShardFromZone("shard0000", "JO")
sh.removeRangeFromZone("mydb.coll", {a: 1, b: 1}, {a: 10, b: 10})

Oplog operations

use local

show collections

You should see an oplog.rs collection.

db.oplog.rs.find().pretty()

special capped collection that keeps a rolling record of all operations that modify the data stored in the databases
idempotent

default oplog size (for Unix and Windows systems):
Storage Engine 	Default Oplog Size 	Lower Bound 	Upper Bound
In-memory 	5% of physical memory 	50MB 	        50GB
WiredTiger 	5% of free disk space 	990MB 	        50GB
MMAPv1 	        5% of free disk space 	990MB 	        50GB

mkdir oplogBackup
cd oplogBackup
mongodump -d=local -c=oplog.rs

| field | optional | description |
| ----- | ------ | ------ |
| ts    | N      | BSON Timestamp. Often converted to 64bit INT for JSON |
| h     | Y      | Random hash |
| v     | N      | oplog protocol version. default 1. |
| op    | N      | Type of op. one of `i`, `u`, `d`, `n`, `c` |
| ns    | N      | BSON Namespacestring. Serialised as  db.collection |
| o     | N      | Operation applied. object |
| o2    | Y      | Object 2. Additional information, usually operand |
| fromMigrate | Y | Boolean. Indicates if this operation is part of chunk migration between shards |

source: https://github.com/mongodb/mongo/blob/master/src/mongo/db/repl/oplog_entry.idl 

Users

list users

show users

create user

> create admin user

db.createUser({
  user: "admin",
  pwd: "<long secure password>",
  roles:[ "userAdminAnyDatabase", "dbAdminAnyDatabase", "readWriteAnyDatabase"] })

Successfully added user: {
	"user" : "admin",
	"roles" : [
		"userAdminAnyDatabase",
		"dbAdminAnyDatabase",
		"readWriteAnyDatabase"
	]
}

db.createUser({user:'userName', pwd:'passwordValue', roles:[
        {role:'readWrite', db:'DatabaseA'},
        {role:'readWrite', db:'DatabaseB'}
    ]});
> create normal user
db.AddUser({user: "<username>", pwd: "<password>", roles: ["readWrite"]});

delete user

use mydb

db.dropUser('<username>')

Collections

show collections

show collections
db.getCollectionNames()

// get the collection statistics
db.<collectionName>.stats()
db.printCollectionStats()

// latency statistics for read, write operations including average time taken for reads, writes
// and related umber of operations performed
db.<collectionName>.latencyStats()

// get collection size for data and indexes
db.<collectionName>.dataSize() // size of the collection
db.<collectionName>.storageSize() // total size of document stored in the collection
db.<collectionName>.totalSize() // total size in bytes for both collection data and indexes
db.<collectionName>.totalIndexSize() // total size of all indexes in the collection

clone database to remote server

db.copyDatabase(<from_db>, <to_db>, <from_hostname>, <username>, <password>);

Create Collection

db.createCollection('posts')

Show Collections

show collections

Insert Row

db.posts.insert({
  title: 'Post One',
  body: 'Body of post one',
  category: 'News',
  tags: ['news', 'events'],
  user: {
    name: 'John Doe',
    status: 'author'
  },
  date: Date()
})

Insert Multiple Rows

db.posts.insertMany([
  {
    title: 'Post Two',
    body: 'Body of post two',
    category: 'Technology',
    date: Date()
  },
  {
    title: 'Post Three',
    body: 'Body of post three',
    category: 'News',
    date: Date()
  },
  {
    title: 'Post Four',
    body: 'Body of post three',
    category: 'Entertainment',
    date: Date()
  }
])

Get All Rows

db.posts.find()

Get All Rows Formatted

db.find().pretty()

Find Rows

db.posts.find({ category: 'News' })

Sort Rows

# asc
db.posts.find().sort({ title: 1 }).pretty()
# desc
db.posts.find().sort({ title: -1 }).pretty()

Count Rows

db.posts.find().count()
db.posts.find({ category: 'news' }).count()

Limit Rows

db.posts.find().limit(2).pretty()

Chaining

db.posts.find().limit(2).sort({ title: 1 }).pretty()

Foreach

db.posts.find().forEach(function(doc) {
  print("Blog Post: " + doc.title)
})

Find One Row

db.posts.findOne({ category: 'News' })

Find Specific Fields

db.posts.find({ title: 'Post One' }, {
  title: 1,
  author: 1
})

Update Row

db.posts.update({ title: 'Post Two' },
{
  title: 'Post Two',
  body: 'New body for post 2',
  date: Date()
},
{
  upsert: true
})

Update Specific Field

db.posts.update({ title: 'Post Two' },
{
  $set: {
    body: 'Body for post 2',
    category: 'Technology'
  }
})

Increment Field ($inc)

db.posts.update({ title: 'Post Two' },
{
  $inc: {
    likes: 5
  }
})

Rename Field

db.posts.update({ title: 'Post Two' },
{
  $rename: {
    likes: 'views'
  }
})

Delete Row

db.posts.remove({ title: 'Post Four' })

Sub-Documents

db.posts.update({ title: 'Post One' },
{
  $set: {
    comments: [
      {
        body: 'Comment One',
        user: 'Mary Williams',
        date: Date()
      },
      {
        body: 'Comment Two',
        user: 'Harry White',
        date: Date()
      }
    ]
  }
})

Find By Element in Array ($elemMatch)

db.posts.find({
  comments: {
     $elemMatch: {
       user: 'Mary Williams'
       }
    }
  }
)

Add Index

db.posts.createIndex({ title: 'text' })

Text Search

db.posts.find({
  $text: {
    $search: "\"Post O\""
    }
})

Greater & Less Than

db.posts.find({ views: { $gt: 2 } })
db.posts.find({ views: { $gte: 7 } })
db.posts.find({ views: { $lt: 7 } })
db.posts.find({ views: { $lte: 7 } })

Start mongod (server) on custom db path

mongod --dbpath {path}

Start mongod with a config file

mongod --config /usr/local/etc/mongod.conf

Show collections in current db

show collections

backup database

# backup all databases
$ mongodump -v -u username -p strongpassword --port 27018

# backup single database
$ mongodump -v -u username -p strongpassword --port 27018 --db=app

# mongo shell: if you want to restore an existing db and keep the old version around
> db.copyDatabase('app', 'app_20200428')

# restore all databases from backup
$ mongorestore -v -u username -p strongpassword --host="rs1/node1:27021,node2:27021,node3:27021" --dir ./restore/

# restore db app from backup with new name app_dev
$ mongorestore -v -u username -p strongpassword --db app_dev --dir dump/app 

# restore a collection from backup (e.g. 'sessions' collection)
$ mongorestore -v -u username -p strongpassword --host="rs1/node1:27021,node2:27021,node3:27021" --drop --db=app --collection=sessions ./restore/app/sessions.bson

# mongo shell: check if the old and restored dbs match
> use app
> db.stats()
mongodump --db <database> \
	-c <collection>

Mongo Dump & Mongo Restore

//Mongo Dump
(REMOTE)
mongodump --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb>

(LOCAL)
mongodump

(REMOTE specific DB and Collection)
mongodump --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb> --db <dumpDatabase> --collection <dumpCollection> --out <directoryName>

(REMOTE to Archive)
mongodump --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb> --db <dumpDatabase> --collection <dumpCollection> --archive=<fileName> --gzip

//Mongo Restore 

(REMOTE)
mongorestore --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb>

(LOCAL)
mongorestore

(REMOTE from specific directory)
mongorestore --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb> --dir <directoryName>

(REMOTE from Archive)
mongorestore --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb> --archive=<fileName> --gzip

Mongo export & Mongo import

//Mongo export
(REMOTE)
mongoexport --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb> --db <database> --collection <collection> --out <filename>

(LOCAL)
mongoexport  --db <database> --collection <collection> --out <filename>

(LOCAL jsonArray)
mongoexport  --db <database> --collection <collection> --out <filename> --jsonArray

(LOCAL CSV)
mongoexport  --db <database> --collection <collection> --out <filename> --type=csv 

(LOCAL with Query)
mongoexport  --db <database> --collection <collection> --out <filename>  --query '{<query>}' 

//Mongo import

(REMOTE)
mongoimport --host <hostname> --username <username> --password <password> --authenticationDatabase <authDb> --db <database> --collection <collection> --file <filename>

(LOCAL)
mongoimport  --db <database> --collection <collection> --file <filename>

(LOCAL jsonArray)
mongoimport  --db <database> --collection <collection> --file <filename> --jsonArray

(LOCAL CSV)
mongoimport  --db <database> --collection <collection> --file <filename> --type=csv 

https://docs.mongodb.com/