MongoDB Administration Checklist for MySQL DBAs
If you are MySQL DBA, starting MongoDB administration is not always an easy transition. This post provides a checklist for MongoDB administrators who are familiar with MySQL.
Join the DZone community and get the full member experience.
Join For FreeIn 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
select*from zips limit1G
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:
1row inset(0.00sec)
MongoDB: FIND
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
CREATE TABLE users(
idMEDIUMINT NOTNULLAUTO_INCREMENT,
user_id varchar(30),
age Number,
status char(1),
PRIMARY KEY(id)
MongoDB: Flexible Schema
db.users.insert({
user_id:"abc123",
age:55,
status:"A"}
MySQL: Config file
MongoDB:
/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
Databases
mysql>show databases;
+--------------------+
|Database|
+--------------------+
|information_schema|
...
mysql>usezips
Database changed
Tables
mysql>show tables;
+----------------+
|Tables_in_zips|
+----------------+
|zips|
+----------------+
MongoDB: Databases
Databases
>show dbs;
admin0.000GB
local0.000GB
osm13.528GB
test0.000GB
zips0.002GB
>usezips
switched todb zips
Collections
>show collections
zips
>show tables//same
zips
MySQL: Storage Engines
MyISAM
InnoDB
TokuDB
MyRocks
MongoDB: Storage Engines
MMAPv1:memory mapped
WiredTiger:transactional+compression
TokuMX/PerconaFT
RocksDB
MySQL: Processlist
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
1row inset(0.00sec)
MongoDB: CurrentOp()
>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
mysql>grant all on*.*
touser@localhost
identified by'pass';
MongoDB: createUser
>useproducts
db.createUser({
user:"accountUser",
pwd:"password",
roles:["readWrite","dbAdmin"]
MySQL: Index
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
>db.zips.getIndexes()
"v":1,
"key":{
"_id":1
"name":"_id_",
"ns":"zips.zips"
MySQL: Add Index
mysql>alter table zips
add key(postal_code);
Query OK,0rows affected(0.10sec)
Records:0Duplicates:0Warnings:0
MongoDB: Create Index
>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
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
1row inset(0.00sec)
MongoDB: Explain
>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
mysql>alter table wikistats_innodb_n
add url_md5 varbinary(16);
Query OK,0rows affected(37min10.03sec)
Records:0Duplicates:0Warnings:0
mysql>update wikistats_innodb_n
set url_md5=unhex(md5(lower(url)));
Query OK,
85923501rows affected(42min29.05sec)
Rows matched:85923511Changed:85923501...
MongoDB: Flexible schema
No Alter statement. Just insert the newdocument version...
Different documents can have different schema versions
MySQL: Slow Query Log
mysql>set global long_query_time=0.1;
Query OK,0rows affected(0.02sec)
mysql>set global slow_query_log=1;
Query OK,0rows affected(0.02sec)
mysql>show global variables
like'slow_query_log_file'G
****1.row****
Variable_name:slow_query_log_file
Value:/var/lib/mysql/slow.log
1row inset(0.00sec)
MongoDB: Profiling
//db.setProfilingLevel(level,slowms)
//Level:0=no profiling,
//1=only slow ops
//2=all ops
//Slowms same aslong_query_time
//inmilliseconds
>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
$pt-query-digest--limit100slow.log
>slow.log.report.txt
MongoDB: Mtools
$mlogfilter mongo.log-20150915
--from'Sep 14 06:00:00'--to'Sep 14 23:59:59'
|mlogvis--line-max100000--out'mongo.html'
MySQL 5.7: GIS
SELECT osm_id,name,
round(st_distance_sphere(shape,
st_geomfromtext(
'POINT (-78.9064543 35.9975194)',1)
),2)asdist,
st_astext(shape)
FROM points_new
WHERE
st_within(shape,
create_envelope(@lat,@lon,10))
and(other_tags
like'%"amenity"=>"cafe"%'
orother_tags
like'%"amenity"=>"restaurant"%')
andname isnotnull
ORDER BY dist asc LIMIT10;
MongoDB 3.2: GIS
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
Published at DZone with permission of Alexander Rubin, DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.
Comments