Relation between boiling, vapour pressure and atmospheric pressure

The boiling point of water is always defined as the temperature at which the vapour pressure of water is equal to the atmospheric pressure. How does the definition relate to why there is intense nucleation exactly at the boiling point? I know that the water in the container will start evaporating the moment the vapour pressure exceeds the partial pressure of water in the atmosphere during when the liquid is being heated, which may occur well before boiling begins. What then causes water to start bubbling so vigorously the moment the vapor pressure reaches the boiling point?

Basic Idea:

For gaseous bubbles to exist, you need that the pressure inside the bubbles is greater than (or equal) the pressure acting on the bubble. Otherwise pressure gradient force would make the bubbles to collapse.

The pressure inside the bubbles is created by water vapor. The vapor pressure $e$ will be greater than its saturation vapor pressure $e_s$ (due to its phase).

The ambient air pressure is $p$. The pressure acted on the bubble is $p + g\rho \Delta z$. (for hydrostatic water, $z,g,\rho$ is depth, gravitational const., density of water)

Conclusion

So if $e > e_s \ge p$, water vapor bubbles may exist in the liquid (near surface). This leads to $T \ge T_{boiling}$ via the clausius-clapeyron eqn.

As you get further underwater from surface, the boiling point increases as the pressure acted on the bubble increases.

Obviously, water molecules on the surface can evaporate (escape and never come back) at any temperature. Concerning what happens in the bulk of the water, Lukas has already provided the correct answer. Let me elaborate on what he means by bubble collapsing.

Consider water at $25^\circ\mathrm{C}$ and $1\,\mathrm{atm}$. Out of random fluctuations, a bubble made of vapor forms within the bulk of the water. What is its subsequent fate?

For simplicity, suppose that this bubble has the same temperature as the surrounding water.$\dagger$ Then, its pressure would be about $0.03\,\mathrm{atm}$ (vapor pressure of water at $25^\circ\mathrm{C}$), whereas the pressure of the surrounding water is $1\, \mathrm{atm}$. The bubble would shrink until its pressure would become $1\,\mathrm{atm}$, at which point it would already have turned into a liquid. What if the bubble had a much higher temperature, say, $100^\circ \mathrm{C}$? Then, it would be cooled by giving away heat to the surrounding water at $25^\circ \mathrm{C}$, and then, the same fate. In summary, even if a bubble made of vapor is formed within the bulk of the water, it will be very unstable and immediately be liquefied.

The story is completely different for a bubble in water at $100^\circ\mathrm{C}$ and $1\,\mathrm{atm}$, and I think that OP has enough information to figure out its fate.

$\dagger$ Strictly speaking, temperature and pressure may be ill-defined for objects that are formed temporarily out of fluctuations. However, we can always ask whether a stable object (to which thermodynamics can be applied) with such properties can exist.

There is no "intense nucleation" when water is boiling. As a liquid's temperature increases from its freezing point, the average size of the domains held together by intermolecular forces gradually decreases, until its typically just a few molecules at the boiling point.