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

In this post, we will guide you how to install MySQL 8.0 and create a database on an Ubuntu 22.04 Linux VPS. The guide was written by hosting experts and developers to give you the right information to get you started.

Key Takeaways

You need an updated operating system running Ubuntu 22.04 server with a non-root administrative user.
Once installed, MySQL is not secure, you need to set up a password.
You need to configure the MySQL after installation by removing anonymous users and disallowing remote root logins.
You can set up user privileges for the database.

Before we get to the tutorial, here are the details you need to know about the systems and hosting solutions. However, if you know about these solutions, you can skip straight to the guide.

What is Ubuntu?

Ubuntu is a popular and free open source Linux-based operating system that powers millions of PCs and laptops globally. Itâ€™s primarily intended for personal computers but can be used on a virtual private server.

It incorporates all the features of a Unix OS and employs GNOME, GUI and a set of desktop applications for Linux.

What are the Benefits of Ubuntu

Hereâ€™s what you get when you create a database on Ubuntu.

Its an open source software
It works in a range of computing platforms
Ubuntu is completely customizable
Minimal system or hardware requirements
The malware risks are negligible
The Software Center has tons of free software
Itâ€™s user friendly even for beginners
You can update Ubuntu without restraining your PC

Why Should You Go with Ubuntu?

You should consider Ubuntu out of all the available operating systems in the market because,

Itâ€™s free: You can download Ubuntu from its official website for free.
Security level: It has kernel enhancement that restricts how programs behave.
Active community: They help in fixing bugs, troubleshooting and introducing updates.

What Can You Do With The Ubuntu Database?

Creating a database on Ubuntu 22.04 Linux VPS allows you to,

Access Microsoft Office software
Browse the web
Faster access to email access whether you use Microsoft Exchange, Gmail, Hotmail, POP or IMAP.
Free apps to help you manage, edit, organize and share your photos and videos.
Access to multiple games.

What is MySQL?

MySQL is one of the most popular open source database platforms globally. Itâ€™s commonly installed as part of the LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. Supported by an active and huge community of open-source developers, itâ€™s available on over 20 platforms and operating systems, including Linux, Unix, Mac OS, and Windows OS.

To use this database management system in your business, you can either choose the free community version or paid enterprise option. If you are running a large business, the paid version is more ideal because it has more features for the enterprise environment.

Why is MySQL Popular?

MySQL is the most popular Database Management System that uses Structured Query Language. Itâ€™s popular because itâ€™s an open-source software that is freely available. Itâ€™s flexible enough for a dynamic business environment.

Additionally, itâ€™s compatible with most programming languages including C++, Java, Python, Javascript, PHP, and Ruby.

Requirements for Installing MySQL 8.0 and Creating a Database on an Ubuntu 22.04 Linux VPS

Hereâ€™s what you need to install MySQL 8.0:

Up to date operating system running Ubuntu 22.04 server with a non-root administrative user
A firewall configured with UFW.
Server with stable internet access
Server with sudo privileges
Terminal window / Command line

Note

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.

How to Set Up MySQL 8.0

Follow these step-by-step instructions to set up your MySQL.

1. Update the System Repository

Before proceeding, ensure that the server is up to date. Update the repository and then do a system upgrade to ensure all the installed packages are the latest versions.

Run the apt-get update command to update our server.

2. Install the MySQL Server

Install the MySQL server with the following command;

apt-get install â€“y mysql-server (The â€“y will skip the confirmation question).

Give it a few minutes as the installation of the MySQL will take some time to complete.

3. Start MySQL Service

Once the installation process is done, start the MySQL service by issuing this command: systemctl start mysql

You can also check the status using the systemctl status mysql command.

4. Verify the MySQL Service Version

Before moving to the next step, verify that the mysql version is 8.0 and that itâ€™s currently running on Ubuntu 22.04 operating system.

Check the mysql versiÃ³n with the mysql â€“version command or using the mysql â€“e â€œSELECT VERSION();â€

To avoid any errors, confirm that all your programs are the right versions.

How to Secure MySQL Installation

After the set up, you need to secure your installation. By default, MySQL does not include most basic and essential security features. However, it has an installation script that walks you through the configuration process.

To secure your installation, youâ€™ll need to,

Establish a password policy
Set a root password
Limit the password to connect from the localhost
Disable anonymous logins
Get rid of test database and privileges of any user to access any database that begins with test_

Note

NOTICE: As of July 2022, if you try to run mysql_secure_installation without taking some steps, it would raise an error. The error occurs because the script attempts to set a password for the MySQL root account. However, on Ubuntu installations, this account is not configured to connect using a password.

Prior to this, the script would fail quietly if you attempted to set the root account password, but it would not interfere with the rest of the prompts.

Once the error occurs, you will enter into an everlasting loop. You need to undertake some steps before since the mysql_secure_installation scripts executes some actions that are needed to keep the server secure. Therefore, you have to use other tactics to avoid the error and loop.

Follow these steps to avoid the error.

1. Access MySQL

2. Set the Root Password

Launch the following query to set root password

ALTER USER â€˜rootâ€™@â€™localhostâ€™ IDENTIFIED WITH mysql_native_password BY â€˜passwordâ€™;

Replace the password with whatever password you want to use.

3. Exit MySQL

4. Validate Password Plugin

To secure MySQL, youâ€™ll receive several prompts, one of them to configure the Validate Password Plugin. You can also use the same process to test the password strength of your new MySQL users to confirm validity.

