DevOps: Local MySQL Server

ClarityDX is exceptionally proud of our development and engineering team, and the enterprise-grade development processes they have developed and continue to evolve. Our development team deserve a lot of credit towards the progress and success of ClarityDX, and not solely through client work, but also through our company culture and internal innovation.

For those uninitiated with the term, ‘DevOps’ is a software development methodology that combines software development with IT service level of operations. The goal of DevOps is to shorten development lifecycles while also delivering fixes, features, and updates as frequently and as close to the alignment of business needs, as possible.

This article explores our journey with a local MySQL dev server.

---

Back in 2016, ClarityDX was growing fast and as with all agencies, there came a when we started to take on bigger and bigger projects. One of those was Kantox, a B2B currency exchange platform, which was one of our biggest projects to date back in 2016.

Due to the size of the project, we needed to have more than one developer working on it simultaneously and quite quickly we started to have issues with stepping on each other’s toes (code-wise that is, our studio is very spacious) because we didn’t yet have a shared database. So, due to the urgency of the matter, we shared a database from one of the developers across the entire team.

The solution, which we’d now call bootstrapped, worked and we managed to finish the project without too many difficulties, but we knew that was only temporary and we clearly needed our own offering.

Thus began the start of a discovery phase to see which would work better for our development team. Our options were: Using a remote database or a local database.

Local server

Although a remote database would have fit our needs without any problems, we eventually decided to set up an internal server for the job, as this would benefit us as a development team long term. We came to this conclusion as during the discovery phase we had some ideas on how to benefit from having an internal server for future projects and also because our Lead Developer Alessandro had been playing with Unix and Linux for over 15 years.

Sometimes, when the choices aren’t clear, you have to bite the bullet and just make a decision. So, we bought installed a local development server running Ubuntu Server Edition.

MySQL

We eventually made the decision to use MySQL due to it’s ease of use, and it’s open-source credentials. It was simple installing and configuring MySQL server on Ubuntu using its package manager.

All you need to do is just open your terminal and run the command:

[pastacode lang=”bash” manual=”sudo%20apt-get%20install%20mysql-server” message=”” highlight=”” provider=”manual”/]

As mentioned earlier, It’s relatively simple getting MySQL up and running on your server and it works out of the box with no hassle.

Allowing remote access

Now that the MySQL server was working, we needed to allow access from all the computers in the new network.
For security reasons we used Ubuntu Firewall in conjunction, so the first order of business was to allow the firewall using the ufw command:

[pastacode lang=”bash” manual=”sudo%20ufw%20allow%20mysql” message=”” highlight=”” provider=”manual”/]

If you don’t have the firewall enabled, we recommend that you turn it on.

Add a database user

As the root account has no restricted privileges over all the databases, it is preferable, for security reasons, to create a limited user to allow remote connection.

Connect to the MySQL server with:

[pastacode lang=”bash” manual=”mysql%20-u%20root%20-p” message=”” highlight=”” provider=”manual”/]

Create the new user:

[pastacode lang=”bash” manual=”CREATE%20USER%20’%5BYOUR%20USER%20NAME%5D’%40’%25’%20IDENTIFIED%20WITH%20mysql_native_password%20BY%20’%5BYOUR%20PASSWORD%20GOES%20HERE%5D’%3B” message=”” highlight=”mysql” provider=”manual”/]

by replacing [YOUR USER NAME] with the username you want to create, and [YOUR PASSWORD GOES HERE] with a password of your choice.

Now we can give some privileges to the user created:

[pastacode lang=”bash” manual=”GRANT%20SELECT%2C%20INSERT%2C%20UPDATE%2C%20DELETE%2C%20ALTER%2C%20CREATE%2C%20DROP%2C%20INDEX%2C%20LOCK%20TABLES%2C%20REFERENCES%0A%20%20ON%20*.*%20TO%20%5BYOUR%20USER%20NAME%5D’%40’%25’%3B” message=”” highlight=”” provider=”manual”/]

Vagrant

In any industry, it’s good practice to stay updated by keeping an eye on what’s going on and this is exactly the same for a developer in the digital world.

Thanks to this continuous striving for self-improvement, I discovered Vagrant as a tool in 2016. Vagrant helps improve your entire workflow of using Puppet, improving development and process for both developers and operations.

After installing it on a machine and doing some tests locally to explore the real potential of using Vagrant, we were blown away by it’s ability to add new websites using GitHub.

After the local experiment was successful, we installed and configured it on our server, moved all the database from the local MySQL install to the one provided by Vagrant, and also forwarded the port so that the database and the website were available on the entire network.

Docker

Still all the way back in 2016, at the time Vagrant 2 would be not available for another 6-12 months, and we were concluding that whilst our first attempt/iteration had worked solidly, it was beginning to present some limitation for our ever-evolving development needs. So after finding out about a potentially superior solution called Docker, I started reading up on it and joined a course provided by Udemy to see if it could be a replacement for Vagrant but in that it could prove to be a better fit for our previously stated DevOps vision.

Following on from that course, we’ve started creating our first docker image (A Docker image is a file, comprised of multiple layers, used to execute code in a Docker container.) to be used for both local development and to power up the local websites on our local server.