RDS database from Command line
In cases where firewall is blocking connection to RDS through sequel pro then another way to connect is using another server. You need to create a security group then on inbound tab add mysql and then under source should be from anywhere i.e. this 0.0.0.0/0.0.
To assign that security group to RDS by going to that RDS->modify->pickup your security group. Once that don type in this command:
mysql -h staging.cn6xw5jrfyba.eu-west-1.rds.amazonaws.com -u staging_master –p
then enter your password.
You may also need to install mysql client such as mysql-client-core-5.6
From here you can create a database. You can do a database dump like so this commnd:
mysqldump -u user -p database > /mnt/sites/db_dump.sql
Then give the user privileges to this database:
GRANT ALL PRIVILEGES ON database-name.* TO "user"@"hostname" IDENTIFIED BY "password"; GRANT ALL PRIVILEGES ON caw_2016_staging.* TO "user"@"rddb" IDENTIFIED BY "password"; Then flush previleges as below: FLUSH PRIVILEGES;
To remove a user:
DROP USER 'jeffrey'@'localhost';
The above will create the user as well. To see the users on the DB type in this command;
SELECT User FROM mysql.user;