Although, creating DB on RDS is very straightforward. You have to follow the steps shown while creating the DB on RDS. You can see multiple options to choose like Engine Type, DB template, instance size, Storage and additional settings menu to enable logging etc.
You can search keyword RDS in aws console search bar. It will show you RDS and by clicking on that, you can go to the RDS dashboard. Dashboard will show you the count of running DB instances, clusters, snapshots etc. You can create a new DB from the dashboard. Here are some steps you will see while creating a DB:
1. Click on “Create Database”
2. Choose creation method and DB engine
3. Select Template and set master username and password. By default, the master username is ‘admin’.
4. Select DB instance size as per your application’s need. Choose instance size wisely as this will cost specific.
5. In the storage section, you can select the storage option and enable/disable autoscaling.
6. There is an option to choose whether you want multiple availability zone deployment.
7 . Make DB public if you want your DB to be accessible from outside.
8. Additional configuration
In additional configuration, you can configure auto backup and logging.
After creating the database, you can see the DB instance on the RDS dashboard. Select your DB instance and check the endpoint. It is your hostname to make connections to RDS.
Connect to DB:
Syntax: mysql -u <master username> -p -h <endpoint>
prompt to enter master Password
If you are not able to make a connection to your DB, please ensure you have made it public while creating the DB. If you made it public then go to security groups(inbound rules) and allow it to be accessible from your IP or anywhere(if required).
After making the connection, you can create a user and set privileges.
Create user and set privileges
mysql> create database myDB; mysql> CREATE USER 'abhishek'@'%' IDENTIFIED BY 'dbAdmin@123'; mysql> GRANT ALL PRIVILEGES ON * . * TO 'abhishek'@'%'; ERROR 1045 (28000): Access denied for user 'admin'@'%' (using password: YES)
This gives the error as *.* work on mysql but not in RDS as RDS is amazon’s service so you have to specify the DB name instead of *.*
GRANT ALL PRIVILEGES ON myDB.* TO abhishek@'%'; exit;
Check by login using user created
Syntax: mysql -u <username> -p -h <hostname>;
$ mysql -u abhishek -p -h database-1.ccbnrszzzqen.ap-south-1.rds.amazonaws.com Password: Abhishek#123 $ show databases;