Archive for the ‘database’ Category

How to run a flawless technical demo

Май 13th, 2012

Why demos?

For as long as I can remember in my public speaking activities, I have always planned my presentations with some sort of live demo in it. I am always surprised when a conference venue asks me to provide my slides in advance, to be loaded in an anonymous computer with no chance of demos. I always turn down such offers, as I always want to provide a demo.

There have been times when technical or time constraints prevented me from demoing something, and in these cases I felt that the presentation was lacking a vital part. But I always try. I have even given demos during lightning talks, and those were the ones that made me feel really good.

I have given hundreds of presentations, and hundreds of demos, and as in every human activity, I have made plenty of mistakes. I believe I have learned some valuable lesson from my mistakes, and this article is my attempt at sharing the joy with wannabe presenters and also with presenters who want to embrace this method.

So, why having a (risky) live demo in your presentation? Here are some of the reasons that may also appeal to you.

Show trust in your product

You want to talk about your product. The audience assumes that you have confidence in whichever product you want to talk about. However, if you limit your presentation to showing slides, no matter how beautiful and dynamic they are, the audience will be left with the dormant impression that you were talking about something not really trustworthy, or even (gasp!) unreal. If your audience start thinking that you are dealing with vaporware, nothing can dispel that thought faster and more convincingly than a demo. If you trust your product, then you should demo it. If you don't include a demo because you are afraid that the demo would fail, then don't present the topic. It's as simple as that.

Improve the entertainment level of your presentation

A demo makes a presentation more lively. The audience sees that you abandon the comfortable protection of your slide deck, where you are totally in control, and you risk your hide with a live demo of something that (as any technological artifact) can fail and blow up on your face, burning your reputation and your ego at the same time. Entertainment in a presentation is very important. As Guy Kawasaki said [1], if you make your presentation full of important things, chances are that people will forget all of them because your presentation will be boring, but if your make your presentation entertaining, then you can easily sneak in some important stuff, and the audience will remember that.

increase your reputation as a guru

Including live demos in your presentation will make the audience think of you as some sort of guru, and your reputation will grow. Now, I don't do demos for this reason. As I said before, I like presenting technical things with live examples. And then I realized that people attending my presentations had a high opinion of me, because of my demos. If not your reputation, live demos will increase your self confidence, and sometimes they amount to the same thing.

Demos DOs and DON’Ts

Down to business. A demo is not a casual happening. A successful demo has a long story behind it.

When you are on stage with a demo, you are not a boring presenter. You are a magician pulling rabbits from a top hat. You are a gymnast showing your dexterity. You are the center of attention, and success is within your grasp.

All this comes at a price, though. Read on.

DO: Master the topic

First and foremost. You must be really comfortable with the topic being presented. If you aren’t, it shows, and the audience will feel your fear. Therefore, the first requirement for a good demo is that you really understand what you are doing, and why. Not only because you are unlikely to demo successfully something that you don’t fully understand, but also because you will fall at the first question from the audience. (Incidentally, if a presenter maneuvers the presentation to prevent questions, it may be a sign of lack of self confidence, or even downright ignorance.)

DO: make a plan of what you want to show

Knowing your stuff doesn’t mean that you can convey your enthusiasm for the product to the audience just by showing some random commands. You must decide beforehand what you want to demo, and design a set of steps to follow during the demo. Think of the reasons why you believe your product is wonderful, and try to define these reasons as a set of examples that will make the audience share your feelings.

DO: include in the demo your product’s best features

When you plan, you have to give the audience the amazing stuff. If your slides claim that your product can make men walk on water, you will have to bring an inflatable pool on stage, fill it with water, bare your feet, and take a stroll in front of everyone. That’s a bit extreme, as we are dealing with software here, and your claims are, hopefully, less daring, but you get the gist. If you claim features that could be compared to walking on water, be prepared to show the miracle.

Whatever it is that your product has promised, you must show it live. The audience won’t be satisfied by your demo of secondary marvelous features if you don’t show evidence of your primary goods. There are exceptions, of course. If your goods require 30 minutes of processing to show their full potential, you can’t show all of it live. But you may try to give a reduced demo of whatever can be achieved in the time allotted for your presentation. One thing that I often do is start the presentation with a short demo where I get the process started, inform the audience that this process will take 30 minutes, and then get on with the slide show. 30 minutes later, I resume the demo, explain what has happened in the meantime, and finally I show the magic part. This is simple, honest, and very effective.

DO: Practice

