Amazon RDS Limitations

Amazon RDS Limitations

• Amazon's RDS service does not provide a shell access the to the machine where it is running. That means we cannot install our own software in that machine. For example we cannot install our own favorite monitoring software in that machine.
• We need to depend heavily on using Amazon Cloudwatch to monitor RDS instances. 
• Database Replication support is still at preliminary stage.
• Database Clustering is not supported yet.
• We don't have direct access to MySQL configuration. For any configurations we want to do, we need to do it via DB Parameter group using command line tool.
• Autoscaling is still not supported.
• Accessing RDS instance is not provided through same API's as EC2. For example you cannot use Elastifox to access your RDS instance.
• When a DB Instance changes database parameter groups (i.e. it stops using its current DB Parameter Group and starts using another), it must be restarted. However when adding or modifying a parameter the rebooting may depend on whether the add/modify parameter is dynamic or static. 

Amazon RDS Instance Sizes and Maximum concurrent connections

RDS Class  max_connections innodb_buffer_pool_size---------  --------------- -----------------------
t1.micro   34              326107136
m1-small   125             1179648000
m1-large   623             5882511360
m1-xlarge  1263            11922309120
m2-xlarge  1441            13605273600
m2-2xlarge 2900            27367833600
m2-4xlarge 5816            54892953600

Some Important Points when working with Amazon RDS: 

1. You are not given SUPER privilege and there is no direct access to my.cnf

2. To change startup options of MySQL you need to create your own DB Parameter Group and change its default values using RDS CLI. 

MySQL Error number 2003?

I'm always getting Error 2003 when connecting to my newly set mysql database on a different server. 


I found out the solution to this problem from Ubnuntu forums: 

The default from mysql server is to be binding to localhost (127.0.0.1), this means it will only accept connections from local applications. 


To change that:


edit /etc/mysql/my.cnf and comment out the line bind-address = 127.0.0.1: 


Then you will need to restart the mysql server with: 


/etc/init.d/mysql restart 


That solved the problem. 

How to Change Mysql root password

MySQL Change root Password

There are 2 ways for doing this: 

1. Using mysqladmin

2. Updating using mysql database commands

Using mysqladmin

To setup root password for first time, use mysqladmin command at shell prompt as: 

$ mysqladmin -u root password NEWPASSWORD

If there was an old password already set for e.g., oldpwd, you can change it to a new one by following command:

$ mysqladmin -u root -p'oldpwd' password 'newpwd'

Using mysql database shell commands

mysql stores all user information into a user table inside Mysql database. You can directly update the password for a user in this database table. All you need to do is login to the database and fire below command: 

mysql> update user set password=PASSWORD("newpwd") where User='root';

mysql> flush privileges;

mysql> quit

How to Allow MySQL Client to Connect to Remote MySQL server

I started getting an error “ERROR 1130: Host is not allowed to connect to this MySQL server” when I was trying to connect to a new mysql server which i had recently setup somewhere else instead of my own local machine. 


After a lot of troubleshooting and reading I found that by default, MySQL does not allow remote clients to connect to the MySQL database. And that was preventing my local machine to connect to this remove server which I have setup. 


To actually verify if your local machine is allowed or not you can use the simply telnet command as shown below: 


telnet my-sql-server-hostname 3306


If your local machine is not allowed to connect to this mysql host then you will get an error such as: 



$ telnet my-sql-server-hostname 3306host 192.168.1.3 is not allowed to connect to this mysql server
$ mysql -u root -p
Enter password:
mysql> use mysql
mysql> GRANT ALL ON *.* to root@'192.168.1.3' IDENTIFIED BY 'mysql-password';
mysql> FLUSH PRIVILEGES;



If you want to allow a specific client ip-address (for example: 192.168.1.3) to access the mysql database running on a server, you should execute the following command on the server that is running the mysql database.

Also, update firewall rules to make sure port# 3306 is open on the server that is running the mysql database.

After the above changes, when you try to connect to the mysql database from a remote client, you’ll not get the “Host is not allowed to connect to this MySQL server” error message anymore.

Getting error java.lang.ClassNotFoundException: com.mysql.jdbc.Driver.

My WebService used to work great on my development machine but when I moved it to production after creating a war file of it, I started receiving this error 


java.lang.ClassNotFoundException: com.mysql.jdbc.Driver


After debugging, i figured that the dependent mysql jar were not exported to WEB-INF/lib. After copying it to /lib it started working correctly. 

Can I configure Amazon RDS to use UTF-8?

By default RDS uses latin1 as default character set. This can be easily modified by changing the DB Parameter Group that the RDS is assigned to. 


Try: 


rds-modify-db-parameter-group my_db_parameter_group \ --parameters="name=character_set_server, value=utf8, method=immediate" \ --parameters="name=collation_server, value=utf8_general_ci, method=immediate"


This will change RDS to use UTF-8. 


Once you do it make sure you reboot your RDS instance and verify that changes have been taken place by following below: 

rds-reboot-db-instance my_db

mysql> SHOW VARIABLES LIKE '%character_set_%';
+--------------------------+-------------------------------------------------+
| Variable_name            | VALUE                                           |
+--------------------------+-------------------------------------------------+
| character_set_client     | utf8                                            |
| character_set_connection | utf8                                            |
| character_set_database   | utf8                                            |
| character_set_filesystem | BINARY                                          |
| character_set_results    | utf8                                            |
| character_set_server     | utf8                                            |
| character_set_system     | utf8                                            |