MySQL is an open source SQL relational database management system that’s developed and supported by Oracle. For many open source developers it is the Go to relational database management system.
In my particular case, I am Installing Team City On Ubuntu Server and want to use MySQL to store build history, users, build results and some run time data for my Internal CI/CD server. I therefore needed to install MySQL server on one of VM Servers.
I make use of ProxMox Open-Source Virtualization Platform and have server in my home office to host a number of Virtual Servers. These servers are not directly accessible from the internet and are primarily used for testing, POC and staging environments before I deploy to Cloud Providers like AWS, Azure or GCP which are fairly popular with my clients.
Installing and configuring MySQL is a fairly straightforwrd process but I did ht one or two snags that I thought I document for future reference.
Install MySQL using default Ubuntu Repository
The default Ubuntu repository currently only contains version 5,7.x of MySQL but for my particular use case this will suffice. So this guide will only guide you through installing MySQL 5.7.
To install MySQL i simply connected to the server I created using ssh and ran the following command.
This will install MySQL server, and by default you will be able to access it using the root account of the server you’re installing it on. The installation process will no longer prompt you to configure a root password.
Once the installation is complete I test that I can access the mySQL server by attempting to login. So for instance, you have created a root account on your server, using your name. You can simply login ensuring you use the
If you are able to successfully login the simply test that your user can access the databases by simply using the SHOW DATABASES command
You should see a responses similar too
Then this confirms the installation worked as expected. Exit out of the mysql using the
We can now configure the security of the mysql server.
Configure the MySQL security
MySQL ships with a handy security utility to help implement security recommendations.
mysql_secure_installation program enables you to improve the security of your MySQL installation in the following ways:
- You can set a password for
- You can remove
rootaccounts that are accessible from outside the local host.
- You can remove anonymous-user accounts.
- You can remove the
testdatabase (which by default can be accessed by all users, even anonymous users), and privileges that permit anyone to access databases with names that start with
To execute the application is as simple as running the command below and answering a few questions.
You will be prompted to answer a few questions as follows
We have now completed all the rudimentary security precautions for our MySQL server.
Bind MySQL server to IP Adress
We will now need to Bind our MySQL to the IP address you it is running on. To do so we need to edit a configuration file and to do so we will use nano a light weight text editor that is shipped with Ubuntu.
We now have the file simply search for the lines
Change the IP address to whatever the IP address of your server is. i.e
192.168.0.2 the simply exit and save the file using CTRL + X
We now have to restart the MySQL daemon for the change to take effect
Create User Account
We don’t want o access our MySQL server using our root account remotely and in fact we have just disabled doing so in the steps above. So we will need to create some user accounts that we will use.
To ensure all the commands take effect you will need to Flush the privileges.
Connect to your MySQL server remotely
You should be able to connect to your MySQL server with the new Username your created remotely. So using the Client application of your choice connect.
In my case I make use of Jetbrains DataGrip so I simply configure the connection as follows
It is a fairly simple task to install MySQL on Ubuntu, but there are some additional steps to ensure you are able to securely connect to the mySQL remotely.
Denizon's product line successfully integrate IoT, Artificial Intelligence and Blockchain technology to enable efficient, productive, secure and scalable solutions to help organisations address increasing energy demands, ecological impact and Health & Safety concerns of their staff.