There is no force that balances the centripetal force, and because of this, you get acceleration inward and circular motion. If the force were balanced, you would get no acceleration, and no circular motion.
Firstly, it is important to know that centifugal force is not a real force, it is only an apparant force. The centrfugal force is really just how a rotating object feels the centripetal force.
Imagine you are on one of those spin-rides like the Gravitron where you sit up against the wall as it spins. You will feel a centrifugal force, but an observer from the outside will only see the centripetal force.
Newtons first law (aka law of inertia) says an object in motion wants to stay in that motion. At any instance on the ride, you are moving in a straight line tangential to the circular path. But the walls then provide the centripetal force that pushes you back in. So what you are feeling is your body literally running into a wall via your linear velocity. If the wall pushes on you, newtons third law says you will feel yourself push on the wall.
Now back to the string and ball example, we can see that the centrifugal force is actually just a product of newtons third law of motion - every action has an opposite and equal reaction. While the ball is in motion, your hand has to provide a force(in this case the centripetal force) on the string to keep the ball in its circular path. Consequently, the string must then pull on your hand equally and oppositely. That is the centrifugal force your hand feels. As for the ball, it keeps trying to go linearly but gets pulled in. If you yourself replaced the ball and was spun about, your body would want to keep moving in a straight line (bc of its inertia) but you kept being pulled inwardly instead. As you are spun, the rope pulls on you and so you must then pull on the rope. Thus, it will feel like your body is trying to fly outward.
The moment you let go, the centripetal foce and its apparent centifugal force is gone and the ball continues moving in the direction of its linear velocity at that moment.
Best Answer
The centripetal force does exist in the rotating frame. The centripetal force is a real force, so it exists in all frames.
The centrifugal force is an inertial force so it only exists in the rotating frame and not in the inertial frame.
So in an inertial frame the centripetal force exists and causes an inward acceleration of the object.
In the rotating frame both the centripetal and centrifugal forces exist, and they cancel each other out so that the object remains at rest.