Your experience with the product is not enough to guarantee a good demo. You must make sure that:

  • What you want to demo is actually feasible. If you promise something that your product can’t deliver, there is no amount of penance that can save your reputation;
  • You know how to perform the tasks that you have planned;
  • The tasks happen in a predictable way, so that you know that a given sequence of events will end up with the result that you want.
  • There are no side effects determined by other tasks running in your computer (or computers) that will prevent a positive result.

This means that you will repeat the demo several times, until you are satisfied that nothing can surprise you, and everything goes as planned. This phase is very important for you, and also for your product. You are likely to find important bugs when getting ready for a demo. Two birds with a stone!

DO: Time it!

Your time for a demo is short. No matter how much you want to show your product live, you can’t go beyond the time allotted for the whole presentation. More realistically, your demo will last from 1/4 to 2/3 of the presentation, with 1/3 being the more common duration. Thus, you need to make sure that your demo doesn’t run out of time. Especially if your punch line is at the end of your demo, you won’t be able to show it if the attendees are rushing from your room to attend the next presentation.

Have a plan B

Despite your preparation, there are things that may happen that will keep you longer than expected at your demo, and you may find yourself short of time. Then you need to have an alternative demo plan, i.e. a shorter demo that you can show from that moment on instead of the original one. What this means is that you need to practice two plans. And maybe three. Such is life!

DO: Practice some more - Make sure your demo is visible

When you practice, you are looking at your computer and you may think that what you see is the same thing that your audience will see. Don’t make this assumption! When you are on stage, things are much different from what you see at home.

In person, in a large ballroom

When you are using a projector, or an external screen, you may have a different experience from what you had at home or at the office, with your dedicated 24in screen, where you did prepare a beautiful demo. If the projector has a maximum resolution of 1024x768 (which is quite common nowadays) or even 800x600, you must review your demo, and be ready to scale down your ambitions. What you need to do:

  • find out in advance, days or weeks before the presentation, if possible, what kind of projector you will be dealing with, and try to test with the same resolution.
  • When you are at the venue, test with the projector before the presentation, and make sure that your demo is visible from every seat in the room. Adjust your demo if needed.
  • If there is no advance testing time allocated, grab an apple or a sandwich and do it at breakfast or lunch time. Skipping a meal is less important than risking your reputation.

Online, when giving a webinar

When you are presenting online, in addition to the resolution of the software delivering your webinar, there is also the possibility of more limitations or complex setups that will stand in the way of a successful demo. You will need to test the webinar software, possibly with two computers: one to deliver the demo, and one to check what another attendee would see. Don’t ever accept a denial along the lines of “we can’t do a dry run, but the software is a piece of cake, nothing can go wrong.” You know that everything can go wrong, so insist and make sure that you get testing time. Cancel the demo if you can’t get it.

DON’T: make mistakes

This seems an unnecessary recommendation. It goes together with Practice your demo. But we need to stress some points in the matter of mistakes. There are simple mistakes, like misspelling a command when you are typing (I do a lot of SEELCT instead of SELECT), but this kind of mistakes are not the ones that get you in trouble. They may even increase the audience awareness that they are witnessing a live event,

The mistakes you must avoid are the ones that make the demo fail; the ones that may show your lack of familiarity with the product (which won’t happen if you have been practicing). Therefore: focus on the task, and you will win.

There are, though, mistakes that you can include in your demo. If one of your product’s features is the ability to recover from mistakes, you can include such mistakes in your demo, provided that:

  • You tell the audience beforehand that you are going to make a deliberate mistake, just to show how your product can save your butt. (You may also try the theatrical trick of making the error and then emphatically announce that you did that on purpose. The result really depends on how good your theatrics are.)
  • You include this mistake in your demo plan, and you practice it as thoroughly as you did the rest.

DON’T: Run other applications in background during the demo

Depending on the product you are showing, there are many ways of spoiling the demo through applications that run when they should not. Let me give you a non-comprehensive list:

  • A Skype balloon saying I miss you honey bunny will not improve your credibility;
  • Twitter and Facebook notifications with more or less embarrassing remarks should be also avoided;
  • Your computer starts a file reindex when you are showing a resource intensive task using three virtual machines, and performance drops to a crawl;
  • The remote server that you are using for your demo goes down for maintenance;
  • A planned backup starts in youd database server right when you need it to be responsive at its best;
  • A daily test starts on your remote server, and removes your demo setup.

There are more, and more. If you can think of it, t may happen!

DON’T: Deviate from your well rehearsed script

Once you have defined a demo plan, stick to it. Make no exceptions. If you must make exceptions, you must plan for them as well. Therefore: make no exceptions. This recommendation closely resembles the next one.

DON’T: Make some brilliant improvements at the last minute

