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

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.

How to Install MYSQL 8

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).

How to Install MYSQL 8

How to Install MYSQL 8

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.

How to Install MYSQL 8

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 Install MYSQL 8

 

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.

How to Install MYSQL 8

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

How to Install MYSQL 8

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.

How to Install MYSQL 8

3. Exit MySQL

How to Install MYSQL 8

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.

How to Install MYSQL 8

The next step is executing mysql_secure_installation

How to Install MYSQL 8

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.

How to Install MYSQL 8

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.

How to Install MYSQL 8

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.

How to Install MYSQL 8

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 Install MYSQL 8

 

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.

How to Install MYSQL 8

2. Issue a Query

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

How to Install MYSQL 8

3. View the Databases

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

How to Install MYSQL 8

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.

How to Install MYSQL 8

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

How to Install MYSQL 8

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.

How to Install MYSQL 8

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

);

How to Install MYSQL 8

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.

How to Install MYSQL 8

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 Install MYSQL 8

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;

Further Reading:

If you want to learn more about how to install and set up MySQL and create databases on Ubuntu Linux VPS, here are some useful resources.

 

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.

How To Set up a VSFTPD Server on a CentOS 7 VPS or Dedicated Server

Brief Description FTP is usually insecure exposing clear-text passwords, userna
2 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How To Set up a VSFTPD Server on an Ubuntu 16.04 VPS or Dedicated Server

Brief Description FTP data is usually insecure since information (usernames, pa
2 min read
Eliran Ouzan
Eliran Ouzan
Web Designer & Hosting Expert

How to use phpMyAdmin to develop a website (without MySQL experience)

Brief description A web developer who is not well versed into coding websites f
2 min read
Idan Cohen
Idan Cohen
Marketing Expert

How to Install MySQL on a Windows Web Server Running Apache

This tutorial will show you how to install the MySQL database on a Windows serve
3 min read
Michael Levanduski
Michael Levanduski
Expert Hosting Writer & Tester
HostAdvice.com provides professional web hosting reviews fully independent of any other entity. Our reviews are unbiased, honest, and apply the same evaluation standards to all those reviewed. While monetary compensation is received from a few of the companies listed on this site, compensation of services and products have no influence on the direction or conclusions of our reviews. Nor does the compensation influence our rankings for certain host companies. This compensation covers account purchasing costs, testing costs and royalties paid to reviewers.
Click to go to the top of the page
Go To Top