An important aspect that seems to be neglected in many of the answers here is that while technical aspects cannot be completely dismissed, they are secondary to other concerns.
To be specific, the primary weapon of heavy cavalry is its momentum, while heavy infantry (among which musketeers from 18th century onward are counted) relies on its discipline in formation and the ability to stand its ground.
The reason why a spear, pike, halberd, or equivalently a musket with a bayonet, are a foil to cavalry is that if the infantry wielding them maintains discipline and remains in formation, cavalry cannot simply trample them and needs to resort to either harassing the formation or fighting in close quarters. If that happens, individual riders are at a disadvantage, and there is decidedly fewer of them than there is of the infantry.
Presenting an impenetrable wall of bodies and steel to an infantry charge and thus robbing it of its momentum is in fact the way to deal with heavy cavalry. Besides ancient phalanxes, this has been successfully attempted by Scottish schiltrons, Swiss pike formations or Hussite wagon forts as early as the 13th century and spelt the end of heavy cavalry dominance on the battlefield. This was later cemented by Spanish (and later German) tercios and by the time of the Napoleonic wars, a cavalry charge against well-formed infantry was essentially hopeless.
Now, not to diminish the significance of bayonets, there are several important contributions that they, and related technologies, made.
- First, the combination of bayonet and a musket meant that you no longer needed to maintain separate missile troops and heavy infantry (where earlier it was not feasible for an archer or an arbalesteer to carry a pike around). This permitted the transition from earlier pike-and-shot formations to homogeneous musket infantry.
- Second, the specific technology of a socket bayonet mitigated vulnerability of infantry to sudden charges, either by cavalry or other infantry, because you could have your bayonet on and be ready to defend yourself in close combat while still being able to shoot (even if muzzleloading was made a bit more difficult by the blade sticking up from the end of your barrel). This, some historians claim, is what made the difference against tactics like the Highland Charge (see Battle of Culloden).
- Third, in comparison to earlier missile weapons, a musket was something you could train very nearly anyone to operate. Where getting reasonable proficiency with a sling would require a lifetime of training, with a bow, years, and with a crossbow months, pretty much anyone could be drilled to load and shoot a musket in a week or so. This in turn prompted the development (or more precisely, refinement) of standardized infantry drill for fighting with a bayonet, marching and maintaining unit cohesion, all of which are crucial when facing heavy cavalry.
So to conclude, it was not really the weapon that made the difference (although it helps to be able to shoot the horse from under a charging cavalryman - a lot easier and just as deadly alternative to trying to shoot the cavalryman himself), but how it was being used. Earlier heavy infantry tactics, such as pike squares, were reasonably effective when well executed, but the proliferation of the musket with a bayonet, as well as of thorough infantry drill, only finally made sure that in a head on confrontation, cavalry was rendered impotent.
As an addendum, remember that this did not make cavalry completely useless. Well into the Napoleonic wars, cavalry would play an important role in scouting as well as pursuit, and it was still possible to find infantry that was either not disciplined enough to withstand the primal terror of a cavalry charge, or was simply caught by surprise. It was also possible to get plain lucky and have the infantry square break at an inopportune moment, as happened at the Battle of Garcia Hernandez.
Furthermore, an infantry battalion that has formed square is effectively pinned down, and the massed formation makes it an easy target for artillery (because any good hit will necessarily take out more of the enemy) or infantry (on account of the square being a lot harder to miss, even with a musket). Following best practices for combined-arms tactics is vital.