快速部署有身份验证和授权的MongoDB Replica SET复制集

在启用身份验证后, MongoDB将强制所有客户端在访问数据库前先做身份验证authentication。 Authorization, 授权允许管理员限制用户使用的资源和允许做的操作。 对于产品环境中使用mongodb replica set 复制集,必须启用Authentication 和Authorization。

 

所有的MongoDB部署均支持身份验证。 默认情况下MongoDB不检测是否授权用户。对于现有部署和新部署都可以启用身份验证和授权。 但是要启用它们必须有停机时间,无法对在线的mongodb做修改。

以下我们将部署一个三节点的replica set复制集,其中的三个节点:

  1. rep0.dbdao.com    默认的primary
  2. rep1.dbdao.com    Secondary
  3. rep2.dbdao.com   Secondary

注意以下所有操作均在ubuntu 上以root用户执行,如果你要以其他用户执行,请注意权限。

ubuntu启用-root账号 可以参考: https://www.askmac.cn/archives/ubuntu%E5%90%AF%E7%94%A8-root%E8%B4%A6%E5%8F%B7.html

 

1、首先创建必要的目录

三个节点均运行

su – root                                                  ==》三个节点均运行

mkdir -p /m01/repdbdao                   ==》三个节点均运行

2、启动primary节点上的mongod

mongod –dbpath /m01/repdbdao –port 35001 –storageEngine wiredTiger

3、在无需身份验证的primary上创建用户

mongo rep0.dbdao.com:35001     ==》登陆 primary

 如下创建了siteUserAdmin和siteRootAdmin 2个超级用户

use admin
db.createUser( {
 user: "siteUserAdmin",
 pwd: "dbdao",
 roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
 });
db.createUser( {
 user: "siteRootAdmin",
 pwd: "dbdao",
 roles: [ { role: "root", db: "admin" } ]
 });

4、 ctrl+c 关掉步骤2启动的mongod

5、 在primary节点上生成一个为replica set 成员准备的SSL KEY文件

openssl rand -base64 741 > mongodb-keyfile
chmod 600 mongodb-keyfile

 

6、 将mongodb-keyfile 拷贝到其他2个节点上

 

scp mongodb-keyfile rep1.dbdao.com:~/
scp mongodb-keyfile rep2.dbdao.com:~/

 

7、 启动三个节点上的mongod

 rep0
  mongod --dbpath /m01/repdbdao --port 35001 --storageEngine wiredTiger  --replSet "rsdbao" --keyFile ~/mongodb-keyfile
  
 rep1 
  mongod --dbpath /m01/repdbdao --port 35001 --storageEngine wiredTiger  --replSet "rsdbao" --keyFile ~/mongodb-keyfile
  
 rep2 
  mongod --dbpath /m01/repdbdao --port 35001 --storageEngine wiredTiger  --replSet "rsdbao" --keyFile ~/mongodb-keyfile

8、连接到primary节点 开始Replica SET配置

mongo rep0:35001/admin -u siteRootAdmin -p

输入密码 dbdao

 

9、初始化replica set配置

执行

rs.initiate()

10、 将rep1和rep2节点加入replica set

rs.add(“rep1.dbdao.com:35001”)
rs.add(“rep2.dbdao.com:35001”)

 

11、查看replica set  状态

 

rsdbao:PRIMARY> rs.status()
{
        "set" : "rsdbao",
        "date" : ISODate("2015-05-14T03:07:00.883Z"),
        "myState" : 1,
        "members" : [
                {
                        "_id" : 0,
                        "name" : "rep0.dbdao.com:35001",
                        "health" : 1,
                        "state" : 1,
                        "stateStr" : "PRIMARY",
                        "uptime" : 345,
                        "optime" : Timestamp(1431572809, 1),
                        "optimeDate" : ISODate("2015-05-14T03:06:49Z"),
                        "electionTime" : Timestamp(1431572753, 2),
                        "electionDate" : ISODate("2015-05-14T03:05:53Z"),
                        "configVersion" : 3,
                        "self" : true
                },
                {
                        "_id" : 1,
                        "name" : "rep1.dbdao.com:35001",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 22,
                        "optime" : Timestamp(1431572809, 1),
                        "optimeDate" : ISODate("2015-05-14T03:06:49Z"),
                        "lastHeartbeat" : ISODate("2015-05-14T03:07:00.011Z"),
                        "lastHeartbeatRecv" : ISODate("2015-05-14T03:07:00.650Z"),
                        "pingMs" : 1,
                        "syncingTo" : "rep0.dbdao.com:35001",
                        "configVersion" : 3
                },
                {
                        "_id" : 2,
                        "name" : "rep2.dbdao.com:35001",
                        "health" : 1,
                        "state" : 2,
                        "stateStr" : "SECONDARY",
                        "uptime" : 10,
                        "optime" : Timestamp(1431572809, 1),
                        "optimeDate" : ISODate("2015-05-14T03:06:49Z"),
                        "lastHeartbeat" : ISODate("2015-05-14T03:07:00.078Z"),
                        "lastHeartbeatRecv" : ISODate("2015-05-14T03:07:00.228Z"),
                        "pingMs" : 23,
                        "configVersion" : 3
                }
        ],
        "ok" : 1
}




12、为了今后的管理可以为单个数据库创建管理者

 

use products
db.createUser(
  {
    user: "productsDBAdmin",
    pwd: "dbdao",
    roles:
    [
      {
        role: "dbOwner",
        db: "products"
      }
    ]
  }
)

 

以上基本完成了对有身份验证和授权的replica set的配置,可以使用如下命令登陆三个节点:

mongo rep1.dbdao.com:35001/admin -u siteRootAdmin -p dbdao
mongo rep0.dbdao.com:35001/admin -u siteRootAdmin -p dbdao
mongo rep2.dbdao.com:35001/admin -u siteRootAdmin -p dbdao

如上配置的replica set 会自动启用failover,但primary失败时会自动投票切换到secondary,无需人工接入。

 

 

 

MongoDB 用户角色授权与AUTH启用

MongoDB中几种常用用户角色:

dbAdmin 在db范围内包括下面的权限:

  • collStats
  • dbHash
  • dbStats
  • find
  • killCursors
  • listIndexes
  • listCollections
  • dropCollection 和 createCollection on system.profile only

userAdmin在db范围内包括如下权限:

  • changeCustomData
  • changePassword
  • createRole
  • createUser
  • dropRole
  • dropUser
  • grantRole
  • revokeRole
  • viewRole
  • viewUser

readAnyDatabase  对所有数据库中的collection可读,同时包含listDatabases权限

readWriteAnyDatabase  对所有数据库中的collection可读且可写,同时包含listDatabases权限

userAdminAnyDatabase 对所有数据库拥有userAdmin角色,同时包含listDatabases权限

dbAdminAnyDatabase 对所有数据库拥有dbAdmin角色,同时包含listDatabases权限

cluster相关的权限  clusterMonitor、hostManager、clusterManager、clusterAdmin

root权限, 包含 readWriteAnyDatabase, dbAdminAnyDatabase, userAdminAnyDatabase 和 clusterAdmin 等角色。 但不能访问system. 开头的collection(root does not include any access to collections that begin with the system. prefix.)

__system 超级角色

相关官方文档:
http://docs.mongodb.org/manual/reference/built-in-roles/#__system

__system包含下面这些权限:

 

