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.
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 the
- sudo appropriate installServidor MySQL
Use it to make sure the server is running
ctl 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.
warning: As of July 2022, an error occurs when running the
mysql_secure_installationScript without further configuration. The reason is that this script tries to set a password for the installation.rootMySQL account, but by default on Ubuntu installations this account is not configured to connect with a password.
Prior to July 2022, this script would fail silently after trying to run therootEnter your account password and continue with the remaining prompts. However, as of this writing, after entering and confirming a password, the script returns the following error:
Salida... 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:(Video) How to setup MySQL on Digital Ocean (or any Linux Server)
This puts the script in a recursive loop that you can only get out of by closing your terminal window.
mysql_secure_installationThe script performs other useful actions for the security of your MySQL installation. However, it is still recommended that you run it before you start using MySQL to manage your data. However, to avoid getting into this recursive loop, you must first customize, like yours.rootAuthenticated MySQL user.
First, open the MySQL command prompt:
Then run the following
CHANGE USERCommand to change therootUser authentication method to one that uses a password. The following example changes the authentication method to
- CHANGEUSER 'Root'@'host of facilities'IDENTIFIED WITH mysql_native_password BY'password';
After making this change, exit the MySQL prompt:
You can then run the
Once the security script completes, you can reopen MySQL and therootreset user authentication method to default,
auth_socket. To authenticate asrootMySQL user using a password, run this command:
- mysql -u raíz -p
Then go back to the default authentication method with this command:
- CHANGEUSER 'Root'@'host of facilities'IDENTIFIED WITH auth_socket;
This means you can reconnect to MySQL as your ownrootuser with the
Run the security script with
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 typing
2— requires passwords to be at least eight characters and contain a mix of uppercase, lowercase, digits, and special characters:
SalidaSecure 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(Video) How To Install Apache, MySQL, PHP on Ubuntu Digital Ocean
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:
SalidaSet 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 it
Yto continue the script:
SalidaEstimated 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 press
INPUTaccept 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 MySQL
5.7(and later versions)rootThe MySQL user is configured to authenticate with the
auth_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 it
sudopermissions to access therootMySQL-Benutzer:
Use:If you installed MySQL using another tutorial and enabled password authentication forroot, you need to use another command to access the MySQL shell. The following runs your MySQL client with regular user rights and only gains admin rights within the database when authenticating:
- mysql -u raíz -p
Once you have access to the MySQL prompt, you can create a new user with a
CREATE USEROpinion. These follow this general syntax:
- CREATEUSER 'Username'@'host'IDENTIFIED WITHplugin_authenticationVON'password';
CREATE 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 that
facility 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. He
auth_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 them
CONplugin_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 with
caching_sha2_password. Be sure to change
sammyto your preferred username and
passwordto a secure password of your choice:
- CREATEUSER 'sammy'@'host of facilities'IDENTIFIED BY'password';
use: There is a known issue with some versions of PHP that causes problems with
caching_sha2_password. If you plan to use this database with a PHP application, eg phpMyAdmin, you may want to create a user to authenticate with the older, but secure one.
- CREATEUSER 'sammy'@'host of facilities'IDENTIFIED WITH mysql_native_password BY'password';
If you're not sure, you can always create a user to authenticate with
CHANGElater with this command:
- 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:
PRIVILEGEThe value in this sample syntax defines what actions the user can perform on the
Tisch. 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 to
DROPSDatabases, tables and users as well as the possibility
EXTINGUISHData from each table on the server. It also grants the user the ability to query data with
CHOOSE, create foreign keys with the
REFERENCESkeyword and perform
LAVARoperations with the
RECHARGEPrivilege. 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.
sammywith 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 includes
WITH SCHOLARSHIP OPTION. This allows your MySQL user to grant any privileges they have to other users on the system.
warning: Some users may want their MySQL user to have the
ALL PRIVILEGES-Privilege that grants them broad superuser privileges similar torootUser rights, like this:
- GRANT ALL PRIVILEGES TO *.* TO'sammy'@'host of facilities'WITH SCHOLARSHIP OPTION;
Privileges so wideshould not be given lightly, since anyone with access to that MySQL user has full control over every database on the server.
So it's a good exercise to do
DOWNLOAD PRIVILEGESDomain. This frees up any memory that the server may have cached as a result of the above.
- DOWNLOAD PRIVILEGES;
You can then exit the MySQL client:
To log in as your new MySQL user in the future, you would use a command like this:
-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 with
sudo systemctl mysql starten.
For further verification, you can try connecting to the database with the database
mysqladminTool, 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 change
sammyto 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:
Salidamysqladmin 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.
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
How do I install MySQL on Ubuntu 20.04 terminal? ›
- 1 - Download and install MySQL server and client.
- 2 - Secure your MySQL root user account.
- 3 – Check the MySQL version.
- 4 - Add MySQL user and set privileges.
- 5 - Test MySQL 5.7.
- 6 – Log in to your MySQL server.
- Step 1 – Update your system. ...
- Step 2 – Searching for MySQL 8 server packages on Ubuntu 22.04 LTS. ...
- Step 3 – Installing MySQL 8 server package. ...
- Step 4 – Securing MySQL 8 server. ...
- Step 5 – Enabling the MySQL server at boot time. ...
- Step 6 – Starting/Stopping/Restarting the MySQL server.
- Re-run Installer From Start Menu.
- Obtain MySQL files.
- Register MySQL as a Service.
- Start MySQL and Test.
In this tutorial we will use the driver "MySQL Connector". We recommend that you use PIP to install "MySQL Connector". PIP is most likely already installed in your Python environment.How to install MySQL in Linux step by step? ›
- Update package information for the MySQL APT repository: sudo apt-get update.
- Update the MySQL APT repository configuration package with the following command: sudo apt-get install mysql-apt-config. ...
- Install MySQL Shell with this command: sudo apt-get install mysql-shell.