MySQL replication master-slave

Question

I'm learning master-slave replication with MySQL and I could get it to work and also use the Percona backup tool to restore a slave. I learnt from this project https://github.com/vbabak/docker-mysql-master-slave

Now I wonder if the master-slave replication must be two separate mysql instances of if it is possible to configure master-slave replication between two databases only instead of needing two separate mysql instances. I think it's not possible.

The reason I want to know is that I want to automate failover and restore scenario and in my environment a new mysql instance always runs on the default port because of infrastructure automation, and therefore it is not possible today to start two mysql instances on the same host machine server, therefore it looks like I need to create two VMs with one master and one slave just to perform the test which is quite overkill for a test scenario and would be slow.

Answer 1

VMs are fine for testing, performance overhead exists but it is at most 30% under high load, not orders of magnitude.

If you want to run multiple database instances on the same machine, what you are looking for is mysqld_multi. You should probably avoid it, though, it is a terrible, messy solution. VMs are a better approach. Or containers if you really feel you have to avoid VMs at all cost.

Answer 2

• VM, as mentioned
• Docker for each instance, with port-forwarding
• Different ports, when use -P 4567 on command lines

Search for MHA.

Be aware of the "split-brain" issue when trying to automate failover.