> use admin
switched to db admin
> db.createUser(
...   {
...     user: "maclean_dbdao2",
...     pwd: "maclean_dbdao2",
...     roles: [ { role: "__system", db: "admin" } ]
...   }
... )
Successfully added user: {
	"user" : "maclean_dbdao2",
	"roles" : [
		{
			"role" : "__system",
			"db" : "admin"
		}
	]
}
> 
> 
> 
bye
10:~ maclean$ mongo localhost:35002/admin -u maclean_dbdao2  -p
MongoDB shell version: 3.0.2
Enter password: 
connecting to: localhost:35002/admin
> show roles
{
	"role" : "__system",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "backup",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "clusterAdmin",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "clusterManager",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "clusterMonitor",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "dbAdmin",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "dbAdminAnyDatabase",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "dbOwner",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "hostManager",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "read",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "readAnyDatabase",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "readWrite",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "readWriteAnyDatabase",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "restore",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "root",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "userAdmin",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}
{
	"role" : "userAdminAnyDatabase",
	"db" : "admin",
	"isBuiltin" : true,
	"roles" : [ ],
	"inheritedRoles" : [ ]
}


mongodb 3.0中db.getUsers() 获得db中的用户信息


> db.getUsers();
[
	{
		"_id" : "admin.maclean",
		"user" : "maclean",
		"db" : "admin",
		"roles" : [
			{
				"role" : "userAdminAnyDatabase",
				"db" : "admin"
			}
		]
	},
	{
		"_id" : "admin.maclean1",
		"user" : "maclean1",
		"db" : "admin",
		"roles" : [
			{
				"role" : "__system",
				"db" : "admin"
			}
		]
	},
	{
		"_id" : "admin.maclean_dbdao2",
		"user" : "maclean_dbdao2",
		"db" : "admin",
		"roles" : [
			{
				"role" : "__system",
				"db" : "admin"
			}
		]
	}
]

 

 

启用mongodb授权认证的方法:

1、以–auth 启动mongod

2、在配置文件mongod.conf 中加入 auth = true

第一次启用–auth时会出现:

2015-05-13T11:20:22.296+0800 I ACCESS   [conn1] note: no users configured in admin.system.users, allowing localhost access

2015-05-13T11:20:22.297+0800 I ACCESS   [conn1] Unauthorized not authorized on admin to execute command { getLog: “startupWarnings” }

2015-05-13T12:07:08.680+0800 I INDEX    [conn1] build index on: admin.system.users properties: { v: 1, unique: true, key: { user: 1, db: 1 }, name: “user_1_db_1”, ns: “admin.system.users” }

即之前未定义过用户,所以mongod将允许本地直接访问

mongo 登陆后 创建一个合适的超级用户

 

use admin
db.createUser(
  {
    user: "maclean",
    pwd: "maclean",
    roles: [ { role: "__system", db: "admin" } ]
  }
)

http://docs.mongodb.org/manual/reference/method/db.createUser/


给一个用户授权 :

use admin
db.grantRolesToUser(
  "macleanz",
  [
    { role: "readAnyDatabase", db:"admin" }
  ]
)


http://docs.mongodb.org/manual/tutorial/assign-role-to-user/

启用replica set 时需要做的授权:

use admin
db.createUser( {
    user: "siteUserAdmin",
    pwd: "",
    roles: [ { role: "userAdminAnyDatabase", db: "admin" } ]
  });
db.createUser( {
    user: "siteRootAdmin",
    pwd: "",
    roles: [ { role: "root", db: "admin" } ]
  });


http://docs.mongodb.org/manual/tutorial/deploy-replica-set-with-auth/

【MySQL学生手册】MySQL架构概览 – MySQL架构

章节中会介绍MySQL所使用的客户/服务器模型。你会了解:

 

  • 对MySQL C/S模式的描述
  • 理解通信协议(Communication Protocols)
  • 理解服务器如何支持存储引擎
  • 关于MySQL如何使用内存和磁盘空间的基础知识

 

2.1 MySQL架构概览

 

MySQL架构实际上是一组为了完成数据库服务器任务而协同工作的相关功能组合。这些功能集包含超过50,0000行代码。下图中对其中的功能子系统进行了分层展示,层级之间通过相应API进行交互。多数情况下,每个子系统会对信息进行接收,处理然后再传送给下一个子系统以完成所分配的任务。子系统之间相对独立,这样就会有更大的自由度(如由于执行语句的存储引擎独立性,客户端不必知道哪个存储引擎执行其请求)。
m31_ch2.1_mysql_subsystem_overview

 

2.1.1 核心共享子系统(Core Shared Subsystems)

在MySQL中的每个子系统都能自成一章。由于篇幅所限,这里仅对每个核心共享子系统进行简单描述,以给大家对其性能特点有一个概括性理解。

 

进程,线程和资源管理器(Process, Thread and Resource Management):

MySQL使用了一个基于线程的服务器架构,允许各种执行线程(或称为轻量级进程)访问核心共享资源。MySQL这种的多线程单进程架构能保证多个执行线程之间不会相互冲突或覆盖重要数据。使用基于线程的服务器架构最值得注意的优点是:

  • 节约成本 – 和进程相比,线程的建立和销毁成本更低。新的线程可使用其父进程的地址空间而不需要额外的地址空间。
  • 切换代价低 – 由于线程运行于相同的服务器进程空间中, 因此线程之间的切换代价很小。
  • 极小的开销 – 由于线程可对父进程地址空间进行访问,因此在共享资源下开销也变得极小。

 

缓存(Cache/Buffer)管理:

此子系统专注于缓冲并取回服务器进程中所有线程执行所用的大量数据类型。由于已经将返回的数据进行内存缓存,因此数据缓存使得MySQL可以降低大量对于基于磁盘I/O代价昂贵的操作。

 

网络管理(Networking Management):

此子系统的职责是通过处理在多平台间发送和接收带有MySQL连接请求和命令的网络数据包这样的工作,使得各种通信协议(TCP/IP, 命名管道Named Pipes等)对连接线程变得透明。它也包括了处理安全套接字层(Secured Socket Layers: SSL)这样的工作。

 

日志管理:

这个子系统是为了将各种日志事件子类被维护在一个日志类下而建立的。这样能使得开发者能在不破坏系统核心功能的情况下增加日志和日志事件。通过对日志系统中子系统的区分。各种系统活动(启动,多语句事务,自动增量auto-increment值改变等)就可以通过子类事件进行记录。

 

访问及授权管理:

此子系统定义了所有为执行命令所需的GRANT权限并主要用于保证客户端和服务器间的安全。它会验证用户在登陆过程中的访问权限及查询权限。此子系统也包含了一些对授权表的内存版本修改功能及密码生成功能。

 

2.1.2 存储引擎接口(Storage Engine Abstraction)

此子系统使得MySQL可以在系统架构中使用不同的表数据handlers(处理子程序)。尽管不是所有存储引擎都完整实现了相关handler API,但大部分独立的handler API都被用于转换数据、schema和索引格式,以使其符合MySQL内部记录格式所需(内存记录格式)。

 

2.1.3 查询解析,优化和执行

由于这些子系统负责接收SQL语句,将语句解构为各种数据结构并以最佳路径进行执行,因此它们被称为MySQL服务器的大脑。

  • 查询解析:

这是一个将SQL语句解析为一个抽象语法的过程。由于此过程非常复杂,因此解析时不能对任何用户变量进行改变。

  • 优化:

此子系统负责找到查询的最优执行计划。

  • 执行:

此子系统又被称为语句执行单元,负责按照通过SQL命令解析和优化后所得的最优执行路径进行执行。执行进程的基本功能是都有一个指针作为其第一参数以将结构数据包发回给客户端。

 

2.1.4 查询缓存(Query Cache)

不像其它MySQL子系统,此“子系统”组件由一系列类组成。它不仅负责缓存被执行的SQL命令,还存储命令执行后的结果。

