I lived in the Frankfurt area for a couple of years when my husband was in the army. Now, we're returning with 3 kids in tow. We are flying out of Frankfurt on New Year's Day in the evening, so we need a place to spend a kid-friendly NYE in Frankfurt or nearby. I know Frankfurt gets really crazy. Would Wiesbaden or Mainz be better? I even thought of one of the cute medieval towns along the Rhein. My kids are 14, 12 and 7.
I think you'd probably find more to do in Frankfurt than Mainz or Wiesbaden. How long are you in Frankfurt?
NYE is tough. All the stores, etc. will closed down at 14:00. Most of the museums are closed that day too, though some are open. The Zoo and Palmengarten are open on NYE, but will close early. Some hotels have a nice dinner available, but you would want to book that ASAP.
Last year, they didn't have fireworks for sale, so there were not quite as many as usual. I would not ever want to be downtown on the streets on NYE. Just a mess. I don't know yet if they will sell them again. Lots of talk of this practice ending as it is awful for the birds and pets, and leaves a ton of trash to be cleaned up the next day. I wish they would just have official fireworks or an LED show like some cities do.
All towns will be letting off fireworks, but the medieval towns have wooden houses and sometimes this is forbidden.
The best for Frankfurt would be to get a room that is on a top floor, like at the Mariott by the Messe or Lindner on the river.
With kids, my suggestion would be to get some food in the daytime and hunker down in your hotel room at night. Silvester(NYE) in Germany is usually wilder than any American can imagine. Walking outside at night means drunk people throwing firecrackers at your feet. Most of the Germans I know celebrate it at someone's house, rather than going out.
If it were me, I'd tire the kids out during the day and then watch any NYE celebrations in Europe, etc at night. Just be warned that with the energy crisis many of them could be scaled back.