Amazon RDS: Tips for Using RDS Database with Codebeamer
The experiences learned from migrating data to Amazon RDS's mysql is:
• Amazon RDS does not grant SUPER permission, and a special log_bin_trust_function_creators=1 mysql parameter must be set to allow stored procedures: http://stackoverflow.com/questions/11601692/mysql-amazon-rds-error-you-do-not-have-super-priviledges
• Before loading the MySQL dump it is important to increase max_allowed_packed otherwise the import may fail with connection timeouts.
• The mysql dump must not contain DEFINER clauses, because the RDS does not grant SUPER permission to the db-users. This is the error appears while importing the dump:
ERROR 1227 (42000) at line 13324: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
This must be fixed by removing these from the mysql dump file before starting the import. See how.. This command removes the DEFINER's from the dump file, use the fixed.dump for importing.
.. This command removes the DEFINER's from the dump file, use the fixed.dump for importing.sed -e 's/DEFINER=[^ ]* / /' < mysql-codebeamer.dump > fixed.dump
• Finally I've added following custom parameters to RDS's MySQL server:
Parameter | mysql-cb |
|---|---|
character_set_database | utf8 |
character_set_filesystem | utf8 |
character_set_server | utf8 |
innodb_flush_log_at_trx_commit | 1 |
innodb_log_buffer_size | 83886080 |
innodb_log_file_size | 1000000000 |
log_bin_trust_function_creators | 1 |
max_allowed_packet | 1073741000 |