快速部署MongoDB Replica Set复制集

本文介绍如何快速部署MongoDB  Replica Set复制集,注意本文中的配置是不涉及授权验证的。

MongoDB版本为3.0.2 , 该部署全部在同一台服务器上完成,实际产品环境 Replica Set 中master和slave显然应当在不同的服务器上, 仅仅需要对本文中涉及IP的地方有所修改即可

我们假定用户已经成功安装mongoDB 3.0.2

 

启动 replica set的primary节点上的mongodb

sudo mkdir -p /m01/mongo1
sudo mkdir -p /m01/mongo2
sudo mkdir -p /m01/mongo3



mongod -dbpath /m01/mongo1 --replSet "rs_maclean" --port 35001 --storageEngine wiredTiger   &
mongod -dbpath /m01/mongo2 --replSet "rs_maclean" --port 35002 --storageEngine wiredTiger   &
mongod -dbpath /m01/mongo3 --replSet "rs_maclean" --port 35003 --storageEngine wiredTiger   &


登陆到已经启动的primary mongodb

mongo localhost:35001
MongoDB shell version: 3.0.2
connecting to: localhost:35001/test


初始化 replica set  成员

rs.initiate()

获得 replica set配置信息

rs.conf()

rs_maclean:PRIMARY> rs.conf();
{
	"_id" : "rs_maclean",
	"version" : 1,
	"members" : [
		{
			"_id" : 0,
			"host" : "shard0.dbdao.com:35001",
			"arbiterOnly" : false,
			"buildIndexes" : true,
			"hidden" : false,
			"priority" : 1,
			"tags" : {
				
			},
			"slaveDelay" : 0,
			"votes" : 1
		}
	],
	"settings" : {
		"chainingAllowed" : true,
		"heartbeatTimeoutSecs" : 10,
		"getLastErrorModes" : {
			
		},
		"getLastErrorDefaults" : {
			"w" : 1,
			"wtimeout" : 0
		}
	}
}



添加其他2个replica set member 

rs.add("ip2:35002")
rs.add("ip3:35003")


检查 Replica set 状态

rs_maclean:PRIMARY> rs.status()
{
	"set" : "rs_maclean",
	"date" : ISODate("2015-05-12T14:34:57.205Z"),
	"myState" : 1,
	"members" : [
		{
			"_id" : 0,
			"name" : "shard0.dbdao.com:35001",
			"health" : 1,
			"state" : 1,
			"stateStr" : "PRIMARY",
			"uptime" : 398,
			"optime" : Timestamp(1431441212, 1),
			"optimeDate" : ISODate("2015-05-12T14:33:32Z"),
			"electionTime" : Timestamp(1431440950, 2),
			"electionDate" : ISODate("2015-05-12T14:29:10Z"),
			"configVersion" : 2,
			"self" : true
		},
		{
			"_id" : 1,
			"name" : "shard1.dbdao.com:35002",
			"health" : 1,
			"state" : 2,
			"stateStr" : "SECONDARY",
			"uptime" : 85,
			"optime" : Timestamp(1431441212, 1),
			"optimeDate" : ISODate("2015-05-12T14:33:32Z"),
			"lastHeartbeat" : ISODate("2015-05-12T14:34:56.044Z"),
			"lastHeartbeatRecv" : ISODate("2015-05-12T14:34:56.061Z"),
			"pingMs" : 0,
			"configVersion" : 2
		}
.......
	],
	"ok" : 1
}


此时我们触发 primary的mongod 进程被kill

root@shard0:~# ps -ef|grep mongod
root     24591 24566  1 10:28 pts/1    00:00:12 mongod -dbpath /m01/mongo1 --replSet rs_maclean --port 35001 --storageEngine wiredTiger

root     24970 24566  0 10:41 pts/1    00:00:00 grep --color=auto mongod
root@shard0:~# 
root@shard0:~# kill -9 24591




此时触发 replSet possible election tie


2015-05-12T10:59:59.476-0400 E REPL     [rsBackgroundSync] sync producer problem: 10278 dbclient error communicating with server: shard0.dbdao.com:35001
2015-05-12T10:59:59.478-0400 I REPL     [ReplicationExecutor] could not find member to sync from
2015-05-12T11:00:00.382-0400 I NETWORK  [ReplExecNetThread-1] DBClientCursor::init call() failed
2015-05-12T11:00:00.382-0400 I REPL     [ReplicationExecutor] Standing for election
2015-05-12T11:00:00.384-0400 I REPL     [ReplicationExecutor] replSet possible election tie; sleeping 961ms until 2015-05-12T11:00:01.345-0400
2015-05-12T11:00:01.345-0400 I REPL     [ReplicationExecutor] Standing for election
2015-05-12T11:00:01.346-0400 I REPL     [ReplicationExecutor] replSet info electSelf
2015-05-12T11:00:01.348-0400 I REPL     [ReplicationExecutor] replSet election succeeded, assuming primary role
2015-05-12T11:00:01.348-0400 I REPL     [ReplicationExecutor] transition to PRIMARY
2015-05-12T11:00:01.605-0400 I REPL     [rsSync] transition to primary complete; database writes are now permitted
2015-05-12T11:00:04.853-0400 I NETWORK  [conn15] end connection 127.0.0.1:34149 (3 connections now open)
2015-05-12T11:00:05.735-0400 I NETWORK  [initandlisten] connection accepted from 127.0.0.1:34160 #18 (4 connections now open)
2015-05-12T11:00:08.011-0400 I NETWORK  [conn18] end connection 127.0.0.1:34160 (3 connections now open)
2015-05-12T11:00:21.642-0400 I NETWORK  [conn17] end connection 104.236.21.67:44231 (2 connections now open)
2015-05-12T11:00:21.643-0400 I NETWORK  [initandlisten] connection accepted from 104.236.21.67:44264 #19 (3 connections now open)
2015-05-12T11:00:22.548-0400 I NETWORK  [initandlisten] connection accepted from 127.0.0.1:34189 #20 (4 connections now open)
2015-05-12T11:00:23.932-0400 I NETWORK  [conn20] end connection 127.0.0.1:34189 (3 connections now open)
2015-05-12T11:00:51.671-0400 I NETWORK  [conn19] end connection 104.236.21.67:44264 (2 connections now open)
2015-05-12T11:00:51.671-0400 I NETWORK  [initandlisten] connection accepted from 104.236.21.67:44310 #21 (3 connections now open)
2015-05-12T11:01:21.700-0400 I NETWORK  [conn21] end connection 104.236.21.67:44310 (2 connections now open)
2015-05-12T11:01:21.701-0400 I NETWORK  [initandlisten] connection accepted from 104.236.21.67:44356 #22 (3 connections now open)


secondary 接管primary 



当原primary节点重新启动会成为SECONDARY member

MongoDB BSONObj size is invalid错误一例

某个MongoDB数据库出现如下错误”assertion 10334 BSONObj size: 1684627202 (0x64695F02) is invalid. Size must be between 0 and 16793600(16MB) First element: EOO ns:local.oplog.$main query:{ query: {}, orderby: { $natural: -1 } }”一例:

 

 

