How to Connect to an Amazon RDS MySQL Database

AWS RDS allows you easily to set up, manage, and scale MySQL databases in the cloud. You can deploy multiple editions of MySQL with resizable compute capacity.

With AWS RDS, you can focus on the application deployment while the system operates database administration tasks such as provisioning, monitoring, software patching, backups, hardware scaling, etc.

The fastest way to back up and restore a database is to use full backup files (.bak files). You can access files stored in AWS S3 instead of using the local file system on a database server.

You can back up your local server, save it on S3, and restore it onto an existing RDS database instance any time you need. Also, it's possible to make backups from AWS RDS, store them on S3, and restore them wherever you want.

AWS RDS supports AWS Aurora, MySQL, MariaDB, Oracle, SQL Server, and PostgreSQL database engines.

To connect to an Amazon RDS MySQL instance from your local command line interface (CLI), run the following command:

mysql -h your-rds-endpoint.amazonaws.com -P 3306 -u yourUsername -p

You will need:

• The MySQL client installed on your machine
• The endpoint of your RDS instance
• The port number to connect to AWS RDS MySQL
• Your RDS username and password

1. Connect to your RDS MySQL instance.

mysql -h your-endpoint -P 3306 -u your_username -p

Replace:

• your_endpoint with your RDS endpoint (e.g., mydb.123456789012.us-east-1.rds.amazonaws.com)
• your_username with your database username

2. Select your database.

USE your_database_name;

Replace your_database_name with the actual database name.

3. Create a stored procedure.

DELIMITER //

CREATE PROCEDURE GetAllUsers()
BEGIN
    SELECT * FROM users;
END //

DELIMITER ;

Replace GetAllUsers() and users with your procedure name and database table.

AWS RDS provides the following advantages for MySQL:

• Automated backup and recovery: you can customize the backup and recovery of a MySQL database instance at any point in time and within a specified retention period.
• Easy deployments: in AWS RDS, it's possible to pre-configure settings and parameters for MySQL database instances and launch them just in a few clicks.
• Security and isolation: AWS RDS provides the tools (VPC, KMS) that guarantee a high level of security for MySQL databases.
• Monitoring: you can use Amazon CloudWatch metrics for your database instances with no extra charge. Additionally, AWS RDS Enhanced Monitoring allows you to access 50 CPU, memory, file system, and disk I/O metrics.
• High availability and read replicas: AWS RDS Multi-AZ deployments present enhanced availability for database instances and RDS Read Replicas simplify flexible scaling beyond the capacity limitation of a database instance.
• Storage: AWS RDS has two SSD-backed storage options for a MySQL database. If required, you can get additional storage with no downtime.

You should know that you may face the following restrictions in AWS RDS for MySQL:

• You cannot use InnoDB as the name of a MySQL database, table, or column, since it's a reserved word in RDS for MySQL.
• If storage of a MySQL database instance is full, you can have metadata inconsistencies, dictionary mismatches, orphan tables, etc. To avoid these issues, AWS RDS automatically stops the instance with the storage-full state.
• Currently, there is a bug in managing the InnoDB buffer pool size for MySQL v5.7. The value of the innodb_buffer_pool_size parameter can be set to a large value. This leads to the fact that the InnoDB buffer pool grows and uses much memory. Such an effect impacts negatively on the MySQL database engine. It can stop working or launching.
• Also, there is a bug in the MySQL query optimizer for MySQL v5.5.37. You may get incorrect results if queries use index merge optimization.
• AWS RDS does not support the MySQL keyring_aws Amazon Web Services Keyring Plugin.
• AWS RDS has a case-sensitive file system. That's why you should keep your eye on some MySQL values, which you want to use, with the lower_case_table_names and long_query_time.
• There is a size restriction on BLOBs written to the redo log for MySQL.
• The maximum provisioned storage limit restricts the size of a table to a maximum size of 16 TB if you use InnoDB file-per-table tablespaces. Also, this limit constrains the system tablespace to a maximum size of 16 TB.