Skip to main content

MySQL

MySQL Backup​

The mysqldump command does NOT backup all database object by default. The following flags must be added to ensure all database objects are included in the dump.

mysqldump -u %username% -p %databasename% --create-options --events --routines --triggers --single-transaction > mydump.sql

From AWS Aurora / MySQL 8.0

mysqldump %databasename% --host=%serverhost% -u %username% -p --create-options --events --routines --triggers --single-transaction --ssl-mode=disable --column-statistics=0 > mydump.sql

Remove Definers from Backupfile. (this changes the source file)​

sed -i 's/DEFINER=[^[:space:]]\+ //' mydump.sql

Restore Backup​

mysql --host=%serverhost% -u %username% -p %databasename% < ./mydump.sql

Use a bastion host to connect to Auroa MySQL Serverless​

ssh -i ~/.ssh/private-key.pem -f -N -L %PORT-ON-LOCALHOST%:%AURORA-ENDPOINT%:%AURORA-PORT% ubuntu@%BASTION-HOPST% -v

Example:

ssh -i ~/.ssh/my-2021.pem -f -N -L 3309:someserver-mysql.cluster-123456.ap-southeast-2.rds.amazonaws.com:3306 ubuntu@ec2-11-222-32-444.ap-southeast-2.compute.amazonaws.com -v

Once the connection has been established connect to the Aurora instance using: localhost as the hostname on port 3309 with valid credentials.