2015-05-12T16:12:43.872+0800 I -        ***** SERVER RESTARTED *****
2015-05-12T16:12:43.883+0800 I -        [initandlisten] MongoDB starting : pid=1697 port=27019 dbpath=arb/mongodb master=1 64-bit host=ubuntu
2015-05-12T16:12:43.883+0800 I -        [initandlisten] 
2015-05-12T16:12:43.884+0800 I -        [initandlisten] ** NOTE: This is a development version (2.7.8) of MongoDB.
2015-05-12T16:12:43.884+0800 I -        [initandlisten] **       Not recommended for production.
2015-05-12T16:12:43.884+0800 I -        [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2015-05-12T16:12:43.884+0800 I -        [initandlisten] 
2015-05-12T16:12:43.884+0800 I -        [initandlisten] db version v2.7.8
2015-05-12T16:12:43.884+0800 I -        [initandlisten] git version: 0e9cb3b20498b9f167afaff7a5c4a4d1da7e06a2
2015-05-12T16:12:43.884+0800 I -        [initandlisten] build info: Linux build4.nj1.10gen.cc 2.6.32-431.3.1.el6.x86_64 #1 SMP Fri Jan 3 21:39:27 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-05-12T16:12:43.884+0800 I -        [initandlisten] allocator: tcmalloc
2015-05-12T16:12:43.884+0800 I -        [initandlisten] options: { config: "/etc/mongodb.conf", net: { bindIp: "0.0.0.0" }, processManagement: { fork: true, pidFilePath: "arb/mongodb/mongodb.pid" }, sharding: { clusterRole: "configsvr" }, storage: { dbPath: "arb/mongodb", preallocDataFiles: false }, systemLog: { destination: "file", logAppend: true, path: "ar/log/mongodb.log" } }
2015-05-12T16:12:43.887+0800 I JOURNAL  [initandlisten] journal dir=arb/mongodb/journal
2015-05-12T16:12:43.888+0800 I JOURNAL  [initandlisten] recover : no journal files present, no recovery needed
2015-05-12T16:12:43.914+0800 I -        [initandlisten] Assertion: 10334:BSONObj size: 1684627202 (0x64695F02) is invalid. Size must be between 0 and 16793600(16MB) First element: EOO
 mongod(main+0x134) [0x7bbf34]
 libc.so.6(__libc_start_main+0xF5) [0x7f13b661aec5]
 mongod(+0x3EEC01) [0x7eec01]
-----  END BACKTRACE  -----
2015-05-12T15:55:47.951+0800 I COMMANDS [initandlisten] assertion 10334 BSONObj size: 1684627202 (0x64695F02) is invalid. Size must be between 0 and 16793600(16MB) First element: EOO ns:local.oplog.$main query:{ query: {}, orderby: { $natural: -1 } }
2015-05-12T15:55:47.951+0800 I COMMANDS [initandlisten]  ntoskip:0 ntoreturn:1
2015-05-12T15:55:47.952+0800 I STORAGE  [initandlisten] exception in initAndListen: 13106 nextSafe(): { $err: "BSONObj size: 1684627202 (0x64695F02) is invalid. Size must be between 0 and 16793600(16MB) First element: EOO", code: 10334 }, terminating
2015-05-12T15:55:47.952+0800 I COMMANDS [initandlisten] now exiting
2015-05-12T15:55:47.952+0800 I NETWORK  [initandlisten] shutdown: going to close listening sockets...
2015-05-12T15:55:47.952+0800 I NETWORK  [initandlisten] removing socket file: /tmp/mongodb-27019.sock
2015-05-12T15:55:47.952+0800 I NETWORK  [initandlisten] shutdown: going to flush diaglog...
2015-05-12T15:55:47.952+0800 I NETWORK  [initandlisten] shutdown: going to close sockets...
2015-05-12T15:55:47.952+0800 I STORAGE  [initandlisten] shutdown: waiting for fs preallocator...
2015-05-12T15:55:47.952+0800 I STORAGE  [initandlisten] shutdown: final commit...
2015-05-12T15:55:47.953+0800 I STORAGE  [initandlisten] shutdown: closing all files...
2015-05-12T15:55:47.954+0800 I STORAGE  [initandlisten] closeAllFiles() finished
2015-05-12T15:55:47.954+0800 I JOURNAL  [initandlisten] journalCleanup...
2015-05-12T15:55:47.954+0800 I JOURNAL  [initandlisten] removeJournalFiles
2015-05-12T15:55:47.958+0800 I STORAGE  [initandlisten] shutdown: removing fs lock...
2015-05-12T15:55:47.958+0800 I COMMANDS [initandlisten] dbexit:  rc: 100
.


针对该 BSONObj size invalid,主要是mongodb读取到数据文件后发现存在BSON对象的大小超过16MB的非法对象所以报错,由于其认为数据库可能存在损坏,所以会强制关闭mongd实例

 

针对此类案例一般考虑:

1、repair database ,修复数据库

2、如果确实无法修复数据库,则考虑从备份中恢复数据库

 

一、repair database的例子:

 

mkdir  /m01/mongo-data02


mongodb数据文件位于 /m01/mongo-data01  ,使用repairpath指定修复后的文件的位置。

注意/m01/mongo-data01 下需要有journal 


root@shard0:~# 
root@shard0:~# mongod -dbpath /m01/mongo-data01 --repair --repairpath /m01/mongo-data02  --storageEngine wiredTiger|less
2015-05-12T04:39:41.234-0400 I CONTROL  [initandlisten] MongoDB starting : pid=19575 port=27017 dbpath=/m01/mongo-data01 64-bit host=shard0.dbdao.com
2015-05-12T04:39:41.234-0400 I CONTROL  [initandlisten] ** WARNING: You are running this process as the root user, which is not recommended.
2015-05-12T04:39:41.234-0400 I CONTROL  [initandlisten] 
2015-05-12T04:39:41.234-0400 I CONTROL  [initandlisten] 
2015-05-12T04:39:41.234-0400 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/enabled is 'always'.
2015-05-12T04:39:41.234-0400 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-05-12T04:39:41.234-0400 I CONTROL  [initandlisten] 
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] ** WARNING: /sys/kernel/mm/transparent_hugepage/defrag is 'always'.
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] **        We suggest setting it to 'never'
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] 
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] db version v3.0.0
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] git version: a841fd6394365954886924a35076691b4d149168
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] OpenSSL version: OpenSSL 1.0.1f 6 Jan 2014
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] build info: Linux ip-10-179-177-12 3.13.0-24-generic #46-Ubuntu SMP Thu Apr 10 19:11:08 UTC 2014 x86_64 BOOST_LIB_VERSION=1_49
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] allocator: tcmalloc
2015-05-12T04:39:41.235-0400 I CONTROL  [initandlisten] options: { repair: true, storage: { dbPath: "/m01/mongo-data01", engine: "wiredTiger", repairPath: "/m01/mongo-data02" } }
2015-05-12T04:39:41.262-0400 W -        [initandlisten] Detected unclean shutdown - /m01/mongo-data01/mongod.lock is not empty.
2015-05-12T04:39:41.263-0400 W STORAGE  [initandlisten] Recovering data from the last clean checkpoint.
2015-05-12T04:39:41.263-0400 I STORAGE  [initandlisten] wiredtiger_open config: create,cache_size=1G,session_max=20000,eviction=(threads_max=4),statistics=(fast),checkpoint=(wait=60,log_size=2GB),statistics_log=(wait=0),
2015-05-12T04:39:41.317-0400 I STORAGE  [initandlisten] Repairing size cache
2015-05-12T04:39:41.320-0400 I STORAGE  [initandlisten] WiredTiger progress session.verify 2
2015-05-12T04:39:41.320-0400 I STORAGE  [initandlisten] Verify succeeded on uri table:sizeStorer. Not salvaging.
2015-05-12T04:39:41.321-0400 I STORAGE  [initandlisten] Repairing catalog metadata
2015-05-12T04:39:41.323-0400 I STORAGE  [initandlisten] WiredTiger progress session.verify 2
2015-05-12T04:39:41.323-0400 I STORAGE  [initandlisten] Verify succeeded on uri table:_mdb_catalog. Not salvaging.
2015-05-12T04:39:41.329-0400 I STORAGE  [initandlisten] repairDatabase local
2015-05-12T04:39:41.329-0400 I STORAGE  [initandlisten] Repairing collection local.startup_log
2015-05-12T04:39:41.332-0400 I STORAGE  [initandlisten] WiredTiger progress session.verify 2
2015-05-12T04:39:41.332-0400 I STORAGE  [initandlisten] Verify succeeded on uri table:collection-0--8766758909797526255. Not salvaging.
2015-05-12T04:39:41.338-0400 I INDEX    [initandlisten] build index on: local.startup_log properties: { v: 1, key: { _id: 1 }, name: "_id_", ns: "local.startup_log" }
2015-05-12T04:39:41.338-0400 I INDEX    [initandlisten]          building index using bulk method
2015-05-12T04:39:41.343-0400 I STORAGE  [initandlisten] repairDatabase test
2015-05-12T04:39:41.343-0400 I STORAGE  [initandlisten] Repairing collection test. otherStuff
2015-05-12T04:39:41.349-0400 I STORAGE  [initandlisten] WiredTiger progress session.verify 2
2015-05-12T04:39:41.350-0400 I STORAGE  [initandlisten] Verify succeeded on uri table:collection-4--6404712422294369090. Not salvaging.
2015-05-12T04:39:41.355-0400 I INDEX    [initandlisten] build index on: test. otherStuff properties: { v: 1, key: { _id: 1 }, name: "_id_", ns: "test. otherStuff" }
2015-05-12T04:39:41.355-0400 I INDEX    [initandlisten]          building index using bulk method
2015-05-12T04:39:41.360-0400 I STORAGE  [initandlisten] Repairing collection test.alpha

 

