How To Install Mysql Workbench On Ubuntu 20.04

MySQL is one of the most commonly used open source relational database management systems. It is the third component of the LAMP and LEMP stacks used for developing and deploying dynamic web applications and websites. MySQL is currently developed by Oracle.

In this tutorial, we will cover how to install MySQL on Ubuntu 20.04. We will also show you how to create a sample database and table with some records.

Prerequisites

A user with sudo privileges is required to install MySQL on Ubuntu 20.04.

Install MySQL on Ubuntu 20.04

MySQL is available in the default Ubuntu package repository. First of all, update the Ubuntu package repository with the following command.

$ sudo apt update

Now, run the command below to install MySQL on Ubuntu 20.04.

$ sudo apt install mysql-server

Enter y to continue when prompted.

After the installation process completes successfully, run the next command to check the status of the MySQL service.

$ sudo systemctl status mysql

Press Q to return to the command prompt.

Note: If it turns out that the MySQL service is not active, try to start it with:

$ sudo systemctl start mysql-server

Configure MySQL

There is a script named mysql_secure_installation that comes as part of the MySQL installation. You would need to run this script to secure your MySQL installation as follows.

$ sudo mysql_secure_installation

The script will take you through a series of steps as shown in the sample output below.

          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: 1 Please set the password for root here.  New password:  Re-enter new password:  Estimated strength of the password: 50 Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y By default, a MySQL installation has an anonymous user, allowing anyone to log into MySQL without having to have a user account created for them. This is intended only for testing, and to make the installation go a bit smoother. You should remove them before moving into a production environment.  Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Success.   Normally, root should only be allowed to connect from 'localhost'. This ensures that someone cannot guess at the root password from the network.  Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Success.  By default, MySQL comes with a database named 'test' that anyone can access. This is also intended only for testing, and should be removed before moving into a production environment.   Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y  - Dropping test database... Success.   - Removing privileges on test database... Success.  Reloading the privilege tables will ensure that all changes made so far will take effect immediately.  Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y Success.  All done!

Login to MySQL

After configuring MySQL, you may now login with the root account as follows.

$ sudo mysql -u root -p

If you see the mysql> prompt, then you are on course.

Let us run our first query to show available databases.

mysql> SHOW DATABASES;

Create an Alternate MySQL Root User

Rather than login as the default MySQL root user, you could create an alternate user and then grant all permissions on all databases as follows. Change mysqluser and mySQLp@ssw0rd to your own values.

Create a MySQL user:

mysql> CREATE USER mysqluser@localhost IDENTIFIED by 'mySQLp@ssw0rd';

Grant all privileges on all databases to the new user:

mysql> GRANT ALL PRIVILEGES ON *.* to mysqluser@localhost;

Or to grant ALL PRIVILEGES to a specific user on a specific database, run:

mysql> GRANT ALL PRIVILEGES ON continents.* TO 'mysqluser'@'localhost';

Reload the grant tables to apply the changes:

mysql> FLUSH PRIVILEGES;

After that, quit MySQL:

mysql> QUIT

Now, Login again as the newly created user:

mysql> mysql -u mysqluser -p

Create a Database in MySQL

Run the query below to create a new database in MySQL.

mysql> CREATE DATABASE continents;

To select the newly created database, run the next query.

mysql> USE continents;

Create a table in MySQL

Let us proceed to create a simple table with 2 fields inside the continents database and populate it as follows.

mysql> CREATE TABLE europe (country varchar (25), abbreviation char (2) );

Add records to the table:

mysql> INSERT INTO europe VALUES ('Germany', 'DE'), ('Italy', 'IT');

To view the records in the europe table, run:

mysql> SELECT * FROM europe;

To delete the europe table, run:

mysql> DROP TABLE europe;

To delete the continents database, run:

mysql> DROP DATABASE continents;

Conclusion

In this tutorial, we covered how to install and configure MySQL on Ubuntu 20.04. We also showed you how to create an alternate MySQL root user. Further, we described how to create a database with a table and sample records.