You are an expert in your field, and an expert of the product that you are presenting. You may also be one of the developers of that project. It is thus very natural and common that you think of improvements that will make your product behave much better. That’s good and commendable. But don’t make these changes on the build that you will use for the demo. NEVER. EVER.

I did it. A few times. And I regretted it. Every time.

If you make a change, then you must have time to test the whole demo from scratch, more than once, or else you must wait to apply your changes after the demo. Similarly, you may think of an improvement of the demo. If that implies deviating from the plan that you have tested, don’t do it, unless you have time to test the whole demo again with the change.

Summing up

Doing a live demo is a lot of work, and what you show on stage is only a tiny part of the work involved. But I can assure you that the thrill of having a flawless demo that amazes the audience is deeply fulfilling. I recommend it to all the public speakers.

Try it. And then you will be hooked. At my company, we all are.


  1. I don’t remember where I read it, as I have read many books and articles by Guy Kawasaki, but I think it was in Reality Check.  ↩


PlanetMySQL Voting: Vote UP / Vote DOWN

Compare and Synchronize Databases with MySQL Utilities

Май 10th, 2012
The mysqldiff and mysqldbcompare utilities were designed to produce a difference report for objects and in the case of mysqldbcompare the data. Thus, you can compare two databases and produce a report of the differences in both object definitions and data rows. 

While that may be very useful, would it not be much more useful to have the ability to produce SQL commands to transform databases? Wait no longer! The latest release of MySQL Utilities has added the ability to generate SQL transformation statements by both the mysqldiff and mysqldbcompare utilities. 

To generate SQL transformations in either utility, simply use the --sql option to tell the utility to produce the statements.

Object Transformations with mysqldiff

If you would like to compare the schema of two databases (the objects and their definitions), mysqldiff can do that for you and produce a difference report in a number of formats including CSV, TAB, GRID, and Vertical (like the mysql client’s \G option).

However, its greatest feature is the ability to generate transformation statements to alter the objects so that they conform. Best of all, mysqldiff works on all object types including the ability to recognize renames so you can get a true transformation path for all objects. For even greater flexibility, you can generate the difference in both directions. This means you can generate transformations for db1-to-db2 as well as db2-to-db1 in the same pass. Cool.

The following shows an example of running mysqldiff on two servers where some of the objects have diverged. It also shows how you can generate the reverse transformation statements.


$ mysqldiff --server1=root@localhost --server2=root@otherhost \
--changes-for=server1 --show-reverse util_test:util_test \
--force --difftype=SQL
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# WARNING: Objects in server1.util_test but not in server2.util_test:
# EVENT: e1
# Comparing util_test to util_test [PASS]
# Comparing util_test.f1 to util_test.f1 [PASS]
# Comparing util_test.p1 to util_test.p1 [PASS]
# Comparing util_test.t1 to util_test.t1 [PASS]
# Comparing util_test.t2 to util_test.t2 [PASS]
# Comparing util_test.t3 to util_test.t3 [FAIL]
# Transformation for --changes-for=server1:
#
ALTER TABLE util_test.t3
DROP COLUMN b,
ADD COLUMN d char(30) NULL AFTER a
ENGINE=MyISAM;
#
# Transformation for reverse changes (--changes-for=server2):
#
# ALTER TABLE util_test.t3
# DROP COLUMN d,
# ADD COLUMN b char(30) NULL AFTER a,
# ENGINE=InnoDB;
#
# Comparing util_test.trg to util_test.trg [FAIL]
# Transformation for --changes-for=server1:
#
DROP TRIGGER IF EXISTS `util_test`.`trg`;
CREATE DEFINER=root@localhost TRIGGER util_test.trg BEFORE UPDATE ON util_test.t1
FOR EACH ROW INSERT INTO util_test.t1 VALUES('Wax on, wax off');
#
# Transformation for reverse changes (--changes-for=server2):
#
# DROP TRIGGER IF EXISTS `util_test`.`trg`;
# CREATE DEFINER=root@localhost TRIGGER util_test.trg AFTER INSERT ON util_test.t1
# FOR EACH ROW INSERT INTO util_test.t2 VALUES('Test objects count');
#
# Comparing util_test.v1 to util_test.v1 [FAIL]
# Transformation for --changes-for=server1:
#
ALTER VIEW util_test.v1 AS
select `util_test`.`t2`.`a` AS `a` from `util_test`.`t2`;
#
# Transformation for reverse changes (--changes-for=server2):
#
# ALTER VIEW util_test.v1 AS
# select `util_test`.`t1`.`a` AS `a` from `util_test`.`t1`;
#
Compare failed. One or more differences found.

Generating Data Transformation with mysqldbcompare

