Genuine question, if this hadn't been done for the TV show but just to satisfy the curiosity of some random individuals, would it still be ok?
Honestly, isn't that why scientific experiments are often done? Sure, sometimes they are done for 'the betterment of human civilization', but it's easily arguable that 1) those reasons are sometimes just the rationale that gets you the grant, and not the real incipient motivation and 2) a vague notion of 'the betterment of human civilization' could very well be present with sheer curiosity experiments as well.
For most situations, the reason why the experiment is happening doesn't matter that much to me. As a blanket set of rules, I would say that:
1) all reasonable precautions should be taken - to fail to do so would constitute negligence.*
2) all costs of accidental damage, even absent negligence, should be covered by the party creating the experiment. Insurance coverage must be adequate to cover all reasonably foreseeable risks.
Why the experiment is happening just doesn't matter a ton. Having a really awesome reason is not going to get you off the hook in the vast majority of situations if you are conducting experiments without reasonable precautions. And if you're taking all reasonable precautions, and we haven't as a society decided the activity is so inherently dangerous there is no way to take reasonable precautions, then I don't see why the reason really matters all that much.
*There are some things that are so inherently dangerous, they exist in a separate category where purpose matters: i.e., nuclear power - you're not allowed to just experiment with that on your own, no matter how careful you are. Note that in the US, a lot of things involving a certain level of explosives and firepower are typically *not* considered so inherently dangerous to fall into this category. We allow all kinds of explosives/pyrotechnics/etc for movies, rock concerts, and just plain recreation, etc, as PPs have mentioned.
Now, I don't know if Mythbusters was negligent in this case or not. From what little I've read, it sounds like they weren't. It's possible the site owners/managers were negligent if they agreed to allow their site to be used despite it's unsuitability. If Mythbusters knew, or should have known, that the site was unsuitable, then perhaps they were negligent. I don't know. But I would have to know a lot more about the nature of the site, the nature of the cannon, what information is reasonably determinable about such firepower, etc.