Will I destroy my career if I published a paper with a serious mistake?
Congrats on your paper. No, it wouldn't destroy your career but it would be awkward and embarrassing. It could potentially hurt your career if the mistake was the result of obvious sloppiness, gross incompetence, and worst of all, outright dishonesty. But for the first two of those, the damage would very likely be containable and if you keep doing research, after publishing another paper or two that had no mistakes, no one would remember this minor incident.
With that said, your advisor is correct that it's best to avoid publishing papers with mistakes in them if at all possible, so do make a sincere effort to check everything to the best of your abilities before submitting the paper.
I have a feeling that your advisor is using fear to make you work with extra care on the paper. While his intention is good, I don't like this method at all. He is definitely exaggerating.
Unless the mistake is a scientific misconduct, statistically speaking only one paper can't have much impact in your career, either in a negative or positive way.
Many published papers contain a lot of mistakes, even the most important ones. You should always write the best papers you can. But if errors happen, then just move on.
Probably not.
The "mortal sins" you definitely want to avoid are plagiarism and fabrication. Hopefully your supervisor has explained to you the ethics of scientific research, so that there is no chance you will commit these.
Everything else is forgiveable. Your most likely mistakes when starting out with research will be leaving out citations to some important previous work, or accidentally introducing bugs in your code or math. There are three lines of defense against such errors:
You yourself should do a thorough literature research, and carefully proofread your paper and test your algorithms;
Your supervisor presumably will check over your work before approving it for publication;
The reviewers of your paper will spot some (but probably not all) potential errors that slip through.
Once the paper is accepted, any remaining errors will be visible for the world to see. But note that you can publish errata or revisions to your paper later on on your own personal web site, which is where most readers will download and read your paper (in the field of computer science, at least). So if you spot a minor error down the line, you still have a chance to announce and fix it.
What if there is a huge, gaping, embarrassing problem with your paper, and the reviewers miss it? That's still not the end of the world, provided that your good work outweighs your bad. Many years ago in computer graphics, a researcher published a fundamentally flawed paper at a top-tier conference (it wasn't obvious at the time, but it relied essentially on the false premise that rotations commute.) This spurred unrelated researchers to publish the report Errors and Omissions in Marc Alexa’s "Linear Combination of Transformations".
You know you're in trouble when people start pointing out your "errors and omissions"! But Marc Alexa went on to become a very well-respected professor in computer graphics.