This is related to Hatcher, Algebraic Topology Cor 3A.7(b).
Cor 3A.7(b) $f:X\to Y$ induces integral homology isomoprhism iff it induces isomorphism with coefficients over $Q$ and for all $Z_p$. "Statement (b) follows by mapping cone."
My consideration is the following. $f:X\to Y$. Denote cone $X$ as $CX$. Set the tip of $CX=(X\times I)/(X\times 1)$ as $z=X\times 1\in CX$. Then mapping cone $C(f)=CX\cup Y$ where $\cup$ is identified under $f$ mapping at the bottom $X\times 0$.
Consider $A=X\times [1/2,1]\subset CX$ and $B=X\times [0,1/2]\cup Y$. Clearly $A\cap B=X\times\{1/2\}$. Now $B$ deformation retracts to $Y$ and $A$ deformation retracts to $z$. Consider the following chain complex to induce mayer-vietoris sequence. Note that $A$ deformation retracts to a point implies that reduced homology is trivial.
$0\to C_\star(A\cap B)\to C_\star(A)\oplus C_\star(B)\to C_\star(A\cup B)\to 0$
For all reduced homology, this is equivalent to $\dots H_\star(X)\to H_\star(Y)\to H_\star(C(f))\to H_{\star-1}(X)\dots$ sequence. Then it follows $H_\star(C(f),Z)=0$ iff $H_\star(C(f),Q)=0$ and $H_\star(C(f),Z_p)=0$ for all $p$.
$\textbf{Q:}$ Normally, one would construct $f:C(X)\to C(Y)$ and define $C(f)=C(Y)\oplus C(X)[-1]$. Then this gives short exact sequence $0\to C(Y)\to C(f)\to C(X)[-1]\to 0$. How would I construct such a thing without knowing existence of simplicial theory say starting point is singular complex only?
You do not need chain complexes, but you can use directly the Mayer-Vietoris sequence (see Hatcher p.149):
For a pair of subspaces $A, B \subset X$ such that $X$ is the union of the interiors of $A$ and $B$, this exact sequence has the form $$ \dots \to H_n(A \cap B) \stackrel{\Phi}{\rightarrow} H_n(A) \oplus H_n(B) \stackrel{\Psi}{\rightarrow} H_n(X) \stackrel{\partial}{\rightarrow} H_{n-1}(A \cap B)\to \dots \to H_0(X) \to 0$$
Hatcher's proof also shows how $\Phi, \Psi$ look like. In fact, if $i_A : A \cap B \to A, i_A : A \cap B \to B, j_A : A \to X, j_B : B \to X$ are the inclusion maps, then $\Phi(\xi) = ((i_A)_*(\xi),-((i_B)_*(\xi))$ and $\Psi(\eta_A,\eta_B) = (j_A)_*(\eta_A) + (j_B)_*(\eta_B)$.
Note that Hatcher proves that there is also a formally identical Mayer–Vietoris sequence for reduced homology groups (see p.150). We shall use it in that form.
Let $p : X \times I \sqcup Y \to C(f)$ denote the quotient map. Let $A = p(X \times [0,1) \sqcup Y)$ and $B = p(X \times (0,1])$. These are open subspaces of $X$ whose union is $C(f)$. $B$ is contractible and $A$ strongly deformation retracts to $Y$ (via $r : A \to Y$). We have $A \cap B = p(X \times (0,1))$ and $\tilde{H}_n(B) = 0$. The "projection" $\pi : p(X \times (0,1)) \to X$ is a homotopy equivalence and you see that $f \pi \sim r i_A$. This gives you an exact sequence $$ \dots \to \tilde{H}_n(X) \stackrel{f_*}{\rightarrow} \tilde{H}_n(Y) \to \tilde{H}_n(C(f)) \to \tilde{H}_{n-1}(X) \to \dots \to \tilde{H}_0(C(f)) \to 0$$ Now you can apply part (a) of Corollary 3A.7.