Maybe what we need is a set of CLI commands that mirror Ansible modules in performing idempotent operations through SSH and APIs.
Maybe the problem is that I'm not an ansible professional.
And yes, there are public roles for bringing up MySQL, here's one of the more popular:
Along the same lines, there are also .cfg files and roles that can live outside the root directory.
I've been able to get decent reusability out of roles, but that's mostly because I can live with my servers all being deployed the same way (e.g. they all have the same nginx config with minimal customization points and share the same certificates).
Usually virtualenv + requirements.txt + all configuration inside playbook repository should make for a painless experience without one (save for speed).
This is in stark contrast with my experience with the Puppet Forge, which my environment uses 20+ modules from for managing different parts of our systems.
Example: V1 of a playbook installs htop. V2 doesn't. Now, a machine that went through V1 then V2 has a different state than a machine that just went through V2. Of course, you can make V2 explicitly uninstall htop - but that's a hack, and while it's easy for this particular example it sucks for real life problems. And even if you implement explicit 'clean up after previous versions', then you have to manage those as well, figure out how long to keep them, make sure they don't have side effects on other machines (maybe something else installed htop and actually needs it?), etc.
If you want idempotency, try NixOS.
This has nothing to do with idempotence. Idempotence means you can apply the same action more than once, and all applications after the first are no-ops. In Ansible's case, that applies to its tasks; eg if you run a task that starts a service but the service is already started, the task simply succeeds without any other effect, as opposed to failing or some other behavior.