Setting read_only on a replication slave cluster gets overwritten

MySQL & MariaDB Percona Operator for MySQL
,
1

Hi,
I’m using the 1.12 XtraDB operator on Openshift 4.10.37.
I have 2 openshift clusters and I’m replicating happily between the master and the slave.

Ideally, I’d like to have 2 way replication (XtraDB in each cluster is both a master and a slave).

Is this supported ?

What would the YMAL look like ?

Thanks

Malc

2

hi @mtye ,

Do you want to have something like that?

photo_2023-02-07 21.32.40
photo_2023-02-07 21.32.401252×798 80 KB

3

Yes. @Slava_Sarzhan

The 2 way replication is to make GR recovery easier. I’ll be setting 1 side into read_only.

thanks

Malc

4

@mtye,
In your original post you said XtraDB cluster, but then just above you said GR (Group replication). These are not the same. Please clarify which you are using.

5

Hi @matthewb. The GR I’m referring to is Geographical Redundancy.

6

I’ve written the YAML for the 2 way replication.

Site 1 :

spec:
  pxc:
    replicationChannels:
    - name: pxc1_to_pxc2
      isSource: true
    - name: pxc2_to_pxc1
      isSource: false
      configuration:
        sourceRetryCount: 3
        sourceConnectRetry: 60
        ssl: false
        sslSkipVerify: true
        ca: '/etc/mysql/ssl/ca.crt'
      sourcesList:
      - host: 10.224.77.4
        port: 3306
        weight: 100
      - host: 10.224.77.5
        port: 3306
        weight: 100
      - host: 10.224.77.6
        port: 3306
        weight: 100

Site 2 :

spec:
  pxc:
    replicationChannels:
    - name: pxc1_to_pxc2
      isSource: false
      configuration:
        sourceRetryCount: 3
        sourceConnectRetry: 60
        ssl: false
        sslSkipVerify: true
        ca: '/etc/mysql/ssl/ca.crt'
      sourcesList:
      - host: 10.224.76.2
        port: 3306
        weight: 100
      - host: 10.224.76.3
        port: 3306
        weight: 100
      - host: 10.224.76.5
        port: 3306
        weight: 100
    - name: pxc2_to_pxc1
      isSource: true

After applying it on the respective sites, the slave status on both Openshift clusters is only for pxc1_to_pxc2.

On site1, I’d expect the Channel_name to be pxc2_to_pxc1 and the Master_Host to be one of the 10.224.77.X addresses.

Site 1

mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.224.76.5
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000008
          Read_Master_Log_Pos: 2618
               Relay_Log_File: cluster1-pxc-0-relay-bin-pxc1_to_pxc2.000002
                Relay_Log_Pos: 367
        Relay_Master_Log_File: binlog.000008
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2618
              Relay_Log_Space: 779
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: 4acc0a05-a6d7-11ed-ab20-0a580a8d2873
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 3
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set: c7162d6f-a6d1-11ed-bc02-0a580a8d2bd6:1-6,
e4dc0ad5-a6d1-11ed-960d-0a5eede1c5a1:1-15
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: pxc1_to_pxc2
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

Site 2 :

mysql> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 10.224.76.5
                  Master_User: replication
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: binlog.000008
          Read_Master_Log_Pos: 2618
               Relay_Log_File: cluster1-pxc-0-relay-bin-pxc1_to_pxc2.000002
                Relay_Log_Pos: 411
        Relay_Master_Log_File: binlog.000008
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1110
              Relay_Log_Space: 3251
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 0
                  Master_UUID: 4acc0a05-a6d7-11ed-ab20-0a580a8d2873
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 3
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: e4dc0ad5-a6d1-11ed-960d-0a5eede1c5a1:10-14
            Executed_Gtid_Set: b915eec4-a6d9-11ed-bb39-0a580a29288a:1-12,
e10ac66a-a6d9-11ed-9524-c641570b8577:1-20,
e4dc0ad5-a6d1-11ed-960d-0a5eede1c5a1:1-9
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name: pxc1_to_pxc2
           Master_TLS_Version:
       Master_public_key_path:
        Get_master_public_key: 0
            Network_Namespace:
1 row in set, 1 warning (0.01 sec)

Any ideas ?

Setting read_only now seems to persist (the original reason for creating the post), I guess the operator maybe sets read_only=0 periodically on a slave instance ?

Thanks

7

Hi @mtye ,

Currently we do not support two way replication in PXC operator. You can create JIRA task for us, and we will discuss it and add to our roadmap.

8

@Slava_Sarzhan thanks for confirming that. I’ll raise a jira for it