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
GRANT ALL PRIVILEGES ON remote_db.* TO 'remote_user'@'remote_IP';
then, just flush the privileges and we are good to go!
and exit the MySQL console
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.