Tag Archives: MariaDB

How to Move a Database with Users from One MariaDB RDS Instance to Another on AWS

Leave a reply

Migrating a database with its associated users between two MariaDB RDS instances on AWS can be daunting, but with careful planning, it’s manageable. This blog walks you through the detailed steps, including pre-migration preparations, database migration, user transfer, and post-migration validation.

1. Pre-Migration Checklist

Before starting, ensure the following:

  • Access Permissions: You have admin rights on both source and target RDS instances.
  • Backup Plan: Always have a recent backup in case anything goes wrong.
  • Network Configuration: Security groups and VPCs of both RDS instances must allow communication.
  • Downtime Notification: Notify stakeholders if downtime is required.
  • RDS Parameter: Compare the parameter and choose the largest value like Session, process etc
  • RDS Configuration: Compare the both RDS configuration and choose the largest one.

2. Exporting the Database from Source RDS

Dump the Database: Use mysqldump to export the database schema and data.

    mysqldump -h source-rds-endpoint -u admin -p --databases source_database_name > database_dump.sql

    Export User Accounts and Privileges: Since AWS RDS doesn’t allow direct access to the mysql.user table, export user creation and privilege statements using a custom query.

    Create a script file, e.g., export_users.sql:

    SELECT CONCAT('CREATE USER \'', user, '\'@\'', host, '\' IDENTIFIED BY PASSWORD \'', authentication_string, '\';')
FROM mysql.user
WHERE user NOT IN ('rdsadmin', 'mysql.sys');

    Execute the script:

    mysql -h source-rds-endpoint -u admin -p -e "source export_users.sql" > users_dump.sql

    3. Importing the Database to Target RDS

    Restore the Database: Import the database dump to the target RDS.

    mysql -h target-rds-endpoint -u admin -p < database_dump.sql

    Restore Users and Privileges: Use the exported users_dump.sql file to recreate users and their privileges.

    mysql -h target-rds-endpoint -u admin -p < users_dump.sql

    4. Post-Migration Steps

    1. Verify the Data: Ensure the data matches between the source and target databases. Use queries or tools like pt-table-checksum for verification.
    2. Test User Access: Confirm that users can log in and their privileges are intact: mysql -h target-rds-endpoint -u migrated_user -p
    3. Update Application Configuration: Change the database connection string in your application to point to the new RDS instance.
    4. Monitor the New RDS Instance: Check performance metrics and error logs in the AWS Management Console to ensure the target instance is operating smoothly.

    Note: Sometime SQLDUMP generate backup of database with definer. So to avoid this issue

    1. Command to Skip DEFINER During mysqldump

    The --skip-definer option isn’t natively supported in mysqldump, but you can work around this by excluding DEFINER references during or after the dump. we can use sed command as follows:

    This approach uses mysqldump to create a full dump and sed to strip out the DEFINER clause.

    mysqldump -h source-rds-endpoint -u admin -p \
--routines --triggers --databases your_database_name \
| sed -e 's/DEFINER=[^*]*\*/\*/g' > database_dump.sql

    2. Alternative: Editing the Dump After Export

    If you already have a dump file, use the sed command to edit it directly:

    sed -i 's/DEFINER=[^*]*\*/\*/g' database_dump.sql