RDS MySQL/Aurora¶

Restoring a mysql dump to RDS¶

Restoring an existing dump into a running RDS can be easily from any server that can reach both the dump and the RDS instance:

root@ui-db-01:~# gunzip -c /path/to/dump.sql.gz | mysql -h my_instance.eu-west-1.rds.amazonaws.com -u admin -p database

Restoring a mysql dump with stored procedures¶

As the admin user provided by Amazon is not a full root user some commands in the dump might through errors, eg when trying to define stored procedure. Trying to set a definer throws an error, the offending part is the DEFINER= part of the CREATE PROCEDURE statement:

DELIMITER ;;
CREATE DEFINER=`user`@`localhost` PROCEDURE `someProcedure`(IN pa1 int(10), IN par2 varchar(128))
BEGIN
...

The solution is to use sed (or another tool of your choice) to dynamically remove the offending clause:

root@ui-db-01:~# gunzip -c /path/to/dump.sql.gz | sed 's/DEFINER=[^ ]*// | mysql -h my_instance.eu-west-1.rds.amazonaws.com -u admin -p database

Troubleshooting¶

When the above command fails without a clear error message (eg just "MySQL server has gone away") make sure that max_allowed_packet is set to a high enough value. In particular check:

• that the RDS instance has been assigned a non-default parameter group (in case of Aurora make sure the parameter group is the same across all instances)
• that max_allowed_packet has been set to a high enough value (eg 512MB)

Connecting RDS as slave to a standary MySQL instance¶

When setting up the RDS instance from a mysql dump, the dump must have been created with --single-transaction and --master-data=2 parameters:

root@ui-db-01:~# mysqldump --defaults-extra-file=~/.my_ui.cnf -h localhost --single-transaction --master-data=2 --quick --routines --databases db1 db2 ... | gzip -c > /path/to/dump.sql.gz

Setting master-data=1 will lead to an error on restore as the admin user is not allowed to issue the CHANGE MASTER statement that would be included in the code.

With a dump that satisfies the requirements these steps are needed:

1. restore the dump (see above)
2. extract binlog coordinates from dump file
3. optional: create replication user on master or set replication password
4. Call mysql.rds_set_external_master() to configure replication

Extract binlog coordinates from dump file¶

root@ui-db-01:~# zgrep "CHANGE MASTER" /path/to/dump.sql.gz
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.001108', MASTER_LOG_POS=15046293;
root@ui-db-01:~#

Define replication user/password on master¶

If no replication user exists on the master, one has to be created:

mysql> CREATE USER 'replication_user' IDENTIFIED BY 's3cr3t';

If a replication user already exists, but the password is not known, give it a new password. We will need the password for the mysql.rds_set_external_master() call. Make sure that there are no further slaves replicating from this master or change the password everywhere!

mysql> SET PASSWORD FOR 'replication_user' = PASSWORD('s3cr3t');

(notice the additional use of the PASSWORD() function!)

Set up replication on slave¶

Configure replication with the coordinates we extracted above. If a slave is already running (eg in a broken state), stop it before calling mysql.rds_set_external_master(). Afterwards start replication:

mysql> CALL mysql.rds_stop_replication();
mysql> CALL mysql.rds_set_external_master('1.2.3.4', 3306, 'replication_user', 's3cr3t', 'mysql-bin.001108', 15046293, 0);
mysql> CALL mysql.rds_start_replication();