Hi,
I want to know if it’s possible to integrate pxc cluster ( 3 pods) to existing mysql cluster, I want to add pxc cluster with our current replication to get data from it.
Thanks.
1 Like
Hi,
Yes, it is possible to configure it using cross-site replication feature in our PXC operator. Please have a look at documentation
And also we have blog post about it Migration of a MySQL Database to a Kubernetes Cluster Using Asynchronous Replication - Percona Database Performance Blog
1 Like
Thanks for your reply but I have one cluster with pxc operator and one cluster with classic mysql replication and want to add mysql cluster as source for pxc operator with replication.
1 Like
In this case please read blogpost. It looks like your use case.
1 Like
Hello @no_way,
You can log into any of the PXC nodes directly and configure it as an async replica of your classic MySQL source just like always (ie: CHANGE REPLICATION SOURCE TO ...).
1 Like
Hello @matthewb
I’ve dumped data from primary node and loaded to pxc cluster, i’hve set replication like this :
CHANGE REPLICATION SOURCE TO SOURCE_HOST='host,SOURCE_USER='user', SOURCE_PASSWORD='pass', SOURCE_LOG_FILE='mysql-bin.015151', SOURCE_LOG_POS=63275821;
and I had this error :
Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON
I tried :
MySQL [(none)]> SET @@GLOBAL.GTID_MODE = ON_PERMISSIVE ; SET @@GLOBAL.GTID_MODE = OFF_PERMISSIVE;
Query OK, 0 rows affected (0,104 sec)
Query OK, 0 rows affected (0,206 sec)
and now I’ve :
SHOW REPLICA STATUS\G
...
Last_Error: Node has dropped from cluster
...
thanks
1 Like
All involved mysql servers need GTID on.
1 Like
I’ve set GTID on mariadb source server :
MariaDB [(none)]> show variables like "%gtid%";
+------------------------+----------------------------------------------+
| Variable_name | Value |
+------------------------+----------------------------------------------+
| gtid_binlog_pos | 0-1-2253351293 |
| gtid_binlog_state | 0-4-2252982488,0-3-1728477311,0-1-2253351293 |
| gtid_current_pos | 0-1-2253351293 |
| gtid_domain_id | 0 |
| gtid_ignore_duplicates | ON |
| gtid_pos_auto_engines | |
| gtid_seq_no | 0 |
| gtid_slave_pos | 0-1-2253351293 |
| gtid_strict_mode | ON |
| last_gtid | |
| wsrep_gtid_domain_id | 4 |
| wsrep_gtid_mode | OFF |
+------------------------+----------------------------------------------+
12 rows in set (0.003 sec)
and :
CHANGE REPLICATION SOURCE TO SOURCE_HOST='host',SOURCE_USER='user', SOURCE_PASSWORD='pass', SOURCE_AUTO_POSITION=1;
but still have error :
MySQL [(none)]> SHOW REPLICA STATUS\G
...
Last_IO_Errno: 13117
Last_IO_Error: The replication receiver thread cannot start because the master has GTID_MODE = OFF and this server has GTID_MODE = ON
...
thanks
1 Like
@no_way ,
This isn’t going to work. You are using MariaDB which uses a non-standard GTID methodology. MariaDB does not follow the rest of the MySQL community and the incompatibilities grow with each release. You could still try traditional binlog-file-pos but that would require you to disable GTID within your PXC and I’m not sure the K8S operator will support that. Your best option is to swap out MariaDB for Percona MySQL, then you can do regular GTID-based replication.
1 Like
I can swap out MariaDB for Percona MySQL, our production application used it… and I think we can’t disable GTID from k8s operator, it’s use it to have mutli master cluster…
It’s possible to add a replica with MySQL gtid ? then plug it to actual Mariadb cluster, activate log_slave_updates and plug pxc cluster to it ?
1 Like
That’s not how PXC works. GTIDs are separate from Galera replication protocol (which has its own internal GTID).
Yes, but it would be faster to just swap out your maria for mysql. Then you don’t need yet another server in this setup.
1 Like
I’ve tried to disable GTID mode on pxc and now I’ve this error :
MySQL [(none)]> CHANGE REPLICATION SOURCE TO SOURCE_AUTO_POSITION=0;
Query OK, 0 rows affected (0,174 sec)
MySQL [(none)]> CHANGE REPLICATION SOURCE TO SOURCE_HOST=host', SOURCE_USER='user', SOURCE_PASSWORD='pass', SOURCE_LOG_FILE='mysql-bin.015151', SOURCE_LOG_POS=63275821;
Query OK, 0 rows affected, 2 warnings (0,166 sec)
SHOW REPLICA STATUS\G
...
Relay_Source_Log_File: mysql-bin.015151
Replica_IO_Running: Yes
Replica_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: 1047
Last_Error: Node has dropped from cluster
Yes, but it would be faster to just swap out your maria for mysql. Then you don’t need yet another server in this setup.
I can’t swap out maria for mysql…
1 Like
You said you can. But now you can’t? I’m confused.
I don’t see any attempts to start replication. You only changed the params, never started it.
1 Like
You said you can. But now you can’t? I’m confused.
My bad @matthewb I’ve wrote too fast, I can’t swap out MariaDB for Percona MySQL, I’ve to make lot of tests before to full switch to pxc k8s opertor/Percona MySQL
I don’t see any attempts to start replication. You only changed the params, never started it.
Again, wrote too fast, I’ve started it :
MySQL [(none)]> START REPLICA;
Query OK, 0 rows affected (0,051 sec)
MySQL [(none)]> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
Replica_IO_State: Waiting for master to send event
Source_Host: host
Source_User: user
Source_Port: 3306
Connect_Retry: 60
Source_Log_File: mysql-bin.015175
Read_Source_Log_Pos: 60707965
Relay_Log_File: pxc-0-relay-bin.000002
Relay_Log_Pos: 455
Relay_Source_Log_File: mysql-bin.015151
Replica_IO_Running: Yes
Replica_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: 1047
Last_Error: Node has dropped from cluster
Skip_Counter: 0
Exec_Source_Log_Pos: 63275821
Relay_Log_Space: 2514169513
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Source_SSL_Allowed: No
Source_SSL_CA_File:
Source_SSL_CA_Path:
Source_SSL_Cert:
Source_SSL_Cipher:
Source_SSL_Key:
Seconds_Behind_Source: NULL
Source_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1047
Last_SQL_Error: Node has dropped from cluster
Replicate_Ignore_Server_Ids:
Source_Server_Id: 1
Source_UUID:
Source_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Replica_SQL_Running_State:
Source_Retry_Count: 86400
Source_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp: 221018 20:37:19
Source_SSL_Crl:
Source_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: 3396ae57-4743-11ed-b62c-2f12ab5d30c4:1-20713
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Source_TLS_Version:
Source_public_key_path:
Get_Source_public_key: 0
Network_Namespace:
1 row in set (0,051 sec)
MySQL [(none)]>
thanks a lot
1 Like
And just to confirm, you are running CHANGE REPLICATION SOURCE ... and START REPLICA on one of the PXC nodes, correct? And you ran CHANGE REPLICATION SOURCE TO SOURCE_HOST="hostname-of-mariadb" right?
If so, I need to see the mysql error log contents around the time you started replication on the pxc node you started it on.
1 Like
And just to confirm, you are running
CHANGE REPLICATION SOURCE ...andSTART REPLICAon one of the PXC nodes, correct? And you ranCHANGE REPLICATION SOURCE TO SOURCE_HOST="hostname-of-mariadb"right?
Exactly, right!
I port-forward from locahost to haproxy svc, and connect to it from localhost :
kubectl port-forward svc/cluster-haproxy 3306:3306
mysql -h127.0.0.1 -uroot -ppass
I need to see the mysql error log contents around the time you started replication on the pxc node you started it on.
mysql -h127.0.0.1 -uroot -ppass
MySQL [(none)]> select @@hostname;
+------------+
| @@hostname |
+------------+
| cluster-pxc-0 |
+------------+
1 row in set (0,110 sec)
MySQL [(none)]> STOP REPLICA;
Query OK, 0 rows affected (0,074 sec)
MySQL [(none)]> START REPLICA;
Query OK, 0 rows affected (0,054 sec)
and logs, hope that what you need ?
kubectl logs -f cluster-pxc-0 -c pxc
...
2022-10-18T20:55:38.093886Z 47701 [Warning] [MY-010897] [Repl] Storing MySQL user name or password information in the master info repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START SLAVE; see the 'START SLAVE Syntax' in the MySQL Manual for more information.
2022-10-18T20:55:38.095236Z 47702 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Node has dropped from cluster, Error_code: MY-001047
2022-10-18T20:55:38.138568Z 47701 [System] [MY-010562] [Repl] Slave I/O thread for channel '': connected to master 'user@host:3306',replication started in log 'mysql-bin.015175' at position 79128103
2022-10-18T20:55:38.217963Z 47701 [Warning] [MY-010584] [Repl] Slave I/O for channel '': Unknown system variable 'SERVER_UUID' on master. A probable cause is that the variable is not supported on the master (version: 5.5.5-10.3.7-MariaDB-1:10.3.7+maria~jessie-log), even though it is on the slave (version: 8.0.23-14.1), Error_code: MY-001193
Thanks
1 Like
Once again, we are seeing MariaDB’s incompatibilities with the rest of the ecosystem. SERVER_UUID does not exist on MariaDB. It doesn’t look like this will be possible due to MariaDB doing their own thing.
2 Likes
It doesn’t look like this will be possible due to MariaDB doing their own thing.
Totally understand, question, do you think it’s possible do add mysql server as replica with log_slave_updates to actual mariadb cluster and use it as source to pxc cluster ? or maybe we will have same behavior between mariadb → mysql ?
thanks lot
1 Like
Probably same issue since PXC is just Percona MySQL at the core.
2 Likes
thanks a lot @matthewb
so do you think I’ve no solution to do it ? only to swap out MariaDB for Percona MySQL
thanks.
1 Like