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