Competitive multiplayer, particularly of the online variety (at least people behave themselves in local multiplayer), requires a specific mindset for anyone to enjoy: you must win at all costs. This environment is something I personally cannot stand, especially with the widespread immaturity which dominates these types of communities.
Cooperative multiplayer is something which really makes me wonder why it has only become prevalent only within the past decade, and I would say this is probably the way to go with multiplayer in general. Competition is not something everyone enjoys (though quite a few do, even if I don't), but working together towards a common goal is a ton of fun; co-op modes can make an otherwise mediocre experience amazing.
---
To address the general question of if multiplayer hurts gaming? The evidence generally points towards no; but as many have pointed out already, the extreme focus on online competitive multiplayer is worrying (especially for those who aren't into it, myself included). Players who predominantly enjoy single player games can also enjoy co-op modes with minimal problems, but it seems that almost too many games are trying to be built around an amazing competitive multiplayer experience. These games also get single player campaigns, some of which can safely be declared to be entirely unnecessary if not pointless.
When I get a game which is usually known for its multiplayer, I get it because I intend to enjoy it's single player campaign. I've played through and enjoyed all of the single-player campaigns of the Halo franchise (except ODST, I always seem to lose interest in that one before too long). I have tried the competitive online play, but it just isn't for me; as such I tend to stick to either Firefight, Campaign, or Forge (ironically, the last one has probably been the most fun for me).
For another perculiar example, World of Warcraft. An online-only game that I played for five years, and predominantly for the solo (or soloable) content. I couldn't of cared less for PvP (in just about all of its forms), and every time I dipped into the PvE content I had to be around players I couldn't stand to be around. To be honest, I probably should've quit the game LONG before I did... but I stuck it out and ended up obtaining the "Loremaster" title (complete the vast majority of the quests available in the game) for one of my characters (I also should've quit after getting it instead of still continuing to play for several months).
Anyhow, the amount of attention online competitive multiplayer gets is somewhat worrying. It seems to be where the money is, but there's still a large portion of players who prefer single-player games. Co-op modes bridge the gap nicely between both crowds, but we still don't want to see single-player gameplay get neglected because of where most of the money flows from.