I will give it a try:
MySQL doesn't support multi-master node so adding units to one MySQL
service doesn't make any sense.
MySQL Community Server doesn't support multi-master node is correct. For multi-master node setup will you need
MySQL Cluster or a fork. I can also confirm that at time of writing this answer did it not exist a Juju Charm for
MySQL Cluster. But
MySQL Community Serversupports other high-available opportunities thorough its replication. One of the most obvious one is Switching Masters During Failover.
If you have a closer look on the implementation of the MySQL Charm can we see under
hooks\ha* something I might think is a switch of master. Tho, i have not dived so deep that I can confirm it is that.
Why not just create a new service with relation to the master node?
On the first hand, if we keep the master switch in mind from the answer above, does it make sense to add units to the master
MySQL Charm as it will give it a pool of replicas that could be switched to become new Masters on a failure. On the other hand, defining a new service as slave and give it a relation to the master will make them stay as slaves.
if the primary slave server dies all the unites attached to it become useless as well
That is not exactly correct, as the slave servers will gladly be serving read requests. The MySQL replicas can also be 'forced' to serve write/update requests but that is strictly advise against as it comes with major complications for keeping semantics.
According to answer of this question, by a
Dimiter Naydenov a member of Canonical Juju-core, is adding of more units to the MySQL Charm not creating replication slaves.
If the extra units nodes should be able to be promoted, to new master would that behavior be needed. So my best guess then, if his answer still hold ground, is that it is wasted units serving the purpose of inviting people to contribute on it.
A project that you might find of interest or inspiring, could be this one.
Improvements and corrections are welcomed.