关于repair 更多信息可以参考 http://docs.mongodb.org/manual/reference/command/repairDatabase/

 

二、通过原有备份的mongodump来恢复数据

MongoDB Aggregation聚集测试

db.alpha.insert({_id:1, region:"NW1", leads:1 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:2, region:"NW1", leads:1 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:3, region:"NW1", leads:2 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:4, region:"SE1", leads:8 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:5, region:"SE2", leads:4 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:6, region:"SE2", leads:2 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:7, region:"SE2", leads:4 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:8, region:"SW1", leads:1 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:9, region:"SW1", leads:2 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:10, region:"SW2", leads:2 , email: "maclean@dbdao.com"});
db.alpha.insert({_id:11, region:"SW2", leads:5, email: "maclean@dbdao.com"});


> db.alpha.find();
{ "_id" : 1, "region" : "NW1", "leads" : 1, "email" : "maclean@dbdao.com" }
{ "_id" : 2, "region" : "NW1", "leads" : 1, "email" : "maclean@dbdao.com" }
{ "_id" : 3, "region" : "NW1", "leads" : 2, "email" : "maclean@dbdao.com" }
{ "_id" : 4, "region" : "SE1", "leads" : 8, "email" : "maclean@dbdao.com" }
{ "_id" : 5, "region" : "SE2", "leads" : 4, "email" : "maclean@dbdao.com" }
{ "_id" : 6, "region" : "SE2", "leads" : 2, "email" : "maclean@dbdao.com" }
{ "_id" : 7, "region" : "SE2", "leads" : 4, "email" : "maclean@dbdao.com" }
{ "_id" : 8, "region" : "SW1", "leads" : 1, "email" : "maclean@dbdao.com" }
{ "_id" : 9, "region" : "SW1", "leads" : 2, "email" : "maclean@dbdao.com" }
{ "_id" : 10, "region" : "SW2", "leads" : 2, "email" : "maclean@dbdao.com" }
{ "_id" : 11, "region" : "SW2", "leads" : 5, "email" : "maclean@dbdao.com" }


> 
> db.alpha.aggregate ( [ { "$group" : { "_id" : "$region" , leads : { "$sum" :1 }}} , { "$match" : { "leads" : { "$gte" :3 }}} ])
{ "_id" : "SE2", "leads" : 3 }
{ "_id" : "NW1", "leads" : 3 }
> 

 

db.alpha.aggregate ( [ { "$group" : { "_id" : "$region" , leads : { "$sum" :1 }}} , { "$match" : { "leads" : { "$gte" :3 }}} ])
{ "_id" : "SE2", "leads" : 3 }
{ "_id" : "NW1", "leads" : 3 }

其相当于SQL的

select region,count(*) from alpha group by region having count(*)>=3;

db.alpha.aggregate ( [ { "$group" : { "_id" : "$region" , "leads" : { "$sum" : "$leads" }}} , { "$match" : { "leads" : { "$gte" :2 }}} ]);

其相当于SQL的
select region,sum(leads) from alpha group by region having sum(leads)>=3;

db.collection.aggregate 的语法如下:

db.collection.aggregate()

 

New in version 2.2.

 

Definition

 

db.collection.aggregate(pipeline, options)
Calculates aggregate values for the data in a collection.

Type
Description
array

A sequence of data aggregation operations or stages. See theaggregation pipeline operators for details.

 

Changed in version 2.6: The method can still accept the pipeline stages as separate arguments instead of as elements in an array; however, if you do not specify the pipeline as an array, you cannot specify theoptions parameter.

document

Optional. Additional options that aggregate() passes to theaggregate command.

 

New in version 2.6: Available only if you specify the pipeline as an array.
Parameter
pipeline
options

 

db.stuff.insert ( { _id:1 , "x":1, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:2 , "x":2, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:3 , "x":3, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:4 , "x":4, "y" : [1,2,3,4]});
db.stuff.insert ( { _id:5 , "x":5, "y" : [1,2,3,4]});



> db.stuff.aggregate([ { $unwind : "$y" }]);
{ "_id" : 1, "x" : 1, "y" : 1 }
{ "_id" : 1, "x" : 1, "y" : 2 }
{ "_id" : 1, "x" : 1, "y" : 3 }
{ "_id" : 1, "x" : 1, "y" : 4 }
{ "_id" : 2, "x" : 2, "y" : 1 }
{ "_id" : 2, "x" : 2, "y" : 2 }
{ "_id" : 2, "x" : 2, "y" : 3 }
{ "_id" : 2, "x" : 2, "y" : 4 }
{ "_id" : 3, "x" : 3, "y" : 1 }
{ "_id" : 3, "x" : 3, "y" : 2 }
{ "_id" : 3, "x" : 3, "y" : 3 }
{ "_id" : 3, "x" : 3, "y" : 4 }
{ "_id" : 4, "x" : 4, "y" : 1 }
{ "_id" : 4, "x" : 4, "y" : 2 }
{ "_id" : 4, "x" : 4, "y" : 3 }
{ "_id" : 4, "x" : 4, "y" : 4 }
{ "_id" : 5, "x" : 5, "y" : 1 }
{ "_id" : 5, "x" : 5, "y" : 2 }
{ "_id" : 5, "x" : 5, "y" : 3 }
{ "_id" : 5, "x" : 5, "y" : 4 }


