Without loss of generality (Nash embedding theorem) we may assume the Riemannian manifold is an embedded submanifold of Euclidean space: its metric at any point is just the restriction of the Euclidean inner product to the tangent plane. Imagine we live on this submanifold (just like we live on a sphere called Earth) and we want to calculate things, such as our acceleration as we run around our planet.
Remember, the metric gives us a means of measuring distances and angles, but no direct way of computing rates-of-change of vector fields. A connection is what determines the rates-of-change of vector fields (such as acceleration, which is the rate-of-change of velocity vectors). And connections are just "infinitesimal limits" of parallel transport. So the question becomes, given a submanifold of Euclidean space, is there a canonical way of defining parallel transport which is useful in some way?
Often things are "useful" if they correspond to what happens in the real world. So how should parallel transport be defined on our planet? How is it defined on Earth?
The very first thing might be to agree on what path we would take if we are told to walk in a straight line. If we did this on Earth, we would walk along a great circle even though we think we are walking in a straight line. Why? Because after each level step we take, gravity pulls our foot back down to Earth. We think we are going straight, but gravity causes our path to curve in the ambient Euclidean space. (For what it is worth, we tend to interpret this "curve" that gravity induces in our path, as the least change required to keep us on the surface of our planet, so to speak.)
Requirement 1: When we are told to walk in a straight line, the curve we actually trace out (due to gravity, or mathematically, due to Euclidean projection back to the submanifold) should be a geodesic, i.e., have zero acceleration.
Now, imagine as we walk, we are holding a lance. Maybe the lance is pointing straight ahead, but maybe it is pointing to our left. Regardless, we are told not to move the lance as we walk in a straight line. Now, from the perspective of the ambient Euclidean space, where the lance points is going to change as we walk. But from our perspective, we are very comfortable being told to walk without moving the lance. We want the evolution of the lance's position to correspond to parallel transport. Indeed, parallel transport defines how a vector is moved along a curve, and it is quite natural/useful to define parallel transport to be what results if we are told to walk with the lance/vector in our hand without moving it at all. The curvature of the Earth causes it to move, but we believe we are not moving it.
Requirement 2: Parallel transport corresponds to carrying a "vector" with us as we walk along a path without consciously moving the vector. (This actually includes Requirement 1 as a special case when the vector is our own velocity vector.)
These requirements uniquely define the Levi-Civita connection and explain why it is natural/useful. It corresponds to the world we live in.
Now, a few words can be said about the usual axioms used to define the Levi-Civita connection: metric connection with zero torsion. The metric connection means when we parallel transport vectors, their norms and the angles between them do not change. Certainly, if we are carrying two lances and told not to move them, we expect the angle between them to stay the same, and we expect the length of each lance to stay the same too. This on its own is not enough for geodesics to be the "correct" curves, i.e., those curves that result when we are told to walk in a straight line. Torsion actually decomposes into two parts (see Millman's 1971 paper "Geodesics in Metrical Connections"). One part controls what geodesics look like, and the other part determines whether parallel transport will cause a vector to spin orthogonal to the direction of motion along a geodesic. If we start holding a lance straight up (it wouldn't be in the tangent plane but ignore this technicality or think in higher dimensions), but as we walk straight ahead, we rotate the lance so it goes from pointing up to pointing right, then down, then left, then up etc, then our parallel transport has torsion. Hence, taken together, a metric connection with zero torsion gives us the definition of parallel transport corresponding to "do not move the vector as you walk along the curve". This is the Levi-Civita connection.
ps. In Appendix 1.D of the second edition of "Mathematical Methods of Classical Mechanics" by Arnold, a geometric way of constructing parallel transport to have no torsion is explained. Given a tangent vector at a point on a geodesic, the aim is to transport it without altering it any more than necessary, as explained above. Without a Euclidean embedding, this can be done intrinsically by considering families of geodesic curves (see Appendix 1.D of Arnold's book). The infinitesimal requirement reduces to the no-torsion equation $\nabla_X Y - \nabla_Y X = [X,Y]$. Thus, the geometric meaning of $\nabla_X Y - \nabla_Y X = [X,Y]$ is that parallel transport will not induce any extraneous movement of the tangent vector. (The geometric picture in Appendix 1.D of Arnold takes a few paragraphs to explain even though the concept itself is straightforward enough.)