Search code examples
amazon-rdsamazon-aurora

Cannot exporting data from an Aurora MySQL DB instance for replication

I'm having some issues when trying to export binlog information and mysql dump with --master-data=1 from my Aurora MySQL instance. The error I'm receiving is

"mysqldump: Couldn't execute 'FLUSH TABLES WITH READ LOCK': Access denied for user 'user'@'%' (using password: YES) (1045)"

After some digging I found out that one way to do it is to create a read replica from the master, stop replication then perform the dump.

Sadly this does not work as I expected. In all AWS guides I've found they say to create a read replica from the "Actions" button, but I have no such option, doesn't even appear in the dropdown.

One option does appear, "Add a reader", which I did and after connecting to it, it seems like it's not a replica but more like a master with read only permissions, even if in the AWS console the "replica latency" column for that instance has a value attached to it.

It's a replica but it's not really a replica?

My main question here is how could I perform a dump of an Aurora MySQL in order to start replication on another instance?

I tried following most of the guides that are available from aws regarding mysql replication as well as lots of other stackoverflow questions.

Solution

  • Here is a quick guide on migrating AWS MySQL 5.7 to Azure MySQL Flex 5.7 and what challenges I have encountered in order to help other people with this as well.

    1. Create a new database from a snapshot taken from the master in AWS RDS with the bin_log parameter group enabled. This will now act as our new "fakeMaster". I will refer to it as "fakeMaster" from this point on.

    2. Create a replication user on your master

    CREATE USER 'replication-user'@'%' IDENTIFIED BY '<repl-user-password>';
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'replication-user';
FLUSH PRIVILEGES;
    1. Get bin log information from the master
    SHOW MASTER STATUS;
    1. Get bin log details from your fakeMaster
    SHOW BINARY LOGS;

    Correlate the binlog log name from your master with one of the results from the fakeMaster. This way you will find the bin log position. Copy the log_name and position from your fakeMaster, you will need them later.

    1. Make a full dump of the fakeMaster
    mysqldump -h <name>.<region>.rds.amazonaws.com -u <user> -p<password> --routines --column-statistics=0 <dbname> > dump.sql

    --column-statistics=0 is required only if you have MySQL version 8 installed

    1. If you performed the dump with MySQL client version 8, delete the following lines from dump.sql, otherwise skip this step.

    Those should be at the very top of the file

    ​​SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

--
-- GTID state at the beginning of the backup
--

SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ 'someid';

    This should be at the very end of the file

    SET @@SESSION.SQL_LOG_BIN = @MYSQLDUMP_TEMP_LOG_BIN;

    1. Power up your replica on Azure. If gtid_mode is enabled on your master (AWS), make sure your replica also has it enabled. This comes by default on HA(high availability) but if you're just testing things out you need to do the following extra steps to enable it.

      7.1 Change the setting of “enforce_gtid_consistency” to ON and save the setting.

      7.2 Change the setting of “gtid_mode” from OFF to OFF_PERMISSIVE and save the setting.

      7.3 Change the setting of “gtid_mode” from OFF_PERMISSIVE to ON_PERMISSIVE and save the setting.

      7.4 Change the setting of “gtid_mode” from ON_PERMISSIVE to ON and save the setting.

    2. Import dump from AWS fakemMaster to Azure replica

    cat dump.sql | mysql -h <host>.mysql.database.azure.com -u <user> -p<password> <db-name>
    1. Change master on Azure replica

    Resource: https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-data-in-replication?tabs=command-line#link-source-and-replica-servers-to-start-data-in-replication

    CALL mysql.az_replication_change_master('<host>.<region>.rds.amazonaws.com', 'replication-user', '<replication-user-password>', 3306, '<bin-log-file-from-step-4>', <bin-log-position-from-step-4>, <certificate-if-any>);
    1. Start replication
    CALL mysql.az_replication_start;
    1. Check status
    SHOW SLAVE STATUS\G;

    Here are a few useful commands in case you need:

    • Stop replication
    CALL mysql.az_replication_stop;
    • Remove master
    CALL mysql.az_replication_remove_master;
    • Import timezone database
    CALL mysql.az_load_timezone();

    Important to know: When testing things out make sure you are using MySQL client version 5.7 otherwise you would end up with something similar to this question:

    Aurora -> MySQL replication failing because of incompatible collation

    Other useful links you might want to read before or during replication:

    Replication requirements: https://learn.microsoft.com/en-us/azure/mysql/flexible-server/concepts-data-in-replication#requirements

    High overview of replication to azure: https://learn.microsoft.com/en-us/azure/mysql/flexible-server/how-to-data-in-replication?tabs=command-line

    VPN Peering: https://infra.engineer/azure/52-site-to-site-vpn-between-aws-and-azure

    For VPN peering, remember to allow the Azure local IP for inbound connections on port 3306 to your AWS database security group.

    Using the VPN Site-To-Site you can safely perform replications without exposing any of the databases to the internet.