How to Install MYSQL 8.0 and Create a Database on an Ubuntu 18.04 Linux VPS

Introduction>

MySQL is one of the most utilized open source database platforms globally. It’s available both as a community version (free) and enterprise version (paid) which has more features for enterprise environment.

MySQL is a relational database system with Structure Query Language (SQL) commands [SELECT, CREATE TABLE, UPDATE, DELETE, INSERT, DROP TABLE, e.t.c] for managing relational databases. In this tutorial, we will show how to install MySQL 8.0 Community version which is currently the latest version (and includes powerful features, yet very easy to set up and use). We will then illustrate how to retrieve the mysql root password, alter it and create a database in Ubuntu 18.04 Linux VPS.  

Special Note: before installing MySQL on your server we recommend that you consider if you have the right hosting service. Hosting Services that specialize in MySQL database hosting (such as A2hosting) will be able to provide better support on MySQL related topics. Visit Hostadvice's reviews for the best MySQL hosting services.

MySQL Installation

Download MySQL Apt Repository

$ wget https://dev.mysql.com/get/mysql-apt-config_0.8.10-1_all.deb

Install the MySQL Apt Repository

$ sudo dpkg -i mysql-apt-config_0.8.10-1_all.deb

MySQL installer appears as shown below and prompts you to choose the appropriate mysql repository’s version. Ensure the selected mysql version in “MySQL Server & Cluster” is “mysql-8.0”. Then press the Down key in your computers to select “OK” then press Enter key.

Then you’re returned to the shell prompt upon completion of mysql apt repo installation.

You can confirm that the repository for MySQL 8.0 has been installed by checking the file /etc/apt/sources.list.d/mysql.list

$ sudo vim /etc/apt/sources.list.d/mysql.list

The file should have content as shown below:

### THIS FILE IS AUTOMATICALLY CONFIGURED ###
# You may comment out entries below, but any other modifications may be lost.
# Use command 'dpkg-reconfigure mysql-apt-config' as root for modifications.
deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-apt-config
deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-8.0
deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-tools
#deb http://repo.mysql.com/apt/ubuntu/ bionic mysql-tools-preview
deb-src http://repo.mysql.com/apt/ubuntu/ bionic mysql-8.0

Install MySQL 8.0 Server and Start MySQL Service

First of all update the repository by running command:

$ sudo apt-get update

Then install the mysql server

$ sudo apt-get install -y mysql-server

MySQL Installer will provide several prompts during the installation as follows:

Enter root password:

Repeat root password

Information on the new changes in MySQL 8.0 about the new improved SHA-256 password authentication method. Press Tab to highlight the “OK”, then Enter to continue.

Select the default authentication plugin. This is a new feature in MySQL 8.0 which recommends usage of improved SHA-256 password authentication method which is the highlighted “Use Strong Password Encryption (RECOMMENDED)”. Incase you were doing an upgrade from MySQL 5.7 to 8.0, choosing this method will prevent old clients from connecting to the server. If this is the case, you’ll have to choose the other option “Use Legacy Authentication Method (Retain MySQL 5.* Compatibility)

Then Press Enter for the installation to continue.

At the end of installation start mysql server.

$ sudo systemctl start mysqld

Confirm the mysql version is 8.0

$ mysql -version
mysql Ver 8.0.11 for Linux on x86_64 (MySQL Community Server - GPL)

Secure the MySQL Installation

$ sudo mysql_secure_installation
 
Securing the MySQL server deployment.
 
Enter password for user root:
 
VALIDATE PASSWORD PLUGIN 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 plugin?
 
Press y|Y for Yes, any other key for No: No
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) : No
 
 ... skipping.
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!

Create MySQL Database

Log into mysql server as root

$ mysql -u root -p

Enter the  password for root in the prompt.
Create new database called ‘myfirstdb

mysql> CREATE DATABASE myfirstdb;

List all databases in the mysql-server

mysql> SHOW DATABASES;

Sample Output

$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 8.0.11 MySQL Community Server - GPL
 
Copyright (c) 2000, 2018, 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.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE DATABASE myfirstdb;
Query OK, 1 row affected (0.03 sec)
 
mysql> SHOW DATABASES;
+--------------------+
| Database  	     |
+--------------------+
| information_schema |
| myfirstdb      	|
| mysql          	|
| performance_schema |
| sys            	|
+--------------------+
5 rows in set (0.00 sec)
 
mysql> quit;
Bye

Conclusion

We have now gotten you up and running in the most critical part (i.e. installation of the latest version of MySQL Server and creating a database). The next step is to learn how to run more MySQL commands in order to manipulate the database to your liking. These include creating new mysql users, granting users privileges, creating tables in databases, inserting and updating records in tables, making queries to obtain records from the tables and many other functionalities. You should also consider learning how to backup and restore databases from the command line.

 

Check out these top 3 VPS services:

Was this article helpful?