MFA/2FA is a great feature for any service. Google's implementation is particularly nice. It's theoretically possible to circumvent if a hacker gains access to your cookies, but I have yet to hear of that being done en masse.
The application-specific passwords are an awesome addition. Even if your laptop is stolen, intruders still don't have access to your real password. You can easily deactivate passwords that have been compromised. Resist the temptation to reuse app-specific passwords: it defeats the purpose. Although someone with an app-specific password or your cookies can gain access to your account, it's only temporary, and you can easily invalidate the cookie/password. Rather importantly, they can't change your password to hijack your account.
You can only use your real password in applications that explicitly support Google's 2FA. Most applications let Google handle authentication: they'll give you an embedded web browser with a Google login page. When you're logging into most Google websites, including the embedded authentication page, you can use your real password. Behind the scenes, Google creates a temporary password for the application or website that you're using, much like an app-specific password. A notable desktop application that works this way is Google Chrome, as you would probably expect.
When you're logging into applications that require a traditional password--in particular, e-mail programs that use IMAP/SMTP--you'll need to create an application-specific password manually.
If you want to get really fancy, you can get a hardware MFA device. These are super-secure--more secure than text messages or Google Authenticator. They also make you look like a computer ninja. Here's a picture of one type that I've used, though there are lots of different kinds:
(I'm not sure that those work with Google specifically, as I haven't tested that type with Google. They're pretty generic, though.)
We've had some requests to add MFA support to NamePros. I think it's a great idea, so hopefully we'll get around to that in the future. (Remind me if I forget!)