Installing MySQL on Ubuntu 20.04: 6 Easy Steps

• January 18th, 2022

Installing MySQL on Ubuntu FI

MySQL is a Relational Database Management System. This Open-source tool is one of the best RDBMS available in the market that is being used to develop web-based software applications among others. MySQL is scalable, intuitive, and swift when compared to its contemporaries. It houses a Client-Server architecture. At the core of the MySQL Database lies the MySQL Server. This Server is availed as a separate program and handles all the database instructions, commands, and statements. Ubuntu is a modern, Open-source operating system on Linux that can be leveraged for Desktop, Cloud, Enterprise Server, and IoT.

This blog talks about the different steps involved in MySQL Ubuntu Installation. It also gives a brief introduction to MySQL and Ubuntu before covering the prerequisites for MySQL Linux Installation and finally the steps involved in installing MySQL on Ubuntu.

Table of Contents

What is MySQL?

Installing MySQL on Ubuntu: MySQL Logo
Image Source

MySQL was used by more than 39% of developers in the world back in 2019, making it the world’s most popular Database. It certainly lacks the extensive features that are provided by PostgreSQL, but it is still useful for a variety of use cases like web applications. 

Since it comes standard in the LAMP Stack, where LAMP stack is an Open-Source suite of web applications that consists of Linux, Apache HTTP Server, MySQL, PHP; MySQL is the go-to choice for scalable web applications. Let’s talk about a few salient features that make MySQL such a great catch. Also, see how you can integrate from MySQL to PostgreSQL

Key Features of MySQL

  • Maintained by Oracle: Oracle owns and maintains MySQL. It also offers premium versions of MySQL with additional services, proprietary plugins, user support, and extensions. 
  • Long History: MySQL has been around for over 20 years since its first release in 1995.
  • Frequent Updates: MySQL is getting more robust with frequent updates alongside new features and security improvements. The latest release is Version 8.0.23 released on 18 January 2021.
  • MVCC Features: MySQL recently started offering MVCC (Multi-Version Concurrency Control) features. 
  • A Supportive Community: A devoted community of developers is available to help with troubleshooting as and when needed.
  • Open-Source: MySQL is also a Free and Open-Source Relational Database Management System (RDBMS).
  • Users: MySQL is used widely by Google, NASA, Flickr, GitHub, Netflix, Tesla, Twitter, Uber, Wikipedia, YouTube, Zendesk to name a few.

What is Ubuntu?

Installing MySQL on Ubuntu: Ubuntu Logo
Image Source

Ubuntu is well renowned as a Linux Distribution that consists of Open-source and Free Software. With Ubuntu, you get more robust Open-source security as compared to Linux. Also, you can easily secure your open-source apps by patching up the full stack from kernel to library and its applications. Ubuntu provides you with sustainable Open-source software at the lowest costs in the marketplace. With Ubuntu, you can automate everything from Edge Clusters, Multi-cloud Operations, Bare-metal Provisioning, and IoT.

Ubuntu is preferred by professional developers across the globe since it offers Cloud DevOps and optimized ML/AI. Laced with features like configuration lockdown, full disk encryption, local Kubernetes for Offline Development, and certified workstations make Ubuntu, one of the best picks of the lot. 

Key Features of Ubuntu 20.04 OS

Here are a few salient features of Ubuntu 20.04 OS:

  • Enhanced Support: With Ubuntu 20.04 OS, you can provide support for new hardware including Initial Tiger Lake platforms and Intel Comet Lake CPUs along with various other powerful features.
  • Network Configuration: With the release of this Ubuntu version, netplan.io has sprung forth with various new features like support for GSM modems through the NetworkManager backend via the modems section. Apart from this, the ability to set ipv6-address-generation for the NetworkManager backend has also been provided in this version.
  • File Systems/Storage: Starting from the Eoan release, Ubuntu Focal ships zfs 0.8.3. There are a couple of new features provided here such as Pool TRIM, Device Removal, Native Encryption to name a few.
  • RISC-V Image: With Ubuntu 20.04 OS, you can now avail of the RISC-V Images for HiFive SiFive Unmatched and Unleashed boards. These images can also be used as a Virtual Machine with QEMU on any Ubuntu 20.04 machine.

What are the Prerequisites for Installing MySQL on Ubuntu?

The prerequisites for MySQL Ubuntu Installation are as follows:

  • Network Connection.
  • A System Running Ubuntu 20.04.
  • Access to a Terminal.
  • A User Account with Sudo privileges.

Simplify MySQL ETL with Hevo’s No-code Data Pipeline

