What you are seeing here is the expected systemd behavior. The Requires= dependency will make sure mysql.service is started whenever tomcat.service starts, but once started the two units are independent and one won't be stopped when the other is.
If you really want mysql.service to be stopped when tomcat.service is, you can use the PartOf= directive which links units on stop and restart.
For the example you described (having mysql.service stop whenever tomcat.service is stopped), what you need is to add PartOf=tomcat.service to the definition of mysql.service. Usually, the best way to do so is to use an override file, which you can do with systemctl edit mysql.service which will open a text editor with an empty file, then you can add this snippet to it:
[Unit]
PartOf=tomcat.service
This will get saved in a file /etc/systemd/system/mysql.service.d/override.conf which becomes part of mysql.service, you can check that with systemctl cat mysql.service.
After those changes and a systemctl daemon-reload, this should work as you expect...
Regarding ordering, everything should work as you expect with the single After=mysql.service you have in tomcat.service, since the dependencies are respected in the reverse order when stopping services. (Which means, in this case, tomcat.service will be stopped first, followed by mysql.service.)
Stopping units this way might not be always a good idea... Perhaps a slightly better approach is to create a separate .target unit to group all services you want to control together. Perhaps something like webservices.target.
You would create that unit with contents such as:
[Unit]
Description=Web Services
Requires=tomcat.service mysql.service
After=tomcat.target mysql.service
[Install]
WantedBy=multi-user.target
And then have both tomcat.service and mysql.service set a PartOf=webservices.target, using the override mechanism described above.
Enable the target unit with systemctl enable webservices.target, and then you can start and stop both services together with systemctl start webservices.target or systemctl stop webservices.target.