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!