IntroductionMySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It implements the relational model and uses Structured Query Language (better known as SQL) to manage its data. Show This tutorial will go over how to install MySQL version 8.0 on an Ubuntu 20.04 server. By completing it, you will have a working relational database that you can use to build your next website or application. PrerequisitesTo follow this tutorial, you will need:
Step 1 — Installing MySQLOn Ubuntu 20.04, you can install MySQL using the APT package repository. At the time of this writing, the version of MySQL available in the default Ubuntu repository is version 8.0.27. To install it, update the package index on your server if you’ve not done so recently:
Then install the
Ensure that the server is running using the
These commands will install and start MySQL, but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next. Step 2 — Configuring MySQLFor fresh installations of MySQL, you’ll want to run the DBMS’s included security script. This script changes some of the less secure default options for things like remote root logins and sample users. Warning: As of July 2022, an error will occur when you run the Prior to July 2022, this script would silently fail after attempting to set the root account password and continue on with the rest of the prompts. However, as of this writing the script will return the following error after you enter and confirm a password:
This will lead the script into a recursive loop which you can only get out of by closing your terminal window. Because the First, open up the MySQL prompt:
Then run the following
After making this change, exit the MySQL prompt:
Following that, you can run the Once the security script completes, you can then reopen MySQL and change the root user’s authentication method back to the default,
Then go back to using the default authentication method using this command:
This will mean that you can once again connect to MySQL as your root user using the Run the security script with
This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the password strength of new MySQL users before deeming them valid. If you elect to set up the Validate Password Plugin, any MySQL user you create that authenticates with a password will be required to have a password that satisfies the policy you select. The strongest policy level — which you can select by entering
Regardless of whether you choose to set up the Validate Password Plugin, the next prompt will be to set a password for the MySQL root user. Enter and then confirm a secure password of your choice:
Note that even though you’ve set a password for the root MySQL user, this user is not currently configured to authenticate with a password when connecting to the MySQL shell. If you used the Validate Password Plugin, you’ll receive feedback on the strength of your new password. Then the script will ask if you want to
continue with the password you just entered or if you want to enter a new one. Assuming you’re satisfied with the strength of the password you just entered, enter
From there, you can press Once the script completes, your MySQL installation will be secured. You can now move on to creating a dedicated database user with the MySQL client. Step 3 — Creating a Dedicated MySQL User and Granting PrivilegesUpon installation, MySQL creates a root user account which you can use to manage your database. This user has full privileges over the MySQL server, meaning it has complete control over every database, table, user, and so on. Because of this, it’s best to avoid using this account outside of administrative functions. This step outlines how to use the root MySQL user to create a new user account and grant it privileges. In Ubuntu systems running MySQL
Note: If you installed MySQL with another tutorial and enabled password authentication for root, you will need to use a different command to access the MySQL shell. The following will run your MySQL client with regular user privileges, and you will only gain administrator privileges within the database by authenticating:
Once you have access to the MySQL prompt, you can create a new user with a
After You have several options when it comes to choosing your user’s authentication plugin. The As an alternative, you can leave out the Run the following command to create a user that
authenticates with
Note: There is a known issue with some versions of PHP that causes problems with
If you aren’t sure, you can
always create a user that authenticates with
After creating your new user, you can grant them the appropriate privileges. The general syntax for granting user privileges is as follows:
The To illustrate, the following command grants a user global privileges to You can find the full list of available privileges in the official MySQL documentation. Run this
Note that this statement also includes Warning: Some users may want to grant their MySQL user the
Such broad privileges should not be granted lightly, as anyone with access to this MySQL user will have complete control over every database on the server. Following this, it’s good practice to run the
Then you can exit the MySQL client:
In the future, to log in as your new MySQL user, you’d use a command like the following:
The Finally, let’s test the MySQL installation. Step 4 — Testing MySQLRegardless of how you installed it, MySQL should have started running automatically. To test this, check its status.
You’ll see output similar to the following:
If MySQL isn’t running, you can start it with
For an additional check, you can try connecting to the database using the
You should see output similar to this:
This means MySQL is up and running. ConclusionYou now have a basic MySQL setup installed on your server. Here are a few examples of next steps you can take:
|