We did this by using pt-online-schema-change
1. Since we are using RDS with Read Replica, and the tool require log_bin_trust_function_creators = 1, we did a failover to Read Replica, modifying parameter group of Master, reboot Master, then failback to Master and repeat this for Read Replica.
If you have SUPER privilege of the DB, skip this step.
2. Prepare a dsns table and put all read replicas host/port into the table, repeat the insert statement if you have more than 1 read replica / slave:
CREATE TABLE `dsns` (`id` int(11) NOT NULL AUTO_INCREMENT, `parent_id` int(11) DEFAULT NULL, `dsn` varchar(255) NOT NULL, PRIMARY KEY (`id`));
INSERT INTO dsns(dsn) VALUES('h=read1.ap-southeast-1.rds.amazonaws.com,P=3306');
3. Download the tool from Percona, and test run it:
pt-online-schema-change --dry-run --nocheck-replication-filters --recursion-method="dsn=D=testdb,t=dsns" --chunk-size=2000 --alter-foreign-keys-method=rebuild_constraints --alter 'MODIFY amount DECIMAL(14,2), MODIFY amount2 DECIMAL(14,2)' h=testdb.ap-southeast-1.rds.amazonaws.com,D=testdb,t=authors,u=howmun --ask-password
4. Check if there’s any SQL command error from dry-run, else just pick a good maintenance window and run the tool with –execute:
pt-online-schema-change --execute--nocheck-replication-filters --recursion-method="dsn=D=testdb,t=dsns" --chunk-size=2000 --alter-foreign-keys-method=rebuild_constraints --alter 'MODIFY amount DECIMAL(14,2), MODIFY amount2 DECIMAL(14,2)' h=testdb.ap-southeast-1.rds.amazonaws.com,D=testdb,t=authors,u=howmun --ask-password
