This tutorial will step us through the creation of a MySQL cloud database server on Rackspace cloud servers, we will not be using the “Rackspace Cloud Databases”. The ultimate goal will be to have a dedicated database server which will be accessed by multiple web servers that are load balanced in a Rackspace cloud hosting environment.
There are a few reasons to set up a dedicated database server:
- Performance: We will custom-prepare the server for running databases, not web pages
- Security: We will keep the database off of the web server and made available to our web servers within the Rackspace private network
- Scale: We will be able to scale our web site or web application quickly by adding additional web servers behind a load balancer, without having to worry about our database server
Creating a Rackspace Cloud Account
At this point, if you do not have an account on Rackspace, that will be your first step. So go ahead and follow the following link and create your account. For this tutorial, make sure and click on the box that does not include managed hosting services.
We can now go ahead and log into our Rackspace cloud account:
Creating the New Server
- After we have logged in, click on the “Servers” tab in the primary navigation menu
- Once the “Servers” page has loaded, click on the “Create Server” button
- Once the form loads, start by naming our new server “Database01“
- Under the “Regions” drop-down, Look for the “First Generation Servers” and select the “Dallas (DFW)” option
- Under the “Image” heading, select the “Ubuntu 12.04 LTS” operating system
- Under the “Flavor” heading, select top option, which is “256 MB RAM / 10 GB Storage” (If at some point our database requires additional resources, we can scale up the RAM and disk space)
- Finally, click on the “Create Server” button at the bottom of the form
- Save the password that Rackspace issues, we will use it to login and configure our server in the next section of this tutorial
When the server is done being built, we will be able to access the new database server from the “Servers” tab in our Rackspace Cloud account. Rackspace will assign two IP address to our database. One is called the “PublicNet” IP address, that is the external IP address used accessing our server from the Internet. The other is the “ServiceNet” IP address, this is the internal Rackspace IP address that our web servers will be accessing.
Configuring the Database Server
We are now ready to access our new server. To log in to our server, we will need to use the secure shell (SSH) protocol. Open a SSH terminal window and enter the following command, inserting the PublicNet IP corresponding to the server. When prompted, use the password Rackspace issued for our server in the previous step.
ssh root@[PublicNet IP Address]
Type yes to verify the identity of our Cloud Server. This will prompt an RSA key to be stored in our computer’s list of known hosts. This message will not be presented upon subsequent logins.
We need to check for the latest versions of Ubuntu’s installed packages, so we need to make a list of what is already installed. To do this we will run the following command:
sudo apt-get update
This command will update the package listing for the operating system to interact with and the information is cached for the Aptitude package manager to use in operating system upgrade.
We can now check for the latest version of the items in the installed packages list, enter this command:
sudo apt-get upgrade
Apt-get will prompt to review the packages. When prompted to continue, enter a capital ‘Y’. This step will take several minutes.
During the update process the OS will appear and prompt with some options, make sure to choose “Install the package maintainers version” to ensure use of the latest version of GRUB.
Once the upgrades are finished running we will reboot the server to ensure that it is running the latest and greatest version with all current packages.
Next we can configure Ubuntu’s “uncomplicated firewall” (UFW).
First we’ll will turn off all ports on the server except for the specific ones we’ll need for security reasons. We can do this by simply enabling UFW with the following command:
sudo ufw enable
We will receive a note this may disrupt SSH connections. No worries since we will open the SSH (22) port next. When prompted “Proceed with operation (y|n)”, press “y” to continue.
Now we will open the ports required for MySQL (3306) and SSH (22) with the following commands:
sudo ufw allow 3306
sudo ufw allow 22
Now the firewall will only listen for connections on the ports we have specified. These ports apply to the MySQL and SSH protocols.
Installing and Configuring MySQL
Now that the Operating System has been updated and secured, we are ready to install MySQL.
sudo apt-get install mysql-server mysql-client
The process will not take long but during the installation process we will be prompted to set a password for the MySQL ‘root user’. So choose a strong password and keep it in a safe place for future reference.
When the installation is complete, run the following command to secure our MySQL:
Next we will update the MySQL config file to meet our needs (/etc/mysql/.my.cnf)
sudo nano /etc/mysql/my.cnf
We will change the following “bind-address” and “query_cache” settings:
bind-address = 127.0.0.1 ... query_cache_limit = 1M query_cache_size = 16M
Change the above settings:
- from “127.0.0.1” to “0.0.0.0“
- from “1M” to “2M“
- from “16M” to “32M“
bind-address = 0.0.0.0 ... query_cache_limit = 2M query_cache_size = 32M
We will now restart the MySQL service to apply all or our changes.
service mysql restart
Now we can log in to our MySQL environment and create our first database.
Log into MySQL, replace “[user]” with the database admin username.:
mysql -u [user] -p
Create the database, replace “[db_name]” with the new database name.
mysql> CREATE DATABASE [db_name];
Add a user to the database to access from any IP Address (‘%‘), this simple step ensures that any web server behind the load balancer will have access to the newly created database. Replace “[db_name]” with the name of our new database, “[user]” with the username that will be accessing this database, and “[password]” with the user’s password. If our website has a configuration file for accessing the MySQL database, these values will need to be included in that file.
mysql> GRANT ALL on [db_name].* to '[user]'@'%' IDENTIFIED BY '[password]';
mysql> FLUSH PRIVILEGES;
Log out of MySQL
It’s a Wrap
We now have a dedicated cloud database server that is custom-configured to run MySQL within our Rackspace cloud environment. All of our web servers will now be able to share a common database and to have direct access to that database from behind the firewall on the Rackspace network.