Amazon RDS + MySQL Commands

Connect to Amazon RDS

1mysql -uaamnah --password --host=mysql.hostmarkaz.com

Creating MySQL database

1mysql> CREATE DATABASE databasename ;

Creating MySQL User

1mysql> CREATE USER 'username'@'host' IDENTIFIED BY 'password' ;

Allowing User to Connect
// grant usage on server so the user can connect

1mysql> GRANT USAGE ON *.* TO 'username'@'host' ;

Granting Privileges

1mysql> GRANT ALL PRIVILEGES ON databasename.* TO 'username'@'host' ;

CHECK if you can connect to the created database with the user you crteated

1mysql -uusername -p --host=mysql.hostmarkaz.com databasename

Change password

1alter user 'user'@'host' identified with mysql_native_password by 'PASSWORD';

Deleting MySQL User

1mysql> DROP USER 'username'@'host' ;

Import SQL database

1mysql -u username -p password databasename < filename.sql

List all users

1SELECT User,Host FROM mysql.user;

View Users and Permissions

1SELECT user, host, password, select_priv, insert_priv, shutdown_priv, grant_priv FROM mysql.user

View User Permissions for individual Databases

1SELECT user, host, db, select_priv, insert_priv, grant_priv FROM mysql.db

Sample Command:

1mysqldump --user=root --password=password --host=host.aamnah.com wpblog | mysql --host=host.hostmarkaz.com --user=aamnah --password=password wpblog

NOTES

• If can not connect, check if the port is open on the server you are connecting from. RDS uses 3306 (default MySQL port). On KH server it couldn’t connect because the port was closed
• If the connection is not getting through the error would be could not connect
• If login is incorrect the error would be ‘access denied’. mysql_connect(): Access denied for user
• The user you are connecting with needs to be created at RDS. It doesn’t matter if the server you are connecting from has it or not

Opening Ports

Make sure ports are opened in the firewall configuration.

This can be accessed in WHM » Plugins » ConfigServer Security & Firewall » Firewall configuration » Port settings.