MySQL High-Availability for databases having tables without primary key.

3132167

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

Dave Stokes-MySQL Community Team-Oracle

InnoDB will pick a primary key for you if you do not designate one and will probably pick poorly.  Please rethink your schemas. 

You can run gold old MySQL async or semi-sync replication off an InnoDB cluster more for use in reporting or other areas.

3132167

Thank you for the reply. Innodb engine puts a hidden  key if we do not specify a primary key on the table but innodb cluster does not recognize this, for example, in a database that is part of the innodb cluster (using group replication), if I insert into a table without a primary key, it fails with "Error 3098 (HY000): The table does not comply with the requirements by an external plugin." Once I create a primary key, the insert succeeds in the primary as well as the read-only slave instance.

Unfortunately, some of these tables have a requirement to not have a primary key.

Thanks, Gayathri

Dave Stokes-MySQL Community Team-Oracle

Why the requirement for no primary keys? 

3132167

Some of these tables contain raw data and some have log information with no requirement to uniquely identify a record. Since some of these tables may be used in app, we cannot add an auto increment primary column without analyzing if it would break anything.

Dave Stokes-MySQL Community Team-Oracle

Well, you could pull a time stamp via a generated column and use that a as primary.  But if you are intending to not use primary keys when primary keys are required for group replication I wish you the best of luck.

3132167

Thank you for your reply. Is there an automated way to pull a time stamp via a generated column and use that as a primary for any table that exists or may be created without primary key?

If there is a different way of HA setup for MySQL (not using Group replication) that does not have these limitations (similar to active dataguard for oracle or always on for sql server), Please let me know as well.

I reviewed Galera cluster for MySQL HA and they have limitations on delete statements if used with tables without primary key (it fails).

Thanks, Gayathri

Dave Stokes-MySQL Community Team-Oracle

1.   'column-name' DATETIME CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

2. There are several of various level of success.  I have used various systems including standard MySQL replication with various fail over systems.  But I would urge you to reconsider Group Replication

3132167

Thank you. How do I add this timestamp column automatically as primary key when a new table is created without primary key. From what I have seen there is no trigger on "create table".

What is the equivalent of Oracle's dataguard or Microsoft's always-on for a complete database HA solution for MySQL (that includes everything in the database without limitations)? A simple MySQL master-slave replication requires manual setup to failover and failback.

Thanks, Gayathri

Dave Stokes-MySQL Community Team-Oracle

You do not need a trigger to get the timestamp to default to the current time -- create table foo (mytime TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

And look at Group Replication or NDP cluster for the HA solution