I've worked in manufacturing roles in 3 companies for the last dozen years and before that was in academia/laboratory roles as well as a stint in a start-up.
Only you can answer, of course, what is best for your career trajectory, but it is possible to say something about the general differences between those two career paths and also something about career switching in your context.
Manufacturing engineering in most places is lumped together with the vast domain of "supply chain management". The people in such jobs work with a very much "convergent" perspective. Their job is to create their deliverables in a planned and structured way. Performance is measured on speed, cost, and repeatability. The ultimate goal is to meet expectations without surprises. In manufacturing engineering you work as a "bridge" between engineering and contract manufacturers. That might sound interesting but what it usually means in big companies is that much of your interactions will be heavily mediated by project managers on the engineering side and account managers on the CM side (with interests that are orthogonal to each other). Until you develop the right relationships with technical folks, on your own with no help, you will be vulnerable to very pushy supply chain people who only care about their own KPI's and are pulling/pushing you in different directions.
I made it sound like Manufacturing engineering sucks, but I do work in it and I like it. It's not that I enjoy dealing with supply-chain people, the endless turgid ECO process, shitty MRP software, and engineering departments that won't give me the time of day, but I have been able to carve out a niche doing satisfying, technically interesting work. In my case, I focus on failure mode analysis and mfg-test application software and process control. It certainly is not possible in many places to do what I do, YMMV.
By contrast in Design engineering, in the best cases, work is done with an "emergent" perspective. You're creating new possibilities and are judged not just on speed and consistency but also on more intangible aspects of the work. There will be a problem, I think, to cross back into Design if you venture out in to a Manufacturing job. Manufacturing, in many places, isn't regarded as engineering and large orgs with organizational "silos" don't like career-changers. Moreover, you won't necessarily develop a deep understanding of the products while in manufacturing unless you somehow develop solid relationships with people in engineering.
To explicitly answer your questions:
Will taking up a job as a manufacturing engineer effect my job
prospects to mechanical engineering roles in the future?
Yes.
Also: Will companies entertain my requests if I ask for a
slight change in the job title to something like "Mechanical Engineer,
Advanced Manufacturing"?
A big company? Probably not. The job title is just a title, anyway. I would focus instead on what they actually expect you to do more than the job title.