> db.stuff.aggregate([ { $unwind : "$y" }  , { $project : { x:1, y:1, _id:0}}] );
{ "x" : 1, "y" : 1 }
{ "x" : 1, "y" : 2 }
{ "x" : 1, "y" : 3 }
{ "x" : 1, "y" : 4 }
{ "x" : 2, "y" : 1 }
{ "x" : 2, "y" : 2 }
{ "x" : 2, "y" : 3 }
{ "x" : 2, "y" : 4 }
{ "x" : 3, "y" : 1 }
{ "x" : 3, "y" : 2 }
{ "x" : 3, "y" : 3 }
{ "x" : 3, "y" : 4 }
{ "x" : 4, "y" : 1 }
{ "x" : 4, "y" : 2 }
{ "x" : 4, "y" : 3 }
{ "x" : 4, "y" : 4 }
{ "x" : 5, "y" : 1 }
{ "x" : 5, "y" : 2 }
{ "x" : 5, "y" : 3 }
{ "x" : 5, "y" : 4 }
> 

> db.stuff.aggregate([ { $unwind : "$y" }  , { $project : { x:1, y:1, _id:0}}, {$out:"otherStuff"}]);
> 
> db.otherStuff.find();
{ "_id" : ObjectId("5551b7566f1f9528e8ba7209"), "x" : 1, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720a"), "x" : 1, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720b"), "x" : 1, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720c"), "x" : 1, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720d"), "x" : 2, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720e"), "x" : 2, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba720f"), "x" : 2, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7210"), "x" : 2, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7211"), "x" : 3, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7212"), "x" : 3, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7213"), "x" : 3, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7214"), "x" : 3, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7215"), "x" : 4, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7216"), "x" : 4, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7217"), "x" : 4, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7218"), "x" : 4, "y" : 4 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba7219"), "x" : 5, "y" : 1 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba721a"), "x" : 5, "y" : 2 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba721b"), "x" : 5, "y" : 3 }
{ "_id" : ObjectId("5551b7566f1f9528e8ba721c"), "x" : 5, "y" : 4 }

MongoDB索引与排序

对于下面的例子而言,mongoDB会如何处理这个查询?

 

ongos> db.users.insert({user: { login:"maclean", desc:"maclean liu" , date : new Date("2015-01-01") }});

mongos> 
mongos> db.users.findOne();
{
        "_id" : ObjectId("555179f2315a40007db97d3c"),
        "user" : {
                "login" : "maclean",
                "desc" : "maclean liu",
                "date" : ISODate("2015-01-01T00:00:00Z")
        }
}


mongos> db.users.ensureIndex( { "user.login" :1 , "user.date" :-1} , "myIndex" );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 1,
                        "numIndexesAfter" : 2,
                        "ok" : 1
                }
        },
        "ok" : 1
}


