This question is too broad, and it is impossible to give a short answer.
In the beginning of 20th century, Cantor's discoveries led to what is called a "crisis of foundations" in mathematics. You are right. Hilbert was one of the principal defenders of Cantor's theory, but paradoxes were soon discovered in set theory and the heated discussion continued.
There were several programs on how to get rid of these paradoxes, the most famous of them being "Hilbert's program". Two other alternatives were Intuitionism and Type Theory. Later, Gödel showed that Hilbert program would not work. For the full story, I recommend an excellent book:
Fraenkel, Abraham A.; Bar-Hillel, Yehoshua Foundations of set theory. Studies in Logic and the Foundations of Mathematics North-Holland Publishing Co., Amsterdam 1958.
One can say that the discussion on the foundations of set theory still continues, but most mathematicians are satisfied with ZFC axioms.
