Setting up MySQL Server using Ubuntu Server

I’ve recently started to refresh my knowledge of MySQL, that last time I used MySQL, besides during my post secondary education, was when needing to get into the database backend of the previous version of this blog.

Although the course is about 4 years old I’ve found Mosh Hamedani’s course to be a nice refresher on MySQL and easy to digest.

In getting started with this course I’ve been trying to figure out how to setup an environment to use MySQL, do I install it locally on my laptop or desktop computer and clutter my setup, or do I look into virtual machines?

I figured a virtual machine might be a step in the right direction to keep my desktop setup separate from any server software. The main tool I have been using on Linux for virtual machines has been Martin Wimpress’ Quickemu app¹. It has been a great way to quickly spin up virtual machines and even has a built-in tool to download an ISO of your choosing (Linux, macOS, or Windows) and it will automatically generate the necessary configuration and command to run the virtual machine.

The only downside to it, at least in the 1-2 years I have been using it is that I can’t seem to figure out a way to bridge the network so that I can communicate to the virtual machine using another private IP address generated by my router rather than using*@localhost** instead.*

With virtual machines out of the question I figured I would repurpose a Raspberry Pi that my brother was using to act as a server hosting MySQL and Printing services.

One particularly useful resource was Abstract Programmer on YouTube, his explanation was amazing in explaining how to setup MySQL server, securing the MySQL installation, and setting up phpMyAdmin as well. The explanation was fairly straight forward and amounted to doing the following in Ubuntu Server:

sudo apt install mysql-server 
sudo mysql 
alter user 'root'@'localhost' identified with mysql_native_password by 'ENTER PASSWORD HERE'; exit;

After installation of MySQL Server: mysql_secure_installation

mysql_secure_installation 

1. You will be asked to enter the root password from the previous commands 

2. For the first prompt about VALIDATE PASSWORD COMPONENT select Y to indicate YES 

3. For the next prompt about the PASSWORD VALIDATION POLICY, select the highest value 

4. You will be asked if you want to change the password for ROOT --> NO 

5. Remove anonymouse users, --> YES 

6. Disallow root login remotely? --> YES 

7. Remove test database...? --> YES 

8. Reload privilege tables now? --> YES

Once you have completed the above steps you will no longer be able to sign in to MySQL with sudo mysql , but rather mysql -u username -p , where username is the account you are signing in with such as root.

Another option is to create another user so you don’t have to use the root account, this can be done by logging into mysql and running the following command:

create user 'tysonsmith'@'localhost' identified with mysql_native_password by 'ENTER PASSWORD HERE';

Note: If you are going to use this MySQL server from another device on your network you will want to replace localhost with %.

You will need to grant access to this new account, you can do so with the following command, you can replace database with the name of the database you want to grant access to for the new account or if you want to grant access to all databases you can replace database with…

grant all on database.* to 'tysonsmith'@'localhost';

Once this is done you can proceed to install php and phpmyadmin:

sudo apt install php 
sudo apt install phpmyadmin

As part of the process to install phpmyadmin it will ask you to choose which web server software to use, I use apache2.

At this point the next stage of the setup process can differ, for example if you are using a firewall you will need to adjust to settings for apache2 and mysql.

The next stage will be to install the MySQL Workbench which Abstract Programmer covers in the preceeding videos. The only thing I would give you a heads up on is regarding remote connections, in my case I wanted to limit access to my local network which directed me to enter my server’s private IP address in the /etc/mysql/mysql.conf.d/mysqld.cnf file under where it says bind-address instead of what Abstract Programmer does in his video.