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

Introduction

mysqlis an open source database management system, commonly installed as part of the popularLIGHTBULB(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 cover how to install MySQL version 8.0 on an Ubuntu 22.04 server. When complete, you'll have a working relational database that you can use to build your next website or app.

previous requirements

To follow this tutorial, you will need:

• An Ubuntu 22.04 server with a non-root administrative user and a firewall configured with UFW. To set this up, follow ourinitial server setup guide for ubuntu 22.04.

Paso 1: Instalar MySQL

On Ubuntu 22.04 you can install MySQL using the APT package repository. At the time of writing this article, the version of MySQL available in the default Ubuntu repository is version 8.0.28.

To install it, update the package index on your server if you haven't done so recently:

sudo fitupdate

Then install theservidor mysqlpackage:

sudo fit installservidor mysql

Make sure the server is running using thestart systemctldomain:

sudosystemctl start mysql.service

These commands will install and start MySQL, but will not prompt you to set a password or make any other configuration changes. Since this leaves your MySQL installation insecure, we'll cover it next.

Step 2: Configure MySQL

For new MySQL installations, you'll want to run the security script included with the database management system. This script changes some of the less secure default options for things like disallowing remote accessfuenteExample of user logins and deletion.

Salida
... Mistake! Error: SET PASSWORD has no meaning for user 'root'@'localhost' since the authentication method used does not store authentication data on the MySQL server. Consider using ALTER USER if you want to change the authentication parameters. New Password:

sudomysql

TO ALTEROF THE USER 'fuente'@'localhost'IDENTIFIED WITH mysql_native_password BY'password';

salida

Run the security script withsudo:

sudomysql_secure_installation

This will take you to a series of prompts where you can make some changes to the security options of your MySQL installation. The first prompt will ask 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.

If you choose to configure the password validation plugin, any MySQL user you create and authenticate with a password must have a password that complies with the selected policy:

Salida
Secure MySQL server implementation. Connecting to MySQL using a blank password. The VALIDATE PASSWORD COMPONENT can be used to test passwords and improve security. Verifies 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, upper and lower case letters, and special characters STRONG Length >= 8, numeric, upper and lower case letters, special characters, and dictionary file Enter 0 = LOW, 1 = MEDIUM and 2 = STRONG:2

Regardless of whether you choose to configure the password validation plugin, the next prompt will be to set a password for MySQLfuenteEnter and confirm a secure password of your choice:

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

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

If you used the password validation 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 if you want to enter a new one. Assuming you're happy with the strength of the password you just entered, typeYto continue the script:

Salida
(Video) How to install mysql and phpmyadmin on ubuntu 22.04
Estimated password strength: 100Do you want to continue with the provided password? (Press y|Y for Yes, any other key for No):Y

From there you can pressYand thenTO TYPEto accept 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.

mysql -u raíz -p

TO ALTEROF THE USER 'fuente'@'localhost'IDENTIFIED WITH auth_socket;

Once the script is complete, your MySQL installation is safe. You can now create a dedicated database user with the MySQL client.

Step 3 – Create a dedicated MySQL user and grant privileges

After installation, MySQL creates afuenteuser account that you can use to manage your database. This user has full privileges on the MySQL server, which means they have full control over all databases, tables, users, etc. Therefore, it is best to avoid using this account outside of administrative functions. This step describes how to use thefuenteMySQL User to create a new user account and grant it privileges.

On Ubuntu systems running MySQL 5.7 (and later versions), thefuenteThe MySQL user is configured to authenticate using theauth_socketdefault plugin instead of a password. This plugin requires the username of the operating system calling the MySQL client to match the MySQL username specified in the command, so it must callmysqlcomsudoaccess privileges tofuenteMySQL user:

sudomysql

mysql -u raíz -p

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

CREATEOF THE USER 'Username'@'host'IDENTIFIED WITHplugin_authenticationBY'password';

AfterCREATE USER, specifies a username. This is immediately followed by a@sign and then the hostname from which this user will connect. If you plan to access this user only locally from your Ubuntu server, you can specifylocal host. It is not always necessary to enclose the username and host in single quotes, but it can help prevent errors.

You have several options when it comes to choosing your user authentication plugin. EITHERauth_socketThe aforementioned plugin can be convenient as it provides 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 omit theCOMplugin_authenticationpart of the syntax entirely for the user to authenticate with the standard MySQL plugin,cache_sha2_password.MySQL documentation recommends this pluginfor users who want to log in with a password due to its strong security features.

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

CREATEOF THE USER 'sammy'@'localhost'IDENTIFIED BY'password';

CREATEOF THE USER 'sammy'@'localhost'IDENTIFIED WITH mysql_native_password BY'password';

TO ALTEROF THE USER 'sammy'@'localhost'IDENTIFIED WITH mysql_native_password BY'password';

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

GRANTPRIVILEGEABOUTdatabase.low hillFOR'Username'@'host';

OPRIVILEGEThe value in this example syntax defines what actions the user can perform on thedatabasemilow hill. You can grant multiple privileges to the same user in a single command, separating them with a comma. You can also grant user privileges globally by entering 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 privileges toCREATE,TO ALTER, miSHOOT DOWNdatabases, tables, and users, as well as the power ofINSERT,UPDATE, miDELETEdata from any table on the server. It also grants the user the ability to query data withSELECT, create foreign keys with theREFERENCESkeyword and executeBLUSHoperations with theRECHARGEprivilege. However, you should only grant users the permissions they need, so feel free to adjust your own user privileges as needed.

You can find the full list of privileges available atthe official MySQL documentation.

spin thisGRANTstatement, replacingsammywith your own MySQL username, to grant your user these privileges:

GRANT CREATE, ALTER, DROP, INSERT, UPDATE, INDEX, DELETE, SELECT, REFERENCES, RELOAD on *.* FOR'sammy'@'localhost'WITH CONCESSION OPTION;

Please note that this statement also includesWITH CONCESSION OPTION. This will allow your MySQL user to grant whatever permissions they have to other users on the system.

GRANT ALL PRIVILEGES ON *.* TO'sammy'@'localhost'WITH CONCESSION OPTION;

After that, it is good practice to run theRELEASE PRIVILEGESdomain. This will free any memory that the server may have cached as a result of the above procedure.CREATE USERmiGRANTstatements:

RELEASE PRIVILEGES;

You can then exit the MySQL client:

salida

In the future, to log in as your new MySQL user, you would use a command like the following:

mysql-usammy-page

O-pagewill cause the MySQL client to request your MySQL user password to authenticate.

Finally, let's test the MySQL installation.

Step 4: Test MySQL

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

systemctl status mysql.service

The output will be similar to the following:

Salida
● mysql.service - MySQL Community Server Loaded: Loaded (/lib/systemd/system/mysql.service; enabled; Vendor Preconfiguration: enabled) Active: active (running) since Monday 11/04/2022 16:04: 39 UTC; 2 h 36 min ago Process: 2593 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exit, status=0/SUCCESS) Main PID: 2601 (mysqld) Status: "Server is operational" Tasks : 38 ( limit: 1119) Memory: 354.3M CPU: 19.944s CGroup: /system.slice/mysql.service └─2601 /usr/sbin/mysqld

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

For further verification, you can try connecting to the database using themysqladmintool, which is a client that allows you to run administrative commands. For example, this command tells you to log in as a MySQL user namedsammy(- yousammy), 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 -usammyversion

Below is an example of the output:

Salida
mysqladmin Ver 8.0.28-0ubuntu4 for Linux on x86_64 ((Ubuntu))Copyright (c) 2000, 2022, Oracle and/or its affiliates. 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.28-0ubuntu4Protocol version 10Localhost connection via UNIX socketUNIX socket /var/run/mysqld/mysqld.sockActive time: 2 hours 31 min 57 secThreads: 2 Questions: 25 Queries slow: 0 Openings: 160 Tables Level: 3 Open Tables: 79 Queries Per Second Average: 0.000

This means that MySQL is working.

Conclusion

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