A fully managed No-code Data Pipeline platform like Hevo helps you integrate data from 100+ data sources (including 40+ Free Data Sources) like MySQL to a destination of your choice in real-time in an effortless manner. Hevo with its minimal learning curve can be set up in just a few minutes allowing the users to load data without having to compromise performance. Its strong integration with umpteenth sources provides users with the flexibility to bring in data of different kinds, in a smooth fashion without having to code a single line. 

GET STARTED WITH HEVO FOR FREE

Check Out Some of the Cool Features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • Transformations: Hevo provides preload transformations through Python code. It also allows you to run transformation code for each event in the Data Pipelines you set up. These can be configured and tested before putting them to use.
  • Connectors: Hevo supports 100+ Integrations to SaaS platforms, files, databases, analytics, and BI tools. It supports various destinations including Amazon Redshift, Firebolt, Snowflake Data Warehouses; Databricks, Amazon S3 Data Lakes, MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL databases to name a few.  
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100+ sources, that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
  • Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.

Simplify your Data Analysis with Hevo today! 

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

How to Install MySQL on Ubuntu 20.04?

Here are the steps involved in MySQL Ubuntu Installation 20.04:

Installing MySQL on Ubuntu: Update Package Repository & Install MySQL

  • Step 1: Open up the terminal and execute the following command:
sudo apt update
  • Step 2: Supply your password and wait for the update to finish. Next, run the following command:
sudo apt upgrade
  • Step 3: Enter Y when prompted to continue with the upgrade and press the ENTER button. Once you have updated the package repository, you can move on to the MySQL installation.
  • Step 4: Run the following command to install MySQL Server:
sudo apt install mysql-server
  • Step 5: When the system asks you to continue the installation, answer Y and hit the ENTER button. This is what the output should look like:
Installing MySQL on Ubuntu: Update Package Repository & Install MySQL Step 5
Image Source
  • Step 6: The system will then download the MySQL Packages and install them on your designated machine.
  • Step 7: Next, you need to check if MySQL was successfully installed by running the following command:
mysql --version
  • Step 8: The output will depict the version of MySQL installed on your machine as follows:
Installing MySQL on Ubuntu: Update Package Repository & Install MySQL Step 8
Image Source

Installing MySQL on Ubuntu: Configure the MySQL Installation

  • Step 1: For fresh installations of MySQL, you will have to run DBMS’s Included Security Script. This script will modify some of the less secure default options for things like sample users and remote root logins. Run the security script with sudo as follows:
sudo mysql_secure_installation
  • Step 2: This will take you through various prompts where you can make changes to your MySQL installation’s security options. The first prompt will ask if you’d like to set up the Valid Password Plugin, which can be leveraged to test the password strength of new MySQL users before calling them valid.
  • Step 3: The strongest policy level for Valid Password Plugin can be selected by entering 2. This will require passwords to be at least 8 characters long and consist of a mix of Numeric, Lowercase, Uppercase, and Special Characters:
Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?

Press y|Y for Yes, any other key for No: Y

There are three levels of password validation policy:

LOW    Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary                  file

Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:
 2
  • Step 4: The next prompt you will face will ask you to set up a password for the MySQL root user. Enter a password of your choice and confirm it as follows:
Please set the password for root here.


New password:

Re-enter new password:
  • Step 5: A point to note here is that even though you’ve set up a password for the root MySQL user, this user hasn’t been configured to authenticate using a password when connecting to a MySQL Shell currently.
  • Step 6: If you leveraged the Validate Password plugin, you’ll obtain feedback on the strength of your new password. Next, the script will ask if you wish to continue with the password you just entered or if you wish to enter a new one. So, if you are satisfied with the strength of the password, enter Y to continue the script:
Estimated strength of the password: 100
Do you wish to continue with the password provided? (Press y|Y for Yes, any other key for No) : Y
  • Step 7: From here, you can click on Y and then ENTER to accept the defaults for all the questions that follow. This will result in the removal of some anonymous users and the test database, disable root logins, and load these new rules so that MySQL can instantly respect the changes you’ve made. After the completion of this script, your MySQL installation will be secured. You can now create a dedicated user with the MySQL client. 

Installing MySQL on Ubuntu: Add a Dedicated MySQL User

  • Step 1: Having installed MySQL, a root user account is created which can be used to manage your MySQL account. This user is given full privileges over the MySQL Server, which means that it has complete control over every table, database, user, and so on. Therefore, it is recommended that you do not use this account outside of administrative functions.
  • Step 2: In Ubuntu systems running MySQL 5.7 or later versions, the root MySQL user is set to authenticate utilizing the auth_socket plugin by default as opposed to leveraging a password. This plugin needs the name of the operating system user invoking the MySQL to match the name of the MySQL user specified in the command. Therefore, you should leverage mysql with sudo privileges to obtain access to the root MySQL Server with the following snippet:
