How To Install MySQL On Ubuntu 20.04 | digital ocean (2023)

introduction

mysqlis an open source database management system that is often included as part of the popularLAMP(Linux, Apache, MySQL, PHP/Python/Perl). It implements the relational model and uses Structured Query Language (better known as SQL) to manage its data.

This tutorial will show you how to install MySQL version 8.0 on Ubuntu 20.04 server. When complete, you'll have a working relational database that you can use to build your next website or app.

requirements

To follow this tutorial you will need:

• An Ubuntu 20.04 server with a non-root user and a firewall configured with UFW. To set this up, follow ourInstructions for initial server setup for Ubuntu 20.04.

Paso 1 - Instalar MySQL

On Ubuntu 20.04, you can install MySQL from the APT package repository. As 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 haven't done so recently:

sudo appropriateUpdate

Then install theServidor MySQLPackage:

sudo appropriate installServidor MySQL

Use it to make sure the server is runningctl boot systemDomain:

sudosystemctl mysql.service started

These commands install and start MySQL, but do not prompt you to set a password or make any other configuration changes. Since this makes your MySQL installation insecure, we'll cover that below.

Step 2: configure MySQL

For fresh installations of MySQL, you must run the security script provided with the DBMS. This script changes some of the less secure default options for things like remote root logins and token users.

Salida
(Video) How to setup MySQL on Digital Ocean (or any Linux Server)
... Failed! Error: SET PASSWORD has no meaning for user 'root'@'localhost' because the authentication method used does not store any authentication data on the MySQL server. Use ALTER USER instead if you want to change the authentication parameters. New Password:

sudomysql

CHANGEUSER 'Root'@'host of facilities'IDENTIFIED WITH mysql_native_password BY'password';

salida

mysql -u raíz -p

CHANGEUSER 'Root'@'host of facilities'IDENTIFIED WITH auth_socket;

Run the security script withsudo:

sudomysql_secure_installation

This will take you through a series of prompts where you can make some changes to the security options of your MySQL installation. The first message asks if you want to configure the Validate Password plugin, which can be used to test the strength of new MySQL users' passwords before considering them valid.

When you configure the password validation plugin, any MySQL user you think is authenticated with a password must have a password that conforms to the policy you select. The strongest policy level, which you can select by typing2— requires passwords to be at least eight characters and contain a mix of uppercase, lowercase, digits, and special characters:

Salida
(Video) How To Install Apache, MySQL, PHP on Ubuntu Digital Ocean
Secure MySQL server implementation. Connecting to MySQL with a blank password. The VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. Checks password strength and allows users to set only those passwords that are strong enough. Do you want to configure the VALIDATE PASSWORD component? Press y|Y for yes, any other key for no:YThere are three levels of password validation policy: LOW length >= 8 MEDIUM length >= 8, numeric, case sensitive, and special characters STRONG length >= 8, numeric, case sensitive, special characters, and dictionary file Enter 0 = LOW, 1 = MEDIUM on and 2 = STRONG:2

Regardless of whether you configured the Validate Password plugin, the next prompt is to set a password for MySQLrootUser. Enter a strong password of your choice and then confirm it:

Salida
Set the password for root here. New password: Retype the new password:

Note that even if you have set a password for therootMySQL user, this user is not currently configured to authenticate with a password when connecting to the MySQL shell.

If you have used the Validate Password plugin, you will receive feedback on the strength of your new password. The script will then ask you if you want to continue with the password you just entered or enter a new one. Assuming you're happy with the strength of the password you just entered, enter itYto continue the script:

Salida
Estimated password strength: 100Do you want to continue with the password you provided? (Press y|Y for yes, any other key for no):Y

From there you can pressYand thenINPUTaccept the defaults for all subsequent questions. This will remove some anonymous users and the test database, disable remote root logins, and load these new rules so MySQL will immediately honor any changes you make.

Once the script is complete, your MySQL installation is secured. You can now proceed to create a dedicated database user using the MySQL client.

Step 3 – Create a dedicated MySQL user and grant privileges

During installation, MySQL creates arootUser account with which you can manage your database. This user has full access rights to the MySQL server, which means they have full control over all databases, tables, users, etc. Because of this, it is best not to use this account outside of administrative functions. This step describes how to use therootMySQL User to create a new user account and grant it permissions.

On Ubuntu systems running MySQL5.7(and later versions)rootThe MySQL user is configured to authenticate with theauth_socketPlugin by default and not with password. This plugin requires that the name of the operating system user invoking the MySQL client matches the name of the MySQL user specified in the command, so you must invoke itmysqlconsudopermissions to access therootMySQL-Benutzer:

sudomysql

mysql -u raíz -p

Once you have access to the MySQL prompt, you can create a new user with aCREATE USEROpinion. These follow this general syntax:

CREATEUSER 'Username'@'host'IDENTIFIED WITHplugin_authenticationVON'password';

