we are looking to do a table partition in MYSQL RDS running 5.6, table size is large and takes around 6 hours to partition. Can we partition on rds slave and then later promote it to master. is this do able, if not what is best way to partition this large table
Add a comment
|
2 Answers
First, let's discuss what benefit you will get from partitioning. Please provide SHOW CREATE TABLE (with the partitioning). Partitioning, in itself, does not provide any performance benefits. Let's see the query/queries that you think will benefit.
pt-online-schema-change is likely to be a way to convert to partitioning with virtually no downtime. But check carefully; there may be restrictions on partitioning.
The manual says nothing about whether you can have replication between a non-partitioned on the master and a partitioned table on the slave. The silence of the manual worries me; there could be issues.
answered Jun 13, 2015 at 22:38
-
I understand we can partition on slave and not on master and replication still works and manual also do not say anything about it, I was worried would this also apply to AWS RDS Mysql too ? Jun 14, 2015 at 10:02
-
2I know of no reason for RDS to cause additional limitations. Create a separate database, put a small non-partitioned table Master. Then
ALTERit on a Slave. Perform some operations and see what happens. Jun 14, 2015 at 16:50 -
@rogermoore - I suspect that you must use
binlog_format=STATEMENTwhen the Primary's and Replica's tables differ in partitioning. Meanwhile,ROWis becoming the standard. Test on a separate pair of servers (or Docker instances). Suggest you start a Question with just this issue (replicating non-PARTITION to PARTITION). Jun 27, 2022 at 16:47
yes you can.
Can run the partitions on replica [with read_only disable on param group, no need of restart cos its dynamic]. Once the partition and replica in sync, you can promote it as master [it will promote as a new master. M1->s1 == after promote it will be like M1 , S1. ]
