Forum Stats

  • 3,734,697 Users
  • 2,247,027 Discussions
  • 7,857,458 Comments

Discussions

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

3132167
3132167 Member Posts: 5
edited Jul 31, 2018 5:07PM in MySQL Community Space

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

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 350 Employee
    edited Jul 11, 2018 5:25PM

    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
    3132167 Member Posts: 5
    edited Jul 11, 2018 5:39PM

    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.

    pastedImage_0.png

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

    Thanks, Gayathri

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 350 Employee
    edited Jul 12, 2018 9:59AM

    Why the requirement for no primary keys? 

  • 3132167
    3132167 Member Posts: 5
    edited Jul 12, 2018 11:21AM

    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
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 350 Employee
    edited Jul 12, 2018 4:10PM

    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
    3132167 Member Posts: 5
    edited Jul 13, 2018 7:44AM

    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
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 350 Employee
    edited Jul 13, 2018 10:15AM

    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
    3132167 Member Posts: 5
    edited Jul 13, 2018 10:25AM

    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
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 350 Employee
    edited Jul 31, 2018 5:07PM

    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

Sign In or Register to comment.