The mysqldbcompare utility provides all of the object difference functionality included in mysqldiff along with the ability to generate transformation SQL statements for data. This means you can make sure your test or development databases are similar to your production databases or perhaps even your offline, read only databases match your online databases. Like mysqldiff, you can also get the reverse transformations at the same time. Very cool, eh?

The following shows an example of running mysqldbcompare to generate differences in data.

$ mysqldbcompare --server1=root@localhost --server2=root@otherhost \
inventory:inventory -a --difftype=sql --changes-for=server1 \
--show-reverse
# server1 on localhost: ... connected.
# server2 on localhost: ... connected.
# Checking databases inventory on server1 and inventory on server2
#
# WARNING: Objects in server1.inventory but not in server2.inventory:
# VIEW: finishing_up
# VIEW: cleaning
#

[...]

# TABLE supplier pass FAIL FAIL
#
# Row counts are not the same among inventory.supplier and inventory.supplier.
#
# Transformation for --changes-for=server1:
#
# Data differences found among rows:
UPDATE inventory.supplier SET name = 'Wesayso Corporation' WHERE code = '2';
INSERT INTO inventory.supplier (code, name) VALUES('3', 'Never Enough Inc.');
#
# Transformation for reverse changes (--changes-for=server2):
#
# # Data differences found among rows:
# UPDATE inventory.supplier SET name = 'Never Enough Inc.' WHERE code = '2';
# DELETE FROM inventory.supplier WHERE code = '3';
#
# Database consistency check failed.
#
# ...done

PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Utilities Frequently Asked Questions

Май 10th, 2012

Momentum for MySQL Utilities continues to build.  I hosted a webinar recently about MySQL Utilities (available on-demand from the link below), which generated a lot of interest and some good questions.

http://dev.mysql.com/doc/workbench/en/mysql-utilities.html

General

Are these utilities present in the community version of MySQL?

They are included in the community edition of the MySQL Workbench product, which can be downloaded from the following link.

http://www.mysql.com/downloads/workbench/

Should/can we run this on live data?

Yes. Naturally, you would want to test some operations before jumping directly into a production environment. For example, you may want to test any database migration, transformation, or similar massive change in a test environment.

Can we use the utilities in a production environment under the GPL license?

Yes. MySQL Utilities is part of MySQL Workbench so all such licenses apply accordingly.

Storage Engines

Can the utilities be used with MyISAM or CSV?

Yes. There are no storage engine specific limitations in using the utilities. There are some features written specifically for InnoDB so those may not apply but in general no utility is storage engine specific. For example, the mysqldiskusage utility shows exact sizes for MyISAM and InnoDB files but uses estimated sizes for any other storage engine based on number of rows and row size.

Platforms

Can I use MySQL Utilities on Linux?

Yes. MySQL Utilities runs on all platforms supported by MySQL Workbench.

Can the utilities be used on Windows?

Yes!

Do the utilities work both for window-based and linux-based servers?

Yes! They work for any server hosting MySQL.

Installation

Do we have to install the utilities with rpm or can we use the tar ball extract and run from there?

MySQL Utilities is installed as part of MySQL Workbench. You can download and install Workbench using several platform-specific installers.

You can also branch and download MySQL Utilities from Launchpad. You can also build and install it from the source code you’ve downloaded using typical Python install steps (python ./setup.py install).

https://launchpad.net/mysql-utilities

What's the link to download these utilities?

MySQL Utilities is part of MySQL Workbench. You can download MySQL Workbench from the following link.

http://www.mysql.com/downloads/workbench/

Locking

Do the utilities lock tables while running?

Yes, but only for situations that require locks. The mysqldbexport utility also allows you to specify what type of lock to use:

no-locks = do not use any table locks

lock-all = use table locks but no transaction and no consistent read

snaphot (default) = consistent read using a single transaction.

Are there any utilities that can show DB locks (like which query is blocking which one)?

No, not currently but that is an excellent suggestion!

mysqldbcompare

How fast is mysqldbcompare? Say a table with 10 million rows?

It is difficult to predict a precise estimate of run time based on number of rows. However, it is generally such that the more rows there are the longer the utility will run. The mysqldbcompare utility is used to produce a difference of two databases. It creates a difference between objects of the same name for either object definitions, data, or both. When comparing object definitions, the performance is very fast because there isn’t a lot of processing involved. When comparing data, the utility uses an algorithm to create checksums for each row in the table. During this phase, the tables are locked. Once that stage is done, the tables are unlocked and the algorithm begins to compact the checksums into chunks, which are later compared between the servers. If the checksums differ, the chunks are expanded and the differences calculated. Thus, for tables containing millions of rows the utility will take some time to complete. The best time to run this utility is during low usage periods such as times reserved for upgrades, backups, and similar operations.

