How to Migrate from RDS to MySQL on EC2

How to Migrate from RDS to MySQL on EC2

Introduction

A year ago I moved all my websites to AWS using the free tier, using an EC2 t2.micro and the RDS relational database service. Now that my year of free tier is up I’d like to reduce my costs. I want to migrate from RDS to MySQL on EC2.

My t2.micro runs Amazon Linux at around 1-2% CPU usage, probably because of the heavy caching and CDN I have in place. It has a few small spikes up to 20% when backups run, but CPU credits sits on maximum. I have plenty of headroom to move MySQL onto the server itself, eliminating the cost of RDS. There are downsides like no automatic backups and you lose some enterprise features, but I have my own backup scripts already and I can tolerate quite a lot of downtime. Amazon has a useful service called the Database Migration Service, which initially appeared ideal for this task (more on that later)

Moving MySQL from RDS to the EC2 instance using the AWS Database Migration Service is fairly straightforward, as described below. HOWEVER (and it is a very big however) the DMS doesn’t copy indexes or properties, so it actually makes a massive mess that takes a heck of a long time to clean up. So while it will work this way, I STRONGLY encourage people to use a different method for migration – I would do a dump using the mysql tool then import that into MySQL running on the EC2 instance.

Once I finished my EC2 install of MySQL I realised the default mysql-server package on AWS is MySQL 5.5. This lacks some features I need, such as “DEFAULT CURRENT_TIMESTAMP”, so I have to go through some pain to upgrade to MySQL 5.6. I looked at MySQL 5.7, and it’s quite easy to install using the official MySQL community repository, but I expect it would bring additional trouble and effort, so I didn’t bother with it for now.

Once this was all done I changed my EC2 instance from an t2.micro to an t2.nano, which reduces available CPU and RAM by half. This runs MySQL in a low memory configuration, PHP 5.6, and Nginx very well. It can use a full Xeon core as required, eating into CPU credits, so it’s as fast as the t2.micro – just not for as long. In terms of RAM it’s all used but of that 210MB is cached, which tells me that it’s really only using 293MB of RAM.

You can see how to upgrade from MySQL 5.5 to 5.6 on Amazon Linux below.

Installing MySQL 5.6 on Amazon Linux

I used the following commands to install MySQL on my EC2 instance

 

sudo su
yum install mysql56-server

To install the MySQL 5.7 from the official repository use these commands – note that I didn’t do this, I stuck with 5.6 as it does what I need and is in an amazon repo

sudo yum localinstall http://repo.mysql.com/mysql57-community-release-el6.rpm

yum list all | grep mysql

yum install mysql-community-server

Whichever version of MySQL you use, here’s what you do next to set up MySQL to run and set up admin users.

chkconfig mysqld on
service mysqld start
mysqladmin -u root password
mysqladmin -u root -p create [your_new_db]

Inside MySQL console

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'PASSWORD_GOES_HERE' WITH GRANT OPTION;
flush privileges;

Creating MySQL Users

mysql -h localhost -u root -p
CREATE USER '**USER'@'localhost' IDENTIFIED BY '**PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON **DATABASE.* TO 'USER'@'localhost' IDENTIFIED BY '**PASSWORD';
flush privileges;

If need to recreate users because you messed something up

delete from mysql.user where user='hr_user';
drop user [email protected];

flush privileges;

 

MySQL Settings and Configuration for Low RAM Systems

Note that it’s important if commands are in the mysqld or msqld_safe sections. This configuration goes into the file my.cnf

vi /etc/my.cnf

If you want to work out which configuration file MySQL is using you can use these commands

which mysqld
/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

 

The following changes need to be made

[mysqld]

# Disable performance schema to hugely reduce RAM usage
performance_schema = OFF

# I'm not sure if these two commands make any difference, but they don't seem to hurt
innodb_buffer_pool_size=30M
innodb_log_buffer_size=256K

