Bands of connective tissue within muscle are also known as fascia, so self-myofascial release is a term used for self-massage or muscle release. Think about a shoelace, you're pulling tight so the knot gets tighter, when you constantly stretch your muscles, you're pulling the ligament and muscles tighter but those knots need to be kneeling out, worked out, released and less tightened. The tighter your muscles are, the more restrictive your movements will be. Naturally, athletes move, their motions and patterns of movements matter, so with all that hard work being put in, they have to release those muscles that have become wound up and tight.
By applying pressure to specific points on your body, you can help with the recovery of exercised muscle and return it to normal function. Athletes get massages to reset their muscle, release and renew, that's exactly the point of foam rolling. You will renew the muscle to a proper elasticity and readiness to perform again.
Does it hurt? Probably if you have tight points which I don't come across many people who don't. The thing about trigger points is that everyone has them, and depending on their level of activity within each individual, these points can be overworked and super tight or tight from compensation, or a constant position.
Example: An NFL player's quads may be tight due to training practice and running up and down a field all game. A corporate lawyers quads may be tight from sitting all day, making his hamstrings weak, causing the quads to over compensate through other activities like walking up stairs or even a run at the gym. Both peoples' quads are tight and can benefit from foam rolling, just from different causes. So just because you're not a pro athlete, doesn't mean you aren't messed up, as a matter of fact, you might be worse!