How will running mysqldbcompare effect a production database?

If generating a difference for data, the utility will lock the tables long enough to calculate a checksum for each row. Depending on the number of rows this could be for a long time and in those cases you should run mysqldbcompare during low usage periods. The utility will use a consistent read to lock InnoDB tables but will issue table locks for non-InnoDB tables.

Will mysqldbcompare cause table locking on MyISAM table?

Yes. A table lock is issued during checksum creation.

What is the load going to be on the servers when mysqldbcompare runs?

The load on the server itself is minimal. There is moderate CPU usage during checksum creation but nothing that should cause a problem. The longest period of activity is when the table scans are executed for creating a checksum for each row.

mysqldbexport

Is mysqldbexport similar to mysqldump?

Yes, the mysqldbexport is designed to export data in a row-by-row or logical fashion. However, you can export data in CSV, TAB, Vertical formats as well as SQL statements using CREATE TABLE, INSERT, etc. making mysqldbexport more versatile than mysqldump. You would use mysqldbexport in situations where you need special machine or human readable output for operations like transforming the data or examining the structures in more detail – especially if you need a format other than SQL statements.

Replication Utilities

Are the high availability features available for version 5.5 or 5.6?

The general replication utilities such as mysqlrplcheck and mysqlreplicate will work with servers version 5.0 and later. The newest high availability feature, failover, in mysqlrpladmin and mysqlfailover work only for servers that support global transaction identifiers (GTIDs) which were added in version 5.6.5.

You can discover more about GTIDs from the following blog by Luis Soares.

http://d2-systems.blogspot.co.uk/2012/04/global-transaction-identifiers-are-in.html

Where do I get more info about mysqlrpladmin?

The online MySQL Workbench Manual has information about each utility. You can also use the --help option to show all options and their descriptions.

http://dev.mysql.com/doc/workbench/en/mysql-utilities.html

How can I use the utilities to test replication on a single host?

You can use mysqlserverclone to clone an existing, running instance of MySQL or clone from an installation (basedir), then mysqlreplicate to create the replication topology.

Is the replication failover feature only for version 5.6?

Yes. It requires support for global transaction identifiers, which were added in version 5.6.5. A developer milestone release of 5.6 is available for download.

http://dev.mysql.com/downloads/mysql/#downloads

(Select the Development Releases tab)

What features of mysqlrpladmin will work on version 5.5?

All of the features except slave election and failover.

Do you need to create a replication user on the slave site other than the master?

The mysqlreplicate utility provides an option to use a specific user on the master for replication or it will create a user by default. You can also request that a new user be created during the operation.


PlanetMySQL Voting: Vote UP / Vote DOWN

Simple and efficient MongoDB Backup using script

Май 9th, 2012

MongoDB Backup types and strategies are neatly explained in its documentation, which you can check here. In case you are not familiar with MongoDB backup types and strategies, please have a look at its documentation.

What I am describing here is a simple script which we are using since months to take MongoDB backup and transfer it over to our Backup server. Here are few things its doing:

  • As we have multiple MongoDB Replica Sets, the script identify current replica set and check whether current server is Master or Slave, exit if its Master. We take backup only from Slave host.
  • Take Backup using mongodump command.
  • Upon successful completion of dump, transfer that to our Backup server. Ensure that ssh key based authentication is setup between both servers to implement seamless and secure transfer. It creates new directory based on current timestamp under replicaset directory in specified path at Backup server and transfer dump there.
  • Log each steps described above, send alert mail if any step fail with description or send confirmation mail upon successful execution with essential details.
  • Sample confirmation mail is below:
Subject: Backup for  done on  at 09/May/12 08:00:01 AM
Body: Mongo Backup Status for  on 09/May/12 08:00:01 AM. 
 
08:00:01 AM:  is slave and looks OK.
08:00:01 AM: Starting Dump, executing /usr/local/mongodb/mongo/bin/mongodump --out /databases/dump --host ...
08:34:06 AM: Mongodump command completed. Backup size is 25G. 
08:34:10 AM: Directory created on backup server, copying data using scp...
08:57:00 AM: Copied dump to backup server in directory /home/backup/Mongodump/replicaset/datestamp/.
08:57:00 AM: Mongo Backup process completed successfully.

Here is the full bash script, please note that you need to update variables properly and have to check and update it to run in your environment which can be entirely different from mine. Its just an idea to automate MongoDB backup:

##
# Script to take mongo backup using mongodump and store it in Backup Server
##
 
#!/bin/bash
 
