MYSQL database failover

backuphigh-availabilitymariadbMySQLreplication

Current status

I have a MySQL (MariaDB 10.2) server with various databases, to which a lot of people have access to. Which means that users can create MyISAM or InnoDB tables/databases.

Problem

Mentioned MySQL server doesn't have any serious fallback/backup, except daily backups. There used to be simple master-slave replication in use, but after numerious sync problems, it had to be stopped. I guess that most of problems came from mix of MyISAM and InnoDB tables, but I couldn't pinpoint the problem with 100% accuracy.

Question

What is the best way to set-up some kind of replication/fallback, that would work with minimum overwatch?

One of the problems is also that size of databases is around 100GB and downtime is undesirable. If I want to set-up a replication, is there any option to do it without serious downtime?

Cloud solutions aren't an option.

Best Answer

  • Mixing MyISAM and InnoDB in ordinary Replication is OK.
  • MyISAM can screw up in any form of replication.
  • MyISAM is not allowed in the latest HA techniques -- Galera and Group Replication.
  • Master-Master, but writing to only one at a time, will let you continue to have MyISAM, and give you quick (perhaps one-minute) failover.
  • MHA is another approach using standard Replication.

In the long run, you really need to choose between HA and MyISAM.

Related Question