Setting up WordPress to use the Amazon RDS MySQL external database server on your VPS is easy. Changing to an external database will boost stability and possibly speed up EC2. Running Apache and MySQL server with heavy load could lead to that the MySQL database comes to a stop. Running the MySQL on a separate server makes you less vulnerable. When the workload gets to heavy. The first thing that happens could be that the MySQL database server stops. This is why using an external database server that never stops is such a good idea.
Installing WordPress and connect it to an Amazon RDS MySQL external database server is a straightforward process you can do in about an hour.
- Create your MySQL database server with the first database
- Give access rights and permissions
- Import data from your WordPress database
- Update wp-config.php with new database name, user and PSW
After migrating to Amazon AWS RDS then your MySQL database runs on a separate machine. By using a remote MySQL server, your EC2 VPS gets lighter and less vulnerable.
Using RDS will free up the load, CPU, memory and disk space from your EC2 VPS server running om AWS Amazon.
Micro instance is sufficient for an install WordPress Amazon RDS
If you are using the micro server in the our experience is that it´s quite common that the MySQL server consumes all the resources on your VPS causing the server to become unavailable for a few minutes now and then. When running Amazon RDS the database runs on a separate machine leaving more free resources to Apache2.
To further free up resources on your server it´s a good idea to use Amazon AWS ElastiCache especially if you are using WordPress and CloudFlare with the W3 Total Cache WordPress Plugins.
Get a copy of your WordPress database
Start downloading a copy of your database using a WordPress plugin, phpMyAdmin, MySQL Workbench or use your favorite tool.
Create your RDS MySQL database server on the same endpoint as your EC2 instance
Start with creating your RDS database server and the first database. If you are budget minded, you should create the database server and database on the same endpoint as your EC2 instance. If your EC2 instance is running in eu-west-1, then your RDS database should also run in eu-west-1. If budget is not the important factor, then you should use more endpoints and possibly also more regions. It comes down to budget versus safety and speed.
Take a note of the database name, user and password. You need to enter this in the WordPress wp-config.php file.
Test that you can Telnet to your MySQL database from EC2
Getting into trouble? It´s most likely this is the part where it will happen.
Log into your EC2 VPS server with SSH and check that you can connect with Telnet to your RDS MySQL database.
Logg on to your VPS server. Then issue this command:
telnet mydatabase.cwr5tijihedj9ua.eu-west-1.rds.amazonaws.com 3306
- If you get the answer “connected,” then everything is OK.
- If you get an answer about unreachable, then you need to open port 3306 in the firewall on EC2 and or add the security group.
You might need to open port 3306 in the firewall in the security group tab on the EC2 admin panel.
You might also need to add right security group under security groups in the RDS admin panel.
Proceed with the last steps when you can connect to your MySQL RDS database with Telnet from your EC2 instance.
Install MySQL WorkBench on your MAC or PC
There are many desktop programs you can use to administer your MySQL database on AWS Amazon RDS. In this case, are we using the free MY SQL WorckBench
Tunnel your connection with your VPS
Create an SSH tunnel trough your EC2 Linux VPS server to the MySQL database on the Amazon AWS RDS server using MY SQL WorckBench.
If you have many databases and are security focused it might be a good idea to create a database user for each database in stead of using the admin user you created when setting up the RDS database server.
Then import your WordPress data into the database using MySQL WorkBench.
Connect the database to WordPress
In wp-config.php change the server name from localhost to the name of your newly created AWS Amazon RDS database. The database that you successfully connected to with Telnet.
Then change the username and password to your database.
Test that WordPress still works as expected.
All set!
Remove the MySQL server from your EC2 VPS server
You should free up disk space, CPU and memory and make your VPS less vulnerable, you could uninstall the MySQL server from your VPS server as described below. Then install a PHP connector to connect Apache2 to the remote MySQL server.
Remove local MySQL server
sudo apt-get remove --purge mysql-server mysql-client mysql-common sudo apt-get autoremove sudo apt-get autoclean
Then install the PHP MYSQL database connector
sudo apt-get install php7.0-mysql
Complete the install WordPress Amazon RDS by editing wp-config.php
You have now completed the install of Amazon RDS on your EC2. Do not forget to change the database name, user, password and server name to the MySQL database in wp-config.php
Would you like some assistance to install WordPress Amazon RDS
Contact TBT for a quote. We speak English, Norwegian, Swedish, Danish and some German. Installing Amazon RDS and connecting RDS to WordPress should take about one hour.