Can we produce a long exact sequence in cohomology from more than just short exact sequences? It is well known that given a short exact sequence 
$$0\rightarrow A \rightarrow B \rightarrow C \rightarrow 0,$$
we can form a long exact sequence in cohomology. (Example: the proof of the Mayer-Vietoris sequence.)
This is perhaps not too surprising. After all, we are given a nice algebraic relation between $A$, $B$, and $C$, so it seems sensible that we can get good information about their cohomology groups. And, if we're willing to get metaphorical, I suppose we can think of short exact sequences as some kind of algebraic version of a fibration and and the long exact sequence here as an analogue of the long exact sequence for a fibration from algebraic topology. I have two questions along these lines.

  
*
  
*Is there any way to make this intuition more precise? Why should short exact sequences lead to long exact sequences in cohomology? Is
  comparing this result to examples from algebraic topology the right
  way to think about it, or is another approach more philosophically
  enlightening? (I have heard that in general the right way to
  understand results from homological algebra is to go back to their
  roots in algebraic topology to get intuition. Comments on this would
  also be appreciated.)
  
*What's so special about exact sequences with three terms? Can we get any information about cohomology from exact sequences like
$$0\rightarrow A \rightarrow B \rightarrow C \rightarrow D
 \rightarrow 0\, ?$$

I have glanced at nLab, and this page suggests there might be some satisfying general explanation in terms of category theory, but I can't make much sense of it. (I confess I always just assume abelian categories are categories of modules and try not to worry about the details...)
 A: 
I suppose we can think of short exact sequences as some kind of algebraic version of a fibration and and the long exact sequence here as an analogue of the long exact sequence for a fibration from algebraic topology. 

This analogy can be made precise using the notion of homotopy limits, specifically the notion of homotopy fiber and fiber sequence. In short, in any higher category with zero objects (e.g. either chain complexes or pointed spaces), given a morphism $f : E \to B$, repeatedly taking homotopy fibers (the homotopy pullback of the diagram $E \rightarrow B \leftarrow \bullet$, where $\bullet$ is the zero object) gives a "long fiber sequence"
$$\cdots \to \Omega F \to \Omega E \to \Omega B \to F \to E \to B$$
where every object is the homotopy fiber of the preceding morphism. This construction, after possibly applying some auxiliary functors, is responsible for all long exact sequences in mathematics. There is a dual construction involving taking homotopy cofibers (which are certain homotopy pushouts), but it's just this construction in the opposite category, although it's sometimes done without zero objects (you can do it in spaces, not pointed spaces, using the point, which is the terminal object). That looks like
$$A \to B \to C \to \Sigma A \to \Sigma B \to \Sigma C \to \cdots$$
and it's the ability to form these "long cofiber sequences" that triangulated categories imperfectly attempt to capture. 
In particular, this curious period-$3$ behavior in both of the above sequences is not an artifact and hasn't been put in by hand; it falls naturally out of higher-categorical universal properties. 
What's special about short exact sequences with three terms is that the first term is the homotopy fiber of the morphism between the second two terms, or equivalently (this is special to "stable" contexts like chain complexes and spectra) the third term is the homotopy cofiber of the morphism between the first two terms. 
An exact sequence with more than three terms gives rise to a spectral sequence. (Actually this is true even without exactness, although I think exactness makes the spectral sequence nicer.)