[mysqld_safe]

# Settings to reduce RAM
innodb_buffer_pool_size=25M
innodb_log_buffer_size=256K
query_cache_size=10000
max_connections=30
key_buffer_size=80
innodb_ft_cache_size=1600000
innoinnodb_ft_total_cache_size=32000000
table_definition_cache=150

# Settings to reduce RAM: per thread or per operation settings
thread_stack=131072
sort_buffer_size=32K
read_buffer_size=8200
read_rnd_buffer_size=8200
max_heap_table_size=16K
tmp_table_size=50K
bulk_insert_buffer_size=100
join_buffer_size=128
net_buffer_length=1K
innodb_sort_buffer_size=64K

# Settings to reduce RAM: settings that relate to the binary log (if enabled)
binlog_cache_size=4K
binlog_stmt_cache_size=4K

 

After you’ve made these changes restart MySQL

service mysqld restart

 

Migrating from RDS to EC2 MySQL using mysqldump

This is the recommended method of migrating databases between servers.

Run this command to export the database contents.

mysqldump -u root -p -h RDS_HOST_NAME DATABASE_NAME> /tmp/databasename.sql

Run the following commands to log into MySQL, create a new database, and import the dump you created.

mysql -u root -p   (followed by the password when prompted)
create database mydb;
use mydb;
source /tmp/databasename.sql;
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE ON DATABASENAME.* TO 'USER'@'localhost' IDENTIFIED BY 'INSERT_PASSWORD';

That’s it. Quick and simple.

 

Migrating from RDS to EC2 MySQL using AWS Database Migration Service

Once again I need to say THIS IS A TERRIBLE WAY TO MIGRATE WORDPRESS. It doesn’t migrate secondary indexes, stored procedures, or auto_increment values, and fixing this took me about half a day on my installs. You can see how to fix WordPress auto_increment fields and indexes here.

The DMS creates a migration instance that does the actual migration for you. You need to ensure this instance is in a VPC and subnet that can access both the RDS instance and the EC2 instance. You can find more detail about this here.

Start the database migration service, which for me is at this URL and step through the first obvious screen.

Create the replication instance – I used a t2.micro as I have small databases.

AWS database migration service setup 1

 

Configure the migration. Make sure you hit the test buttons to ensure connectivity

AWS database migration service setup 2

 

Set up what you want migrated. Unfortunately at this point when I was trying to create screenshots I hit the wrong button and the DMS wizard exited, so I don’t have a screenshot here. It’s fairly simpleto configure, but note that you have to manually start the migration once it’s setup.

All in all I found the DMS very easy to configure and run, but sadly lacking in areas that only became obvious once the migration was complete and RDS was turned off. Better testing would’ve uncovered these issues earlier. Of course I have an RDS snapshot but since the data has changed it’s too late to do this again. I could dump the database schema then import data manually, and that’s what I’ll probably do at some point.

Important note: make sure after you’re done that you delete your tasks, endpoints, and replication instances to avoid additional costs. You’ll also want to close any security group or NACL ports you opened for the migration.

 

Upgrading from MySql 5.5 to MySql 5.6 on Amazon Linux

The following are the commands I used to upgrade from MySQL 5.5 to MySQL 5.6 on my EC2 amazon linux instance.

sudo su
yum list installed | grep mysql
service mysqld stop
yum remove mysql mysql-config mysql-server mysql55-libs mysql55-server
yum install mysql56-server
vi /etc/my.cnf

(Update the my.cnf as per the settings above – this is important or the server won’t start if you don’t have enough RAM)

service mysqld start
mysql_upgrade -u root -p
mysql -h localhost -u root -p    (then enter your password)
show databases;    (this shows that your databases still exist)
service php-fpm-5.6 restart
service nginx restart

 

 

References

MySQL install guide

Reduce MySQL memory

MySQL 5.7 installation

Facebook Comments

Leave a Reply

Your email address will not be published.