The easiest way to think of color profiles is this: they are complex numeric systems that translate colors from one color space (say, RGB) into another (for example, CMYK) in a way that optimally represents the original color in the destination color space. The math of how color profiles work is far, far beyond my understanding so I won’t try to explain it here.
The reason they are necessary is that color spaces overlap, but not 100% - the RGB color space (perceived via light frequencies) is one of the largest and can represent almost any color, while CMYK (perceived as light reflecting off a surface printed using CMYK inks) is considerably smaller; there are colors that we can see in RGB that can’t be directly represented in CMYK due to the limitations of ink systems. Color profiles define what happens to these out-of-bounds colors through a variety of techniques.
They also “nudge” colors to ensure accurate reproduction - for example, if a CMYK device tends to create output that is slightly orange, the color profile will know this and adjust the colors appropriately to avoid an orange tint.
Ideally, all colors in a print stream are tagged with the color space that the color numbers represent. This often corresponds to the device that generated the color information: your cell phone’s camera has a particular color profile, as does your flatbed scanner. Output devices also have color profiles; your monitor, your printer, your cell phone’s screen, etc. The color engine takes all of this into account and is responsible for making the best effort at consistent color representation.
That’s why it’s important to use color profiles whenever possible. Color data that is not associated with a profile will get treated as generic data and may result in color shifts. They’re not likely to be extreme, but they can be enough to cause concern and color mismatches with other elements.
Support for color profiles is virtually ubiquitous at this point. Since the mid-90s, all the major image editing software supports color profiles; any digital camera you are likely to use is going to automatically embed a profile; even a screenshot taken on your Mac will have an embedded profile. All major operating systems process them.
The nice thing about color profiles and color management in general these days is that it happens silently, providing excellent results without any user intervention required. This quality of output used to be the sole domain of professionals with years of experience and it’s rather amazing how far we’ve come in the past 20-25 years in bringing this technology to the masses.