Migrating existing MySQL database into Amazon RDS

Migrating existing MySQL database into Amazon RDS

Amazon RDS  is a completely managed service by Amazon. It makes database maintenance very easy due to the many possibilities it offers, including automatic backups, point-in-time recovery, snapshots, scalability and replication.

It currently supports MySQL as well as other databases, such as MariaDB, PostgreSQL, Oracle, Microsoft SQL Server, and the MySQL-compatible Amazon Aurora DB engine.

MySQL major versions 5.5, 5.6, and 5.7

  • Supports major Mysql versions 5.5, 5.6, and 5.7
  • Supports database size up to 6 TB.
  • Instances offer up to 32 vCPUs and 244 GiB Memory.
  • Supports automated backup and point-in-time recovery.
  • Supports cross-region read replicas.

RDS Limitations

As Amazon takes care of infrastructure, backups and updates on the Amazon RDS DB instance, the shell access to the instance is disabled.  We can access the instance through Mysql console from other machines.

Steps to Migrate existing Mysql database to Amazon RDS

Note: This tutorial assumes that you have already setup new RDS instance and enabled connection between this instance and connecting machine where database is currently hosted, via security groups.

Execute below command:

mysqldump <db_name> | mysql --host=<hostname> --user=<db_user> -p<password> db_name

Example:

mysqldump az_demo | mysql --host=azdemo.cwddfrlq2.us-east-1.rds.amazonaws.com --user=az_demo -MySecretPass!! az_demo

[Note: Above values are not real,  focus on syntax]

You may need to supply DB credentials in your existing system for Mysql dump.  Command will look as below

mysqldump --user=demo -pDeMoPass az_demo | mysql --host=azdemo.cwddfrlq2.us-east-1.rds.amazonaws.com --user=az_demo -pMySecretPass!! az_demo 

Replace username, password and DB connections as per your setup.

If you want more consistent Database migration, execute below command as recommended by Amazon

mysqldump -u <local_user> \
--databases <database_name> \
--single-transaction \
--compress \
--order-by-primary \
-p<local_password> | mysql -u <RDS_user> \
--port=<port_number> \
--host=<host_name> \
-p<RDS_password>

    • --single-transaction – Use to ensure that all of the data loaded from the local database is consistent with a single point in time. If there are other processes changing the data while mysqldump is reading it, using this option helps maintain data integrity.
    • --compress – Use to reduce network bandwidth consumption by compressing the data from the local database before sending it to Amazon RDS.
  • --order-by-primary – Use to reduce load time by sorting each table’s data by its primary key.
Share on facebook
Facebook
Share on twitter
Twitter
Share on pinterest
Pinterest
Share on linkedin
LinkedIn
More Interested Related Posts
zfs pool usage

ZFS Pool and Dataset Usage

ZFS Pool and Dataset Usage   I am back with another blog to provide the useful script to the Freebsd users to determine the zpool

Assistanz Networks

Installing Zabbix Agent in FreeBSD 12.0

Zabbix Agent Installation and Configuration Guide on FreeBSD 12.0   Zabbix is an open-source monitoring software tool for diverse IT components, including networks, servers, virtual

DirectAdmin Server Management Plans

Premium Support

24/7 End User Support from your Helpdesk
$ 99 Monthly / Server
  • Unlimited Support Plan
  • 24/7 Emergency Phone
  • Chat Support for Admin
  • Separate Account Manager
  • NDA & SLA
  • SLA Review Meetings
  • FREE Consultancy Services
  • Simple SignUp Process
  • Instant Account Activation
  •  

Platinum Support

24/7 Proactive Support
$ 49 Monthly / Server
  • Unlimited Support Plan
  • 24/7 Emergency Phone
  • Chat Support for Admin
  • Separate Account Manager
  • Advance Proactive Monitoring
  • Guaranteed SLA
  • SLA Review Meetings
  • 3rd Party Application Support
  • FREE Consultancy Services
  • Server Migration Support
  • Weekly Status Report
  • No End User Support
Popular

Unlimited Support

24/7 Support

$ 30 Monthly / Server
  • Unlimited Support Plan
  • 24/7 Support
  • Basic Monitoring
  • 30 Minutes Response Time
  • 4 Hours Resolution time for the Possible Issues
  • Security and Performance Optimization
  • React to Customers queries
  • Simple SignUp Process
  • Instant Account Activation
  • No Third party application support
  • No Migration and End User Support

Cpanel Server Management Plans

Premium Support

24/7 End User Support from your Helpdesk

$ 99 Monthly / Server
  • 24/7 End User White Label Support
  • Unlimited Number of Tickets
  • Chat Support for Admin
  • Separate Account Manager
  • Guaranteed SLA
  • Weekly Status Reports
  • FREE Consultancy Services
  • Simple SignUp Process
  • Instant Account Activation

Platinum Support

24/7 Proactive Server Management
$ 49 Monthly / Server
  • Unlimited Support Plan
  • 24/7 Emergency Phone
  • Chat Support for Admin
  • Separate Account Manager
  • Advance Proactive Monitoring
  • Guaranteed SLA
  • SLA Review Meetings
  • 3rd Party Application Support
  • FREE Consultancy Services
  • Server Migration Support
  • Weekly Status Report
  • No End User Support
Popular

Unlimited Support

24/7 Server Management

$ 29 Monthly / Server
  • Unlimited Admin Tasks
  • 24/7 Support
  • Basic Monitoring
  • 30 Minutes Response time
  • 4 hours Response time for possible issues
  • Security and Performance Optimization
  • React to Customers queries
  • No Third party application support
  • No Migration and End User Support

Cpanel Server Management Plans

Premium Support

24/7 End User Support from your Helpdesk

$ 99 Monthly / Server
  • 24/7 End User White Label Support
  • Unlimited Number of Tickets
  • Chat Support for Admin
  • Separate Account Manager
  • Guaranteed SLA
  • Weekly Status Reports
  • FREE Consultancy Services
  • Simple SignUp Process
  • Instant Account Activation

Platinum Support

24/7 Proactive Server Management
$ 49 Monthly / Server
  • Unlimited Support Plan
  • 24/7 Emergency Phone
  • Chat Support for Admin
  • Separate Account Manager
  • Advance Proactive Monitoring
  • Guaranteed SLA
  • SLA Review Meetings
  • 3rd Party Application Support
  • FREE Consultancy Services
  • Server Migration Support
  • Weekly Status Report
  • No End User Support
Popular

Unlimited Support

24/7 Server Management

$ 29 Monthly / Server
  • Unlimited Admin Tasks
  • 24/7 Support
  • Basic Monitoring
  • 30 Minutes Response time
  • 4 hours Response time for possible issues
  • Security and Performance Optimization
  • React to Customers queries
  • No Third party application support
  • No Migration and End User Support