How to move your database to amazon RDS


#1

Has anyone moved their database to an amazon RDS instance?

I would really like to do this but I can’t get it working, I have several websites created using ee on a EC2 local database.

Any help much appreciated.

UPDATE*

So I worked it out in the end, you need to do the following in case any one else wants to do the same thing and gets stuck!

I use Ubuntu 14.04 btw…

First off create RDS in amazon following amazon instructions…

Backup your local databases using mysql (mybackup.sql) and note DB names, usernames and passwords from wp-config.php of each web site!

i.e. - /var/www/website.com/wp-config.php

vim /etc/ee/ee.conf  **edit this file and change :-  

grant-host = localhost

TO

grant-host = %

Then…

vim ~/.my.cnf

and change:-

## [client]
## user = root
## password = IUGUOYVKU

TO

## [client]
## user = root
## password = IUGUOYVKU
[client]
host=amazondbname.ksdblviebr.eu-central-1.rds.amazonaws.com
user=amazon_rds_master_user_name
password=your_amazon_master_password

Go to remote DB by using:-

mysql -h amazondbname.ksdblviebr.eu-central-1.rds.amazonaws.com -P 3306 -u amazon_rds_master_user_name -pyour_amazon_master_password

(note there is space between -u and usernamebut no space between -p and password!!)

Then import database by using:-

source /local/path/to/this/file/on/your/server/mybackup.sql;

Then you need to create users and permissions:-

CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'password';

(Do this for every user in your list!!)

Then create permissions for your users:-

GRANT CREATE, DROP, DELETE, INSERT, SELECT, UPDATE ON databasename.* TO 'username'@'%';

(Do this for every user you have created!!)

before you exit mysql :-

FLUSH PRIVILEGES;

Then type quit to exit

One last change, for every website wp-config.php:-

change.....

/** MySQL hostname */
define( 'DB_HOST', 'localhost' );

to

/** MySQL hostname */
define( 'DB_HOST', 'amazondbname.ksdblviebr.eu-central-1.rds.amazonaws.com' );

then you can go ahead and stop local mysql to test it all works…

service mysql stop

Go and check your websites still work, all done!


#2

For workaround of Amazon RDS instance with easyengine, Please refer below doc :


#3

Thank you for adding this for everyone, could you not also convert my explanation into an article in the docs section for those people that really don’t like PMA, like me?

Also your document doesn’t cover how to change from mysql on server to Amazon RDS once you already have websites set up…

Thank you


#4