The weather in Cancun, Costa Mujeres, and in general the Riviera Maya as in Tulum and Coba the climate is the same. It is the tropical climate of the Mexican Caribbean. When the weather in the Mexican Caribbean is unfavorable, the first thing the authorities do is close the ports. So the only Water activities are usually canceled, they are the ones that involve using a port.
Fortunately, Cancun and the Riviera Maya is a tourist destinations with a wide variety of activities. When it is cloudy or rainy you can visit extraordinary places like Chichen-itzá, Coba, or Cenotes like underground rivers, regardless of the rain you will enjoy this experience. Also, people do zip lines or motorcycles in the jungles with the wet ground is more fun. Go shopping or take a private tour to where local people eat and shop. They will indeed have a great time.