Why become a journal editor?
As you alluded to in your question, there are several things to be gained by being a journal editor:


Greater familiarity with ongoing research in your field
Credit for "service" within your department institution
Greater visibility within your research community
In some cases, monetary compensation (some editor positions are paid, some are unpaid)


Being a journal editor can be a very time-consuming job, and it's certainly not for everyone. However, it can also be an interesting and rewarding experience, even if it's not a journal at the level of Science or Cell. (Then again, many of the journals at the level of Science have in-house professional editors, rather than part-time staff from academia!)

Editing a minor journal can also offset other forms of outreach service, particularly if you are still developing a tenure dossier. In those situations you may find editing more (or less) rewarding than the duties you would take on (or be assigned) otherwise. (You should check with your department chair, however, that such service would be considered acceptable before committing to a position!)

Whether or not the connections are worth it at a particular level of journal is not something we can directly answer. You should consider the time commitments relative to other priorities that you might have, and make a decision accordingly.