0

Migrating on-prem MySQL 5.5 databases to AWS RDS MySQL 5.5

Background Information:

I have a MySQL 5.5 dump. I have migrated the same dump to an EC2 instance and it worked. I did restore to EC2 instance to make sure it works at least in EC2.

I tried to migrate our on-prem MySQL 5.5 databases (from a DB dump) to AWS MySQL RDS 5.5 which does not work (throws some error while migrating)

enter code here error 1153 (08S01) at line 12764: Got a packet bigger than 'max_allowed_packet' bytes aws

By default this parameter is set to 1024-1073741824 range in AWS. I have set the max_allowed_packet to 1073741824 on the mysql server side and yet I see the same above mentioned error. So, I edited the parameter group that I have created with parameter value of 1048576 for max_allowed_packet yet I see the same error.

I tried to pass that particular parameter also like this from the MySQL client but no luck:

mysql -u user_name -p -P 3306 -h hostname < mysqldump.sql

mysql --max-allowed-packet=1048576 -u user_name -p -P 3306 -h hostname < mysqldump.sql

AWS MySQL 5.5:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 1048576 | +--------------------+---------+ 1 row in set (0.01 sec)

AWS MySQL 5.6:

mysql> SHOW VARIABLES LIKE 'max_allowed_packet'; +--------------------+---------+ | Variable_name | Value | +--------------------+---------+ | max_allowed_packet | 4194304 | +--------------------+---------+ 1 row in set (0.00 sec)

Note that both AWS RDS instance have the same size RDS instance (DB instance class: db.m4.xlarge )

To restore AWS RDS MySQL 5.6 I did not change anything. It worked smoothly.

What is causing this issue? Now I am out of idea? Any help is appreciated Thanks

My parameter group looks like this My Parameter Groups

AWS RDS parameter groups

  • Would like to know if you solved your issue ? And how ? ;-) Regards – Desmo Nov 29 '18 at 12:29
  • I did solve this issue. Totally forgot to reply you last year. I tweaked a lot of parameter and made it working. Due to standalone Arch I moved to Aurora later though. Solution was mostly handy. Most of the indexes were broken and I had to rebuild them manually using script. It was big mess though considering the handy work of indexes where I do not have expertise knowledge. @Desmo – bhordupur Feb 2 at 15:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.