Amazon AWS Mysql Database Full

Arrrgghhhh our web site decided to fill the 5GB database it uses !!!!!!!

When you use the AWS console you get the following error

The specified database instance is currently in the storage-full state. The only modification permitted on a database instance in this state is an increase …”

You have to use the RDS command line tools, you can see how to install them in my riveting post ūüôā¬†Change the max_allowed_packet Amazon¬†RDS

Once installed and working make a note of the database instance either using the management console or use

rds-describe-db-instances

Issue this command to increase the size of the database to 10GB, XXXXXXXXX is the database instance name

rds-modify-db-instance XXXXXXXX --allocated-storage 10 --apply-immediately

Once the process was started it took about 10 minutes for the database to increase in size and be back on line.

Good luck with yours !!!!

Al

Advertisements

Change the Query Cache Size Amazon AWS RDS

We are running a PHP web site with the Mysql RDS from Amazon, there is a need to change the PHP cache size to make the server run more effectively. Normally this would be done in the my.cnf file but as you know with RDS you use the parameter groups. If you don’t have the RDS tools on your server then look to the another blogg by me for the instructions on getting it up and running¬†Change the max_allowed_packet Amazon RDS

Once you have it working then back here.

You only need to do this part if you did not create a parameter group

rds-create-db-parameter-group NAMEOFGROUP -f DATABASEVERSION -d “My custom database parameter group”

NAMEOFGROUP – If this is a single multi used database, give it a generic name otherwise give it the name of the RDS name

DATABASEVERSION – at the time of writing mysql5.1 mysql5.5

e.g.

rds-create-db-parameter-group mydbgroup -f mysql5.1 -d “My custom database parameter group”

This has now created the shell to store the parameters such as max_allowed_packet but in this case the query cache size

rds-modify-db-parameter-group NAMEOFGROUP –parameters “name=query_cache_size,value=3048576,method=immediate”

rds-modify-db-parameter-group¬†NAMEOFGROUP¬†–parameters “name=query_cache_type,value=1,method=pending-reboot”

 

NOTE the pending-reboot, for this setting to take effect the database needs a recycle.

Read up on the query_cache stuff in the MySQL site and forums, this blog is all about how you set things in AWS and not how to tune your MySQL server.

Again you only need to do this if you created the group from scratch

rds-modify-db-instance your-instance-here –db-parameter-group-name NAMEOFGROUP

 

Substitute you RDS instance name (your-instance-here and NAMEOFGROUP)

You will need to restart the RDS instance to get these settings to take effect.


Change the max_allowed_packet Amazon RDS

To change the mysql max_allowed_packet size you appear need to use some tools from Amazon

cd ~

mkdir /software

cd software

wget -c http://s3.amazonaws.com/rds-downloads/RDSCli.zip

unzip RDSCli.zip

mv RDSC*/ RDS/

cd RDS

mv credential-file-path.template credentials.txt

We now need to get the credentials for you AWS account, if you are reading this I take it you don’t need to know how to do this, if you do need some help¬†http://aws-portal.amazon.com/gp/aws/developer/account/index.html?action=access-key

vi credentials.txt

Enter the ID and key

The system then needs to understand where the files and paths are stored.

I create a new folder under the opt directory called my company name. This then allows me to add now standard programs, menus etc…

mkdir /opt/xxxxxx

cd ~

cd software

mv RDS/ /opt/xxxxxxx

Edit you ~bashrc file and enter the following

export AWS_RDS_HOME=/software/RDS
export PATH=$PATH:$AWS_RDS_HOME/bin
export AWS_CREDENTIAL_FILE=$AWS_RDS_HOME/credentials.txt
export EC2_REGION=XX-XXX-X

This is basing it on you following the instructions above, amend you path statements based on where you have store the download and credential file.

The Region needs to be replace with the region that you database is stored in

e.g. us-west-1, eu-west-1

NOTE if your database is in eu-west-1b then you need to enter eu-west-1

To find the zone use the RDS module in the AWS-Console, click in the instance and look for the zone and remove the last letter.

Remember you will need to log-off an on for the PATH to take effect an you need JAVA installed on the system.

To see if this is working issue

rds-describe-db-instances

You can now create a parameter group, this will allow you to associate the relevant parameters, this is the equivilant of creating the my.cnf buy the looks of it.

rds-create-db-parameter-group NAMEOFGROUP -f DATABASEVERSION -d “My custom database parameter group”

NAMEOFGROUP – If this is a single multi used database, give it a generic name otherwise give it the name of the RDS name

DATABASEVERSION Рat the time of writing mysql5.1 mysql5.5

e.g.

rds-create-db-parameter-group mydbgroup -f mysql5.1 -d “My custom database parameter group”

This has now created the shell to store the parameters such as max_allowed_packet

rds-modify-db-parameter-group NAMEOFGROUP¬†–parameters “name=max_allowed_packet,value=20971520,method=immediate”¬†

e.g.

rds-modify-db-parameter-group mydbgroup –parameters “name=max_allowed_packet,value=20971520,method=immediate”¬†

We now need to associate the parameter group with the instance

rds-modify-db-instance your-instance-here –db-parameter-group-name¬†NAMEOFGROUP

Substitute you RDS instance name (your-instance-here and NAMEOFGROUP)

Navigate to the RDS console and highlight the instance and press reboot (MAKE SURE YOU HAVE ONLY ONE INSTANCE HIGHLIGHTED AND THE CORRECT ONE)

The database will reboot and should now have the parameter file associated.

The full documentation can be found here http://awsdocs.s3.amazonaws.com/RDS/latest/rds-api.pdf or http://docs.amazonwebservices.com/AmazonRDS/latest/CommandLineReference/command-reference.html