How do i create a database connection in mysql?

Question

The easiest way to create a DB instance is to use the AWS Management Console. After you create the DB instance, you can use standard MySQL utilities such as MySQL Workbench to connect to a database on the DB instance.

Table of Contents Show

Creating a MySQL DB instance
Connecting to a database on a DB instance running the MySQL database engine
Deleting a DB instance
How do I create a database connection?
How do I create a database connection in MySQL Workbench?
How do I access my MySQL database from another computer?
How do I create a new database in MySQL?

Topics

Creating a MySQL DB instance
Connecting to a database on a DB instance running the MySQL database engine
Deleting a DB instance

Creating a MySQL DB instance

The basic building block of Amazon RDS is the DB instance. This environment is where you run your MySQL databases.

You can use Easy create to create a DB instance running MySQL with the AWS Management Console. With Easy create, you specify only the DB engine type, DB instance size, and DB instance identifier. Easy create uses the default settings for the other configuration options. When you use Standard create instead of Easy create, you specify more configuration options when you create a database, including ones for availability, security, backups, and maintenance.

In this example, you use Easy create to create a DB instance running the MySQL database engine with a db.t2.micro DB instance class.

To create a MySQL DB instance with Easy create

Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
In the upper-right corner of the Amazon RDS console, choose the AWS Region in which you want to create the DB instance.
In the navigation pane, choose Databases.
Choose Create database and make sure that Easy create is chosen.
In Configuration, choose MySQL.
For DB instance size, choose Free tier.
For DB instance identifier, enter a name for the DB instance, or leave the default name.
For Master username, enter a name for the master user, or leave the default name.
The Create database page should look similar to the following image.
To use an automatically generated master password for the DB instance, make sure that the Auto generate a password box is selected.
To enter your master password, clear the Auto generate a password box, and then enter the same password in Master password and Confirm password.
(Optional) Open View default settings for Easy create.
You can examine the default settings used with Easy create. The Editable after database is created column shows which options you can change after database creation.
- To change settings with No in that column, use Standard create.
- To change settings with Yes in that column, either use Standard create, or modify the DB instance after it is created to change the settings.
The following are important considerations for changing the default settings:
- In some cases, you might want your DB instance to use a specific virtual private cloud (VPC) based on the Amazon VPC service. Or you might require a specific subnet group or security group. If so, use Standard create to specify these resources. You might have created these resources when you set up for Amazon RDS. For more information, see Provide access to your DB instance in your VPC by creating a security group.
- If you want to be able to access the DB instance from a client outside of its VPC, use Standard create to set Public access to Yes.
  If the DB instance should be private, leave Public access set to No.
Choose Create database.
If you chose to use an automatically generated password, the View credential details button appears on the Databases page.
To view the master user name and password for the DB instance, choose View credential details.
You can use the user name and password that appears to connect to the DB instance as the master user.
You can't view the master user password again. If you don't record it, you might have to change it.

If you need to change the master user password after the DB instance is available, you can modify the DB instance to do so. For more information about modifying a DB instance, see Modifying an Amazon RDS DB instance.
In the Databases list, choose the name of the new MySQL DB instance.
On the RDS console, the details for new DB instance appear. The DB instance has a status of Creating until the DB instance is ready to use. When the state changes to Available, you can connect to the DB instance. Depending on the DB instance class and the amount of storage, it can take up to 20 minutes before the new instance is available.

Connecting to a database on a DB instance running the MySQL database engine

After Amazon RDS provisions your DB instance, you can use any standard SQL client application to connect to a database on the DB instance. In this example, you connect to a database on a MySQL DB instance using MySQL monitor commands.

To connect to a database on a DB instance using MySQL monitor

Install a SQL client that you can use to connect to the DB instance.
You can connect to a MySQL DB instance by using tools such as the mysql command line utility. For more information on using the MySQL client, see mysql - the MySQL command-line client in the MySQL documentation. One GUI-based application that you can use to connect is MySQL Workbench. For more information, see the Download MySQL Workbench page.
For more information on using MySQL, see the MySQL documentation. For information about installing MySQL (including the MySQL client), see Installing and upgrading MySQL.
If your DB instance is publicly accessible, you can install the SQL client outside of your VPC. If your DB instance is private, you typically install the SQL client on a resource inside your VPC, such as an Amazon EC2 instance.
Make sure that your DB instance is associated with a security group that provides access to it. For more information, see Provide access to your DB instance in your VPC by creating a security group.
If you didn't specify the appropriate security group when you created the DB instance, you can modify the DB instance to change its security group. For more information, see Modifying an Amazon RDS DB instance.
If your DB instance is publicly accessible, make sure its associated security group has inbound rules for the IP addresses that you want to access it. If your DB instance is private, make sure its associated security group has inbound rules for the security group of each resource to access it. An example is the security group for an Amazon EC2 instance.
Find the endpoint (DNS name) and port number for your DB instance.
1. Open the RDS console and then choose Databases to display a list of your DB instances.
2. Choose the MySQL DB instance name to display its details.
3. On the Connectivity & security tab, copy the endpoint. Also, note the port number. You need both the endpoint and the port number to connect to the DB instance.
Connect to a database on a MySQL DB instance. For example, enter the following command at a command prompt on a client computer. By doing this, you connect to a database on a MySQL DB instance using the MySQL client.
Substitute the DNS name for your DB instance for <endpoint>. In addition, substitute the master user name that you used for <mymasteruser>, and provide the master password that you used when prompted for a password.
```
PROMPT> mysql -h <endpoint> -P 3306 -u <mymasteruser> -p
```
After you enter the password for the user, you should see output similar to the following.
```
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9738
Server version: 8.0.23 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>
```

For more information about connecting to a MySQL DB instance, see Connecting to a DB instance running the MySQL database engine. If you can't connect to your DB instance, see Can't connect to Amazon RDS DB instance.

Deleting a DB instance

After you connect to and explore the sample DB instance that you created, delete it so you're no longer charged for it.

To delete a DB instance with no final DB snapshot

Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
In the navigation pane, choose Databases.
Choose the DB instance that you want to delete.
For Actions, choose Delete.
For Create final snapshot?, choose No, and select the acknowledgment.
Choose Delete.