## Set variables
TodayDate=`date +"%d/%b/%g %r"`
DateStamp=`date +%d%m%y%H%M%S`
CurrentTime=`date +"%r"`
 
MongoBinPath="/usr/local/mongodb/mongo/bin"
ReplicaSet=`echo 'rs.status()' | $MongoBinPath/mongo | egrep "set" | awk -F \" '{print $4}'`
MongoHost=`hostname`
LocalBackupPath="/databases/dump"
 
LogFile="/var/log/mongo-backup.log"
IsOK=0
CmdStatus=""
 
BackupHost=xx.xx.xx.xx
BackupHostPath="/home/backup/mongobackup"
BackupHostPort=22
 
MailNotification="admin@domain.com anotheradmin@domain.com"
 
echo -e "Mongo Backup Status for $ReplicaSet on $TodayDate. \n" > $LogFile
 
## Check whether host is slave and in good state for backup
for i in `echo "rs.status()" | $MongoBinPath/mongo | egrep "name" | awk -F \" '{print $4}'| cut -f 1 -d :`; do
 IsMaster=`echo "db.isMaster()"| $MongoBinPath/mongo --host $i | grep ismaster|awk -F ":" '{print $2}' | cut -f 1 -d ,`;
 TheState=`echo "rs.status()"| $MongoBinPath/mongo --host $i | grep -i mystate | awk -F ":" '{print $2}' | cut -f 1 -d ,`;
 if [ $IsMaster == "false" -a $TheState -eq 2  ]; then
  MongoHost=$i
  IsOK=1
  echo "$CurrentTime: $MongoHost is slave and looks OK." >> $LogFile
  break
 fi
done
 
CurrentTime=`date +"%r"`
 
## Exit if not good
if [ $IsOK -eq 0 ]; then
   echo "$CurrentTime: Error: Either $MongoHost is not slave or not in good state. Aborting Backup, Please check!" >> $LogFile
   mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile    exit 1; fi ## Remove earlier backup CmdStatus=$(rm -rf $LocalBackupPath/*) ## Start backup process echo "$CurrentTime: Starting Dump, executing $MongoBinPath/mongodump --out $LocalBackupPath --host $MongoHost..." >> $LogFile
 
CmdStatus=`$MongoBinPath/mongodump --out $LocalBackupPath --host $MongoHost`
if [ $? -ne 0 ]; then
  echo "$CurrentTime: There is an issue while trying to take dump in $MongoHost. Aborting dump process, please check! " >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` BackupSize=$(du -sh $LocalBackupPath/. | awk '{ print $1 }') echo "$CurrentTime: Mongodump command completed. Backup size is $BackupSize. " >> $LogFile
 
## dump is fine then scp it to backup server
## create directory first
CmdStatus=$(ssh -p $BackupHostPort $BackupHost "mkdir -p  $BackupHostPath/$ReplicaSet/$DateStamp")
 
if [ $? -ne 0 ]; then
  echo "$CurrentTime: Either failing to connect Backup server using ssh or destination directory already exist!" >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` echo "$CurrentTime: Directory created on backup server, copying data using scp..." >> $LogFile
 
