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.

How to Install MySQL on Ubuntu 20.04?

Here are the steps involved in MySQL Ubuntu Installation 20.04:

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

Are you looking for an easy way to replicate your MySQL data? Hevo is the only real-time ELT No-code Data Pipeline platform that cost-effectively automates data pipelines that are flexible to your needs. With integration with 150+ Data Sources, including MySQL (40+ free sources), we help you not only export data from sources & load data to the destinations but also transform & enrich your data, & make it analysis-ready.

Simplify your Data Analysis with Hevo today! 

SIGN UP HERE FOR A 14-DAY FREE TRIAL!

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.

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, 

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.

mm
Content Marketing Manager, Hevo Data

Amit is a Content Marketing Manager at Hevo Data. He enjoys writing about SaaS products and modern data platforms, having authored over 200 articles on these subjects.

No-code Data Pipeline For MySQL