Upgrade an existing mysql db on aws aurora from latin-1 to utf-8

MySQL & MariaDB
,
1

Hi Team,
I am working on a project to identify how an existing MySQL db in TB size with default charset as latin-1 can be upgraded to a default charset of utf-8.

the problem statement :

  1. existing database with data in roughly 1-9tb.
  2. requires an upgrade for the charset is mainly on a table or column level.

I am able to perform such operations in multi-threaded DB connections with a volatile table or new tables, but I am trying to identify an efficient way. I did read through GHO-st GitHub solution, but I am trying to find a better solution using percona-toolkit.

any recommendations are much appreciated.

2

Don’t change to utf8. You want utf8mb4. This is the new default. Use pt-online-schema-change to manage this.

2 Likes
3

thanks a lot for the input, are there code samples available in the documentation, usually stack-overflow is a good place to see snippets that can be modified to meet the use case. sorry, 2-day old rookie trying to learn impressive tools and functions offered by percona

1 Like
4

It translates pretty much 1-1, but yes, there should be some examples in our docs

ALTER TABLE bar.foo ADD COLUMN name VARCHAR(20) NOT NULL DEFAULT 'Bob';

pt-online-schema-change --alter "ADD COLUMN name VARCHAR(20) NOT NULL DEFAULT 'Bob'" -d bar -t foo --execute
2 Likes
5

Another approach could be to create a second Aurora cluster and link it to the existing one via Mysql replication. Then you could:

  1. temporarily stop replication
  2. convert all tables on the replica side using direct ALTER
  3. start replication again
  4. When clusters are in sync again, switch your applications to the new cluster
2 Likes
6

Thanks a ton for your input. The high-level problem is:

  • The project doesn’t support replication of DB in its current state.
  • even if we are able to add functionality to replication, we would have to stop the replication while altering/upgrading the schema.
  • additionally, if there is a data update happening to the true source, we would need to move/copy over the delta to an upgraded/updated replica in an effort to keep the system synchronous.
  • moreover, the database sizes that I am trying to work on are between range of 500gb to 10TB dependent on the use case for the project.
1 Like
7

@matthewb thanks for all the help,

  • current db version is 5.7.28 I believe.
  • I have tried a good amount of -ask-help command to get a good source on the documentation plus the commands.

I am still running into an issue where running alter query is through an error back :slight_smile:

pt-online-schema-change -d {schemaNm} --user {root/admin} -t {tableNm} --host localhost --chunk-size 2000 --alter "ALTER TABLE {schemaNm}.{tableNm} MODIFY {columnNm1} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci, MODIFY {columnNm2} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci ;" --execute

the goal is to be able to update latin1 MySQL 5.7.28 to support column level utf8 on specific columns in a table.

I have tried the process with and without -d or -t but nothing has helped

keep getting errors:

pt-online-schema-change alters a table’s structure without blocking reads or

writes. Specify the database and table in the DSN. Do not use this tool before

reading its documentation and checking your backups carefully. For more details, please use the --help option, or try 'perldoc /usr/local/Cellar/percona-toolkit/3.4.0/libexec/bin/pt-online-schema-change' for
complete documentation.

would appreciate if you could offer guidance.

1 Like
8

Hmm. It’s asking for a DSN. Instead of using -d and -t, do it this way:

h=localhost,D=sakila,t=actor
2 Likes
9

HI @matthewb @Ivan_Groenewold : Thanks for all the help. I have been using the following process:

pt-online-schema-change D={dbName},t={tableNm},h={awsEndpoint},u={userNm} --alter="MODIFY {columnNm} VARCHAR(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;" --alter-foreign-keys-method="auto" --set-vars="log_bin_trust_function_creators=1" --ask-pass --execute

every time I try this I am getting a bunch of errors:

  • Error setting log_bin_trust_function_creators: DBD::mysql::db do failed: Variable ‘log_bin_trust_function_creators’ is a GLOBAL variable and should be set with SET GLOBAL [for Statement “SET SESSION log_bin_trust_function_creators=1”], line 1. The current value for log_bin_trust_function_creators is OFF. If the variable is read-only (not dynamic), specify --set-vars log_bin_trust_function_creators=OFF to avoid this warning, else manually set the variable and restart MySQL.
  • error creating triggers: 2022-12-02T19:27:40 DBD::mysql::db do failed: You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) [for Statement "CREATE TRIGGER pt_osc_{schemaNm}_{tableNm}_del AFTER DELETE ON

I have tried a bunch of options, but have not been able to get any success.

it be really helpful if you can offer some recommendations.

1 Like