CmdStatus=`scp -P $BackupHostPort -r $LocalBackupPath/* $BackupHost:/$BackupHostPath/$ReplicaSet/$DateStamp/`
if [ $? -ne 0 ]; then
  echo "$CurrentTime: Unable to scp dump to $BackupHost:/$BackupHostPath/$ReplicaSet/$DateStamp using port $BackupHostPort. " >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` echo "$CurrentTime: Copied dump to backup server in directory $BackupHost$BackupHostPath/$ReplicaSet/$DateStamp/." >> $LogFile
echo "$CurrentTime: Mongo Backup process completed successfully." >> $LogFile
mail -s "Backup for $ReplicaSet done on $MongoHost at $TodayDate" $MailRecipients $MailNotification < $LogFile

Its just a basic script and may needs further enhancements. In case you have suggestion/queries, please put it below in comments.

More Related and helpful articles:

 


PlanetMySQL Voting: Vote UP / Vote DOWN

Simple and efficient MongoDB Backup using script

Май 9th, 2012

MongoDB Backup types and strategies are neatly explained in its documentation, which you can check here. In case you are not familiar with MongoDB backup types and strategies, please have a look at its documentation.

What I am describing here is a simple script which we are using since months to take MongoDB backup and transfer it over to our Backup server. Here are few things its doing:

  • As we have multiple MongoDB Replica Sets, the script identify current replica set and check whether current server is Master or Slave, exit if its Master. We take backup only from Slave host.
  • Take Backup using mongodump command.
  • Upon successful completion of dump, transfer that to our Backup server. Ensure that ssh key based authentication is setup between both servers to implement seamless and secure transfer. It creates new directory based on current timestamp under replicaset directory in specified path at Backup server and transfer dump there.
  • Log each steps described above, send alert mail if any step fail with description or send confirmation mail upon successful execution with essential details.
  • Sample confirmation mail is below:
Subject: Backup for  done on  at 09/May/12 08:00:01 AM
Body: Mongo Backup Status for  on 09/May/12 08:00:01 AM. 
 
08:00:01 AM:  is slave and looks OK.
08:00:01 AM: Starting Dump, executing /usr/local/mongodb/mongo/bin/mongodump --out /databases/dump --host ...
08:34:06 AM: Mongodump command completed. Backup size is 25G. 
08:34:10 AM: Directory created on backup server, copying data using scp...
08:57:00 AM: Copied dump to backup server in directory /home/backup/Mongodump/replicaset/datestamp/.
08:57:00 AM: Mongo Backup process completed successfully.

Here is the full bash script, please note that you need to update variables properly and have to check and update it to run in your environment which can be entirely different from mine. Its just an idea to automate MongoDB backup:

##
# Script to take mongo backup using mongodump and store it in Backup Server
##
 
#!/bin/bash
 
## Set variables
TodayDate=`date +"%d/%b/%g %r"`
DateStamp=`date +%d%m%y%H%M%S`
CurrentTime=`date +"%r"`
 
MongoBinPath="/usr/local/mongodb/mongo/bin"
ReplicaSet=`echo 'rs.status()' | $MongoBinPath/mongo | egrep "set" | awk -F \" '{print $4}'`
MongoHost=`hostname`
LocalBackupPath="/databases/dump"
 
LogFile="/var/log/mongo-backup.log"
IsOK=0
CmdStatus=""
 
BackupHost=xx.xx.xx.xx
BackupHostPath="/home/backup/mongobackup"
BackupHostPort=22
 
MailNotification="admin@domain.com anotheradmin@domain.com"
 
echo -e "Mongo Backup Status for $ReplicaSet on $TodayDate. \n" > $LogFile
 
## Check whether host is slave and in good state for backup
for i in `echo "rs.status()" | $MongoBinPath/mongo | egrep "name" | awk -F \" '{print $4}'| cut -f 1 -d :`; do
 IsMaster=`echo "db.isMaster()"| $MongoBinPath/mongo --host $i | grep ismaster|awk -F ":" '{print $2}' | cut -f 1 -d ,`;
 TheState=`echo "rs.status()"| $MongoBinPath/mongo --host $i | grep -i mystate | awk -F ":" '{print $2}' | cut -f 1 -d ,`;
 if [ $IsMaster == "false" -a $TheState -eq 2  ]; then
  MongoHost=$i
  IsOK=1
  echo "$CurrentTime: $MongoHost is slave and looks OK." >> $LogFile
  break
 fi
done
 
CurrentTime=`date +"%r"`
 
## Exit if not good
if [ $IsOK -eq 0 ]; then
   echo "$CurrentTime: Error: Either $MongoHost is not slave or not in good state. Aborting Backup, Please check!" >> $LogFile
   mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile    exit 1; fi ## Remove earlier backup CmdStatus=$(rm -rf $LocalBackupPath/*) ## Start backup process echo "$CurrentTime: Starting Dump, executing $MongoBinPath/mongodump --out $LocalBackupPath --host $MongoHost..." >> $LogFile
 
CmdStatus=`$MongoBinPath/mongodump --out $LocalBackupPath --host $MongoHost`
if [ $? -ne 0 ]; then
  echo "$CurrentTime: There is an issue while trying to take dump in $MongoHost. Aborting dump process, please check! " >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` BackupSize=$(du -sh $LocalBackupPath/. | awk '{ print $1 }') echo "$CurrentTime: Mongodump command completed. Backup size is $BackupSize. " >> $LogFile
 
## dump is fine then scp it to backup server
## create directory first
CmdStatus=$(ssh -p $BackupHostPort $BackupHost "mkdir -p  $BackupHostPath/$ReplicaSet/$DateStamp")
 
if [ $? -ne 0 ]; then
  echo "$CurrentTime: Either failing to connect Backup server using ssh or destination directory already exist!" >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` echo "$CurrentTime: Directory created on backup server, copying data using scp..." >> $LogFile
 
