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.
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 = %
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:-
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!