Without loss of generality we can assume that the support of the measure equals X
(i.e., the measure is faithful),
because we can always pass to the subspace defined by the support of the measure.
The space L^2(X) is independent of the choice of a faithful measure and depends only
on the underlying measurable space of X.
There is a complete classification of measurable spaces up to isomorphism (for simplicity I only consider measurable spaces
that satisfy a certain countability assumption, because
only these spaces can have separable L^2-spaces):
every measurable space canonically decomposes as a disjoint union of
its atomic and diffuse parts.
The atomic part is simply a disjoint union of points, whereas the diffuse part
is a (noncanonical) disjoint union of real lines.
Finite nonempty disjoint unions of real lines are isomorphic to the countable
union of real lines, but otherwise the cardinality of the family determines
the diffuse part uniquely.
Thus isomorphism classes of measurable spaces are in bijection with
pairs of cardinal numbers (m,n), where n is either zero or infinite.
(Here m is the number of points in the atomic part and n is the number of real lines
in the diffuse part.)
L^p(X) (p≥1) is separable if and only if both m and n are at most countable.
Thus there are two families of measurable spaces whose L^p-spaces are separable:
(1) Finite or countable disjoint unions of points;
(2) Disjoint unions of the real line and a space of the type (1).
Equivalent reformulations of the above condition:
(1) L^p(X) is separable if and only if X admits a faithful finite measure.
(2) L^p(X) is separable if and only if X admits a faithful σ-finite measure.
(3) L^p(X) is separable if and only if every measure on X is σ-finite.
(Here I disallow nonsemifinite measures, i.e., measures that are equal to infinity on a set of nonzero measure.
Note also that X is assumed to satisfy the countability
property mentioned above.)
The underlying measurable space of a locally compact group G satisfies the above conditions if and only if G is second countable.
The underlying measurable space of a paracompact Hausdorff smooth manifold M
satisfies the above conditions if and only if M is second countable, i.e.,
the number of its connected components is finite or countable.
More information on this subject can be found in this answer:
http://mathoverflow.net/questions/20740/is-there-an-introduction-to-probability-theory-from-a-structuralist-categorical-p/20820#20820
Bruckner, Bruckner, and Thomson discuss separability of L^p-spaces in Section 13.4 of their textbook Real Analysis:
http://classicalrealanalysis.info/documents/BBT-AlllChapters-Landscape.pdf