CmdStatus=`scp -P $BackupHostPort -r $LocalBackupPath/* $BackupHost:/$BackupHostPath/$ReplicaSet/$DateStamp/`
if [ $? -ne 0 ]; then
  echo "$CurrentTime: Unable to scp dump to $BackupHost:/$BackupHostPath/$ReplicaSet/$DateStamp using port $BackupHostPort. " >> $LogFile
  cat $CmdStatus >> $LogFile
  mail -s "Backup error for $ReplicaSet from $MongoHost on $TodayDate" $MailNotification < $LogFile   exit fi CurrentTime=`date +"%r"` echo "$CurrentTime: Copied dump to backup server in directory $BackupHost$BackupHostPath/$ReplicaSet/$DateStamp/." >> $LogFile
echo "$CurrentTime: Mongo Backup process completed successfully." >> $LogFile
mail -s "Backup for $ReplicaSet done on $MongoHost at $TodayDate" $MailRecipients $MailNotification < $LogFile

Its just a basic script and may needs further enhancements. In case you have suggestion/queries, please put it below in comments.

More Related and helpful articles:

 


PlanetMySQL Voting: Vote UP / Vote DOWN

Can’t Travel to Collaborate 12? Plug-in Virtually Instead! (revised schedule)

Апрель 17th, 2012
  Plug-in to Vegas The program focuses on key topics such as high availability, virtualization, security, business intelligence, Exadata, Cloud Computing and internals.  Recently added, we switched around the schedule to include the Thursday Deep Dive, Avoiding Downtime through the Maximum … Continue reading
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Conference 2012 – keynotes on day 2 (3)

Апрель 12th, 2012
A panel on “Future Perfect: The Road Ahead for MySQL” Brian Aker (HP), Paul Mikesell (Clustrix), Sundar Raghavan (Amazon), Slavik Markovich (McAffee), Ori Hernstadt (Akiban) If there’s one common theme to this panel, and indeed, this whole conference, it is “We’re hiring!” It is amazing how much talent there is at the conference this year [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Conference 2012 – keynotes on day 2 (1)

Апрель 12th, 2012
An exciting and busy day yesterday – lots of good talks, good conversations and good beer! Back at the sessions this morning and the first keynote of the day by Sam Ghods of Box: “MySQL: Still the Best Choice for Mission-Critical Data” The usual story of a (file sharing) application that started out on one [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

MySQL Conference 2012 – The Keynotes (1)

Апрель 11th, 2012
Here it is finally – the MySQL conference 2012 starts with the Keynote Sessions. The first keynote speech is by Peter Zaitsev, founder of Percona and a very smart guy and also by Baron Schwartz (Percona), another very smart guy, the brains behind a number of toolkits for MySQL. They’re talking about the MySQL Evolution [...]
PlanetMySQL Voting: Vote UP / Vote DOWN

List MySQL Indexes With INFORMATION_SCHEMA

Апрель 4th, 2012

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]‘? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

This query uses the INNODB_SYS_TABLES, INNODB_SYS_INDEXES, and INNODB_SYS_FIELDS tables from INFORMATION_SCHEMA and is only available in MySQL 5.6 or Percona Server 5.5. However, it is much much faster than querying the STATISTICS table. It also only shows InnoDB tables.

SELECT t.name AS `Table`,
       i.name AS `Index`,
       GROUP_CONCAT(f.name ORDER BY f.pos) AS `Columns`
FROM information_schema.innodb_sys_tables t
JOIN information_schema.innodb_sys_indexes i USING (table_id)
JOIN information_schema.innodb_sys_fields f USING (index_id)
WHERE t.schema = 'sakila'
GROUP BY 1,2
ORDER BY 1,2;

Assuming that all your tables are InnoDB, both queries will produce identical results. If you have some MyISAM tables in there, only the first query will provide complete results.

+---------------+-----------------------------+--------------------------------------+
| Table         | Index                       | Columns                              |
+---------------+-----------------------------+--------------------------------------+
| actor         | idx_actor_last_name         | last_name                            |
| actor         | PRIMARY                     | actor_id                             |
| address       | idx_fk_city_id              | city_id                              |
| address       | PRIMARY                     | address_id                           |
| category      | PRIMARY                     | category_id                          |
| city          | idx_fk_country_id           | country_id                           |
...
| rental        | rental_date                 | rental_date,inventory_id,customer_id |
| staff         | idx_fk_address_id           | address_id                           |
| staff         | idx_fk_store_id             | store_id                             |
| staff         | PRIMARY                     | staff_id                             |
| store         | idx_fk_address_id           | address_id                           |
| store         | idx_unique_manager          | manager_staff_id                     |
| store         | PRIMARY                     | store_id                             |
+---------------+-----------------------------+--------------------------------------+
42 rows in set (0.04 sec)

PlanetMySQL Voting: Vote UP / Vote DOWN