When is $L^2(X)$ separable?
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 $^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. Every measurable space canonically splits as a disjoint union of its ergodic subspaces, i.e., measurable spaces that do not admit measures invariant under all automorphisms.
Ergodic measurable spaces in their turn can be characterized using two cardinal invariants $(m,n)$, where either $m=0$ or both $m≥ℵ_0$ and $n≥ℵ_0$. The measurable space represented by $(m,n)$ is the disjoint union of $n$ copies of $2^m$, where $2=\{0,1\}$ is a measurable space consisting of two atoms and $2^m$ denotes the product of $m$ copies of 2. The case $m=0$ gives atomic measurable spaces (disjoint unions of points), whereas $m=ℵ_0$ gives disjoint unions of real lines (alias standard Borel spaces).
Thus isomorphism classes of measurable spaces are in bijection with functions M: Card'→Card, where Card denotes the class of cardinals and Card' denotes the subclass of Card consisting of infinite cardinals and 0. Additionally, if $m>0$, then $M(m)$ must belong to Card'.
The Banach space $^p(X)$ (for $p≥1$) is separable if and only if $M(0)$ and $M(ℵ_0)$ are at most countable and $M(m)=0$ for other $m$.
Thus there are two families of measurable spaces whose $^p$-spaces are separable:
- Finite or countable disjoint unions of points;
- The disjoint union of the above and the standard Borel space.
Equivalent reformulations of the above condition assuming $M(m)=0$ for $m>ℵ_0$:
- $^p(X)$ is separable if and only if $X$ admits a faithful finite measure.
- $^p(X)$ is separable if and only if $X$ admits a faithful $σ$-finite measure.
- $^p(X)$ is separable if and only if every (semifinite) measure on $X$ is $σ$-finite.
The underlying measurable space of a locally compact group $G$ satisfies the above conditions if and only if $G$ is second countable as a topological space.
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: Is there an introduction to probability theory from a structuralist/categorical perspective?
Bruckner, Bruckner, and Thomson discuss separability of $^p$-spaces in Section 13.4 of their textbook Real Analysis: http://classicalrealanalysis.info/documents/BBT-AlllChapters-Landscape.pdf
$\sigma$-finiteness of the measure has nothing to do. The only property which matters is the separability of the measure space itself, which (modulo some technicalities) means that there exists a countable family of measurable sets which separate points of $X$ (mod 0). Measure spaces with this property are called Lebesgue spaces (essentially, these are the only measure spaces one meets in the "real life"). Note that such a family of separating sets gives rise to an isomorphism of the original space with the countable product of 2-point sets.
Any Polish space (separable, metrizable, complete) endowed with a purely non-atomic Borel probability measure is isomorphic to the unit interval with the Lebesgue measure on it. In the same way, a Polish space endowed with a $\sigma$-finite purely non-atomic measure is isomorphic to the real line with the Lebesgue measure on it.
In the "Borel language" one talks about so-called standard Borel spaces. Any standard Borel space endowed with a $\sigma$-finite measure on the Borel $\sigma$-algebra is a Lebesgue space.
$L^2$ on any Lebesgue space (be it finite or $\sigma$-finite) is separable in view of the above isomorphisms.
On the other hand, if one takes a measure space which is not separable - like the uncountable product measure in the previous answer - then $L^2$ on this space is not separable either.
My answer was partially prompted by several comments which have since disappeared - otherwise I would have organized it in a somewhat different way. Unfortunately, the whole discussion illustrates the deplorable situation with teaching measure theory, as a result of which people, for instance, don't realize that in the measure category there is no difference between circles and intervals. A well-kept secret is the fact that there is (up to isomorphism) only one "reasonable" non-atomic probability space, and, consequently, only one reasonable non-atomic $\sigma$-finite space. There is a good Wikipedia article about it.
In addition to the measure $\mu$ being $\sigma$-finite, I think you also need some conditions on the measurable space $(X,{\cal A})$.
Proposition 3.4.5 of Cohn's book Measure Theory says that $L^p(X,{\cal A},\mu)$ ($1\leq p < \infty$) is separable if $\mu$ is $\sigma$-finite and $\cal A$ is countably generated. For example, it holds if $X$ is a complete separable metric space, and $\cal A$ is the Borel $\sigma$-algebra.
However, even for a compact group, you can make counterexamples like $[-1/2,1/2]^{[0,1]}$, an uncountable product of a circles. For the product measure, $\mu=\lambda^{[0,1]}$, the coordinate functions are orthogonal in $L^2$ but there are uncountably many.
I haven't checked the details, so take my answer with a grain of salt!