Percona Operator for MySQL, based on Percona XtraDB Cluster (PXC), provides configuration to expose the ProxySQL, HAProxy, and the PXC instances themselves so that you can use Percona Toolkit to connect to MySQL from these endpoints externally. However, you can also create Kubernetes pods to run Percona Toolkit operations there instead if you do not want to expose additional endpoints.
Here’s how to do it.
First, get the PXC pod’s domain name. Here’s a fast way of obtaining it—we can obtain a shell to one of the PXC pods, connect to MySQL, and extract the details from it:
|
1 |
# kubectl exec -it cluster1-pxc-0 -c pxc -- bash<br>bash-4.4$ mysql -uroot -p$MYSQL_ROOT_PASSWORD -e "SHOW GLOBAL VARIABLES LIKE 'wsrep_node_incoming_address'"<br>mysql: [Warning] Using a password on the command line interface can be insecure.<br>+-----------------------------+------------------------------------------------------------+<br>| Variable_name | Value |<br>+-----------------------------+------------------------------------------------------------+<br>| wsrep_node_incoming_address | cluster1-pxc-0.cluster1-pxc.default.svc.cluster.local:3306 |<br>+-----------------------------+------------------------------------------------------------+<br>bash-4.4$ exit |
Now that we have the cluster host details, we can create the pod that will run Percona Toolkit. The MySQL administrator credentials are stored in a secret in the PXC operator. We can reuse the credentials by using the same secret in the pod that we will be creating as well. In particular, the pod will connect as the MySQL root user. Percona Toolkit also has a published image in Docker, so we can use that image in the pod’s container.
|
1 |
# cat pod-pt-mysql-summary.yaml <br>apiVersion: v1<br>kind: Pod<br>metadata:<br> name: pod-pt-mysql-summary<br>spec:<br> containers:<br> - name: percona-toolkit<br> image: percona/percona-toolkit<br> command: ["pt-mysql-summary"]<br> args: <br> - "--host=cluster1-pxc-0.cluster1-pxc.default.svc.cluster.local"<br> - "--port=3306"<br> - "--user=root"<br> - "--password=$(root)"<br> envFrom:<br> - secretRef:<br> name: cluster1-secrets <br> restartPolicy: Never |
Notice that the restart policy is set to Never so that if the pod runs into a problem, it won’t restart the pod immediately.
Now, let’s create the pod:
|
1 |
# kubectl create -f pod-pt-mysql-summary.yaml<br>pod/pod-pt-mysql-summary created |
After some time, the pod will complete its operations, and then it will be terminated:
|
1 |
# kubectl get pods<br>NAME READY STATUS RESTARTS AGE<br>cluster1-haproxy-0 2/2 Running 0 89m<br>cluster1-haproxy-1 2/2 Running 0 86m<br>cluster1-pxc-0 3/3 Running 0 89m<br>cluster1-pxc-1 3/3 Running 0 86m<br>cluster1-pxc-2 3/3 Running 0 85m<br>percona-xtradb-cluster-operator-7c769dcc6d-glnwq 1/1 Running 0 92m<br>pod-pt-mysql-summary 0/1 Completed 0 17s |
Let’s take a look at the pod’s logs.
|
1 |
# kubectl logs pod-pt-mysql-summary<br>mysql: [Warning] Using a password on the command line interface can be insecure.<br># Percona Toolkit MySQL Summary Report #######################<br> System time | 2024-08-05 08:11:15 UTC (local TZ: UTC +0000)<br># Instances ##################################################<br> Port Data Directory Nice OOM Socket<br> ===== ========================== ==== === ======<br># MySQL Executable ###########################################<br># Slave Hosts ################################################<br>No slaves found<br># Report On Port 3306 ########################################<br> User | root@%<br> Time | 2024-08-05 08:11:15 (UTC)<br> Hostname | cluster1-pxc-0<br> Version | 8.0.35-27.1 Percona XtraDB Cluster (GPL), Release rel27, Revision 84d9464, WSREP version 26.1.4.3<br> Built On | Linux x86_64<br> Started | 2024-08-05 06:44 (up 0+01:26:24)<br> Databases | 5<br> Datadir | /var/lib/mysql/<br> Processes | 7 connected, 2 running<br> Replication | Is not a slave, has 0 slaves connected<br> Pidfile | /var/lib/mysql/cluster1-pxc-0.pid (does not exist)<br># Processlist ################################################<br>*** redacted *** |
You can see from the output above that it was able to run pt-mysql-summary successfully.
Let me provide an additional example. Let’s say you want to use pt-online-schema-change to add a column and add an index to it. Then, my corresponding pod definition would look like this:
|
1 |
cat pod-pt-online-schema-change.yaml <br>apiVersion: v1<br>kind: Pod<br>metadata:<br> name: pod-pt-online-schema-change<br>spec:<br> containers:<br> - name: percona-toolkit<br> image: percona/percona-toolkit<br> command: ["pt-online-schema-change"]<br> args: <br> - "--execute"<br> - "--alter=ADD COLUMN testchar varchar(100) default 'test'"<br> - "D=sbtest,t=sbtest1"<br> - "--host=cluster1-pxc-0.cluster1-pxc.default.svc.cluster.local"<br> - "--port=3306"<br> - "--user=root"<br> - "--password=$(root)"<br> envFrom:<br> - secretRef:<br> name: cluster1-secrets <br> restartPolicy: Never |
Then, let’s create the pod:
|
1 |
# kubectl create -f pod-pt-online-schema-change.yaml<br>pod/pod-pt-online-schema-change created |
Then, we can check from the logs and validate if the DDL operation was successful:
|
1 |
# kubectl logs pod-pt-online-schema-change<br>No slaves found. See --recursion-method if host cluster1-pxc-0 has slaves.<br>Not checking slave lag because no slaves were found and --check-slave-lag was not specified.<br>Operation, tries, wait:<br> analyze_table, 10, 1<br> copy_rows, 10, 0.25<br> create_triggers, 10, 1<br> drop_triggers, 10, 1<br> swap_tables, 10, 1<br> update_foreign_keys, 10, 1<br>Altering `sbtest`.`sbtest1`...<br>Creating new table...<br>Created new table sbtest._sbtest1_new OK.<br>Altering new table...<br>Altered `sbtest`.`_sbtest1_new` OK.<br>2024-08-05T08:16:40 Creating triggers...<br>2024-08-05T08:16:40 Created triggers OK.<br>2024-08-05T08:16:40 Copying approximately 109653 rows...<br>2024-08-05T08:16:41 Copied rows OK.<br>2024-08-05T08:16:41 Analyzing new table...<br>2024-08-05T08:16:41 Swapping tables...<br>2024-08-05T08:16:41 Swapped original and new tables OK.<br>2024-08-05T08:16:41 Dropping old table...<br>2024-08-05T08:16:41 Dropped old table `sbtest`.`_sbtest1_old` OK.<br>2024-08-05T08:16:41 Dropping triggers...<br>2024-08-05T08:16:41 Dropped triggers OK.<br>Successfully altered `sbtest`.`sbtest1`. |
Finally, we can clean up the pod list by deleting the pods:
|
1 |
# kubectl delete pod/pod-pt-mysql-summary pod/pod-pt-online-schema-change<br>pod "pod-pt-mysql-summary" deleted<br>pod "pod-pt-online-schema-change" deleted |
In summary, we can use Kubernetes pods to run Percona Toolkit operations so that you do not need to expose an endpoint from the outside to run them.
Percona Toolkit is a collection of advanced open source command-line tools developed and used by the Percona technical staff. These tools are engineered to perform a variety of server and system tasks that are too difficult or complex to perform manually.
