How To Install MySQL In Ubuntu

Introduction

For those who wish to use MySQL to interact with their database on an Ubuntu machine the process can be a bit challenging. I recently went through this process using Ubuntu 20.04.4 LTS and thought I’d share my experience. For this article I am expecting that my readers are familiar with the following terms: relational database management system, SQL, MySQL, MySQL workbench, and Linux Ubuntu. If you’d like to read my article on setting up a software development environment on an Ubuntu machine feel free to give it a read. We will do a majority of this within terminal so without further ado lets get started!

Optional: Uninstall/Remove MySQL

The first step of course is to install MySQL server on your machine. However depending on the state of your machine there is an optional ‘first first’ step, and that is to uninstall MySQL. If you have come to this article via a google search due to your MySQL installation on an Ubuntu machine being faulty. I implore you to uninstall and remove MySQL from your machine before continuing with the rest of this article. Therefore this OPTIONAL ‘first first’ step begins with stopping your MySQL service. Within terminal enter the following:

sudo systemctl stop mysql

At which point you will probably be prompted for your Ubuntu password. Once this has completed and the next terminal line pops up enter the following command to remove MySQL along with all of its dependencies from your machine:

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*

After this process completes there are a few folders/files you’re still going to want to remove in order to start installation of MySQL with a clean slate. Within terminal again enter the following:

sudo rm -r /etc/mysql /var/lib/mysql

This should let you start again from scratch, however just to be sure the following two commands will ensure there is absolutely nothing straggling behind having to do with MySQL. Within terminal optionally enter:

sudo apt autoremove

and

sudo apt autoclean

Now we are absolutely ready for the first step!

Installing MySQL

Its best to ensure everything is up to date before any installation. Within terminal enter the following to install any new updates (be ready to also enter your admin password):

sudo apt update

After all of the updates run your are now ready to install the mysql-server package. Within terminal enter the following:

sudo apt install mysql-server

With this you now have MySQL on your machine. In order to start this new MySQL service up enter the following within terminal:

sudo systemctl start mysql.service

You unfortunately won’t see any confirmation within terminal that this service is running. However MySQL comes with its own command line client that is used for executing SQL queries. In order to enter it as a ‘root’ user enter the following in terminal:

sudo mysql

You should be greeted with mysql> in your terminal window signifying you are successfully inside the MySQL command line client. It is best practice to have a password when accessing MySQL as a root user. For this we need to download the mysql_secure_installation package. However I ran into a continuous loop when I first attempted this in which I was consistently asked to set a new password. A solution for this that worked for me was to alter the root user and setting the new password via the MySQL command line client. It should be noted that after following this step you will henceforth need to enter mysql -u root -p within terminal to login as root, and you will be asked for the password you are about to create. In order to proceed and change your root user password make sure you are still inside the MySQL command line client. It is best practice for your password to be at least 8 characters long and include a symbol as well as numbers and letters. With this in mind enter the following inside the MySQL command line client substituting the bold word for whatever password you’d like:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password by 'password';

After this is complete type exit in order to exit the MySQL command line client. You’ll now want to run a shell script in order to enforce password rules and enable various other privileges. To do this within terminal enter:

sudo mysql_secure_installation

After going through a series of prompts you will then be asked to set a level of password validation policy. The options are 0 through 2 and should be read carefully. If you set up your password as suggested it’s best practice to choose option 2. With this your MySQL installation is complete. Now on to the next challenge. Setting up MySQL workbench.

Setting Up MySQL Workbench

The easiest way I’ve found to install MySQL workbench from terminal is to use a snap bundle. Within terminal run the following to install MySQL Workbench:

snap install mysql-workbench community

After the installation finishes you will undoubtedly run into a problem when you try to connect to your database via MySQL Workbench. It should go headline “Cannot Connect to Database Server” and mention something along the lines of “An AppArmor policy prevents this sender from sending…”. This is because by default snap packages are not allowed access to this service. You’re going to want to enter the following within terminal in order to give this snap package access:

sudo snap connect mysql-workbench-community:password-manager-service :password-manager-service

With this completed you should be able to use MySQL Workbench and have it access your database.

Conclusion

By utilizing open source software like Ubuntu and MySQL we encourage a more inclusive community. Through sharing our knowledge of such technologies we allow those with less means to partake in the services they provide. I hope this article has been useful to those who seek to have MySQL on an Ubuntu machine. Thank you for reading!