How to install MySQL on Ubuntu 20.04
MySQL is the most popular open-source relational database management system. It is fast, easy to use, scalable, and an integral part of the popular LAMP and LEMP stacks.
This article explains how to install and protect MySQL in Ubuntu 20.04.
Make sure that you are logged on as a user with sudo privileges.
INSTALLING MYSQL IN UBUNTU
At the time of writing this article, the latest version of MySQL available in Ubuntu repositories was MySQL version 8.0. To install it, run the following commands:
sudo apt update
sudo apt install mysql-server
After installation is complete, the MySQL service will start automatically. To make sure that the MySQL server is running, type:
sudo systemctl status mysql
The output should show that the service is on and running:
mysql.service — MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Tue 2020-04-28 20:59:52 UTC; 10min ago
Main PID: 8617 (mysqld)
Status: "Server is operational"
.
SECURING MYSQL
The MySQL installation comes with a script named mysql_secure_installation, which makes it easy to improve the security of the database server.
Call the script without arguments:
You will be asked to configure the VALIDATE PASSWORD PLUGIN, which is used to check the reliability of MySQL users’ passwords and improve security:
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD COMPONENT can be used to test passwords.
and improve security. It checks the the password
and allows the users to set only those passwords which are
secure enough. Would you like to setup the VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No: y
There are three levels of password checking policy: low, medium, and strong. Click, Wesley you want to configure a password verification plugin or any other key to proceed to the next step:
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 2
The next invitation will ask you to set a password for the user root MySQL:
Please set the password for root here.
Re-enter new password:
If you installed the password verification plugin, the script will show you the strength of your new password. Enter the password validation plugin:
Estimated strength of the password: 50
Do you wish to continue with the password provided? (Press y|Y for Yes, any other key for No) : y
Next, you will be prompted to delete an anonymous user, restrict root access to the local computer, delete the test database, and reboot the privilege tables. You shall be asked to answer all questions.
LOG IN AS ROOT
To interact with the MySQL server from the command line, use the MySQL client utility, which is installed as a dependency on the MySQL server package.
On MySQL 8.0 the user root auth_socket is checked by default by the plugin.
The auth_socket plugin authenticates users who connect from localhost via Unix socket file. This means that you cannot authenticate as root by providing a password.
To login to the MySQL server as root, type:
You will be presented with the MySQL shell as shown below:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 8.0.19-0ubuntu5 (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.
affiliates. Other may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
If you want to log into your MySQL server as root using an external program such as phpMyAdmin, you have two options.
The first is to change the authentication method from auth_socket to mysql_native_password. You can do this by running the following command:
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'very_strong_password';FLUSH PRIVILEGES;
The second recommended option is to create a new dedicated administrative user with access to all databases:
GRANT ALL PRIVILEGES ON *.* TO 'administrator'@'localhost' IDENTIFIED BY 'very_strong_password ‘;
CONCLUSION
We showed you how to install MySQL on Ubuntu 20.04. Now that the database server is up and running, the next step is to learn how to manage MySQL user accounts and databases.
How do I start/stop mysql server?
I tried to find in some articles describing how to correctly start & stop mysql server.
I ran this command:
but I see this error
After I tried this command:
I entered my password, and again I see the same error.
And when I run this command:
MySQL server success started. Cool!
So, what’s wrong with the other commands? Why do they result in error?
11 Answers 11
Your first two commands weren’t run as root so that is expected behaviour. You need to be root to stop/start mysql.
should work. Indeed it does, for me:
I used restart rather than start, since it was already running, but the effect is the same. Are you sure you entered your password correctly? 🙂 Have you edited your sudo config at all which would stop this working?
The arguments are wrong. an init.d script only takes start or stop or restart — just one word telling it what to do. You cannot give it multiple arguments as you were trying to do.
Anyway, the short answer is the one you actually got to work, is the recommended way. service is replacing all the init.d scripts over time, so you should get into the habit of using service . The page you link to was written in 2008, so has to be taken with some salt 🙂
Как запустить mysql ubuntu
Last updated on: 2019-12-20
Authored by: Jered Heeschen
MySQL is an open-source relational database that is free and widely used. It is a good choice if you know that you need a database but don’t know much about all the available options.
This article describes a basic installation of a MySQL database server on the Ubuntu operating system. You might need to install other packages to let applications use MySQL, like extensions for PHP. Check your application documentation for details.
Install MySQL
Install the MySQL server by using the Ubuntu operating system package manager:
The installer installs MySQL and all dependencies.
If the secure installation utility does not launch automatically after the installation completes, enter the following command:
This utility prompts you to define the mysql root password and other security-related options, including removing remote access to the root user and setting the root password.
Allow remote access
If you have iptables enabled and want to connect to the MySQL database from another machine, you must open a port in your server’s firewall (the default port is 3306). You don’t need to do this if the application that uses MySQL is running on the same server.
Run the following command to allow remote access to the mysql server:
Start the MySQL service
After the installation is complete, you can start the database service by running the following command. If the service is already started, a message informs you that the service is already running:
Launch at reboot
To ensure that the database server launches after a reboot, run the following command:
Configure interfaces
MySQL, by default is no longer bound to ( listening on ) any remotely accessible interfaces. Edit the “bind-address” directive in /etc/mysql/mysql.conf.d/mysqld.cnf:
Restart the mysql service.
Start the mysql shell
There is more than one way to work with a MySQL server, but this article focuses on the most basic and compatible approach, the mysql shell.
At the command prompt, run the following command to launch the mysql shell and enter it as the root user:
When you’re prompted for a password, enter the one that you set at installation time, or if you haven’t set one, press Enter to submit no password.
The following mysql shell prompt should appear:
Set the root password
If you logged in by entering a blank password, or if you want to change the root password that you set, you can create or change the password.
For versions earlier than MySQL 5.7, enter the following command in the mysql shell, replace password with your new password:
For version MySQL 5.7 and later, enter the following command in the mysql shell, replacing password with your new password:
To make the change take effect, reload the stored user information with the following command:
Note: We’re using all-caps for SQL commands. If you type those commands in lowercase, they’ll work. By convention, the commands are written in all-caps to make them stand out from field names and other data that’s being manipulated.
If you need to reset the root password later, see Reset a MySQL root password.
View users
MySQL stores the user information in its own database. The name of the database is mysql. Inside that database the user information is in a table, a dataset, named user. If you want to see what users are set up in the MySQL user table, run the following command:
The following list describes the parts of that command:
- SELECT tells MySQL that you are asking for data.
- User, Host, authentication_string tells MySQL what fields you want it to look in. Fields are categories for the data in a table. In this case, you are looking for the username, the host associated with the username, and the encrypted password entry.
- FROM mysql.user " tells MySQL to get the data from the mysql database and the user table.
- A semicolon (;) ends the command.
Note: All SQL queries end in a semicolon. MySQL does not process a query until you type a semicolon.
User hosts
The following example is the output for the preceding query:
Users are associated with a host, specifically, the host from which they connect. The root user in this example is defined for localhost, for the IP address of localhost, and the hostname of the server. You usually need to set a user for only one host, the one from which you typically connect.
If you’re running your application on the same computer as the MySQL server, the host that it connects to by default is localhost. Any new users that you create must have localhost in their host field.
If your application connects remotely, the host entry that MySQL looks for is the IP address or DNS hostname of the remote computer (the one from which the client is coming).
Anonymous users
In the example output, one entry has a host value but no username or password. That’s an anonymous user. When a client connects with no username specified, it’s trying to connect as an anonymous user.
You usually don’t want any anonymous users, but some MySQL installations include one by default. If you see one, you should either delete the user (refer to the username with empty quotes, like ‘ ‘) or set a password for it.
Create a database
There is a difference between a database server and a database, even though those terms are often used interchangeably. MySQL is a database server, meaning it tracks databases and controls access to them. The database stores the data, and it is the database that applications are trying to access when they interact with MySQL.
Some applications create a database as part of their setup process, but others require you to create a database yourself and tell the application about it.
To create a database, log in to the mysql shell and run the following command, replacing demodb with the name of the database that you want to create:
After the database is created, you can verify its creation by running a query to list all databases. The following example shows the query and example output:
Add a database user
When applications connect to the database using the root user, they usually have more privileges than they need. You can add users that applications can use to connect to the new database. In the following example, a user named demouser is created.
To create a new user, run the following command in the mysql shell:
When you make changes to the user table in the mysql database, tell MySQL to read the changes by flushing the privileges, as follows:
Verify that the user was created by running a SELECT query again:
Grant database user permissions
Right after you create a new user, it has no privileges. The user can log in, but can’t be used to make any database changes.
Give the user full permissions for your new database by running the following command:
Flush the privileges to make the change official by running the following command:
To verify that those privileges are set, run the following command:
MySQL returns the commands needed to reproduce that user’s permissions if you were to rebuild the server. USAGE on \*.\* means the users gets no privileges on anything by default. That command is overridden by the second command, which is the grant you ran for the new database.
Summary
If you’re just creating a database and a user, you are done. The concepts covered here should give you a solid start from which to learn more.
Related articles
Share this information:
©2020 Rackspace US, Inc.
Except where otherwise noted, content on this site is licensed under a Creative Commons Attribution-NonCommercial-NoDerivs 3.0 Unported License
How to Install and Configure MySQL in Ubuntu 20.04 LTS
MySQL, which stands for My (co-founder Michael Widenius’ daughter name) Structured Query Language, is an open source relational database management system. This RDBMS is supported by Oracle and runs on almost all platforms such as Linux, UNIX and MS Windows. MYSQL is usually the first choice for online publishing and web-based applications. Facebook, YouTube and Twitter all use MySQL as their RDBMS. MySQL is part of the very popular LAMP stack: Linux, Apache, MySQL and Python/PHP/Perl.
- Install the latest version of MySQL available in the apt package repository
- Run the MySQL security script
- Configure the root user to access the MySQL shell
- Finally test that MySQL is running
We have run the commands and procedures mentioned in this article on an Ubuntu 20.04 LTS system
MySQL installation and security configuration
In this section, we describe how to install MySQL from the Ubuntu command line and configure security.
You can open the Ubuntu command line, the terminal, either from the application launcher search bar or by pressing Ctrl+Alt+T.
Step 1: Update repository index
To install the latest available version of a software from the Internet repositories, your local repository index must match it. Run the following command as sudo to update your local repository index:
Step 2: Install MySQL server with apt
Please run the following command as sudo to install MySQL from APT repositories.
Please note that only an authorized user on Ubuntu can add, remove and configure software.
The system may ask you for the password for sudo and also offer you a Y/n option to continue the installation. Type Y and then press Enter; MySQL will then be installed on your system. However, the process may take some time depending on your internet speed.
Step 3: Verify the installation (optional)
You can verify your MySQL installation and also check the version number by running the following command in your terminal:
Step 4: Make security configurations by running the provided security script
Whenever you install a new copy of MySQL, there are some default settings that you should change to increase the security of your MySQL installation. These include removing test users, test databases, and allowing remote login by a root user.
Run the following command as sudo to start the security script:
When you run this command, the first thing you will be prompted to do is set up the Validate Password plugin. This allows you to set a secure password for root, depending on the strength of the password you want to choose. Type Y to start the Validate Password plugin and you will get the following prompt:
Enter the number of your choice for the password strength and press Enter. The system will then ask you for the new password of root. Type the password and retype it at the following prompts.
The system will then display the strength of the password you provided and also ask you if you want to continue with the password.
Type Y for Yes and press Enter.
The system will now ask you a series of questions, one at a time, and you can set the security of your system depending on your answers to these questions.
Series of questions:
The first question will ask you if you want to remove the anonymous test users.
Press y and the Enter key.
The second question will ask you if you want to disallow root login from a remote system. This should normally be your choice, because for a secure system, root should only be allowed to connect from localhost.
We, therefore, recommend that you enter y.
The third question asks if you want to remove the default MySQL database named “test” from your system and also delete access to it.
Type y to remove this test database.
In order for all of your changes configured above to take effect, the system must reload the permissions tables. Enter y and all your security changes will be applied.
Configuring root to use the MySQL shell
When running the security script, you specified a password for root. However, this user is not allowed to connect to MySQL Shell with the same password. You can configure root to use MySQL Shell by changing its authentication method from the default “auth_socket” to “mysql_native_password”.
Here’s how to do it:
Step 1: Start MySQL Shell
First, start the MySQL shell by running the following command as sudo:
This will start the MySQL shell so you can work on the MySQL prompt.
Step 2: Check authentication method for MySQL users
At the MySQL prompt, enter the following command, which will allow you to check the authentication method/plugin that all of your MySQL accounts are currently using:
In the above output, you can see that root uses the auth-socket plugin for authentication by default.
Step 3: Change the authentication method for root
Our goal is to have the root user authenticate to MySQL with a password. To do this, run the following command, which will have the root user identified by a mysql_native_password. Please remember that this password must be very strong.
From now on, your root user will no longer have the password you specified when running the provided security script, but the strong password you specified in the above command.
Step 4: Reload grant tables
Now it’s time to tell the server to use the new permission settings from now on. Run the following command at the MySQL command prompt to reload the grant tables and register your changes:
Step 5: Recheck authentication method for MySQL users
Now, if you recheck the authentication method for your MySQL user accounts using the following command, you will see that your root user is now using the mysql_native_password plugin for authentication:
Now that your root user is set up to connect to the MySQL shell with a secure password, you can exit the shell with the exit command as follows:
Testing if MySQL is running
To test whether MySQL is running on your system or not, you can use one of the following methods:
Method 1: Check the status of mysql.service
After you install MySQL on your system, mysql.service should most likely be running automatically. The output of the following command should verify the active status of the service:
If for some reason the service is not running, you can use the following command as sudo to start the service:
You can use the following command to stop the service again if needed:
Method 2: By connecting to MySQL Admin as root and running any administrative command
MySQL Admin is a client that allows you to perform administrative operations on MySQL. As an example, let’s run one of the administrative commands over it to check if the system is running properly and our root is configured to do so.
This command is to connect to MySQL as root, get the root password, and then return the MySQL admin version number.
If the command does what it is supposed to do and produces output similar to the above, you can be sure that your MySQL is up and running.
The process of installing and setting up MySQL on Ubuntu may seem cumbersome to some, especially when using the command line. However, if you carefully follow the above steps one by one, you will have no problems having a reliable, secure and stable installation of MySQL running on your Ubuntu.
Karim Buzdar
About the Author: Karim Buzdar holds a degree in telecommunication engineering and holds several sysadmin certifications. As an IT engineer and technical author, he writes for various web sites. You can reach Karim on LinkedIn