RDS database from Command line

RDS database from Command line

In cases where firewall is blocking connectin 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.

security-group

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;