搜索到
53
篇与
Data
的结果
-
linuxea:mongodb4.4.8分片集群搭建 引用geekdemo课程4核8G的分片集群配置域名解析和分片目录创建分片复复制集并初始化创建config复制集并初始化初始化分片集群加入第一个分片创建分片表加入第二个分片架构如下分布情况安装mkdir /data/db && cd /data/ wget https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-rhel70-4.4.8.tgz tar xf mongodb-linux-x86_64-rhel70-4.4.8.tgz ln -sn mongodb-linux-x86_64-rhel70-4.4.8 mongodb export PATH=$PATH:/data/mongodb/bin echo "export PATH=$PATH:/data/mongodb/bin" > /etc/profile.d/mongodb.sh source /etc/profile.d/mongodb.sh[root@Node-172_16_100_91 /data]# mongod -version db version v4.4.8 Build Info: { "version": "4.4.8", "gitVersion": "83b8bb8b6b325d8d8d3dfd2ad9f744bdad7d6ca0", "openSSLVersion": "OpenSSL 1.0.1e-fips 11 Feb 2013", "modules": [], "allocator": "tcmalloc", "environment": { "distmod": "rhel70", "distarch": "x86_64", "target_arch": "x86_64" } }配置域名解析配置hosts模拟域名记录172.16.100.91 instance1 mongo1.linuxea.com mongo2.linuxea.com 172.16.100.92 instance2 mongo3.linuxea.com mongo4.linuxea.com 172.16.100.93 instance3 mongo5.linuxea.com mongo6.linuxea.com添加之前，我们在每个节点修改hostname，分布是mogodb_instance1,mogodb_instance2,mogodb_instance3for i in static pretty transient; do hostnamectl set-hostname mogodb_instance1 --$i; done写到/etc/profile中而后添加hostsecho "172.16.100.91 mogodb_instance1 mongo1.linuxea.com mongo2.linuxea.com" >> /etc/hosts echo "172.16.100.92 mogodb_instance2 mongo3.linuxea.com mongo4.linuxea.com" >> /etc/hosts echo "172.16.100.93 mogodb_instance3 mongo5.linuxea.com mongo6.linuxea.com" >> /etc/hosts创建目录1,3,5执行mkdir -p /data/shard1/ /data/config/2,4,5执行mkdir -p /data/shard2/ /data/mongos/开始搭建在三台节点都创建用户并修改文件属性groupadd -r -g 490 mongodb useradd -u 490 -s /sbin/nologin -c 'mongodb server' -g mongodb mongodb -M初始化参数echo "never" > /sys/kernel/mm/transparent_hugepage/defrag echo "never" > /sys/kernel/mm/transparent_hugepage/enabled chown -R mongodb.mongodb /data/shard*复制集在member1/member3/member5上执行以下命令。注意以下参数：shardsvr: 表示这不是一个普通的复制集，而是分片集的一部分；wiredTigerCacheSizeGB: 该参数表示MongoDB能够使用的缓存大小。默认值为(RAM - 1GB) / 2不建议配置超过默认值，有OOM的风险；因为我们当前测试会在一台服务器上运行多个实例，因此配置了较小的值；bind_ip: 生产环境中强烈建议不要绑定外网IP，此处为了方便演示绑定了所有IP地址。类似的道理，生产环境中应开启认证--auth，此处为演示方便并未使用；在三台节点都运行如下命令sudo -u mongodb /data/mongodb/bin/mongod --bind_ip 0.0.0.0 --replSet shard1 --dbpath /data/shard1 --logpath /data/shard1/mongod.log --port 27010 --fork --shardsvr --wiredTigerCacheSizeGB 2防火墙放行firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="172.16.100.0/24" port port="27010" protocol="tcp" accept' firewall-cmd --reload firewall-cmd --list-all运行完成后，就有三台节点已经启动，而后组合这三台节点为复制集用这三个实例搭建shard1复制集：任意连接到一个实例，例如我们连接到member1.example.com： mongo --host mongo1.linuxea.com:27010初始化shard1复制集。我们使用如下配置初始化复制集： rs.initiate({ _id: "shard1", "members" : [ { "_id": 0, "host" : "mongo1.linuxea.com:27010" }, { "_id": 1, "host" : "mongo3.linuxea.com:27010" }, { "_id": 2, "host" : "mongo5.linuxea.com:27010" } ] });执行> rs.initiate({ ... _id: "shard1", ... "members" : [ ... { ... "_id": 0, ... "host" : "mongo1.linuxea.com:27010" ... }, ... { ... "_id": 1, ... "host" : "mongo3.linuxea.com:27010" ... }, ... { ... "_id": 2, ... "host" : "mongo5.linuxea.com:27010" ... } ... ] ... }); { "ok" : 1 } shard1:SECONDARY> 只需要稍等，shard1:SECONDARY> 状态将会切换为shard1:SECONDARY> 使用 rs.status()查看状态shard1:PRIMARY> rs.status() { "set" : "shard1", "date" : ISODate("2021-08-07T14:54:37.331Z"), "myState" : 1, "term" : NumberLong(1), "syncSourceHost" : "", "syncSourceId" : -1, "heartbeatIntervalMillis" : NumberLong(2000), "majorityVoteCount" : 2, "writeMajorityCount" : 2, "votingMembersCount" : 3, "writableVotingMembersCount" : 3, "optimes" : { "lastCommittedOpTime" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "lastCommittedWallTime" : ISODate("2021-08-07T14:54:29.354Z"), "readConcernMajorityOpTime" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "readConcernMajorityWallTime" : ISODate("2021-08-07T14:54:29.354Z"), "appliedOpTime" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "durableOpTime" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "lastAppliedWallTime" : ISODate("2021-08-07T14:54:29.354Z"), "lastDurableWallTime" : ISODate("2021-08-07T14:54:29.354Z") }, "lastStableRecoveryTimestamp" : Timestamp(1628348049, 2), "electionCandidateMetrics" : { "lastElectionReason" : "electionTimeout", "lastElectionDate" : ISODate("2021-08-07T14:54:08.916Z"), "electionTerm" : NumberLong(1), "lastCommittedOpTimeAtElection" : { "ts" : Timestamp(0, 0), "t" : NumberLong(-1) }, "lastSeenOpTimeAtElection" : { "ts" : Timestamp(1628348037, 1), "t" : NumberLong(-1) }, "numVotesNeeded" : 2, "priorityAtElection" : 1, "electionTimeoutMillis" : NumberLong(10000), "numCatchUpOps" : NumberLong(0), "newTermStartDate" : ISODate("2021-08-07T14:54:09.234Z"), "wMajorityWriteAvailabilityDate" : ISODate("2021-08-07T14:54:10.227Z") }, "members" : [ { "_id" : 0, "name" : "mongo1.linuxea.com:27010", "health" : 1, "state" : 1, "stateStr" : "PRIMARY", "uptime" : 204, "optime" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "optimeDate" : ISODate("2021-08-07T14:54:29Z"), "syncSourceHost" : "", "syncSourceId" : -1, "infoMessage" : "", "electionTime" : Timestamp(1628348048, 1), "electionDate" : ISODate("2021-08-07T14:54:08Z"), "configVersion" : 1, "configTerm" : -1, "self" : true, "lastHeartbeatMessage" : "" }, { "_id" : 1, "name" : "mongo3.linuxea.com:27010", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 39, "optime" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "optimeDurable" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "optimeDate" : ISODate("2021-08-07T14:54:29Z"), "optimeDurableDate" : ISODate("2021-08-07T14:54:29Z"), "lastHeartbeat" : ISODate("2021-08-07T14:54:36.949Z"), "lastHeartbeatRecv" : ISODate("2021-08-07T14:54:36.137Z"), "pingMs" : NumberLong(0), "lastHeartbeatMessage" : "", "syncSourceHost" : "mongo1.linuxea.com:27010", "syncSourceId" : 0, "infoMessage" : "", "configVersion" : 1, "configTerm" : -1 }, { "_id" : 2, "name" : "mongo5.linuxea.com:27010", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 39, "optime" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "optimeDurable" : { "ts" : Timestamp(1628348069, 1), "t" : NumberLong(1) }, "optimeDate" : ISODate("2021-08-07T14:54:29Z"), "optimeDurableDate" : ISODate("2021-08-07T14:54:29Z"), "lastHeartbeat" : ISODate("2021-08-07T14:54:36.949Z"), "lastHeartbeatRecv" : ISODate("2021-08-07T14:54:36.048Z"), "pingMs" : NumberLong(0), "lastHeartbeatMessage" : "", "syncSourceHost" : "mongo1.linuxea.com:27010", "syncSourceId" : 0, "infoMessage" : "", "configVersion" : 1, "configTerm" : -1 } ], "ok" : 1, "$clusterTime" : { "clusterTime" : Timestamp(1628348069, 1), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } }, "operationTime" : Timestamp(1628348069, 1) } shard1:PRIMARY> config 复制集configsvr是标注这是config服务器的与shard1类似的方式，我们可以搭建config服务器。在member1/member3/member5上执行以下命令：运行config实例：授权目录chown -R mongodb.mongodb /data/config/启动sudo -u mongodb /data/mongodb/bin/mongod --bind_ip 0.0.0.0 --replSet config --dbpath /data/config --logpath /data/config/mongod.log --port 27019 --fork --configsvr --wiredTigerCacheSizeGB 1防火墙放行firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="172.16.100.0/24" port port="27019" protocol="tcp" accept' firewall-cmd --reload firewall-cmd --list-all连接到member1：mongo --host mongo1.linuxea.com:27019初始化config复制集：rs.initiate({ _id: "config", "members" : [ { "_id": 0, "host" : "mongo1.linuxea.com:27019" }, { "_id": 1, "host" : "mongo3.linuxea.com:27019" }, { "_id": 2, "host" : "mongo5.linuxea.com:27019" } ] });执行> rs.initiate({ ... _id: "config", ... "members" : [ ... { ... "_id": 0, ... "host" : "mongo1.linuxea.com:27019" ... }, ... { ... "_id": 1, ... "host" : "mongo3.linuxea.com:27019" ... }, ... { ... "_id": 2, ... "host" : "mongo5.linuxea.com:27019" ... } ... ] ... }); { "ok" : 1, "$gleStats" : { "lastOpTime" : Timestamp(1628348518, 1), "electionId" : ObjectId("000000000000000000000000") }, "lastCommittedOpTime" : Timestamp(0, 0) }稍等等状态从config:SECONDARY> 变为config:PRIMARY> config:SECONDARY> config:SECONDARY> config:SECONDARY> config:PRIMARY> config:PRIMARY> config:PRIMARY> mongos配置mongos的搭建比较简单，我们在member2/member4/member6上搭建3个mongos。注意以下参数：configdb: 表示config使用的集群地址；开始搭建：目录授权chown -R mongodb.mongodb /data/mongos/运行mongos进程：sudo -u mongodb /data/mongodb/bin/mongos --bind_ip 0.0.0.0 --logpath /data/mongos/mongos.log --port 27017 --configdb config/mongo1.linuxea.com:27019,mongo3.linuxea.com:27019,mongo5.linuxea.com:27019 --fork放行防火墙firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="172.16.100.0/24" port port="27017" protocol="tcp" accept' firewall-cmd --reload firewall-cmd --list-all连接到任意一个mongos，此处我们使用member1：mongo --host mongo1.linuxea.com:27017将shard1加入到集群中：sh.addShard("shard1/mongo1.linuxea.com:27010,mongo3.linuxea.com:27010,mongo5.linuxea.com:27010");执行mongos> sh.addShard("shard1/mongo1.linuxea.com:27010,mongo3.linuxea.com:27010,mongo5.linuxea.com:27010"); { "shardAdded" : "shard1", "ok" : 1, "operationTime" : Timestamp(1628349200, 3), "$clusterTime" : { "clusterTime" : Timestamp(1628349200, 3), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } } mongos> 查看mongos> sh.status() --- Sharding Status --- sharding version: { "_id" : 1, "minCompatibleVersion" : 5, "currentVersion" : 6, "clusterId" : ObjectId("610ea073af74e0f6aa13bd64") } shards: { "_id" : "shard1", "host" : "shard1/mongo1.linuxea.com:27010,mongo3.linuxea.com:27010,mongo5.linuxea.com:27010", "state" : 1 } active mongoses: "4.4.8" : 3 autosplit: Currently enabled: yes 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" : "config", "primary" : "config", "partitioned" : true } mongos> 创建分片表上述示例中我们搭建了一个只有1个分片的分片集。在继续之前我们先来测试一下这个分片集。连接到分片集：mongo --host mongo1.linuxea.com:27017sh.status();mongos> sh.status() --- Sharding Status --- sharding version: { "_id" : 1, "minCompatibleVersion" : 5, "currentVersion" : 6, "clusterId" : ObjectId("610ea073af74e0f6aa13bd64") } shards: { "_id" : "shard1", "host" : "shard1/mongo1.linuxea.com:27010,mongo3.linuxea.com:27010,mongo5.linuxea.com:27010", "state" : 1 } active mongoses: "4.4.8" : 3 autosplit: Currently enabled: yes 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" : "config", "primary" : "config", "partitioned" : true } config.system.sessions shard key: { "_id" : 1 } unique: false balancing: true chunks: shard1 1024 too many chunks to print, use verbose if you want to force print创建一个分片表：foo为库名sh.enableSharding("foo"); // 指定库 sh.shardCollection("foo.bar", {_id: 'hashed'}); // 指定foo.bar表，分片键id作为hashed sh.status();执行mongos> sh.enableSharding("foo"); { "ok" : 1, "operationTime" : Timestamp(1628349504, 4), "$clusterTime" : { "clusterTime" : Timestamp(1628349504, 4), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }1mongos> sh.shardCollection("foo.bar", {_id: 'hashed'}); { "collectionsharded" : "foo.bar", "collectionUUID" : UUID("0bec2da5-e75b-4de4-82be-292584633af9"), "ok" : 1, "operationTime" : Timestamp(1628349791, 2), "$clusterTime" : { "clusterTime" : Timestamp(1628349791, 2), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } } mongos> sh.status()查看状态mongos> sh.status() --- Sharding Status --- sharding version: { "_id" : 1, "minCompatibleVersion" : 5, "currentVersion" : 6, "clusterId" : ObjectId("610ea073af74e0f6aa13bd64") } shards: { "_id" : "shard1", "host" : "shard1/mongo1.linuxea.com:27010,mongo3.linuxea.com:27010,mongo5.linuxea.com:27010", "state" : 1 } active mongoses: "4.4.8" : 3 autosplit: Currently enabled: yes 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" : "config", "primary" : "config", "partitioned" : true } config.system.sessions shard key: { "_id" : 1 } unique: false balancing: true chunks: shard1 1024 too many chunks to print, use verbose if you want to force print { "_id" : "foo", "primary" : "shard1", "partitioned" : true, "version" : { "uuid" : UUID("eb727625-7da5-4da6-a162-25ce2ee2973d"), "lastMod" : 1 } } foo.bar shard key: { "_id" : "hashed" } unique: false balancing: true chunks: shard1 2 { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong(0) } on : shard1 Timestamp(1, 0) { "_id" : NumberLong(0) } -->> { "_id" : { "$maxKey" : 1 } } on : shard1 Timestamp(1, 1) 任意写入若干数据：use foo for (var i = 0; i < 10000; i++) { db.bar.insert({i: i}); }执行插入10000条数据mongos> use foo switched to db foo mongos> for (var i = 0; i < 10000; i++) { ... db.bar.insert({i: i}); ... } WriteResult({ "nInserted" : 1 })此时在查看databases字段多了一个foo"_id" : "foo", "primary"： 表示每个分片集群里面都有一个主shard，有些操作会在primary上进行partitioned: true: 表示这张表已经分片了，并且会在下面列出"_id" : "foo", "primary" : "shard1", "partitioned" : true, mongos> sh.status() --- Sharding Status --- .............. databases: { "_id" : "config", "primary" : "config", "partitioned" : true } config.system.sessions shard key: { "_id" : 1 } unique: false balancing: true chunks: shard1 1024 too many chunks to print, use verbose if you want to force print { "_id" : "foo", "primary" : "shard1", "partitioned" : true, "version" : { "uuid" : UUID("eb727625-7da5-4da6-a162-25ce2ee2973d"), "lastMod" : 1 } } foo.bar shard key: { "_id" : "hashed" } unique: false balancing: true chunks: shard1 2 { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong(0) } on : shard1 Timestamp(1, 0) { "_id" : NumberLong(0) } -->> { "_id" : { "$maxKey" : 1 } } on : shard1 Timestamp(1, 1) 向分片集群加入新的分片创建复制集下面我们搭建shard2并将其加入分片集中，观察发生的效果。使用类似shard1的方式搭建shard2。在member2/member4/member6上执行以下命令：授权chown -R mongodb.mongodb /data/shard2/启动sudo -u mongodb /data/mongodb/bin/mongod --bind_ip 0.0.0.0 --replSet shard2 --dbpath /data/shard2 --logpath /data/shard2/mongod.log --port 27011 --fork --shardsvr --wiredTigerCacheSizeGB 1firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="172.16.100.0/24" port port="27011" protocol="tcp" accept' firewall-cmd --reload firewall-cmd --list-all用这三个实例搭建shard2复制集：任意连接到一个实例，例如我们连接到member2.example.com：mongo --host mongo2.linuxea.com:27011初始化shard2复制集。我们使用如下配置初始化复制集：rs.initiate({ _id: "shard2", "members" : [ { "_id": 0, "host" : "mongo2.linuxea.com:27011" }, { "_id": 1, "host" : "mongo4.linuxea.com:27011" }, { "_id": 2, "host" : "mongo6.linuxea.com:27011" } ] });执行> rs.initiate({ ... _id: "shard2", ... "members" : [ ... { ... "_id": 0, ... "host" : "mongo2.linuxea.com:27011" ... }, ... { ... "_id": 1, ... "host" : "mongo4.linuxea.com:27011" ... }, ... { ... "_id": 2, ... "host" : "mongo6.linuxea.com:27011" ... } ... ] ... }); { "ok" : 1 }而后稍作等待shard2:SECONDARY> 切换为shard2:PRIMARY> shard2:SECONDARY> shard2:SECONDARY> shard2:SECONDARY> shard2:PRIMARY> shard2:PRIMARY> 如果没有看到也要在rs.status()中找到primaryshard2:PRIMARY> rs.status() { .................. "members" : [ { "_id" : 0, "name" : "mongo2.linuxea.com:27011", "health" : 1, "state" : 1, "stateStr" : "PRIMARY", "uptime" : 110, "optime" : { "ts" : Timestamp(1628350780, 1), "t" : NumberLong(1) ................ }, { "_id" : 1, "name" : "mongo4.linuxea.com:27011", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 66, "optime" : { ............. }, { "_id" : 2, "name" : "mongo6.linuxea.com:27011", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 66, "optime" : { "ts" : Timestamp(1628350780, 1), "t" : NumberLong(1) .......... }加入第二个分片连接到任意一个mongos。此处使用mongo1：mongo --host mongo1.linuxea.com:27017将shard2加入到集群中：sh.addShard("shard2/mongo2.linuxea.com:27011,mongo4.linuxea.com:27011,mongo6.linuxea.com:27011");mongos> sh.addShard("shard2/mongo2.linuxea.com:27011,mongo4.linuxea.com:27011,mongo6.linuxea.com:27011"); { "shardAdded" : "shard2", "ok" : 1, "operationTime" : Timestamp(1628351344, 3), "$clusterTime" : { "clusterTime" : Timestamp(1628351344, 4), "signature" : { "hash" : BinData(0,"AAAAAAAAAAAAAAAAAAAAAAAAAAA="), "keyId" : NumberLong(0) } } }观察sh.status()：sh.status();mongos> sh.status() --- Sharding Status --- ....... shards: { "_id" : "shard1", "host" : "shard1/mongo1.linuxea.com:27010,mongo3.linuxea.com:27010,mongo5.linuxea.com:27010", "state" : 1 } { "_id" : "shard2", "host" : "shard2/mongo2.linuxea.com:27011,mongo4.linuxea.com:27011,mongo6.linuxea.com:27011", "state" : 1 } active mongoses: ........ unique: false balancing: true chunks: shard1 1 shard2 1 { "_id" : { "$minKey" : 1 } } -->> { "_id" : NumberLong(0) } on : shard2 Timestamp(2, 0) { "_id" : NumberLong(0) } -->> { "_id" : { "$maxKey" : 1 } } on : shard1 Timestamp(2, 1) mongos> 可以发现原本shard1上的两个chunk被均衡到了shard2上，这就是MongoDB的自动均衡机制。现在就可以通过mongos进行操作了集群重启恢复先启动configsudo -u mongodb /data/mongodb/bin/mongod --bind_ip 0.0.0.0 --replSet config --dbpath /data/config --logpath /data/config/mongod.log --port 27019 --fork --configsvr --wiredTigerCacheSizeGB 1在启动复制集1和复制集2，有几个启动几个sudo -u mongodb /data/mongodb/bin/mongod --bind_ip 0.0.0.0 --replSet shard1 --dbpath /data/shard1 --logpath /data/shard1/mongod.log --port 27010 --fork --shardsvr --wiredTigerCacheSizeGB 2sudo -u mongodb /data/mongodb/bin/mongod --bind_ip 0.0.0.0 --replSet shard2 --dbpath /data/shard2 --logpath /data/shard2/mongod.log --port 27011 --fork --shardsvr --wiredTigerCacheSizeGB 1最后启动mongossudo -u mongodb /data/mongodb/bin/mongos --bind_ip 0.0.0.0 --logpath /data/mongos/mongos.log --port 27017 --configdb config/mongo1.linuxea.com:27019,mongo3.linuxea.com:27019,mongo5.linuxea.com:27019 --fork
-
linuxea:mongodb4.4.8分片集群学习1 引用geekdemo课程分片集群和复制集不同，分片集群有路由，配置节点，并且对数据量增加，访问性能日渐降低，高并发，数据量大的场景有着显著的作用。而mongoos路由为应用程序提供集群的单一入口。根据请求转发到多个节点，并且把请求的返回进行合并后返回给应用端。config1配置节点： 提供高可用，提供集群元数据(mongodb数据存储位置等)。shard表存储key范围等信息，通过对照表信息将信息加载到内存进行对比，从而才能得知请求应该发送到那个shard上。mongodb：一个分片就是一个复制集，每一个分片对应的必须是一个复制集，分片和分片的数据是不重复的，每一个分片存储了一部分数据如下分片集群特点应用透明，无特殊处理，使用复制的代码可以无缝使用在分片中数据自动均衡，mongodb会自动检测分片的分布情况动态扩容，无需下线提供三种分片方式：基于范围，基于hash，基于zone基于范围查询性能较好hash为了解决上面的热点写和写分布不均匀的情况，使用hash。按某个字段的hash，不连续的随机的写到各个不同的范围中的节点上。zone通过有效打标签的方式可以有效的把地域化的分片组织到当地的分片。image-20210807175421800.png合理配置分片集群是否需要分片，需要多少分片，数据分布规则选择需要分片的表，正确的片键，合适的负载均衡足够的资源，CPU，RAM,存储分片大小关于数据：不超过2TB，保持2TB以内一个片关于索引：常用索引必须容纳进内存需要多少个分片从存储看：存储总量/单服务器容量=分片节点从内存看：热数据内存大小：mongodb默认用来做缓存的是%60的内存空间。48g内存，工作集100G数据，而真正用来做缓存的是%60为0.6. 热点数据和索引数据和除（内存总大小乘0.6）等于分片节点数100G / (48G*0.6) = 2 ，只看内存的话，工作集就需要2个分片从并发看：0.7为额外开销总的并发数除/（单台并发数乘以0.7)等于分片节点数其他考虑：是否跨机房灾备等。片键的选择片键shard key：文档中的一个或多个字段，组成一个核心数据分布的准则，需要选择一个键来作为分片影响片键效率的因素：取值基数(cardinality)，取值分布，分散写，集中读，被尽可能多的业务场景用到，避免单调递增或递减的片键取值基数(cardinality)选择基数(值的范围)大的片键，基数决定的了片的大小。如果备选值有限，那么快就不能超过有限的值的范围，随数据增多块大小会越来越大。而这些太大的块会导致水平扩展困难，在负载中移动块将会非常困难取值分布选择分布均匀的片键。数据均衡是以块为单位的，而对于一些不均匀的片键会造成某些快的数据急剧增大，压力也随之增大。定向4个分片的集群，希望读某条特定的数据，如果用片键作为查询条件，mongos可以直接定位到具体的分片，如果不用片键，mongos需要把查询发到4个分片，等最后一个分片响应，mongdos才能响应应用端以email作为例子{ _id: ObjectID(), user:123 time: Date(), subject: "", recipients: [], body: "", attachements: [] }如上，最常见的使用_id作为片键，_id是16个字节无穷大的数值，基数是非常理想的，而id自增的，新写入的都会进入第一个分片或者最后一个分片，写入完成后在搬入到另一个分片节点，这样一个写入就变成了两个写入，这样一来写入就以热分片的方式固定在某一个区域。对于查询也是不友好的。这并不是一个好的片键。hash作为片键相比较之上，把自增的去掉了，hash随机写，但是还是 有一个定向查询的问题，查询一次还是会到每个节点查询用户user_id作为片键，如果基于用户id，定向查询是可以的，写分片也是ok的，但是基数不够大，一个用户的邮件是在一个块里面，短期内写入量少，但是如果使用时间太长，数据沉淀太多，使用一个id存储了大量的数据就变成了一个超级块，超级块的负载移动将会成后困难。最佳实践：组合片键用户id和时间，用户会导致基数不够大导致大块，而解决这个问题需要增加字段，userid加上time字段，综合起来就是一个比较理想的片键。文档doc:包含shard key的一行数据，存储的基本单位块chunk:包含n个文档，逻辑概念，通常是64Mb，集群在做均衡的时候以快为单位均衡分片shard:包含n个chunk ，一般为三个节点的复制集组成分片集群数据里的集合集群cluster：包含n个分片资源mongos与config通常消耗较少的资源资源消耗较大的是shard的服务器：需要足以容纳热数据索引的内存正确创建索引后CPU通常不会成为瓶颈，除非涉及到非常多的计算磁盘使用SSD足够的资源是必要的，当监控项使用率在60%以上则开始考虑扩展，因为扩展的需要均衡，均衡需要时间，如果资源即将耗尽，均衡也很低效
-
linuxea:mongodb性能诊断工具 下载mongodb-database-tools后就自带了mongostat命令使用mongostat使用超级管理员查看admin库 1 是间隔1秒的意思 ./mongostat -h 127.0.0.1:27020 -u superadmin --password=password --authenticationDatabase=admin 1insert query update delete getmore command ：在最近的一秒钟发生的操作。getmore：是针对一些游标操作，第一次拿了100条，而游标返回了10000w条，需要一次次抓回来，这里表示有多少个getmore在执行command ：表示除了增删改查，包含增删改查，包括index等状态的命令都是通过command 来完成的dirty ：mongodb是通过内存来缓存和增加数据，mongodb通过缓存来管理一些数据，默认是百分之60的内存使用，在这些内存之间有多少是被更新的，修改的，或者插入的。这些操作且没有刷盘的，就是dirty。这里的百分比表示没有刷盘的占用百分比。如果没有超过百分之5，后台程序会5分钟刷一次数据到盘上。如果百分比超过5，后台会启动程序自动把这些刷到盘上。从而尽量的保证在百分之5以下。而一旦超过百分之20，mongodb可能会停止接受前面的请求也要将数据刷到盘上，也就是超过百分之20时阻塞新请求。这种情况，要么是刷盘跟不上，磁盘IO跟不上。要么是写入压力太大。used ：默认是百分之60的内存使用，百分之60的内存已经有百分之多少被使用了。低于百分之80的时候，mongdb不会做什么事情，超过百分之80开始清理动作，基于一些很久没有被使用的数据剔除，缓存清理动作。超过百分之95时阻塞新请求，并专注于清理缓存。vsize ：使用的内存大小res: 实际使用的内存大小qrw /arw ： 超过10存在等待net_in /net_out ： 进出conn ： 连接数使用mongotop./mongotop -h 127.0.0.1:27020 -u superadmin --password=password --authenticationDatabase=admin 2间隔2秒抓一次ns : 来源库total：总时间消耗read：读时间消耗write：写时间消耗使用日志配置db.getProfilingStatus()查看是否开启rs02:PRIMARY> db.getProfilingStatus() { "was" : 1, "slowms" : 10, "sampleRate" : 1, "$clusterTime" : { "clusterTime" : Timestamp(1628512885, 1), "signature" : { "hash" : BinData(0,"RXoRmbr9npCqd6IxClxVYWeIjzg="), "keyId" : NumberLong("6993996578134425604") } }, "operationTime" : Timestamp(1628512885, 1) }设置：db.setProfilingLevel(level, slowms)查看：db.system.profile.find()evel：0，关闭；1，记录慢速操作；2，记录所有操作。slowms：时间阈值，单位ms。表示10毫秒的：db.setProfilingLevel( 1 , 10);默认100ms示例2019-10-25T11:20:30.775+0800 I COMMAND [conn12] command test.test appName: "MongoDB Shell" command: find { find: "test", filter: { i: 10000.0 }, $db: "test" } planSummary: COLLSCAN keysExamined:0 docsExamined:27108 cursorExhausted:1 numYields:211 nreturned:1 reslen:243 locks:{ Global: { acquireCount: { r: 424 } }, Database: { acquireCount: { r: 212 } }, Collection: { acquireCount: { r: 212 } } } protocol:op_command 19msdocsExamined ： 扫描文档数locks： 锁信息numYields: 执行计划durationMillis: 执行时间mtoolshttps://github.com/rueckstiess/mtools/releaseshttps://github.91chifun.workers.dev//https://github.com/rueckstiess/mtools/archive/refs/tags/v1.6.4.tar.gzmtools可以分析mongo日志yum install python3 -ytar xf v1.6.4.tar.gz && cd mtools-1.6.4 python3 setup.py install常用指令：• mplotqueries 日志文件：将所有慢查询通过图表形式展现；• mloginfo --queries 日志文件：总结出所有慢查询的模式和出现次数、消耗时间等；
-
linuxea:mongodb4.4.8复制(副本)集简单配置 mongodb复制集是原生提供这种方式 ，复制集的主要意义在于实现服务的高可用，它的依赖于两个方面的功能输入写入时将数据快速复制到另外一个独立节点上在接受写入的节点发生故障时自动选举一个新的替代节点在实现高可用的同时，复制集实现了其他几个附加的作用数据分发： 将数据 从一个区域复制到另一个 区域，减少另一个区域的读延迟读写分离：不同类型的压力分别在不同的节点上执行异地容灾：在数据中心故障的时候快速的切换到异地一个典型的复制集由三个以上的具有投票权的节点组成，包括：一个主节点 (PRIMARY)： 接受写入 操作和选举时投票两个或多个从节点 (SECONDARY) : 复制主节点上的新数据 和选举时投票不推荐使用Arbiter(投票节点)数据是如何复制的？当一个修改操作 ，无论是插入更新或者删除，到达主节点时，他对数据的操作被记录下来，经过一些 必要的转换，这些记录 称为oplog从节点通过在主节点上打开一个 tailable游标不断获取新进入的主节点的oplog，并在自己的数据上回放，以此保持跟主节点的数据一致通过选举完成的故障恢复具有投票 圈的节点两两互信发送心跳当5次心跳未收到时判断为节点失联如果失联的是主节点，从节点发起选举，选出新的主节点如果失联的是从节点则不会产生新 的选举选举基于RAFT一致性算法实现，选举成功的必要条件是大多数投票节点存活复制集中最多可以有50个节点，但具有投票权的节点最多 7个影响选举的因素整个集群必须有大多数节点存货被选举为主节点的节点必须满足以下条件：能够与多数节点建立连接具有较新 的oplog具有较高的优先级(如果配置了的话)复制集节点有一下常见的选配项目 ：是否具有投票权（v）： 有就参与投票优先级（priority）：优先级越高的节点优先称为主节点，优先级为0的节点无法称为主节点隐藏（hidden）：复制 数据，但对应用不可见。隐藏节点可以 具有投票权，但优先级必须 为0延迟（slaveDelay）：复制n秒之前的数据，保持与主节点的 时间差（通常用来作为一个规避误操作的备份节点）复制集的注意事项所有的节点都有可能称为主节点，配置应该一致复制集版本一致在写频繁的情况下，增加节点不会增加系统的写性能。除非是读多写少的情况其他配置ssh-keygen -t rsa ssh-copy-id 172.16.100.92 ssh-copy-id 172.16.100.93配置ulimit[root@Node-172_16_100_91 ~]# ulimit -n 1000000安装脚本if ! grep ntp.aliyun.com /var/spool/cron/root >/dev/null 2>&1;then (crontab -l; echo -e "10 * * * * ntpdate ntp.aliyun.com") | crontab -;fi timedatectl set-timezone Asia/Shanghai hostnamectl set-hostname mongodb1 wget https://fastdl.mongodb.org/linux/mongodb-linux-x86_64-rhel70-4.4.8.tgz tar xf mongodb-linux-x86_64-rhel70-4.4.8.tgz -C /usr/local cd /usr/local/ ln -s mongodb-linux-x86_64-rhel70-4.4.8 mongodb ## ulimit echo "never" > /sys/kernel/mm/transparent_hugepage/defrag echo "never" > /sys/kernel/mm/transparent_hugepage/enabled groupadd -r -g 490 mongodb useradd -u 490 -s /sbin/nologin -c 'mongodb server' -g mongodb mongodb -M mkdir /u01/data/mongodb/{data,logs,pid,conf} -p chown -R mongodb.mongodb /u01/data/mongodb ln -s /usr/local/mongodb/bin/mongo /usr/local/bin/ #cp /etc/firewalld/zones/public.xml /etc/firewalld/zones/public.xml.oldone #cat > /etc/firewalld/zones/public.xml << EOF #<?xml version="1.0" encoding="utf-8"?> #<zone> # <short>Public</short> # <service name="ssh"/> # <service name="dhcpv6-client"/> # <port protocol="tcp" port="27020"/> #</zone> #EOF #systemctl restart firewalld firewall-cmd --permanent --zone=public --add-rich-rule='rule family="ipv4" source address="172.16.100.0/24" port port="27020" protocol="tcp" accept' firewall-cmd --reload firewall-cmd --list-all cat > /etc/systemd/system/mongodb.service << EPF [Unit] Description=mongodb Service After=network.target syslog.target [Service] Environment=ZOO_LOG_DIR=/u01/data/mongodb/logs SyslogIdentifier=mongodb LimitFSIZE=infinity LimitCPU=infinity LimitAS=infinity LimitMEMLOCK=infinity LimitNOFILE=64000 LimitNPROC=64000 User=mongodb Group=mongodb Type=forking PIDfile=/u01/data/mongodb/pid/mongod.pid ExecReload=/bin/kill -s HUP $MAINPID ExecStart=/usr/local/mongodb/bin/mongod -f /u01/data/mongodb/conf/mongodb.conf ExecStop=/usr/local/mongodb/bin/mongod --shutdown -f /u01/data/mongodb/conf/mongodb.conf Restart=on-failure PrivateTmp=true [Install] WantedBy=default.target EPF systemctl daemon-reload chmod +x /etc/systemd/system/mongodb.service systemctl enable mongodb配置文件配置文件ip和域名ip域名主机名172.16.100.91mongo1.linuxea.com mongo2.linuxea.commogodb_instance1172.16.100.92mongo3.linuxea.com mongo4.linuxea.commogodb_instance2172.16.100.93mongo5.linuxea.com mongo5.linuxea.commogodb_instance3echo "172.16.100.91 mogodb_instance1 mongo1.linuxea.com mongo2.linuxea.com" >> /etc/hosts echo "172.16.100.92 mogodb_instance2 mongo3.linuxea.com mongo4.linuxea.com" >> /etc/hosts echo "172.16.100.93 mogodb_instance3 mongo5.linuxea.com mongo6.linuxea.com" >> /etc/hosts修改每个节点的主机名for i in static pretty transient; do hostnamectl set-hostname mogodb_instance1 --$i; done节点1cat > /u01/data/mongodb/conf/mongodb.conf << EOF systemLog: destination: file logAppend: true path: /u01/data/mongodb/logs/mongod.log storage: dbPath: /u01/data/mongodb/data journal: enabled: true directoryPerDB: true wiredTiger: engineConfig: cacheSizeGB: 2 directoryForIndexes: true processManagement: fork: true pidFilePath: /u01/data/mongodb/pid/mongod.pid net: port: 27020 bindIp: localhost,mongo1.linuxea.com maxIncomingConnections: 5000 #security: #keyFile: /u01/data/mongodb/conf/keyfile #authorization: enabled replication: # oplogSizeMB: 1024 replSetName: rs02 EOF节点2cat > /u01/data/mongodb/conf/mongodb.conf << EOF systemLog: destination: file logAppend: true path: /u01/data/mongodb/logs/mongod.log storage: dbPath: /u01/data/mongodb/data journal: enabled: true directoryPerDB: true wiredTiger: engineConfig: cacheSizeGB: 2 directoryForIndexes: true processManagement: fork: true pidFilePath: /u01/data/mongodb/pid/mongod.pid net: port: 27020 bindIp: localhost,mongo3.linuxea.com maxIncomingConnections: 5000 #security: #keyFile: /u01/data/mongodb/conf/keyfile #authorization: enabled replication: # oplogSizeMB: 1024 replSetName: rs02 EOF节点3cat > /u01/data/mongodb/conf/mongodb.conf << EOF systemLog: destination: file logAppend: true path: /u01/data/mongodb/logs/mongod.log storage: dbPath: /u01/data/mongodb/data journal: enabled: true directoryPerDB: true wiredTiger: engineConfig: cacheSizeGB: 2 directoryForIndexes: true processManagement: fork: true pidFilePath: /u01/data/mongodb/pid/mongod.pid net: port: 27020 bindIp: localhost,mongo5.linuxea.com maxIncomingConnections: 5000 #security: #keyFile: /data/mongodb/conf/keyfile #authorization: enabled replication: # oplogSizeMB: 1024 replSetName: rs02 EOF初始化rs.initiate({ _id: "rs02", "members" : [ { "_id": 0, "host" : "mongo1.linuxea.com:27020" }, { "_id": 1, "host" : "mongo3.linuxea.com:27020" }, { "_id": 2, "host" : "mongo5.linuxea.com:27020" } ] });执行> rs.initiate({ ... _id: "rs02", ... "members" : [ ... { ... "_id": 0, ... "host" : "mongo1.linuxea.com:27020" ... }, ... { ... "_id": 1, ... "host" : "mongo3.linuxea.com:27020" ... }, ... { ... "_id": 2, ... "host" : "mongo5.linuxea.com:27020" ... } ... ] ... }); { "ok" : 1 }稍等片刻等待状态：SECONDARY变为PRIMARYrs02:SECONDARY> rs02:PRIMARY> rs02:PRIMARY> rs02:PRIMARY> rs02:PRIMARY> sh.status() printShardingStatus: this db does not have sharding enabled. be sure you are connecting to a mongos from the shell and not to a mongod. rs02:PRIMARY> rs.status() { ............. "members" : [ { "_id" : 0, "name" : "mongo1.linuxea.com:27020", "health" : 1, "state" : 1, "stateStr" : "PRIMARY", "uptime" : 66, ......... "_id" : 1, "name" : "mongo3.linuxea.com:27020", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 20, ........... "_id" : 2, "name" : "mongo5.linuxea.com:27020", "health" : 1, "state" : 2, "stateStr" : "SECONDARY", "uptime" : 20, .......... rs02:PRIMARY> 开启集群认证和用户认证mongodb默认没有一个超级用户的，默认任何人都可以登录。当开启用户认证后仍然可以不使用密码登录，但是不使用密码登录，不能访问里面的资源 ，且只能创建用户创建用户创建超级用户db.createUser({ user: 'superadmin', pwd: 'password', roles: [{role: 'root', db: 'admin'}] } )执行rs02:PRIMARY> use admin switched to db admin rs02:PRIMARY> db.createUser({ ... user: 'superadmin', ... pwd: 'password', ... roles: [{role: 'root', db: 'admin'}] ... } ... ) Successfully added user: { "user" : "superadmin", "roles" : [ { "role" : "root", "db" : "admin" } ] }而后就使用创建的账户登录mongo --host mongo3.linuxea.com:27020 -u superadmin -p password --authenticationDatabase admin而后创建mark库的读写用户当创建了amdin用户后，开启认证后，只能登录admin授权其他用户db.createUser({ user: 'writer', pwd: 'password', roles: [{role: 'readWrite', db: 'mark'}] } ) db.createUser({ user: 'reader', pwd: 'password', roles: [{role: 'read', db: 'mark'}] } )创建mark库的读写超级用户db.createUser({ user: 'mark', pwd: 'TdmMzIyNjRmMjViOTc1MGIwZGU', roles: [{role: 'dbAdmin', db: 'mark'}] } )一旦这样授权，在登录的时候就需要指定库--authenticationDatabase markmongo --host mongo3.linuxea.com:27020 -u mark -p TdmMzIyNjRmMjViOTc1MGIwZGU --authenticationDatabase mark创建集群认证文件用户创建完成后，还需要创建集群内认证文件 cd /u01/data/mongodb/conf/ openssl rand -base64 756 > keyfile chmod 400 keyfile chown mongodb.mongodb keyfile复制到92和93scp keyfile 172.16.100.92:/u01/data/mongodb/conf/ scp keyfile 172.16.100.93:/u01/data/mongodb/conf/ ssh 172.16.100.92 "chown mongodb.mongodb /u01/data/mongodb/conf/keyfile" ssh 172.16.100.93 "chown mongodb.mongodb /u01/data/mongodb/conf/keyfile" ssh 172.16.100.93 "chmod 400 /u01/data/mongodb/conf/keyfile" ssh 172.16.100.92 "chmod 400 /u01/data/mongodb/conf/keyfile"开启securitysecurity: keyFile: /u01/data/mongodb/conf/keyfile authorization: enabled而后重启集群并观察日志systemctl restart mongodb tail -f /u01/data/mongodb/logs/mongod.log 尝试登录超级管理登录[root@Node-172_16_100_91 ~]# mongo --host mongo1.linuxea.com:27020 -u superadmin -p password --authenticationDatabase admin MongoDB shell version v4.4.8 connecting to: mongodb://mongo1.linuxea.com:27020/?authSource=admin&compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("4f20880e-2ab3-44b6-8ce9-825640ef13d4") } MongoDB server version: 4.4.8 --- Enable MongoDB's free cloud-based monitoring service, which will then receive and display metrics about your deployment (disk utilization, CPU, operation statistics, etc). The monitoring data will be available on a MongoDB website with a unique URL accessible to you and anyone you share the URL with. MongoDB may use this information to make product improvements and to suggest MongoDB products and deployment options to you. To enable free monitoring, run the following command: db.enableFreeMonitoring() To permanently disable this reminder, run the following command: db.disableFreeMonitoring() --- rs02:PRIMARY> show dbs admin 0.000GB config 0.000GB local 0.027GB mark 0.007GBmark用户登录[root@Node-172_16_100_91 ~]# mongo --host mongo3.linuxea.com:27020 -u mark -p TdmMzIyNjRmMjViOTc1MGIwZGU --authenticationDatabase mark MongoDB shell version v4.4.8 connecting to: mongodb://mongo3.linuxea.com:27020/?authSource=mark&compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("b6204420-bf30-452f-9cef-e7bb84c73a4d") } MongoDB server version: 4.4.8 rs02:SECONDARY> 登录PRIMARY[root@Node-172_16_100_91 ~]# mongo --host mongo1.linuxea.com:27020 -u mark -p TdmMzIyNjRmMjViOTc1MGIwZGU --authenticationDatabase mark MongoDB shell version v4.4.8 connecting to: mongodb://mongo1.linuxea.com:27020/?authSource=mark&compressors=disabled&gssapiServiceName=mongodb Implicit session: session { "id" : UUID("9d19034d-e7b9-4ccb-b657-bc84122d0468") } MongoDB server version: 4.4.8 rs02:PRIMARY> show dbs mark 0.007GB rs02:PRIMARY> 开启从节点读slaveOk()添加节点rs.add("hostname And ip:port")Compose下载compass进行配置选择Fill in connection fields individually
-
linuxea: 基于Galera cluster的MaxScale 2.4读写分离笔记 在此前的两个版本中，不同于数据库集群大小和数据大小做了不同的线上测试，有一系列问题，最终被下架更换到haproxy。在新的环境中使用了2.4，因为2.4有很多变动。仍然保持热情的进行体验。Galera cluster集群模式中节点都可以作为读写，但是通常而言，会在一台节点进行写入，其他节点读取。因此选择读写分离模块readwritesplit路由模式。readwritesplit读写分离readwritesplit旨在提高群集的只读处理能力，同时保持一致性。不修改数据的数据分散在多个节点上，而所有写查询都将发送到单个节点上。那么，除了readwritesplit，maxscale还提供SchemaRouter和SmartRouter,有兴趣可以查看。此外，maxscale提供了新的Clustrix作为监视工具MaxCtrl安装建议下载rpm包，查看相应的版本下载即可。假如打不开，推荐到github下载压缩包进行安装即可下载完成在本地安装即可yum localinstall maxscale-2.4.7-1.centos.7.x86_64.rpm -y他的配置文件默认在/etc下[root@linuxea_Node ~]# rpm -ql maxscale |grep etc /etc/maxscale.cnf.template /etc/prelink.conf.d /etc/prelink.conf.d/maxscale.conf配置我现在有两台数据库是Galera cluster集群，现在我们配置成maxscale读写分离配置文件分几个部分，分别是全局，节点，service，监控模块和listener。相比之前的版本少了监控的端口配置。其他配置参考官网配置。全局配置全局配置中配置一些必要的配置信息节点配置除了必要的信息之外，可以添加priority设置优先级，如果为0，永远不会成为主节点用户请求从4006进入，将请求发给RW-Service，RW-Service根据读写分离策略将请求发给后端，Galera-Monitor作为管理和调度如下:# 全局配置 [maxscale] threads=auto ms_timestamp=1 syslog=0 maxlog=1 log_warning=1 log_notice=0 log_info=0 log_augmentation=1 # service配置 [RW-Service] type=service router=readwritesplit user=maxscale password=F3BF776B14BE7CAB61E83256E1C2B56C cluster=Galera-Monitor #listener [RW-Listener] type=listener service=RW-Service protocol=MariaDBClient address=0.0.0.0 port=4006 # 节点配置 [server1] type=server address=172.25.109.5 port=3306 protocol=MariaDBBackend [server2] type=server address=172.25.109.4 port=3306 protocol=MariaDBBackend # 监控模块 [Galera-Monitor] type=monitor module=galeramon servers=server1,server2 user=maxscale # maxkeys /var/lib/maxscale/ # maxpasswd /var/lib/maxscale KLJMKMXOPQOP1 KLJMKMXOPQOP1是授权的密码 password=F3BF776B14BE7CAB61E83256E1C2B56C monitor_interval=10000 disable_master_failback=true use_priority=true数据库授权授权给maxscaleCREATE USER 'maxscale'@'%' IDENTIFIED BY 'password'; GRANT SELECT ON mysql.user TO 'maxscale'@'%'; GRANT SELECT ON mysql.db TO 'maxscale'@'%'; GRANT SELECT ON mysql.tables_priv TO 'maxscale'@'%'; GRANT SELECT ON mysql.roles_mapping TO 'maxscale'@'%'; GRANT SHOW DATABASES ON *.* TO 'maxscale'@'%';完成授权后，修改maxscale的配置文件password字段[root@linuxea_node_maxscale-1 ~]# maxkeys /var/lib/maxscale/ [root@linuxea_node_maxscale-1 ~]# maxpasswd /var/lib/maxscale password 70DB8B57832941E87AB14C5BC47C6292 [root@linuxea_node_maxscale-1 ~]# sed -i 's/password=.*/password=70DB8B57832941E87AB14C5BC47C6292/g' /etc/maxscale.cnf而后启动[root@linuxea_node_maxscale-1 ~]# systemctl restart maxscale [root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log MariaDB MaxScale /var/log/maxscale/maxscale.log Sat Mar 21 17:03:08 2020 ---------------------------------------------------------------------------- 2020-03-21 17:03:08 notice : (mxb_log_set_syslog_enabled): syslog logging is disabled. 2020-03-21 17:03:08 notice : (mxb_log_set_maxlog_enabled): maxlog logging is enabled. 2020-03-21 17:03:08.752 notice : (mxb_log_set_highprecision_enabled): highprecision logging is enabled. 2020-03-21 17:03:08.752 notice : (mxb_log_set_priority_enabled): The logging of warning messages has been enabled.监控模块[root@linuxea_node_maxscale-1 ~]# maxctrl list services ┌────────────┬────────────────┬─────────────┬───────────────────┬──────────────────┐ │ Service │ Router │ Connections │ Total Connections │ Servers │ ├────────────┼────────────────┼─────────────┼───────────────────┼──────────────────┤ │ RW-Service │ readwritesplit │ 0 │ 0 │ server1, server2 │ └────────────┴────────────────┴─────────────┴───────────────────┴──────────────────┘[root@linuxea_node_maxscale-1 ~]# maxctrl list servers ┌─────────┬──────────────┬──────┬─────────────┬─────────────────────────┬───────┐ │ Server │ Address │ Port │ Connections │ State │ GTID │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤ │ server1 │ 172.25.109.5 │ 3306 │ 0 │ Slave, Synced, Running │ │ ├─────────┼──────────────┼──────┼─────────────┼─────────────────────────┼───────┤ │ server2 │ 172.25.109.4 │ 3306 │ 0 │ Master, Synced, Running │ 0-1-6 │ └─────────┴──────────────┴──────┴─────────────┴─────────────────────────┴───────┘[root@linuxea_node_maxscale-1 ~]# maxctrl list listeners RW-Service ┌──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┬──────────────────────────────────────────────────┐ │ Name │ Port │ Host │ State │ ├──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┼──────────────────────────────────────────────────┤ │ { │ { │ { │ { │ │ "id": "RW-Listener", │ "id": "RW-Listener", │ "id": "RW-Listener", │ "id": "RW-Listener", │ │ "type": "listeners", │ "type": "listeners", │ "type": "listeners", │ "type": "listeners", │ │ "attributes": { │ "attributes": { │ "attributes": { │ "attributes": { │ │ "state": "Running", │ "state": "Running", │ "state": "Running", │ "state": "Running", │ │ "parameters": { │ "parameters": { │ "parameters": { │ "parameters": { │ │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ "protocol": "MariaDBClient", │ │ "port": 4006, │ "port": 4006, │ "port": 4006, │ "port": 4006, │ │ "socket": null, │ "socket": null, │ "socket": null, │ "socket": null, │ │ "authenticator_options": "", │ "authenticator_options": "", │ "authenticator_options": "", │ "authenticator_options": "", │ │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ "address": "0.0.0.0", │ │ "authenticator": null, │ "authenticator": null, │ "authenticator": null, │ "authenticator": null, │ │ "ssl": "false", │ "ssl": "false", │ "ssl": "false", │ "ssl": "false", │ │ "ssl_cert": null, │ "ssl_cert": null, │ "ssl_cert": null, │ "ssl_cert": null, │ │ "ssl_key": null, │ "ssl_key": null, │ "ssl_key": null, │ "ssl_key": null, │ │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ "ssl_ca_cert": null, │ │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ "ssl_version": "MAX", │ │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ "ssl_cert_verify_depth": 9, │ │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ "ssl_verify_peer_certificate": false │ │ }, │ }, │ }, │ }, │ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ "authenticator_diagnostics": [ │ │ { │ { │ { │ { │ │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ │ "host": "%" │ "host": "%" │ "host": "%" │ "host": "%" │ │ }, │ }, │ }, │ }, │ │ { │ { │ { │ { │ │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ "user": "maxscale", │ │ "host": "%" │ "host": "%" │ "host": "%" │ "host": "%" │ │ } │ } │ } │ } │ │ ] │ ] │ ] │ ] │ │ } │ } │ } │ } │ │ } │ } │ } │ } │ └──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┴──────────────────────────────────────────────────┘ [root@linuxea_node_maxscale-1 ~]# 用户授权数据库授权后，每一个登陆的授权用户同时仍然需要授权给maxscale。比如：maxscale的ip是172.25.109.8。假设现在授权一个用户sean用户从172.25.111.6来进行访问。使用maxscale，还需要将sean授权给maxscale的ip，权限及密码。CREATE DATABASE linuxea_com CHARACTER SET utf8mb4; GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password';MariaDB [(none)]> CREATE DATABASE linuxea_com CHARACTER SET utf8mb4; Query OK, 1 row affected (0.01 sec) MariaDB [(none)]> GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.111.6 IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)假如不授权，是可以登陆，但是会提示没有权限[root@linuxea_node_maxscale-1 ~]# tail -f /var/log/maxscale/maxscale.log 2020-03-21 17:10:04.865 error : (129) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES) 2020-03-21 17:10:09.961 error : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server1'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES) 2020-03-21 17:10:09.961 error : (132) [mariadbbackend] (handle_error_response): Invalid authentication message from backend 'server2'. Error code: 1045, Msg : #28000Access denied for user 'sean'@'172.25.109.8' (using password: YES)如下：[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 124 Server version: 10.0.33-MariaDB-wsrep Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 129 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 132 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed.我们在数据库进行授权maxscale的ipMariaDB [(none)]> GRANT ALL ON `linuxea_com`.* TO `sean`@172.25.109.8 IDENTIFIED BY 'password'; Query OK, 0 rows affected (0.00 sec)回到172.25.111.6登陆[root@linuxea-node-172.25.111.6 ~]# mysql -usean -ppassword -h172.25.109.8 -P4006 Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 124 Server version: 10.0.33-MariaDB-wsrep Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 129 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 132 Current database: *** NONE *** ERROR 2003 (HY000): Authentication with backend failed. Session will be closed. MariaDB [(none)]> show databases; ERROR 2006 (HY000): MySQL server has gone away No connection. Trying to reconnect... Connection id: 174 Current database: *** NONE *** +--------------------+ | Database | +--------------------+ | information_schema | | linuxea_com | +--------------------+ 2 rows in set (0.01 sec) MariaDB [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | linuxea_com | +--------------------+ 2 rows in set (0.00 sec) MariaDB [(none)]> 创建MariaDB [(none)]> use linuxea_com Database changed MariaDB [linuxea_com]> CREATE TABLE MyGuests ( -> id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, -> firstname VARCHAR(30) NOT NULL, -> lastname VARCHAR(30) NOT NULL, -> email VARCHAR(50), -> reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP -> ) -> ; Query OK, 0 rows affected (0.02 sec) MariaDB [linuxea_com]> show tables; +-----------------------+ | Tables_in_linuxea_com | +-----------------------+ | MyGuests | +-----------------------+ 1 row in set (0.00 sec) MariaDB [linuxea_com]> desc MyGuests; +-----------+-----------------+------+-----+-------------------+-----------------------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-----------------+------+-----+-------------------+-----------------------------+ | id | int(6) unsigned | NO | PRI | NULL | auto_increment | | firstname | varchar(30) | NO | | NULL | | | lastname | varchar(30) | NO | | NULL | | | email | varchar(50) | YES | | NULL | | | reg_date | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | +-----------+-----------------+------+-----+-------------------+-----------------------------+ 5 rows in set (0.00 sec)
