# Migrating a MariaDB Database to Amazon RDS for MySQL or Amazon Aurora MySQL You can migrate data from existing on-premises MariaDB or [Amazon RDS for MariaDB](https://aws.amazon.com/rds/mariadb/?nc=sn&loc=3&dn=4) to [Amazon Aurora](https://aws.amazon.com/rds/aurora/) MySQL using [Database Migration Service](https://aws.amazon.com/dms/). Amazon Aurora is a MySQL and PostgreSQL-compatible relational database built for the cloud. Amazon Aurora features a distributed, fault-tolerant, self-healing storage system that auto-scales up to 64 TB per database instance. It delivers high performance and availability with up to 15 low-latency read replicas, point-in-time recovery, and continuous backup to Amazon S3, and replication across three Availability Zones (AZs). Some key features offered by Aurora MySQL are the following: + High throughput with low latency + Push-button compute scaling + Storage autoscaling + Custom database endpoints + Parallel queries for faster analytics In the following sections, we demonstrate migration from MariaDB as a source database to an Aurora MySQL database as a target using AWS DMS. At a high level, the steps involved in this migration are: + Provision MariaDB as a source DB instance and load the data + Provision Aurora Mysql as target DB instance + Provision DMS replication instance and create DMS endpoints + Create DMS task, migrate data and perform validation For the purpose of this section, we are using the AWS CloudFormation templates for creating Amazon RDS for MariaDB, Aurora MySQL database and AWS DMS replication instance with their source and endpoints. We will be loading sample tables and data in MariaDB located on [GitHub](https://github.com/aws-samples/aws-database-migration-samples). To estimate what it will cost to run this walkthrough on AWS, you can use the AWS Pricing Calculator. For more information, see [https://calculator.aws/](https://calculator.aws/). **Topics** + [Set up MariaDB as a source database](chap-mariadb2auroramysql.provisioningmariadb.md) + [Set up Aurora MySQL as a target database](chap-mariadb2auroramysql.provisioningauroramysql.md) + [Set up an AWS DMS replication instance](chap-mariadb2auroramysql.provisioningdms.md) + [Test the endpoints for MariaDB database migration](chap-mariadb2auroramysql.testendpoints.md) + [Create a migration task for a MariaDB database](chap-mariadb2auroramysql.createtask.md) + [Validate the MariaDB database migration](chap-mariadb2auroramysql.validate.md) + [Cut over for the migration from a MariaDB database](chap-mariadb2auroramysql.cutover.md) # Set up MariaDB as a source database To provision MariaDB as a source database, download [Mariadb\$1CF.zip with the YAML template](/dms/latest/sbs/samples/Mariadb_CF.zip). This AWS CloudFormation template creates an Amazon RDS for MariaDB instance with the required parameters. 1. On the [AWS Management Console](https://console.aws.amazon.com/), under **Services**, choose **CloudFormation**. 1. Choose **Create stack**, and then choose **With new resources (standard)**. 1. For **Specify template**, choose **Upload a template file**. 1. Select **Choose file**. 1. Choose the `Mariadb_CF.yaml` file, and then choose **Next**. 1. On the **Specify stack details** page, edit the predefined values as needed, and then choose **Next**: + **Stack name** — Enter a name for the stack. + **CIDR** — Enter the CIDR IP range to access the instance. + **DBAllocatedStorage** — Enter the database storage size in GB. The default is 20 GB. + **DBBackupRetentionPeriod** — The number of days to retain backups. + **DBInstanceClass** — Enter the instance type of the database server. + **DBMonitoringInterval** — Interval to publish database logs to Amazon CloudWatch. + **DBSubnetGroup** — Enter the DB subnet group name. For more information, see [Create a DB subnet group](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Tutorials.WebServerDB.CreateVPC.html#CHAP_Tutorials.WebServerDB.CreateVPC.DBSubnetGroup) in the *Amazon RDS User Guide*. + **MariaDBEngine** — Enter the MariaDB engine version.Leave empty to use default. For more information, see [MariaDB on Amazon RDS versions](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MariaDB.Concepts.VersionMgmt.html) in the *Amazon RDS User Guide*. + **DBMasterPassword** — Enter the master password for the DB instance. + **DBMasterUsername** — Enter the master user name for the DB instance. + **PreferredBackupWindow** — Enter the daily time range in UTC during which you want to create automated backups. + **PreferredMaintenanceWindow** — Enter the weekly time range in UTC during which system maintenance can occur. + **RDSDBName** — Enter the name of the database. + **RDSMultiAZ** — Choose **true** to use Amazon RDS Multi-AZ for this instance. The default value for this option is **false**. For more information, see [Multi-AZ deployments for high availability](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Concepts.MultiAZ.html) in the *Amazon Relational Database Service User Guide*. + **VPCID** — Enter the VPC to launch your DB instance. For more information, see [Working with a DB instance in a VPC](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_VPC.WorkingWithRDSInstanceinaVPC.html) in the *Amazon RDS User Guide*. Make sure that you entered the stack name, DB subnet group name, user name, password, database name, and VPC ID. 1. On the **Configure stack options** page, for **Tags**, specify any optional tags, and then choose **Next**. 1. On the **Review** page, select **I acknowledge that AWS CloudFormation might create IAM resources**, and then choose **Next**. 1. Choose **Create stack**. After the Amazon RDS for MariaDB instance is created, log in to MariaDB and run the following statements to create `webdb_user`, a superuser that connects to a DMS instance for migration, and grant necessary privileges. ``` CREATE USER 'webdb_user'@'%' IDENTIFIED BY '******'; GRANT ALL ON migrate.* TO 'webdb_user'@'%' with grant option; grant REPLICATION SLAVE ON *.* TO webdb_user; grant REPLICATION CLIENT ON *.* TO webdb_user; ``` In this walkthrough, we created a database called *migration* and few sample tables, along with stored procedures, triggers, functions, and so on. The following query provides the list of tables in *migration* database: ``` MariaDB [(none)]> use migration Database changed MariaDB [migration]> show tables; +---------------------+ | Tables_in_migration | +---------------------+ | animal_count | | animals | | contacts | | seat_type | | sport_location | | sport_team | | sport_type | +---------------------+ 7 rows in set (0.000 sec) ``` The following query returns a list of secondary indexes. ``` MariaDB [migration]> SELECT DISTINCT TABLE_NAME, INDEX_NAME,NON_UNIQUE -> FROM INFORMATION_SCHEMA.STATISTICS -> WHERE TABLE_SCHEMA = 'migration' and INDEX_NAME <> 'PRIMARY'; +----------------+-------------------+------------+ | TABLE_NAME | INDEX_NAME | NON_UNIQUE | +----------------+-------------------+------------+ | sport_location | city_id_sport_loc | 1 | | sport_team | sport_team_u | 0 | | sport_team | home_field_fk | 1 | +----------------+-------------------+------------+ 3 rows in set (0.000 sec) ``` The following query returns a list of triggers. ``` MariaDB [migration]> select TRIGGER_SCHEMA,TRIGGER_NAME -> from information_schema.triggers -> where TRIGGER_SCHEMA='migration'; +----------------+-----------------------+ | TRIGGER_SCHEMA | TRIGGER_NAME | +----------------+-----------------------+ | migration | increment_animal | | migration | contacts_after_update | +----------------+-----------------------+ 2 rows in set (0.001 sec) ``` The following query returns a list of procedures and functions. ``` MariaDB [(none)]> select routine_schema as database_name, -> routine_name, -> routine_type as type, -> data_type as return_type -> from information_schema.routines -> where routine_schema not in ('sys', 'information_schema', -> 'mysql', 'performance_schema'); +---------------+----------------+-----------+-------------+ | database_name | routine_name | type | return_type | +---------------+----------------+-----------+-------------+ | migration | CalcValue | FUNCTION | int | | migration | loadMLBPlayers | PROCEDURE | | | migration | loadNFLPlayers | PROCEDURE | | +---------------+----------------+-----------+-------------+ 3 rows in set (0.000 sec) ``` After all the data is loaded, use `mysqldump` to back up the database metadata. The `mysqldump` utility to dump one or more databases for backup or transfer to another database server. The dump typically contains SQL statements to create the table, populate it, or both. You can also use `mysqldump` to generate files in comma-separated value (CSV), other delimited text, or XML format. Use the following command exports tables and index definitions: ``` $ mysqldump --no-data --no-create-db --single_transaction -u root -p migration --skip-triggers > mysql_tables_indexes.sql ``` Use following command to exports routines (stored procedures, functions, and triggers) into the `routines.sql` file: ``` $ mysqldump -u root --routines --no-create-info --no-data --no-create-db --skip-opt -p migration > routines.sql ``` The `mysqldump` utility doesn’t provide the option to remove a `DEFINER` statement. Some MySQL clients provide the option to ignore the definer when creating a logical backup, but this isn’t the default behavior. Use the following command in a UNIX or Linux environment to remove the `DEFINER` from `routines.sql`: ``` $ sed -i -e 's/DEFINER=`root`@`localhost`/DEFINER=`master`@`%`/g' routines.sql ``` We now have a backup of MariaDB, in two `–0—sql` files (`mysql_tables_indexes.sql` and `routines.sql`). We will use these files to load the table definition into an Aurora MySQL database. After backups are completed into two .sql files (`mysql_tables_indexes.sql`, `routines.sql`), use these files to load the table definition into the Aurora MySQL database. # Set up Aurora MySQL as a target database To provision Aurora MySQL as a target database, download the [AuroraMysql\$1CF.yaml template](https://aws-database-blog.s3.amazonaws.com/artifacts/mariadb-to-aurora-mysql-migration/AuroraMysql_CF.yaml). This template creates an Aurora MySQL database with required parameters. 1. On the [AWS Management Console](https://console.aws.amazon.com/), under **Services**, choose **CloudFormation**. 1. Choose **Create stack**, and then choose **With new resources (standard)**. 1. For **Specify template**, choose **Upload a template file**. 1. Select **Choose file**. 1. Choose the `AuroraMySQL.yaml` file. 1. Choose **Next**. 1. On the **Specify stack details** page, edit the predefined values as needed, and then choose **Next**: + **Stack name** — Enter a name for the stack. + **CIDR** — Enter the CIDR IP range to access the instance. + **DBBackupRetentionPeriod** — The number of days for backup retention. + **DBInstanceClass** — Enter the instance type of the database server. + **DBMasterPassword** — Enter the master password for the DB instance. + **DBMasterUsername** — Enter the master user name for the DB instance. + **DBName** — Enter the name of the database. + **DBSubnetGroup** — Enter the DB subnet group. + **Engine** — Enter the Aurora engine version; the default is `5.7.mysql-aurora.2.03.4`. + **PreferredBackupWindow** — Enter the daily time range in UTC during which you want to create automated backups. + **PreferredMaintenanceWindow** — Enter the weekly time range in UTC during which system maintenance can occur. + **VPCID** — Enter the ID for the VPC to launch your DB instance in. 1. On the **Configure stack options** page, for **Tags**, specify any optional tags, and then choose **Next**. 1. On the **Review** page, choose **Next**. 1. Choose **Create stack**. After the Aurora MySQL database is created, log in to the Aurora MySQL instance: ``` $ mysql -h mysqltrg-instance-1.xxxxxxxxx.us-east-1.rds.amazonaws.com -u master -p migration -P 3306 MySQL [(none)]> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | awsdms_control | | mysql | | performance_schema | | source | | tmp | | webdb | +--------------------+ 7 rows in set (0.001 sec) MySQL [(none)]> create database migration; Query OK, 1 row affected (0.016 sec) MySQL [(none)]> use migration; Database changed MySQL [migration]> show tables; Empty set (0.001 sec) ``` Use `mysql_tables_indexes.sql` to create table and index structures in Aurora MySQL. ``` $ mysql -h mysqltrg-instance-1.xxxxxxxxx.us-east-1.rds.amazonaws.com -u master -p migration -P 3306 < mysql_tables_indexes.sql Enter password: $ ``` After the tables and indexes are successfully created, the next step is to set up and use AWS DMS. # Set up an AWS DMS replication instance To provision an AWS DMS replication instance, download the [DMS\$1CF.yaml template](https://aws-database-blog.s3.amazonaws.com/artifacts/mariadb-to-aurora-mysql-migration/DMS_CF.yaml). 1. On the [AWS Management Console](https://console.aws.amazon.com/), under **Services**, choose **CloudFormation**. 1. Choose **Create stack**. 1. For **Specify template**, choose **Upload a template file**. 1. Select **Choose File**. 1. Choose the `DMS_CF.yaml` file. 1. Choose **Next**. 1. On the **Specify Stack Details** page, edit the predefined values as needed, and then choose **Next**: + **Stack name** — Enter a name for the stack. + **AllocatedStorageSize** — Enter the storage size in GB. The default is 200 GB. + **DMSReplicationSubnetGroup** — Enter the subnet group for DMS replication. + **DMSSecurityGroup** — Enter the security group for DMS replication. + **InstanceType** — Enter the instance type. + **SourceDBPort** — Enter the source database port. + **SourceDatabaseName** — Enter the source database name. + **SourceServerName** — Enter the IP address of the source database server. + **SourceUsername** — Enter the source database user name. + **SourcePassword** — Enter the source database password. + **TargetDBPort** — Enter the target database port. + **TargetDatabaseName** — Enter the target database name. + **TargetServerName** — Enter the IP address of the target database server. + **TargetUsername** — Enter the target database user name. + **TargetPassword** — Enter the target database password. 1. On the **Configure stack options** page, for **Tags**, specify any optional tags, and then choose **Next**. 1. On the **Review** page, choose **I acknowledge that AWS CloudFormation might create IAM resources**. 1. Choose **Create Stack**. This AWS CloudFormation template creates a replication instance named `mariadb-mysql`. This replication instance has a source endpoint named `maria-on-prem` and a target endpoint named `mysqltrg-rds`. This target endpoint has extra connection attributes to disable foreign key constraint checks during the AWS DMS replication, as shown following. ``` ExtraConnectionAttributes : "initstmt=SET FOREIGN_KEY_CHECKS=0;parallelLoadThreads=1" ``` # Test the endpoints for MariaDB database migration 1. On the navigation pane, choose **Endpoints**. 1. Choose the source endpoint name (`maria-on-prem`) and do the following: 1. Choose **Test connections**. 1. Choose the replication instance to test (`mariadb-mysql`). 1. Choose **Run Test** and wait for the status to be **successful**. 1. On the navigation pane, choose **Endpoints**. 1. Choose the target endpoint name (`mysqltrg-rds`) and do the following: 1. Choose **Test Connections**. 1. Choose the replication instance to test (`mariadb-mysql`). 1. Choose **Run Test** and wait for the status to be **successful**. **Note** If **Run Test** returns a status other than **successful**, the reason for the failure is displayed. Make sure that you resolve the issue before proceeding further. # Create a migration task for a MariaDB database We’ve now verified that the replication instance can connect to both the source and target endpoints. The next step is to create a database migration task. 1. On the navigation pane, choose **Database Migration Tasks**. 1. Choose **Create Task**. Provide the specified values for the following, and then choose **Next**: + **Task identifier** — `maria-mysql` + **Replication instance** — Choose the replication instance, `mariadb-mysql`. + **Source database endpoint** — Choose the source database, `maria-on-prem`. + **Target database endpoint** — Choose the target database, `mysqltrg-rds`. + **Migration Type** — Choose **Migrate existing data and replicate ongoing changes** for CDC, or **Migrate existing data** for full load. 1. For **Task settings**, choose the following settings: + **Target table preparation mode** — Do nothing + **Stop task after full load completes** — Don’t stop + **Include LOB columns in replication** — Limited LOB mode + **Maximum LOB size (KB)** — 32 + **Enable validation** + **Enable CloudWatch logs** 1. For **Table mappings**, choose the following settings: + Schema — Choose **migration** (assuming the schema and database to be migrated appear correctly). + Table name — Enter the table name, or `%` to specify all the tables in the database. + Action — Enter **Include** to include specific tables, or **Exclude** to exclude specific tables. 1. Choose **Create Task**. Your new AWS DMS migration task reads the data from the tables in the MariaDB source and migrates your data to the Aurora MySQL target. You can use an AWS DMS full-load-only migration task to migrate views or a combination of tables and views. For more information, see [Specifying table selection and transformations rules](https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.html) in the *DMS User Guide*. # Validate the MariaDB database migration AWS DMS performs data validation to confirm that your data successfully migrated the source database to the target. You can check the **Table statistics** page to determine the DML changes that occurred after the AWS DMS task started. During data validation, AWS DMS compares each row in the source with its corresponding row at the target, and verifies that those rows contain the same data. To accomplish this, AWS DMS issues the appropriate queries to retrieve the data. After your data is loaded successfully, you can select your task on the AWS DMS page and choose **Table statistics** to show statistics about your migration. The following screen shot shows the **Table statistics** page and its relevant entries. The following screenshot shows the table statics page and its relevant entries. ![\[Table statistics\]](http://docs.aws.amazon.com/dms/latest/sbs/images/sbs-mariadb2aurmysql-validation.png) AWS DMS can validate the data between source and target engines. The **Validation state **column helps us to validate the data migration. This ensures that your data was migrated accurately from the source to the target. # Cut over for the migration from a MariaDB database After the data validation is complete and any problems resolved, you can load the database triggers, functions, and procedures. To do this, use the `routines.sql` file generated from MariaDB to create the necessary routines in Aurora MySQL. The following statement loads all procedures, functions, and triggers into the Aurora MySQL database. ``` $ mysql -h mysqltrg-instance-1.xxxxxxxxx.us-east-1.rds.amazonaws.com -u master -p migration -P 3306 < routines.sql ``` After the routines are loaded, connect to the Aurora MySQL database to validate as shown following. ``` $ mysql -h mysqltrg-instance-1.xxxxxxxxx.us-east-1.rds.amazonaws.com -u master -p migration -P 3306 Enter password: Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Welcome to the MariaDB monitor. Commands end with ; or \g. Your MySQL connection id is 957 Server version: 5.6.10 MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. Enter 'help;' or '\h' for help. Enter '\c' to clear the current input statement. MySQL [migration]> select routine_schema as database_name, -> routine_name, -> routine_type as type, -> data_type as return_type -> from information_schema.routines -> where routine_schema not in ('sys', 'information_schema', -> 'mysql', 'performance_schema'); +---------------+----------------+-----------+-------------+ | database_name | routine_name | type | return_type | +---------------+----------------+-----------+-------------+ | migration | CalcValue | FUNCTION | int | | migration | loadMLBPlayers | PROCEDURE | | | migration | loadNFLPlayers | PROCEDURE | | +---------------+----------------+-----------+-------------+ 3 rows in set (0.002 sec) MySQL [migration]> select TRIGGER_SCHEMA, TRIGGER_NAME from information_schema.triggers where TRIGGER_SCHEMA='migration'; +----------------+-----------------------+ | TRIGGER_SCHEMA | TRIGGER_NAME | +----------------+-----------------------+ | migration | increment_animal | | migration | contacts_after_update | +----------------+-----------------------+ 2 rows in set (0.009 sec) ``` The preceding output shows that all the procedures, triggers, and functions are loaded successfully to the Aurora MySQL database.