AWS Import / Export DUMPS from RDS MySQL

So when you are exporting a MySQL Dump from RDS in AWS and Import to another RDS Instace, you are becoming an error by importing your dump file.

Access denied; you need the SUPER privilege for this operation

https://forums.aws.amazon.com/message.jspa?messageID=183618

 

Export your MySQL to a Dump

 mysqldump -h mydb.tasdasdasd.eu-central-1.rds.amazonaws.com -u my_qa -p my_q > my_qa.sql

In your exported dump file

 

/*!50003 CREATE*//*!50017 DEFINER=`root`@`localhost`*//*!50003TRIGGER temp_product_feature_insert

root@localhost is your enemy 😀

 

Modify your Dump

Import your MySQL Dump after modify with set to your target RDS instance

You must replace it with sed

sed -e 's/DEFINER=`.*`@`.*`/DEFINER=CURRENT_USER /g' my_qasql > fixed_my_qa.sql

This change all entry´s to

/*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER */ /*!50003 TRIGGER temp_product_feature_insert

 

Import your MySQL Dump after modify with set to your target RDS instance

mysql -h myprod.dfsfsdfe3.eu-central-1.rds.amazonaws.com -u my_prod -p myproddb_p  < fixed_my_qa.sql

 

 

 

MySQL Tipps

Slow Log im Betrieb einschalten

SHOW VARIABLES LIKE ‘slow_query_log%’;

SHOW VARIABLES LIKE ‘log_queries%’;

 

SET GLOBAL slow_query_log = ‘ON’;

SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;

SET GLOBAL log_queries_not_using_indexes = ‘ON’;

Slow Log im Betrieb wieder ausschalten

SET GLOBAL slow_query_log = ‘OFF’;

SET GLOBAL slow_query_log_file = ‘/var/log/mysql/mysql-slow.log’;

SET GLOBAL log_queries_not_using_indexes = ‘OFF’;

MySQL Aborted Clients anschauen und status löschen im laufenden Betrieb

show status like ‘Aborted_clients’;

flush status;

Mysql root passwort sicher speichern my.cnf

Oft steht es in der my.cnf im Klartext drin, wie es verschlüsselt geht, steht hier

(More) Secure local passwords in MySQL 5.6 and up

Understanding mysql_config_editor’s security aspects