If you choose to set up the Validate Password Plugin, any MySQL user you create on the database that authenticates with a password must have a password that satisfies the policy you select.

To test the password strength beforehand, use the VALIDATE_PASSWORD_STRENGTH mysql function.

Here are some examples.

The next step is executing mysql_secure_installation

Youâ€™ll receive feedback on the strength of your new password. The script will also ask if you want to continue with the password you just entered or if you want to set up a new one. If you are satisfied with the strength of the password you entered, enter Y to continue the script.

Select any other key if you are not satisfied with the password to set up a new one.

5. Secure the Permissions

Once you are done with the password, the next step is to secure other permissions such as removing some anonymous users and the test database and disallowing remote root logins. Click Y to accept all the questions.

While these permissions are important during set up, they are only intended for testing and make the installation process seamless. However, you should remove all of them before moving to the production stage.

To effect the changes, answer yes to the last question that allows you to reload privileges. This last step will load the new rules on MySQL immediately and save the changes you have made.

To revert the root authentication method back to the original one â€“ auth socket, you should execute the following command; mysql -uroot -p -e â€œALTER USER â€˜rootâ€™@â€™localhostâ€™ IDENTIFIED WITH auth_socket;â€

At this stage, you will be asked for the mysql root password that you configured.

If you need to disable password policy validation launch, you can do so by activating this command,

mysql -uroot -p -e â€œUNINSTALL COMPONENT â€˜file://component_validate_passwordâ€™;â€

Additionally, you can also obtain a list of which components are installed.

How to Create MySQL Database On Ubuntu 22.04

Follow these steps,

1. Log in as Root

After installation, MySQL normally creates a root user account which you can use to manage your database. It has privileges over the MySQL server; giving it complete control over each database, table, and user.

Therefore, itâ€™s best to avoid using this root account apart from administrative functions. However, you can use the root MySQL user to create a new user account and grant it privileges.

To log in as root, you need to enter the password in the prompt.

2. Issue a Query

After logging in to your root account, issue the following query: CREATE DATABASE myfirstdb to create the database.

3. View the Databases

If you want to view all the databases, you can see the list using the command, SHOW DATABASES.

On the other hand, if you are looking for a specific database, you can use the filters to get you all the names that match your search.

You can also select a database to work with. For instance if you had created a database called â€œmyfirstdbâ€, it will appear.

4. View Database Tables

Apart from viewing and selecting the databases to work with, users can see tables on the specific database. However, if you created the database recently, you wonâ€™t have any tables displaying.

Once your database is ready, you need to know the different MySQL commands in order to manipulate the database to your liking. Some of the functions you need to know 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.

How to Create Tables

If you have a new database, hereâ€™s how to create the first table using the default MySQL store engine which is InnoDB. Enter this command,

CREATE TABLE person(
    p_id int,
    lastname varchar(255),
    firstname varchar(255),
    city varchar(255),
    age int
);

Once you have inputted this command, you can use the same steps to view database tables. You will see a list of all the new tables that you have added.

Furthermore, apart from viewing the tables, you can also check the estructure to obtain information regarding the engine, collate and charset used. Hereâ€™s how it appears.

How to Create New Users and Granting Privileges

To create a new user on MySQL, you need to specify a username. If your plan for the user is to access the Ubuntu server locally, you should specify localhost. In addition, you can wrap both the host and username in single quotes to prevent errors.

After creating a new user, youcan grant them appropriate privileges. These privileges define the actions the user is allowed to perform on a specific database and table. You can grant one user multiple privileges or choose whether itâ€™s restricted locally or globally.

Users get privileges that allow them to

Create, alter and drop tables and databases.
Insert, update and delete data from tables on the server
Query data with select foreign keys
Perform flush operations with the Reload privilege

Testing MySQL

Regardless of how you installed it, MySQL should start running automatically. To test its functionality, check its status. You should also test whether itâ€™s accepting connections.

Conclusion

Installing the MySQL 8.0 on Ubuntu 22.04 Linux VPS is a direct process. To make it work, you need to install and secure the MySQL.

Youâ€™ll need an up to date operating system running Ubuntu 22.04 server with a non-root administrative user, a server with stable internet access and sudo privileges. Once the MySQL is up and running, you can create a database.

If you follow the above steps, you will be able to install your MySQL 8.0 and create a database on Ubuntu 22.04 Linux VPS without any problems.

Next Steps: What Now?

Here are some practical steps you can take from this comprehensive guide on how to install your MySQL and create a database on Ubuntu 22.04 VPS;

Create your database on the best VPS hosting providers.
You can install the latest version of MySQL server by using the best dedicated server hosting providers.
Once installed, the MySQL installation needs to be secured using a password.

FAQs

How to Install and Configure MySQL on a Windows Server
The first step of installation is to download the MySQL community server from its official website. You can download MySQL for Windows directly from MySQL.com. Start the installation process by running the MySQL installer file. Read and accept the license agreement, choose a setup and select the â€œServer Onlyâ€ option to complete the process.
Why Do You Need VPS?
A VPS is necessary because itâ€™s a dedicated resource for storing your professional files in a shared server. Itâ€™s more flexible and secure. Additionally, if you donâ€™t have dedicated server space, a VPS is a better alternative than shared hosting.
Why Do I Need To Update Servers?
Before installing MySQL, the server is up to date. Do a system upgrade to ensure all the installed packages are the latest versions to prevent any errors and issues during the installation process.