In this blog, I discuss a MongoDB administration checklist designed to help MySQL DBAs.
If you are MySQL DBA, starting MongoDB administration is not always an easy transition. Although most of the concepts and even implementation are similar, the commands are different. The following table outlines the typical MySQL concepts and DBA tasks (on the left) to corresponding MongoDB ones (on the right). If you happen to be a MongoDB DBA and want to learn MySQL administration, you can use the same table looking from right to left.
I’ve also created a webinar, MongoDB administration for MySQL DBA, that explains the above concepts. You can download the slides to use as a reference.
Don’t forget about our upcoming event Community Open House for MongoDB in New York, June 30, 2016. There will be technical presentations and sessions from key members of the MongoDB open source community. This event is free of charge and open to all.
Architecture: Basic Concepts
Replication:
Sharding:
Day-to-day operations
MySQL: SELECT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | select * from zips limit 1G country_code: US postal_code: 34050 place_name: FPO admin_name1: admin_code1: AA admin_name2: Erie admin_code2: 029 admin_name3: admin_code3: latitude: 41.03750000 longitude: -111.67890000 accuracy: 1 row in set (0.00 sec) |
MongoDB: FIND
1 2 3 4 5 6 7 8 9 10 11 12 13 | MongoDB shell version: 3.0.8 connecting to: zips > db.zips.find().limit(1).pretty() { "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" } |
MySQL: Schema
1 2 3 4 5 6 7 | CREATE TABLE users( id MEDIUMINT NOT NULL AUTO_INCREMENT, user_id varchar(30), age Number, status char(1), PRIMARY KEY (id) ); |
MongoDB: Flexible Schema
1 2 3 4 5 | db.users.insert( { user_id: "abc123", age: 55, status: "A"} ) |
MySQL: Config file
1 | /etc/my.cnf |
MongoDB:
1 2 3 4 5 6 7 8 9 10 11 | /etc/mongod.conf # Where and how to store data. storage: dbPath: /datawt journal: enabled: true engine: wiredTiger ... /usr/bin/mongod -f /etc/mongod.conf |
MySQL: databases
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | Databases mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | ... mysql> use zips Database changed Tables mysql> show tables; +----------------+ | Tables_in_zips | +----------------+ | zips | +----------------+ |
MongoDB: Databases
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | Databases > show dbs; admin 0.000GB local 0.000GB osm 13.528GB test 0.000GB zips 0.002GB > use zips switched to db zips Collections > show collections zips > show tables // same zips |
MySQL: Storage Engines
1 2 3 4 | MyISAM InnoDB TokuDB MyRocks |
MongoDB: Storage Engines
1 2 3 4 | MMAPv1: memory mapped WiredTiger: transactional+compression TokuMX / PerconaFT RocksDB |
MySQL: Processlist
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> show processlistG Id: 137259 User: root Host: localhost db: geonames Command: Query Time: 0 State: init Info: show processlist Rows_sent: 0 Rows_examined: 0 1 row in set (0.00 sec) |
MongoDB: CurrentOp()
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | > db.currentOp() { "inprog" : [ { "desc" : "conn28", "threadId" : "0x19b85260", "connectionId" : 28, "opid" : 27394208, "active" : true, "secs_running" : 3, "microsecs_running" : NumberLong(3210539), "op" : "query", "ns" : "osm.points3", "query" : { "name" : "Durham" }, "planSummary" : "COLLSCAN", "client" : "127.0.0.1:58835", "numYields" : 24905, "locks" : { "Global" : "r", "Database" : "r", "Collection" : "r" }, "waitingForLock" : false, ... } |
MySQL: Grants
1 2 3 | mysql> grant all on *.* to user@localhost identified by 'pass'; |
MongoDB: createUser
1 2 3 4 5 6 | > use products db.createUser( { user: "accountUser", pwd: "password", roles: [ "readWrite", "dbAdmin" ] }) |
MySQL: Index
1 2 3 4 5 6 7 8 9 10 11 12 13 | mysql> show keys from zipsG **** 1. row **** Table: zips Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE |
MongoDB: Index
1 2 3 4 5 6 7 8 9 10 11 | > db.zips.getIndexes() [ { "v" : 1, "key" : { "_id" : 1 }, "name" : "_id_", "ns" : "zips.zips" } ] |
MySQL: Add Index
1 2 3 4 | mysql> alter table zips add key (postal_code); Query OK, 0 rows affected (0.10 sec) Records: 0 Duplicates: 0 Warnings: 0 |
MongoDB: Create Index
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | > db.zips.createIndex({ state : 1 } ) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 1, "numIndexesAfter" : 2, "ok" : 1 } // Index can be sorted: > db.zips.createIndex({ state : -1 } ) { "createdCollectionAutomatically" : false, "numIndexesBefore" : 2, "numIndexesAfter" : 3, "ok" : 1 } |
MySQL: Explain
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | mysql> explain select * from zips where place_name = 'Durham'G **** 1. row **** id: 1 select_type: SIMPLE table: zips type: ref possible_keys: place_name key: place_name key_len: 183 ref: const rows: 25 Extra: Using index condition 1 row in set (0.00 sec) |
MongoDB: Explain
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 | > db.zips.find({"city": "DURHAM"}).explain() { "queryPlanner" : { "plannerVersion" : 1, "namespace" : "zips.zips", "indexFilterSet" : false, "parsedQuery" : { "city" : { "$eq" : "DURHAM" } }, "winningPlan" : { "stage" : "COLLSCAN", "filter" : { "city" : { "$eq" : "DURHAM" } }, "direction" : "forward" }, "rejectedPlans" : [ ] }, "serverInfo" : { ... }, "ok" : 1 } |
MySQL: Alter Table
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> alter table wikistats_innodb_n add url_md5 varbinary(16); Query OK, 0 rows affected (37 min 10.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> update wikistats_innodb_n set url_md5 = unhex(md5(lower(url))); Query OK, 85923501 rows affected (42 min 29.05 sec) Rows matched: 85923511 Changed: 85923501 ... |
MongoDB: Flexible schema
1 2 3 4 | No Alter statement. Just insert the new document version ... Different documents can have different schema versions |
MySQL: Slow Query Log
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> set global long_query_time = 0.1; Query OK, 0 rows affected (0.02 sec) mysql> set global slow_query_log = 1; Query OK, 0 rows affected (0.02 sec) mysql> show global variables like 'slow_query_log_file'G **** 1. row **** Variable_name: slow_query_log_file Value: /var/lib/mysql/slow.log 1 row in set (0.00 sec) |
MongoDB: Profiling
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | // db.setProfilingLevel(level, slowms) // Level: 0 = no profiling, // 1 = only slow ops // 2 = all ops // Slowms same as long_query_time // in milliseconds > db.setProfilingLevel(2, 100); { "was" : 0, "slowms" : 100, "ok" : 1 } > db.system.profile.find( { millis : { $gt : 100 } } ).pretty() { "op" : "query", "ns" : "zips.zips", "query" : { "city" : "DURHAM" }, "ntoreturn" : 0, .. |
MySQL: Percona Toolkit
1 2 | $ pt-query-digest --limit 100 slow.log > slow.log.report.txt |
MongoDB: Mtools
1 2 3 | $ mlogfilter mongo.log-20150915 --from 'Sep 14 06:00:00' --to 'Sep 14 23:59:59' | mlogvis --line-max 100000 --out 'mongo.html' |
MySQL 5.7: GIS
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | SELECT osm_id, name, round(st_distance_sphere(shape, st_geomfromtext( 'POINT (-78.9064543 35.9975194)', 1) ), 2) as dist, st_astext(shape) FROM points_new WHERE st_within(shape, create_envelope(@lat, @lon, 10)) and (other_tags like '%"amenity"=>"cafe"%' or other_tags like '%"amenity"=>"restaurant"%') and name is not null ORDER BY dist asc LIMIT 10; |
MongoDB 3.2: GIS
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | db.runCommand( { geoNear: "points", near: { type: "Point" , coordinates: [ -78.9064543, 35.9975194 ] }, spherical: true, query: { name: { $exists: true, $ne:null}, "other_tags": { $in: [ /.*amenity=>restaurant.*/, /.*amenity=>cafe.*/ ] } }, "limit": 5, "maxDistance": 10000 } ) |
MySQL: Backup
- Backup: mysqldump -A > dump.sql, restore: mysql < dump.sql
- Stop replication slave, copy files
- Percona XtraBackup
MongoDB: Backup
- Backup: mongodump, restore mongorestore
- Stop replica, copy files
- Percona HotBackup for TokuMX only