AfterCREATE USER, provide a username. This is immediately followed by a@characters and then the hostname from which this user is connecting. If you only want to access this user locally from your Ubuntu server, you can specify thatfacility host. It is not always necessary to enclose both the username and the host in single quotes, but doing so can help prevent errors.

You have several options when it comes to choosing your user authentication plugin. Heauth_socketThe plugin mentioned above can be useful as it offers great security without requiring valid users to enter a password to access the database. But it also prevents remote connections, which can complicate things when external programs need to interact with MySQL.

Alternatively, you can also skip themCONplugin_authenticationPart of the syntax to authenticate the user with the default MySQL plugin,caching_sha2_password.The MySQL documentation recommends this plugin.for users who want to log in with a password due to strong security features.

Run the following command to create a user that authenticates withcaching_sha2_password. Be sure to changesammyto your preferred username andpasswordto a secure password of your choice:

CREATEUSER 'sammy'@'host of facilities'IDENTIFIED BY'password';

CREATEUSER 'sammy'@'host of facilities'IDENTIFIED WITH mysql_native_password BY'password';

CHANGEUSER 'sammy'@'host of facilities'IDENTIFIED WITH mysql_native_password BY'password';

After creating your new user, you can grant it the appropriate permissions. The general syntax for granting user rights is as follows:

GRANTPRIVILEGEANDdatabase.TischA'Username'@'host';

HePRIVILEGEThe value in this sample syntax defines what actions the user can perform on thedatabaseYTisch. You can grant multiple permissions to the same user in a single command by separating each one with a comma. You can also grant global permissions to a user by using asterisks (*) instead of the database and table names. In SQL, asterisks are special characters used to represent "all" databases or tables.

To illustrate, the following command grants a user global permissions toCREATE,CHANGE, YDROPSDatabases, tables and users as well as the possibilityINSERTION,UPDATE, YEXTINGUISHData from each table on the server. It also grants the user the ability to query data withCHOOSE, create foreign keys with theREFERENCESkeyword and performLAVARoperations with theRECHARGEPrivilege. However, you should only give users the permissions they need, so you can customize your own users' permissions as needed.

See the full list of permissions availablethe official MySQL documentation.

run thisGRANTstatement, replacesammywith the name of your own MySQL user to grant these permissions to your user:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD auf *.* A'sammy'@'host of facilities'WITH SCHOLARSHIP OPTION;

Please note that this statement also includesWITH SCHOLARSHIP OPTION. This allows your MySQL user to grant any privileges they have to other users on the system.

GRANT ALL PRIVILEGES TO *.* TO'sammy'@'host of facilities'WITH SCHOLARSHIP OPTION;

So it's a good exercise to doDOWNLOAD PRIVILEGESDomain. This frees up any memory that the server may have cached as a result of the above.CREATE USERYGRANTtestify:

DOWNLOAD PRIVILEGES;

You can then exit the MySQL client:

salida

To log in as your new MySQL user in the future, you would use a command like this:

mysql-usammy-PAGE

He-PAGE-flag causes the MySQL client to prompt you for the MySQL user's password to authenticate.

Finally, let's test the MySQL installation.

Step 4 - Test MySQL

Regardless of how you installed it, MySQL should start automatically. To test this, check your status.

systemctl-status mysql.service

You will see output similar to the following:

Salida
● mysql.service - MySQL Community Server Loaded: Loaded (/lib/systemd/system/mysql.service; enabled; factory default: enabled) Active: active (running) since Tuesday 2020-04-21 12:56 :48 UTC; 6 minutes ago Main PID: 10382 (mysqld) Status: "Server is operational" Tasks: 39 (limit: 1137) Memory: 370.0M CGroup: /system.slice/mysql.service └─10382 /usr/sbin/mysqld

If MySQL is not running, you can start it withsudo systemctl mysql starten.

For further verification, you can try connecting to the database with the databasemysqladminTool, which is a client that allows you to run administrative commands. For example, this command tells you to log in as a MySQL user with the namesammy(-tusammy), prompt for a password (-PAGE) and return the version. Be sure to changesammyto the name of your dedicated MySQL user and enter that user's password when prompted:

sudomysqladmin -p -usammyexecution

You should see output similar to the following:

Salida
mysqladmin Ver 8.0.19-0ubuntu5 for Linux on x86_64 ((Ubuntu))Copyright (c) 2000, 2020, 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. Server Version 8.0.19-0ubuntu5 Protocol Version 10 Connection localhost via UNIX socketUNIX socket /var/run/mysqld/mysqld.sock Uptime: 10 min 44 sec Threads: 2 Questions: 25 Slow Queries: 0 Opens: 149 Flush Tables: 3 Open Tables: 69 queries per second Average: 0.038

This means that MySQL is working.

Diploma

You now have a basic MySQL setup installed on your server. Here are some examples of next steps you can take:

• Setting up a LAMP stackoa LEMP stack
• Practice running queries with SQL
• Manage your MySQL installation with phpMyAdmin