I have a master-master cluster (with an arbitrator to make the third node). My cluster contains 1000 databases with 170 tables. Our app connects through the cluster using an IP failover, so if the first server crashes, the app continues to run using the second one.
I need to alter each table to change the charset. Some tables can’t be altered with this tool (circular reference, etc.), but that’s not the issue.
No slaves found. See --recursion-method if host ths-03 has slaves.
Not checking slave lag because no slaves were found and --check-slave-lag was not specified.
Operation, tries, wait:
analyze_table, 10, 1
copy_rows, 10, 0.25
create_triggers, 10, 1
drop_triggers, 10, 1
swap_tables, 10, 1
update_foreign_keys, 10, 1
No foreign keys reference chezjos.apr_price; ignoring --alter-foreign-keys-method.
Altering chezjos.apr_price…
Creating new table…
Created new table chezjos._apr_price_new OK.
Altering new table…
Altered chezjos._apr_price_new OK.
2025-07-28T15:55:49 Creating triggers…
2025-07-28T15:55:49 Created triggers OK.
2025-07-28T15:55:49 Copying approximately 10824 rows…
Copying chezjos.apr_price: 9% 00:00 remain
Copying chezjos.apr_price: 94% 00:00 remain
2025-07-28T15:55:50 Copied rows OK.
2025-07-28T15:55:50 Analyzing new table…
2025-07-28T15:55:50 Swapping tables…
2025-07-28T15:55:50 Swapped original and new tables OK.
2025-07-28T15:55:50 Dropping old table…
2025-07-28T15:55:50 Dropped old table chezjos._apr_price_old OK.
2025-07-28T15:55:50 Dropping triggers…
2025-07-28T15:55:50 Dropped triggers OK.
Successfully altered chezjos.apr_price.
How can I interpret: “No slaves found”?
It seems like the alter only applies to the server where I ran the command.
How are the alters on the “other” master performed?
What is the best place to perform the alter: master, or “backup”-master?
Do you run PXC (Percona XtraDB cluster) setup? If not, please explain about your setup. Or when you mention master-master, is it normal asynchronous replication?
Incase, you have PXC setup, it is normal that it doesn’t find any slaves. i.e. pt-osc tool looks for slaves from PROCESSLIST or SHOW REPLICAS by default, which will not be listed in PXC setup. So this is normal in PXC setup.
If it is PXC, then changes in one node will be replicated to the other node automatically. see documentation. Here, you are not changing anything but rebuilding the table using --alter “ENGINE=InnoDB”. You can check the table status as below on the other node and see if any fragmentation in Data_free row: SHOW TABLE STATUS LIKE 'apr_price'\G
Let us know your setup and so we can help you out further.
NOTE:
Before you use --execute with pt-osc, use --dry-run and see what this tool does actually when you use --execute. see this post for related topic.
When a DDL is being executed, all other cluster activity is stopped for the duration of the DDL to keep the cluster in sync. Having to exeute hundreds of DDL’s (one at a time) will halt all cluster activity and impact operations and thus you will need a maintenance window to carry this task
Since I wasn’t sure the changes were applied on all nodes (without specifying it, and seeing the tools say I have no “slave”), I’m limiting myself to no real changes (only rebuilds to perform the optimize).
I already ran the command on over 400 tables without any error. So, I’ll go ahead and run the “optimize” on the other 600 tables.
Since I have multiple servers, what is the safest method?
Should I use pt-osc on the server that runs the SGBD, or install and run pt-tools from another server?
In the command, is there a difference if I use the IP of my main server or the IP of the “backup”?
My next goal is to change the charset of all tables (1000 database × 170 tables, 170,000 alters ). I can use the pt-osc tool to do 80% of the work. However, I have some tables with foreign keys (including circular FKs).
How can I change the charset of these tables? I can’t run a simple ALTER in SQL, as you mentioned and as I have already tried -_-". The cluster locks and the main node crashes after too many connections accumulate.
PS:
We stop running commande on the server, but the load keep hight …
why ? O_o
You can run the command on the localhost or from remote. Anything is fine. That should not be a concern.
If you are using PXC cluster, you can point this command to any node and run. Or you can run on any node locally. It will replicate the writeset on all other nodes.
I would suggest you to always include the parameter –max-flow-ctl=0 to not cause flow control
If you are concerned about the threads pile up problem. pt-online-schema-change provides lot of options to control your schema change.
critical load can be set to high because if it reaches to that point the pt-osc will abort. So control it using max-load instead and set critical to high value so it cannot abort.
Another option to use to manually hault the pt-osc operation is –pause-file
Refer to pt-online-schema-change for more details about all the options.
This will help you to get more control over pt-osc.
So, I’ll go ahead and run the “optimize” on the other 600 tables.
). I can use the pt-osc tool to do 80% of the work. However, I have some tables with foreign keys (including circular FKs).