Jesse's Software Engineering Blog
CentOS 6.4 MySQL VPS Setup
I recently started using Digital Ocean for VPS hosting and have been very satisfied. At first I set up a single VPS droplet to host both web server and databases but then decided to break MySQL off onto its own droplet. Digital Ocean recently added private networking in my region which ensures the droplets will be in the same data center and data transferred between the two droplets will not incur bandwidth usage.
The advantages to having the multi droplet setup is it allows you to give MySQL, or whatever other database server you use, access to all of the resources on your droplet i.e. using 100% of a 512M server rather than 50% of a 1G. MySQL will not have to compete for resources with other services/applications. When monitoring your network, if your MySQL instance is slowing down and not getting enough RAM or CPU, simply bump the resources up on that droplet. Having separate droplets also helps shield your database server from outside connections by allowing the server to only interact with other servers in your private network.
One disadvantage to consider is that the calls to your database from your application will have to travel through the datacenter to the database server in order to run a query. This will obviously incur more overhead than running MySQL locally.
DISCLAIMER: I am programmer by trade, not a system administrator. I like installing, configuring, and running web servers for my personal projects but have limited professional experience in doing so. The following article should be used as a GUIDE of some of the things to do when setting up a server, and the topics discussed should be researched further before implementing in a production environment.
Initial Set Up
Digital Ocean makes it extremely easy to install your distribution of choice. I will be using CentOS 6.4 for my set up. After the image has been set up on your droplet the first thing to do is change the root password:
Next make a new user and set the password:
useradd newuser passwd newuser
Make the user a sudo’er by adding the user to the wheel group. NOTE: Your wheel group ID # will be different. Also consider making your SSH user and your sudo user different.
vi /etc/group wheel:x:10:newuser
Switch to the new user:
su - newuser
Update the SSH config to prevent root login and access, and modify the login attempts and logout time:
vi /etc/ssh/sshd_config PermitRootLogin no # I also change login attempts and logout time to lower numbers
Disable services you will not need on the server:
# to see active services service --status-all # I always turn off crond if I'm not using it sudo service crond stop sudo chkconfig crond off
Finally update the system to make sure it is current:
sudo yum update
First install the MySQL and MySQL server packages:
sudo yum install mysql mysql-server
After installation turn MySQL on and enable it so that it will auto start on server reboot:
sudo service mysqld start sudo chkconfig mysqld on
Next run the MySQL secure setup script:
/usr/bin/mysql_secure_installation # Restrict access Set root password? Y Remove anonymous users? Y Disallow root login remotely? Y Remove test database and access to it? Y Reload privilege tables now? Y
Verify you can log into MySQL:
mysql -u -p
The main MySQL conf file is located here:
One thing about CentOS is a lot of their packages are out of data. The CentOS distribution is marketed as stable and predictable, which equates to less stress when updating but older versions of software and kernel. To update MySQL, first grab the remi repos and then update (make sure they match your system architecture):
sudo rpm -Uvh http://download.fedoraproject.org/pub/epel/6/x86_64/epel-release-6-8.noarch.rpm sudo rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-6.rpm sudo yum --enablerepo=remi,remi-test update mysql mysql-server
Verify your MySQL version by logging into MySQL and running:
SHOW variables LIKE "%version%";
When setting up security on a system it’s best to grant as few privileges as possible. For this droplet the only machine that needs access is the web server and a SSH user. In the future you may need to allow more access for database replication, nagios monitoring, a git repository, etc. but for now I will just focus on the web server.
Linux IPTables is the firewall for your server. IPTables allows you to write rules which regulate how your server handles packets of information, which ports it accepts information from, and much more beyond the scope of this article. For now I will show some basic IPTables rules for restricting access to the MySQL droplet.
It’s always a good idea to know the rules you want to implement before you start. MySQL operates out of port 3306 by default and SSH out of port 22. It is not uncommon for the ports to be changed in an attempt to mask which service is listening to which port, but for this example we will assume the defaults. For this droplet, the only two ports that need to be opened are port 3306 and 22. The only server that should access the MySQL droplet is the web server, so we will restrict all traffic to these ports from the web server’s PRIVATE IP address.
WARNING: When manipulating IPTables rules there is a good chance you will accidentally create a rule that will prevent you from SSH’ing back onto the server. Make sure you have direct console access before you start managing your rules.
First ensure that the IPTables are turned on and that they are enabled:
sudo service iptables start sudo chkconfig iptables on
Here are some useful IPTable commands:
# check current IPTable rules iptables -L -n # reset rules sudo iptables -F # show what ports are currently doing netstat -tulpn # save and restart Iptables sudo iptables-save | sudo tee /etc/sysconfig/iptables service iptables restart
And here are some IPTable rules that will shut down all ports accept 22 and 3306 from a specific IP address (replace <IP> with actual private IP):
# block some of the most common attacks sudo iptables -A INPUT -p tcp --tcp-flags ALL NONE -j DROP sudo iptables -A INPUT -p tcp ! --syn -m state --state NEW -j DROP sudo iptables -A INPUT -p tcp --tcp-flags ALL ALL -j DROP # allow SSH form web server sudo iptables -A INPUT -p tcp -s <IP> --dport 22 -m state --state NEW,ESTABLISHED -j ACCEPT sudo iptables -A OUTPUT -p tcp --sport 22 -m state --state ESTABLISHED -j ACCEPT # allow MySQL connections from web server sudo iptables -A INPUT -p tcp -s <IP> --dport 3306 -m state --state NEW,ESTABLISHED -j ACCEPT sudo iptables -A OUTPUT -p tcp --sport 3306 -m state --state ESTABLISHED -j ACCEPT # drop everything else sudo iptables -P OUTPUT DROP sudo iptables -P INPUT DROP sudo iptables -P FORWARD DROP
After the rules have been implemented and IPTables have been restarted, you can check the ports by running the nmap utility externally from a different machine (not your web server):
nmap -Pn <MySQL PUBLIC IP> Starting Nmap 6.40 ( http://nmap.org ) at 2014-01-31 18:24 MST Nmap scan report for mysqldb ( PUBLIC IP) Host is up (0.023s latency). All 1000 scanned ports on mysqldb ( PUBLIC IP) are closed Nmap done: 1 IP address (1 host up) scanned in 95.98 seconds
As you can see all ports for the server are closed. When using a VPS sometimes other ports will show up as “filtered” for various different services, which likely has to do with your VPS sharing a physical server. If you don’t see the above results at first, try running the utility a couple more times until you do, or check your IPTable rules to make sure you didn’t make a mistake.
Another thing to verify is that you can SSH into the MySQL server from your web server. Note that you will not be able to test the MySQL connection until you have created a user in the database.
One last security measure is to turn on SELinux. For a bare bones MySQL droplet that is just running the MySQL server, SELinux should not interfere with any of your processes and will provide an extra layer of security. To enable SELinux first install the core:
sudo yum install policycoreutils
Then edit the main config file. Make sure the following two variables are set:
sudo nano /etc/selinux/config SELINUX=enforcing SELINUXTYPE=targeted
Allow key signatures for SSH logins::
sudo setsebool -P allow_ssh_keysign 1
Restart the server and check the status:
SELinux is a very powerful security tool, and fairly complex. I encourage you to take the time to research it more to help understand what it is capable of. I will write more in depth articles on it in the future.
Create Database User
Before you are able to use MySQL you will need to create users for your database. It’s good practice to set up a different user for each application with their own specific permissions. That way if a login gets compromised you can help limit the scope of the damage. For this example I will be making a user with limited privileges for modifying a specific database. You will need to log into MySQL to run the following commands.
Create the database needed for the project:
CREATE DATABASE projectdb;
Create the user. It’s important to specify the private IP address of your web server (after the @ symbol). This will specify that the user can only log in from that IP address. If you want to allow the user to login from any IP address simply use ‘%’.
CREATE USER ‘project_user’@’PRIVATE_IP’ IDENTIFIED BY ‘password’;
Associate the user to the database with the appropriate permissions. In this example I am giving the user table modification privileges to all tables in the projectdb:
GRANT DELETE, INSERT, SELECT, UPDATE ON projectdb.* TO 'project_user'@'PRIVATE_IP';
Reset the privileges and your user creation will be completed:
You should now be able to connect to your MySQL server from your web server using your new user.
The MySQL VPS droplet is now set up and ready to be used. Be sure to test the following:
- Port scanning – Port scan your new droplet from an external computer and verify that all of your ports are closed
- SSH – You should be able to SSH into your MySQL droplet from your web server but not from any other machine. Also verify you cannot SSH out from your droplet to another machine
- Database Connection – Verify that you can query your database ONLY from your web server
One thing to consider is the need to access the private server from a local machine. This can be accomplished using SSH tunneling.