I have a requirement to setup MySQL High availability between 2 MySQL instances. I had setup innodb cluster with one RW MySQL instance (master) and the 2nd instance as RO (slave). Some of the databases in the primary mysql instance have tables without primary key and this poses a limitation in setting up this HA. Could you recommend other ways that HA can be setup between 2 mysql instances that replicates everything in a database (without restrictions)?
Version of MySQL is 8.0.11.
I also need to have internal copies of the same or some databases in the primary mysql instance for reporting in another mysql instance. But this could be real time or scheduled copies of everything in the database which is large (~300GB). Hence mysqldump restore is not looked at. Any recommendation for this would also be helpful.
Thanks for your help,
Gayathri