0

We have a mysql 5.6 cluster, 1 master 5 slaves.

Requirements:- We have to modify the size of a column varchar from 20 to 35 for 2 databases. What is the best approach for doing this accross the cluster?

Approach i tried:- make changes in 1 slave, take its backup and restore it across all slaves and then do a master switch.

issue:- When i made the changes in 1 of the slaves the replication broke for that slave.

command used:- alter table test_table modify LastName varchar(35);

 Column 1 of table 'db1.test_table' cannot be converted from type 'varchar(20)' to type 'varchar(35)' : synchronized with replication@master_ip:3306

The table size is 450Gb each. While testing on slave, It took 2+ hours for the alter statement to complete for 1 table. In total there are 2 tables. So the downtime will be massive if I do it on the master directly.

What could be the best approach in this case to avoid the downtime as much as possible?

Improve this question
4
  • How big is the table? If it is not huge, simply issue the ALTER on the Primary and let it replicate to the Replicas.
    – Rick James
    Commented Feb 3, 2022 at 21:36
  • @Rick James and Ergest the table is huge 450Gb plus, the operation will take a lot of time and will cause a lot of downtime if directly executed on master. (took 2 hours while testing on slave)
    – Rajan Sharma
    Commented Feb 4, 2022 at 20:33
  • Do you have a failover plan for when the Primary crashes?
    – Rick James
    Commented Feb 4, 2022 at 23:40
  • No, it's rare, but when it does, we do a master switch which doesn't take much time
    – Rajan Sharma
    Commented Feb 5, 2022 at 3:14

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.