mongos> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.date":1}).explain();
{
        "queryPlanner" : {
                "mongosPlannerVersion" : 1,
                "winningPlan" : {
                        "stage" : "SINGLE_SHARD",
                        "shards" : [
                                {
                                        "shardName" : "shard0000",
                                        "connectionString" : "shard0.dbdao.com:35001",
                                        "serverInfo" : {
                                                "host" : "shard0.dbdao.com",
                                                "port" : 35001,
                                                "version" : "3.0.0",
                                                "gitVersion" : "a841fd6394365954886924a35076691b4d149168"
                                        },
                                        "plannerVersion" : 1,
                                        "namespace" : "test_db.users",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "user.login" : /^mac.*/
                                        },
                                        "winningPlan" : {
                                                "stage" : "PROJECTION",
                                                "transformBy" : {
                                                        "user" : 1,
                                                        "_id" : 0
                                                },
                                                "inputStage" : {
                                                        "stage" : "SORT",
                                                        "sortPattern" : {
                                                                "user.date" : 1
                                                        },
                                                        "inputStage" : {
                                                                "stage" : "FETCH",
                                                                "inputStage" : {
                                                                        "stage" : "IXSCAN",
                                                                        "filter" : {
                                                                                "user.login" : /^mac.*/
                                                                        },
                                                                        "keyPattern" : {
                                                                                "user.login" : 1,
                                                                                "user.date" : -1
                                                                        },
                                                                        "indexName" : "myIndex",
                                                                        "isMultiKey" : false,
                                                                        "direction" : "forward",
                                                                        "indexBounds" : {
                                                                                "user.login" : [
                                                                                        "[\"mac\", \"mad\")",
                                                                                        "[/^mac.*/, /^mac.*/]"
                                                                                ],
                                                                                "user.date" : [
                                                                                        "[MaxKey, MinKey]"
                                                                                ]
                                                                        }
                                                                }
                                                        }
                                                }
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        ]
                }
        },
        "ok" : 1
}














mongos> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.login":1}).explain();
{
        "queryPlanner" : {
                "mongosPlannerVersion" : 1,
                "winningPlan" : {
                        "stage" : "SINGLE_SHARD",
                        "shards" : [
                                {
                                        "shardName" : "shard0000",
                                        "connectionString" : "shard0.dbdao.com:35001",
                                        "serverInfo" : {
                                                "host" : "shard0.dbdao.com",
                                                "port" : 35001,
                                                "version" : "3.0.0",
                                                "gitVersion" : "a841fd6394365954886924a35076691b4d149168"
                                        },
                                        "plannerVersion" : 1,
                                        "namespace" : "test_db.users",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "user.login" : /^mac.*/
                                        },
                                        "winningPlan" : {
                                                "stage" : "PROJECTION",
                                                "transformBy" : {
                                                        "user" : 1,
                                                        "_id" : 0
                                                },
                                                "inputStage" : {
                                                        "stage" : "FETCH",
                                                        "inputStage" : {
                                                                "stage" : "IXSCAN",
                                                                "filter" : {
                                                                        "user.login" : /^mac.*/
                                                                },
                                                                "keyPattern" : {
                                                                        "user.login" : 1,
                                                                        "user.date" : -1
                                                                },
                                                                "indexName" : "myIndex",
                                                                "isMultiKey" : false,
                                                                "direction" : "forward",
                                                                "indexBounds" : {
                                                                        "user.login" : [
                                                                                "[\"mac\", \"mad\")",
                                                                                "[/^mac.*/, /^mac.*/]"
                                                                        ],
                                                                        "user.date" : [
                                                                                "[MaxKey, MinKey]"
                                                                        ]
                                                                }
                                                        }
                                                }
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        ]
                }
        },
        "ok" : 1
}
mongos> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.date":-1}).explain();
{
        "queryPlanner" : {
                "mongosPlannerVersion" : 1,
                "winningPlan" : {
                        "stage" : "SINGLE_SHARD",
                        "shards" : [
                                {
                                        "shardName" : "shard0000",
                                        "connectionString" : "shard0.dbdao.com:35001",
                                        "serverInfo" : {
                                                "host" : "shard0.dbdao.com",
                                                "port" : 35001,
                                                "version" : "3.0.0",
                                                "gitVersion" : "a841fd6394365954886924a35076691b4d149168"
                                        },
                                        "plannerVersion" : 1,
                                        "namespace" : "test_db.users",
                                        "indexFilterSet" : false,
                                        "parsedQuery" : {
                                                "user.login" : /^mac.*/
                                        },
                                        "winningPlan" : {
                                                "stage" : "PROJECTION",
                                                "transformBy" : {
                                                        "user" : 1,
                                                        "_id" : 0
                                                },
                                                "inputStage" : {
                                                        "stage" : "SORT",
                                                        "sortPattern" : {
                                                                "user.date" : -1
                                                        },
                                                        "inputStage" : {
                                                                "stage" : "FETCH",
                                                                "inputStage" : {
                                                                        "stage" : "IXSCAN",
                                                                        "filter" : {
                                                                                "user.login" : /^mac.*/
                                                                        },
                                                                        "keyPattern" : {
                                                                                "user.login" : 1,
                                                                                "user.date" : -1
                                                                        },
                                                                        "indexName" : "myIndex",
                                                                        "isMultiKey" : false,
                                                                        "direction" : "forward",
                                                                        "indexBounds" : {
                                                                                "user.login" : [
                                                                                        "[\"mac\", \"mad\")",
                                                                                        "[/^mac.*/, /^mac.*/]"
                                                                                ],
                                                                                "user.date" : [
                                                                                        "[MaxKey, MinKey]"
                                                                                ]
                                                                        }
                                                                }
                                                        }
                                                }
                                        },
                                        "rejectedPlans" : [ ]
                                }
                        ]
                }
        },
        "ok" : 1
}


> db.users.find ( { "user.login": /^mac.*/ }  , {"user":1,"_id":0}).sort({"user.login":-1}).explain();
{
	"queryPlanner" : {
		"plannerVersion" : 1,
		"namespace" : "test.users",
		"indexFilterSet" : false,
		"parsedQuery" : {
			"user.login" : /^mac.*/
		},
		"winningPlan" : {
			"stage" : "PROJECTION",
			"transformBy" : {
				"user" : 1,
				"_id" : 0
			},
			"inputStage" : {
				"stage" : "FETCH",
				"inputStage" : {
					"stage" : "IXSCAN",
					"filter" : {
						"user.login" : /^mac.*/
					},
					"keyPattern" : {
						"user.login" : 1,
						"user.date" : -1
					},
					"indexName" : "myIndex",
					"isMultiKey" : false,
					"direction" : "backward",
					"indexBounds" : {
						"user.login" : [
							"[/^mac.*/, /^mac.*/]",
							"(\"mad\", \"mac\"]"
						],
						"user.date" : [
							"[MinKey, MaxKey]"
						]
					}
				}
			}
		},
		"rejectedPlans" : [ ]
	},
	"serverInfo" : {
		"host" : "ac.local",
		"port" : 27017,
		"version" : "3.0.2",
		"gitVersion" : "nogitversion"
	},
	"ok" : 1
}




从上述演示中可以看到:

对于索引 myIndex db.users.ensureIndex( { “user.login” :1 , “user.date” :-1} , “myIndex” );

db.users.find ( { “user.login”: /^mac.*/ } , {“user”:1,”_id”:0}).sort({“user.date”:1}).explain(); ==> 使用了索引myIndex ,但 出现 “stage” : “SORT”, 说明使用索引未避免排序,

 

db.users.find ( { “user.login”: /^mac.*/ }  , {“user”:1,”_id”:0}).sort({“user.login”:1}).explain(); ==》使用了索引myIndex,且未出现 “stage” : “SORT” ; 说明对于index prefix的排序可以利用到索引来避免排序

db.users.find ( { “user.login”: /^mac.*/ }  , {“user”:1,”_id”:0}).sort({“user.login”:-1}).explain();使用了索引myIndex,且未出现 “stage” : “SORT” ; 说明对于index prefix的排序可以利用到索引来避免排序

db.users.find ( { “user.login”: /^mac.*/ }  , {“user”:1,”_id”:0}).sort({“user.date”:-1}).explain();  使用了索引myIndex ,但 出现 “stage” : “SORT”, 说明使用索引未避免排序,

 

以上说明了:

  • 对于索引prefix的字段而言,不管是索引是正序还是倒序,排序是正序需求还是倒序需求,都可以使用到Index索引来避免排序
  • 对于非索引prefix的字段,无法利用其来避免排序,IXSCAN完还需要SORT。

 

MongoDB sharding collection 与 unique index

 

MongoDB中对于已经分片的collection ,仅有索引对应的field是shard key is a prefix的情况才可以建unique index唯一索引,否则不能建为唯一索引。
例如:

mongos>  sh.status();
--- Sharding Status --- 
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("554b241f4df23a46a60f6a9c")
}
  shards:
        {  "_id" : "shard0000",  "host" : "shard0.dbdao.com:35001" }
        {  "_id" : "shard0001",  "host" : "shard1.dbdao.com:35001" }
        {  "_id" : "shard0002",  "host" : "shard2.dbdao.com:35001" }
  balancer:
        Currently enabled:  yes
        Currently running:  no
        Failed balancer rounds in last 5 attempts:  0
        Migration Results for the last 24 hours: 
                No recent migrations
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "test_db",  "partitioned" : true,  "primary" : "shard0000" }
                test_db.test_collection
                        shard key: { "_id" : "hashed" }
                        chunks:
                                shard0000       2
                                shard0001       2
                                shard0002       2
                        { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong("-6148914691236517204") } on : shard0000 Timestamp(3, 2) 
                        { "_id" : NumberLong("-6148914691236517204") } -->> { "_id" : NumberLong("-3074457345618258602") } on : shard0000 Timestamp(3, 3) 
                        { "_id" : NumberLong("-3074457345618258602") } -->> { "_id" : NumberLong(0) } on : shard0001 Timestamp(3, 4) 
                        { "_id" : NumberLong(0) } -->> { "_id" : NumberLong("3074457345618258602") } on : shard0001 Timestamp(3, 5) 
                        { "_id" : NumberLong("3074457345618258602") } -->> { "_id" : NumberLong("6148914691236517204") } on : shard0002 Timestamp(3, 6) 
                        { "_id" : NumberLong("6148914691236517204") } -->> { "_id" : { "$maxKey" : 1 } } on : shard0002 Timestamp(3, 7) 
        {  "_id" : "test",  "partitioned" : false,  "primary" : "shard0000" }

mongos> 
mongos> 
mongos> db.test_collection.find();
{ "_id" : ObjectId("554b296c160953211da4b523"), "x" : 2 }
{ "_id" : ObjectId("554b296c160953211da4b522"), "x" : 1 }
{ "_id" : ObjectId("554b296c160953211da4b524"), "x" : 3 }
{ "_id" : ObjectId("554b296c160953211da4b526"), "x" : 5 }
{ "_id" : ObjectId("554b296c160953211da4b529"), "x" : 8 }
{ "_id" : ObjectId("554b296c160953211da4b525"), "x" : 4 }
{ "_id" : ObjectId("554b296c160953211da4b52c"), "x" : 11 }
{ "_id" : ObjectId("554b296c160953211da4b52b"), "x" : 10 }
{ "_id" : ObjectId("554b296c160953211da4b527"), "x" : 6 }
{ "_id" : ObjectId("554b296c160953211da4b52d"), "x" : 12 }
{ "_id" : ObjectId("554b296c160953211da4b52f"), "x" : 14 }
{ "_id" : ObjectId("554b296c160953211da4b528"), "x" : 7 }
{ "_id" : ObjectId("554b296c160953211da4b52e"), "x" : 13 }
{ "_id" : ObjectId("554b296c160953211da4b530"), "x" : 15 }
{ "_id" : ObjectId("554b296c160953211da4b52a"), "x" : 9 }
{ "_id" : ObjectId("554b296c160953211da4b531"), "x" : 16 }
{ "_id" : ObjectId("554b296c160953211da4b532"), "x" : 17 }
{ "_id" : ObjectId("554b296c160953211da4b533"), "x" : 18 }
{ "_id" : ObjectId("554b296c160953211da4b53b"), "x" : 26 }
{ "_id" : ObjectId("554b296c160953211da4b534"), "x" : 19 }
Type "it" for more

mongos> db.test_index.ensureIndex( { x : 1 } , {unique: true} );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : true,
                        "numIndexesBefore" : 1,
                        "numIndexesAfter" : 2,
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> db.test_index.ensureIndex( { y : 1 } , {unique: true} );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 3,
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> sh.shardCollection("test_db.test_index", { x : 1 } );
{
        "ok" : 0,
        "errmsg" : "can't shard collection 'test_db.test_index' with unique index on { y: 1.0 } and proposed shard key { x: 1.0 }. Uniqueness can't be maintained unless shard key is a prefix"
}
mongos> db.test_index.drop();
true

 

 

