Creating a Dedicated MySQL Server

Ok, so let’s set the scene. You have a Wordpress website hosted on a cloud server through a service like Digital Ocean. But you have noticed that your main services (PHP and MySQL) are constantly fighting for resources.

This generally means it is time to look at moving to a dedicated MySQL server. Especially with WordPress tending to be very heavy on it’s database queries through over use of plugins.

What do I need

  • A linux based server (My favourite is Ubuntu).
  • Some time

Now, before I get going. If you do choose Digital Ocean and have both servers in the same data centre, you can enable private networking which will boost speed and increase security.

Ok, first you want to create your new Ubuntu Server (or your favourite linux flavour) and get SSH’ed in as we have a few commands to run.

When you are in, the first thing we want to do is get MySQL installed.

sudo apt-get update
sudo apt-get install mysql-server

You will need to choose a root MySQL password, I suggest using a password generator like this

Then we want to get the default tables installed, and secure the installation. Use the following commands:

sudo mysql_install_db
sudo mysql_secure_installation

Now for the final bit of initial configuration will be setting up our MySQL installation to listen for remote connections. This is done by updating the bind-address value inside /etc/mysql/my.cnf (This location may differ depending on your Linux version).

sudo vi /etc/mysql/my.cnf

and change the bind-address value to either your database servers private networking ip or it’s public ip.

Obviously by using the private ip you must understand that the server will only be accessible to other instances within that network.

Then, we can restart MySQL and we are ready to rock ‘n’ roll.

sudo service mysql restart

Setup a Database & Credentials

Now that MySQL is setup to accept external connections, we can create a user & database to connect to.

First, let’s get into the MySQL console by typing:

mysql -u root -p

You will be prompted to enter a password, this is your MySQL root password which you set earlier.

We can make that database with the following:

CREATE DATABASE remote_db;

and then a user, which we will only give access to if the request originates from the correct server.

CREATE USER 'remote_user'@'remote_IP' IDENTIFIED BY 'password';

and finally we can grant that user full permissions to the remote_db

GRANT ALL PRIVILEGES ON remote_db.* TO 'remote_user'@'remote_IP';

then, just flush the privileges and we are good to go!

FLUSH PRIVILEGES;

and exit the MySQL console

exit

Connecting to the database

To connect to our remote database, we simply change the wp-config.php file to point to the right place:

define('DB_NAME', 'remote_db');
define('DB_USER', 'remote_user');
define('DB_PASSWORD', 'PASSWORD');
define('DB_HOST', 'database_server_ip'); // Will be either the Public or Private, dependant on which one you have used so far.

And that is it. You now don’t have servers fighting over the shared resources. Allowing you to scale each component independently.

Things to think about

  • You will want to dump your database and import it on the other end. There are many guides out there for this
  • You will also want to try and optimise your web server side to cache requests efficiently, have a look at my redis post.