sudo mysql
  • Step 3: Once you have gained access to the MySQL prompt, you can generate a new user with a CREATE USER statement as per the following syntax:
mysql> CREATE USER 'username'@'host' IDENTIFIED WITH authentication_plugin BY 'password';
  • Step 4: After the CREATE USER statement, you need to specify a username, followed by an @ sign and then the hostname from which the user will connect. You can specify localhost if you only plan on accessing this user locally. You have various options in terms of choosing the authentication plugin, but the auth_socket plugin mentioned previously would work just fine.
  • Step 5: Next, you need to run the following command to generate a user that authenticates with caching_sha2_password. Make sure that you change ‘Sammy’ to your preferred username and password:
mysql> CREATE USER 'sammy'@'localhost' IDENTIFIED BY 'password';

Installing MySQL on Ubuntu: Grant Privileges to Secure MySQL

  • Step 1: To grant user privileges, you can use the following syntax:
mysql> GRANT PRIVILEGE ON database.table TO 'username'@'host';
  • Step 2: The PRIVILEGE value in the previous command describes what actions the user can perform on the mentioned table and database. You can also grant multiple privileges to the same user in one command by distancing them with commas. You can even grant user privileges globally by entering asterisks instead of the database and table names. For instance, you can run the following GRANT statement that grants a user global privileges to ALTER, CREATE, DROP tables, databases, and users along with the power to UPDATE, INSERT, DELETE data from any table in the server plus SELECT, REFERENCES, and RELOAD:
GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sammy'@'localhost' WITH GRANT OPTION;
  • Step 3: Next, it is recommended that you run the FLUSH PRIVILEGES command since it frees up any memory that the server might have cached as a result of the previous CREATE USER and GRANT statements as follows:
FLUSH PRIVILEGES;
  • Step 4: You can then exit the MySQL client with the following command:
exit
  • Step 5: For future use cases, if you wish to log in as your new MySQL user, you can use the following command for the same:
mysql -u sammy -p
  • Step 6: The -p flag causes the MySQL client to prompt you for your MySQL user’s password to authenticate.

Installing MySQL on Ubuntu: Test MySQL & Check if it’s Running

  • Step 1: Irrespective of how you’ve installed MySQL, it should have started running automatically. To test the connection, you can check its status with the following command:
systemctl status mysql.service
  • Step 2: The output to the aforementioned command will be similar to the following:
mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2020-04-21 12:56:48 UTC; 6min ago
   Main PID: 10382 (mysqld)
     Status: "Server is operational"
      Tasks: 39 (limit: 1137)
     Memory: 370.0M
     CGroup: /system.slice/mysql.service
             └─10382 /usr/sbin/mysqld
  • Step 3: If MySQL isn’t running, you can start MySQL with the command sudo systemctl start mysql. For a supplementary check, you can try connecting to the database with the help of the mysqladmin tool, which is a client that allows you to run administrative tasks. For instance, this command says to connect as a MySQL user named Dmitri, prompt for a password, and return the version:
sudo mysqladmin -p -u Dmitri version
  • Step 4: This is the output you can expect for the aforementioned command:
mysqladmin  Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((Ubuntu))
Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Server version        8.0.19-0ubuntu5
Protocol version    10
Connection        Localhost via UNIX socket
UNIX socket        /var/run/mysqld/mysqld.sock
Uptime:            10 min 44 sec

Threads: 2  Questions: 25  Slow queries: 0  Opens: 149  Flush tables: 3  Open tables: 69  Queries per second avg: 0.038

This is confirmation that MySQL is up and running.

Installing MySQL on Ubuntu: Log in to your MySQL Server

  • Step 1: Finally to log in to the MySQL interface, you need to run the following command:
sudo mysql -u root
Installing MySQL on Ubuntu: Log in to your MySQL Server
Image Source
  • Step 2: With this, you can now generate databases, execute queries, and seamlessly test out your new MySQL setup.

Conclusion

This blog talks about the different steps involved in setting up the MySQL Workbench Ubuntu installation seamlessly. It also talks about the key features and benefits of leveraging MySQL and Ubuntu 20.04.

Extracting complex data from a diverse set of data sources to carry out an insightful analysis can be challenging, and this is where Hevo saves the day! Hevo offers a faster way to move data from Databases or SaaS applications like MySQL into your Data Warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.

VISIT OUR WEBSITE TO EXPLORE HEVO

Want to take Hevo for a spin?

SIGN UP and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

No-code Data Pipeline For MySQL