0

I saw a solution like this below:

  1. create a new table like your source table.
  2. alter that new table the way you want.
  3. insert your data into the new table.
  4. create indexes etc. as needed on the new table.
  5. rename your old table to something like ..._old or whatever.
  6. rename your new table to the former name of the old one.
  7. copy any missing rows from the _old table to the new one.

Reference for an above solution

But the above solution might cause data unavailability if there is a huge amount of data added before copying any missing rows from the _old table to the new one.

Is there any better solution than this, using AWS DMS, etc?

I also want to change the collate of all tables present in the database. Is it possible to get all the data replicated between two RDS DBs continuously, like new data entered in database A gets into Database B and viceversa?

Since I have around 50-60GB data any best way to solve this is appreciated.

Update:

  • I have around 50-60GB of data.
  • Mysql version: 5.7
  • I need to change collation on all my tables
Improve this question
10
  • If you wrap the entire batch of queries in a single explicit transaction and use proper locking then the table won't be able to be written to until your batch is complete.
    – J.D.
    Jul 16, 2022 at 3:55
  • @J.D. it will result in downtime for users who are trying to do write operations right?
    – Msvstl
    Jul 16, 2022 at 4:06
  • Depends on what you consider downtime. It will cause other write operations on the table to wait. Depending on how long your batch of queries take to run vs how long your application allows waiting before issuing a timeout could result in other write queries to fail. If your batch of queries are quick, then it's unlikely other write queries will timeout.
    – J.D.
    Jul 16, 2022 at 5:01
  • @J.D. suppose I have a huge number of users, I can't make them wait by keeping the site in maintenance, etc. Any alternative way?
    – Msvstl
    Jul 16, 2022 at 5:05
  • 1
    I don't believe you mentioned your data size anywhere. But I recommend you adding that to your post so others who may be able to help you see that as well. You shoulda also add your table / index definitions and the batch of queries you're currently using.
    – J.D.
    Jul 16, 2022 at 5:15

1 Answer 1

Reset to default
0

There is a way to do most ALTERs with virtually no downtime: pt-online-schema-change (also gh-ost). But they handle one table at a time.

It sounds like you need to change the collation on all the tables. Or is it really the CHARACTER SET you are changing? (Many people confuse the two.) Please specify the exact change(s) you want. And the version of AWS MySQL.

If you change one table at a time, and there is a JOIN depending on comparing strings between the tables, then between one table changed and the other being changed, any JOINs will not be able to use the index. This will lead to slowdowns that may be as bad as the Alters, themselves.

Improve this answer
1
  • I need to change the collation on all the tables, MySql version is 5.7. Updated the same in the question.
    – Msvstl
    Jul 18, 2022 at 3:47

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.