My favorite way to think about Mayer-Vietoris is via sheaf cohomology.

So let $F$ be a sheaf of abelian groups on a locally compact Hausdorff space $X$. Let $X=\bigcup_{i\in I} U_i$ be either an open cover or a closed cover. For $\varnothing \neq J \subseteq I$ write $U_J = \bigcap_{j\in J} U_j$. Then there is a spectral sequence $$ E_1^{pq} = \bigoplus_{\substack{J \subseteq I \\ |J|=p+1}} H^{q}(U_J,F) \implies H^{p+q}(X,F).$$ Note that the $q$th row of the spectral sequence is by definition the Cech complex of the cover $X=\bigcup_{i\in I} U_i$, with respect to the presheaf $\mathscr H^q(-,F)$. So the $E_2$-page of the spectral sequence is the usual $E_2$-page of the Cech-to-derived-functor spectral sequence.

To construct the spectral sequence, consider first the case of a closed cover. Define $F_J = (f_J)_\ast(f_J)^\ast F$, where $f_J : U_J \to X$ is the natural inclusion. There is a complex of sheaves $$ 0 \to F \to \bigoplus_{|J|=1} F_J \to \bigoplus_{|J|=2} F_J \to \bigoplus_{|J|=3} F_J \to \dots$$ The stalk of this complex at a point $x \in X$ is given by tensoring $F_x$ with the acyclic complex $$ 0 \to \mathbf Z \to \bigoplus_{\substack{|J|=1 \\ x \in U_J}} \mathbf Z \to \bigoplus_{\substack{|J|=2 \\ x \in U_J}} \mathbf Z \to \dots $$ so the complex itself is acyclic. Hence $F$ is quasi-isomorphic to the complex $0\to \bigoplus_{|J|=1} F_J \to \bigoplus_{|J|=2} F_J \to \dots $ and the hypercohomology spectral sequence (that is, the spectral sequence associated to the filtration by degree of this complex of sheaves) has the $E_1$-term we are looking for.

Note that the construction would work equally well if $F$ were a complex of sheaves (in which case all complexes above would be double complexes).

For an open cover, set instead $F^J = (f_J)_! (f_J)^! F$. We get a complex of sheaves $$ \dots \to \bigoplus_{|J|=3} F^J \to \bigoplus_{|J|=2} F^J \to \bigoplus_{|J|=1} F^J\to F\to 0 $$ which is acyclic by the same argument as before. Now apply the construction to the Verdier dual $DF$. We get that $DF$ is quasi-isomorphic to a filtered object with graded pieces of the form $\bigoplus_{|J|=p} (DF)^J$. Then the Verdier dual of this filtered object is quasi-isomorphic to $DDF\cong F$, with graded pieces quasi-isomorphic to $\bigoplus_{|J|=p} D(DF)^J \cong \bigoplus_{|J|=p} (Rf_J)_! (f_J)^! F$. So the spectral sequence associated to this filtered object (or rather, the filtered object obtained by applying $R\Gamma$ to this complex of sheaves) again gives us the correct $E_1$-term.

What made the construction tick for closed covers is that $(f_J)_\ast$ and $(f_J)^\ast$ are both $t$-exact functors when $f_J$ is a closed embedding. Pushforward is not exact for open embeddings, but we have instead that both $(f_J)_!$ and $(f_J)^!$ are $t$-exact (whereas $(f_J)^!$ is not exact for closed embeddings). Hence why the two constructions of the spectral sequence differ.

My favorite way to think about Mayer–Vietoris is via sheaf cohomology.

So let $F$ be a sheaf of abelian groups on a locally compact Hausdorff space $X$. Let $X=\bigcup_{i\in I} U_i$ be either an open cover or a closed cover. For $\varnothing \neq J \subseteq I$ write $U_J = \bigcap_{j\in J} U_j$. Then there is a spectral sequence $$ E_1^{pq} = \bigoplus_{\substack{J \subseteq I \\ |J|=p+1}} H^{q}(U_J,F) \Rightarrow H^{p+q}(X,F).$$ Note that the $q$th row of the spectral sequence is by definition the Čech complex of the cover $X=\bigcup_{i\in I} U_i$, with respect to the presheaf $\mathscr H^q(-,F)$. So the $E_2$-page of the spectral sequence is the usual $E_2$-page of the Čech-to-derived-functor spectral sequence.

To construct the spectral sequence, consider first the case of a closed cover. Define $F_J = (f_J)_\ast(f_J)^\ast F$, where $f_J : U_J \to X$ is the natural inclusion. There is a complex of sheaves $$ 0 \to F \to \bigoplus_{|J|=1} F_J \to \bigoplus_{|J|=2} F_J \to \bigoplus_{|J|=3} F_J \to \dotsb.$$ The stalk of this complex at a point $x \in X$ is given by tensoring $F_x$ with the acyclic complex $$ 0 \to \mathbf Z \to \bigoplus_{\substack{|J|=1 \\ x \in U_J}} \mathbf Z \to \bigoplus_{\substack{|J|=2 \\ x \in U_J}} \mathbf Z \to \dotsb $$ so the complex itself is acyclic. Hence $F$ is quasi-isomorphic to the complex $0\to \bigoplus_{|J|=1} F_J \to \bigoplus_{|J|=2} F_J \to \dotsb $ and the hypercohomology spectral sequence (that is, the spectral sequence associated to the filtration by degree of this complex of sheaves) has the $E_1$-term we are looking for.

Note that the construction would work equally well if $F$ were a complex of sheaves (in which case all complexes above would be double complexes).

For an open cover, set instead $F^J = (f_J)_! (f_J)^! F$. We get a complex of sheaves $$ \dots \to \bigoplus_{|J|=3} F^J \to \bigoplus_{|J|=2} F^J \to \bigoplus_{|J|=1} F^J\to F\to 0 $$ which is acyclic by the same argument as before. Now apply the construction to the Verdier dual $DF$. We get that $DF$ is quasi-isomorphic to a filtered object with graded pieces of the form $\bigoplus_{|J|=p} (DF)^J$. Then the Verdier dual of this filtered object is quasi-isomorphic to $DDF\cong F$, with graded pieces quasi-isomorphic to $\bigoplus_{|J|=p} D(DF)^J \cong \bigoplus_{|J|=p} (Rf_J)_! (f_J)^! F$. So the spectral sequence associated to this filtered object (or rather, the filtered object obtained by applying $R\Gamma$ to this complex of sheaves) again gives us the correct $E_1$-term.

What made the construction tick for closed covers is that $(f_J)_\ast$ and $(f_J)^\ast$ are both $t$-exact functors when $f_J$ is a closed embedding. Pushforward is not exact for open embeddings, but we have instead that both $(f_J)_!$ and $(f_J)^!$ are $t$-exact (whereas $(f_J)^!$ is not exact for closed embeddings). Hence why the two constructions of the spectral sequence differ.