This question is too broad, and it is impossible to give a short answer. Cantor's discoveries lead to what is called a "crisis of foundations" in mathematics in the beginning of 20th century. 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 how to get rid of these paradoxes, he most famous of them "Hilbert's program", two other alternatives were Intuitionism and Type theory. Later Godel showed that Hilbert program will 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 discussion on foundations of set theory still continues, but most mathematicians are satisfied with ZFC axioms.