When you set your password, the password isn't stored, the hash is. When you log in, the login program just hashes the password you enter and compares that against the hash in the database. If they are the same, then the odds of your having entered the wrong password and gotten the right hash are so infinitesimal that it's not worth worrying about. MD5 is a 128 bit hash, which means that it's 'number space' is 2 to the 128th power. So basically you could assign one of it's numbers to every star in the known universe.
In reality what goes on is a bit more complex, in a networked environment anyway. Various systems use various schemes, but the one I use in CQC is pretty standard. When you log into a CQC client app, it hashes your password, but if it just sent that over the wire, anyone could pick it up and they'd have your password hash. That's still not perfect for them, but it helps them a lot in breaking into your account.
So what happens is you press enter to logon after you've entered the name/password. The client app sends a logon request to the server, but it doesn't send any password info yet, just the user name, and the server will look up that user in the database and if found will read in the user's password hash that it's stored.
It will then generate two random sequences of bits. It will use the first to encrypt the second, and it will use the user's password hash as a key to a Blowfish encrypter and encrypt both of them. It will then send those two encrypted sequences back to the client. The client has a few seconds in which it must respond. It has to use the hash of the password the user entered as a Blowfish key and decrypt both sequences, it then uses the first as a key to decrypt the second. It then sends back the second part, decrypted.
The server compares that to the second raw sequence it generated, and if they are the same, then obviously you had the right password. But, all of this happened without ever exchanging anything about the 'shared secret' that the two sides have (the hash of the password.) In fact, all that was ever on the wire was random data that was encrypted with the hash, and the data that was returned to the server was doubly encrypted, so what comes back over the wire isn't the plain text of anything that was seen coming from the server.
This kind of scheme makes it very hard for anyone listening in on the wire to get anywhere in terms of cracking the password. It absolutely insures that both sides share the same secret, but never reveals that secret.
The other uses of MD5, as mentioned, are oriented towards digital signatures. If I do an MD5 hash of a document, and publish that hash on a web server somewhere. You can do the same hash and if yours matches the published one, then obviously the document hasn't been modified. That's the basis of things like driver signing and application signing that Windows does. As long as you trust the publisher of the hash, you can be sure that data you have is what was published.
There are more robust schemes when things get thicker, which involve public/private key schemes. They are similar in theory to the digital signature stuff, but take it a step further and provide both signature and encryption/decryption schemes (i.e. they are two way unlike hashes) into the same system, and they are very hard to break because they depend on computational infeasibility, i.e. there's not enough computing cycles on the planet to crack a fairly long key of that sort.