如果分片key是index filed的一部分则可以建立唯一索引

true
mongos> db.test_index.ensureIndex( { x : 1 , y:1 } , {unique: true} );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : true,
                        "numIndexesBefore" : 1,
                        "numIndexesAfter" : 2,
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> sh.shardCollection("test_db.test_index", { x : 1 } );
{ "collectionsharded" : "test_db.test_index", "ok" : 1 }

 

建非unique 索引总是可以:

 

mongos> db.test_index.ensureIndex( { z : 1 }  );
{
        "raw" : {
                "shard0.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 3,
                        "ok" : 1
                },
                "shard1.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 2,
                        "note" : "all indexes already exist",
                        "ok" : 1
                },
                "shard2.dbdao.com:35001" : {
                        "createdCollectionAutomatically" : false,
                        "numIndexesBefore" : 2,
                        "numIndexesAfter" : 2,
                        "note" : "all indexes already exist",
                        "ok" : 1
                }
        },
        "ok" : 1
}
mongos> db.test_index.getIndexes();
[
 {
 "v" : 1,
 "key" : {
 "_id" : 1
 },
 "name" : "_id_",
 "ns" : "test_db.test_index"
 },
 {
 "v" : 1,
 "unique" : true,
 "key" : {
 "x" : 1,
 "y" : 1
 },
 "name" : "x_1_y_1",
 "ns" : "test_db.test_index"
 },
 {
 "v" : 1,
 "key" : {
 "z" : 1
 },
 "name" : "z_1",
 "ns" : "test_db.test_index"
 }
]


mongos> db.test_index.drop();
true
mongos> 
mongos> db.test_index.ensureIndex( { x : 1 } , {unique: true} );
{
 "raw" : {
 "shard0.dbdao.com:35001" : {
 "createdCollectionAutomatically" : true,
 "numIndexesBefore" : 1,
 "numIndexesAfter" : 2,
 "ok" : 1
 }
 },
 "ok" : 1
}
mongos> sh.shardCollection("test_db.test_index", { x : 1 } );
{ "collectionsharded" : "test_db.test_index", "ok" : 1 }
mongos> for (var i = 1; i <= 500; i++) db.test_collection.insert( { x : i , y:i+1} );
WriteResult({ "nInserted" : 1 })
mongos> db.test_index.ensureIndex( { x : 1 , y:1 } , {unique: true} );
{
 "raw" : {
 "shard0.dbdao.com:35001" : {
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 2,
 "numIndexesAfter" : 3,
 "ok" : 1
 },
 "shard1.dbdao.com:35001" : {
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 2,
 "numIndexesAfter" : 3,
 "ok" : 1
 },
 "shard2.dbdao.com:35001" : {
 "createdCollectionAutomatically" : false,
 "numIndexesBefore" : 2,
 "numIndexesAfter" : 3,
 "ok" : 1
 }
 },
 "ok" : 1
}

总结:

MongoDB中对于已经分片的collection ,仅有索引对应的field是shard key is a prefix的情况才可以建unique index唯一索引,否则不能建为唯一索引。

对于shard collection建立non-unique index总是可以的

 

 

” A attempt to create a unique index in previously sharded collection will result in an error message”

这句话说得是不严谨的, 如果创建的unique index 以shard key作为prefix的话那么是可以被创建了,仅仅当index prefix和shard key没关系时是会报错的

 

E11000 duplicate key & mongorestore

mongorestore 可以将mongodump工具导出的数据导入到其他mongodb数据库中。 由于mongoDB中有_id字段总是作为主键存在,所以对于一般不会覆盖完全相同(包括_id)也相同的数据,因为如果导入的数据有重复的_id,那么会触发E11000 duplicate key错误。

 

例如

 

 

ac:~ maclean$ mongo
MongoDB shell version: 3.0.2
connecting to: test
> db.abc
db.abc
> db.duptest.insert({_id:10,x:10});
WriteResult({ "nInserted" : 1 })
> 
> 

ac:~ maclean$ mongodump -h localhost -p 27017  -d test -c duptest  --out duptest 
2015-05-11T22:22:36.743+0800	writing test.duptest to duptest/test/duptest.bson
2015-05-11T22:22:36.744+0800	writing test.duptest metadata to duptest/test/duptest.metadata.json
2015-05-11T22:22:36.745+0800	done dumping test.duptest

MongoDB shell version: 3.0.2
connecting to: test
> db.duptest.update({_id:10},{x:20});
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.duptest.find();
{ "_id" : 10, "x" : 20 }
> 


ac:~ maclean$ mongorestore -h localhost -p 27017 duptest/
2015-05-11T22:23:32.355+0800	building a list of dbs and collections to restore from duptest/ dir
2015-05-11T22:23:32.356+0800	reading metadata file from duptest/test/duptest.metadata.json
2015-05-11T22:23:32.356+0800	restoring test.duptest from file duptest/test/duptest.bson
2015-05-11T22:23:32.357+0800	error: E11000 duplicate key error index: test.duptest.$_id_ dup key: { : 10.0 }
2015-05-11T22:23:32.357+0800	restoring indexes for collection test.duptest from metadata
2015-05-11T22:23:32.358+0800	finished restoring test.duptest
2015-05-11T22:23:32.358+0800	done
ac:~ maclean$ mongo
MongoDB shell version: 3.0.2
connecting to: test
> db.duptest.find();
{ "_id" : 10, "x" : 20 }

面向未来的数据库,你准备好了么?

Oracles数据库正在面向云端。你准备好了么?
现在请关于相关认证路径以获取最新认证。

通过Oracle数据库管理员Associate认证(OCA)以建立确立你的知识基础,而通过成为Oracle专家认证(OCP)则能证明你具有更多相关高级技能。 

通过使用Oracle数据库12c多租户架构和云计算,管理变得比以往更加自动化和简单化。不要让自己变得落伍了,始终保持你的技能最新化。现在有两条升级路线可选;Oracle数据库11g或12c管理员专家认证。每个路线的需求被列示如下。快来让OCP logo展现在你职业道路的下一站中吧:

升级至11g DBA OCP

从Oracle 数据库9i或10g管理员Associate认证(9i 或 10g DBA OCA)升级:

  1. 完成“View Upgrade Path – Oracle Database 11g Administrator OCP – upgrade from Oracle9i or Oracle Database 10g DBA OCA”中第二步列出的课程中的一门
    step2_course
  2. 通过认证考试”Upgrade Oracle9i/10g OCA or OCP to Oracle Database 11g OCP” (1Z0-034).

升级至12c DBA OCP

从Oracle9i Database, Oracle Database 10g或11g管理员Associate认证 (9i, 10g或11g DBA OCA)升级至Oracle Database 12c管理员专家认证(12c DBA OCP)

  1. 完成”View Upgrade Path – Oracle Database 12c Administrator OCP – upgrade from Oracle9i, Oracle Database 10g or 11g DBA OCA.”在第二步中列出的课程
  2. 通过认证考试”Upgrade Oracle9i/10g/11g OCA or OCP to Oracle Database 12c OCP” (1Z0-067).

你需要访问pearsonvue.com/oracle来注册并预约考试1Z0-034 或1Z0-067. 你可以通过Oracle认证网站(快速链接中)获得所有准备细节,包括考查内容,考试目标,考题数量,时间限制及考试价格等信息。

沪ICP备14014813号-2

沪公网安备 31010802001379号