Installing MySQL on Ubuntu 20.04: 6 Easy Steps

MySQL 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. It is scalable, intuitive, and swift when compared to its contemporaries. MySQL Server is availed as a separate program and handles all the database instructions, commands, and statements.

When you combine the capabilities of MySQL with Ubuntu, that can be leveraged for all your applications equally in 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.

What is Ubuntu?

Ubuntu is a freely available open-source Linux operating system, aligning itself with the Debian family of Linux and holding the distinction of being the most widely embraced Linux distribution. Developers globally prefer Ubuntu for its robust Cloud DevOps support and optimized Machine Learning (ML) and Artificial Intelligence (AI) capabilities, making it a top choice among professionals. In the realm of database development, Ubuntu seamlessly accommodates two prominent database servers: MySQL and PostgreSQL.

Requirements for MySQL Installation on Ubuntu

To set up MySQL on Ubuntu, ensure you have the following:

• Ubuntu operating system
• User account with sudo privileges
• Stable network connection
• Access to the terminal

Database Engines

Database engines play a crucial role in the functionality and performance of MySQL on Ubuntu packages. While the default MySQL configuration on Ubuntu is functional and efficient, it’s essential to consider certain aspects before proceeding. MySQL is designed to accommodate different methods of storing data, known as database or storage engines. The two primary engines of interest are InnoDB and MyISAM. Despite the transparent nature of these engines to end-users, they exhibit distinct advantages and disadvantages.

While mixing and matching database engines at a table level is possible and potentially beneficial, this practice diminishes the effectiveness of performance tuning. Resources are split between two engines instead of being dedicated to one. MyISAM, the older of the two engines, may outperform InnoDB in specific situations and is inclined towards a read-only workload. Some web applications are optimized for MyISAM, although this doesn’t imply inferior performance under InnoDB. MyISAM supports the FULLTEXT data type, enabling rapid searches of extensive text data. However, it can only lock an entire table for writing, limiting concurrent updates and lacking journaling, making data recovery challenging after a crash.

InnoDB, a more modern engine, prioritizes ACID compliance, ensuring reliable processing of database transactions. Write locking occurs on a row level within a table, allowing multiple updates simultaneously. In-memory data caching is handled more efficiently at a row level rather than a file block. ACID compliance is achieved through independent journaling of all transactions, facilitating more reliable data recovery and consistency checks.

Since MySQL 5.5, InnoDB is the default engine and is strongly recommended over MyISAM unless specific features unique to MyISAM are required.

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:

• 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:

The MySQL server should be started as soon as the installation is complete. You can check its current status using systems:

sudo service mysql status

The output should look like the following:

● mysql.service – MySQL Community Server

Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)

You can verify the network status of the MySQL service by executing the ss command in the terminal.

sudo ss -tap | grep mysql

Once you run the command, the output should be something similar to the following:

LISTEN 0 151 127.0.0.1:mysql 0.0.0.0:* users:((“mysqld”,pid=149190,fd=29))

LISTEN 0 70 *:33060 *:* users:((“mysqld”,pid=149190,fd=32))

If the server is not functioning properly, you can initiate a restart by entering the following command:

sudo service mysql restart

To initiate the process, a useful initial step is to check the systemd journal. Access it from the terminal prompt using the following command:

sudo journalctl -u mysql

Active: running and active since Tue 2019-10-08 14:37:38 PDT; 2 weeks 5 days ago

Main PID: 2028 (mysqld)

Tasks: 28 (limit: 4915)

CGroup: /system.slice/mysql.service

└─2028 /usr/sbin/mysqld –daemonize –pid-file=/run/mysqld/mysqld.pid

Oct 08 14:37:36 db.example.org systemd[1]: Starting MySQL Community Server.

Oct 08 14:37:38 db.example.org systemd[1]: MySQL Community Server Started 

Allow remote access

To enable remote access to the MySQL database from another machine when iptables is enabled, you must open the necessary port (default: 3306) in your server’s firewall. This step is unnecessary if the MySQL application is running on the same server. Execute the following commands to permit remote access to the MySQL server:

sudo ufw enable
sudo ufw allow mysql

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

• Step 2: With this, you can now generate databases, execute queries, and seamlessly test out your new MySQL setup.

Substituting a Native MySQL Distribution with the MySQL APT Repository

Various entities distribute different versions and branches of MySQL through distinct software repositories or download platforms. If you wish to exchange a native MySQL distribution obtained from your Linux system’s software repository with one from the MySQL APT repository, you can accomplish this in a few steps.

Database Backup

To prevent data loss, it is crucial to back up your database before attempting to replace your MySQL installation using the MySQL APT repository. Refer to the Backup and Recovery section for detailed instructions.

Incorporating the MySQL APT Repository and Choosing a Release Series

Include the MySQL APT repository in your system’s list of repositories. Select your preferred release series by adhering to the provided instructions in the Adding the MySQL APT Repository section.

Replacing the Original Distribution via an APT Update

As part of its functionality, the MySQL APT repository seamlessly substitutes your native MySQL distribution during the upgrade process for MySQL packages. To implement these upgrades, you can follow the instructions in Upgrading  MySQL with the MySQL APT Repository

Note:

The MySQL APT repository is designed to substitute MySQL distributions provided and managed by Ubuntu or Debian. It is not intended to replace any MySQL forks located within or outside the native repositories of these distributions. If you need to replace such MySQL forks, uninstall them before installing them through the MySQL APT repository. Refer to the uninstallation instructions provided by the distributors of the forks. Before proceeding, ensure you have backed up your data and are familiar with the process of restoring it on a new server.

How to Use the MySQL APT Repository?

This section covers how to use the MySQL APT repository, which provides deb packages for installing the MySQL server. This will be useful if you are installing MySQL fresh.

• How do you add the MySQL APT repository to your system? Head on to https://dev.mysql.com/downloads/repo/apt/.
• Download the release package needed for your Linux distribution. This won’t be mandatory for each update. But, it updates MySQL repository information to include the latest information.
• Let’s take an example.  mysql-apt-config_0.8.26-1_all.deb is the first APT repository configuration file, which adds the innovation release track that starts with MySQL 8.1.
• Use the command below to install the downloaded release package. This replaces version-specific-package-name with the name of your downloaded package. There would be a path before this if your command is not inside the folder where the package is):

$> sudo dpkg -i /PATH/version-specific-package-name.deb

For example, for version w.x.y-z of the package, the command is:

$> sudo dpkg -i mysql-apt-config_w.x.y-z_all.deb

• You need to select the version of the MySQL server followed by other details like Workbench required for install.
• Don’t change the default options, in case you aren’t sure which version to choose. 
• Instead, if you want to install a particular component, go for none. 
• Select OK to finish the configuration and installation once selecting all the components.

Updating MySQl on Ubuntu

Updating MySQL on Ubuntu can be achieved by executing the following commands:

sudo apt update

Once the update process is finished, proceed to install updates for each outdated package and dependency by running the upgrade command:

sudo apt upgrade

With MySQL 8.0 successfully updated on your Ubuntu system, you can perform various tasks such as designing databases, populating them with data, executing queries, and more. Managing databases on a MySQL server running on Ubuntu can be done through multiple avenues: through the command line, using a dedicated IDE (e.g., dbForge Studio for MySQL installed via CrossOver), or remotely from an IDE on Windows (similar to the aforementioned Studio).

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.

FAQ