How to install MySQL on Ubuntu – juanrasantana.com

This tutorial is meant to guide you on the installation of MySQL server on Ubuntu. After the completion of this tutorial you will have successfully installed a MySQL server on Ubuntu (also Ubuntu server) with a database created and a new user with access rights from outside.

Install MySQL server

First thing to do is to use the Ubuntu repository to install the latest available version of MySQL for your Ubuntu version. Therefore, please login on your instance and introduce the following commands to install it from Ubuntu Package Manager.

sudo apt-get update
sudo apt-get install mysql-server

It will also install any dependencies required by MySQL Server to work. Once it is installed, it can either ask you to introduce the root password or not, depending on the MySQL Server installed. If it did not ask for the root password, you must run the following command to set the root password.

sudo mysql_secure_installation

This will ask you to choose whether to activate the VALIDATE PASSWORD PLUGIN or not. It is just a validation plugin to ensure that chosen passwords are strong enough. After this, you will be asked to introduce a new password for the MySQL root user. It will also ask you to remove the anonymous user and delete the test database. I recommend you to confirm with Y (‘YES’) all of these options.

Update root password in MySQL Server

In case you are running a MySQL 5.7 version or later (e.g. latest versions for Ubuntu 18.04), the MySQL Server instance uses by default the auth_socket plugin. It means that MySQL root user connects through localhost using the Unix socket file. Thus, you will need to login with your root user from our system (sudo). Root login with the auth_socket is as simple as running the MySQL access command using root:

sudo mysql -u root p

It will ask you for the password from your Ubuntu root user and that’s it. Once you are logged in the MySQL server, use the following command to update the root password:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root_new_password';

Please do not forget that once you update the password, the auth_socket plugin will be disabled. Instead, the mysql_native_password will be the authentication method, and you will need to access the server using the password you defined in the step above.

Create a Database, create an user and grant remote access rights

The first step after completing the MySQL installation would be the creation of the first database. To do so, connect to the MySQL Server with root and use the command below.

mysql -u root -p
mysql> CREATE DATABASE newdb;

After the database creation, the following commands will help you to create a new user.

mysql> CREATE USER 'newuser'@'localhost' IDENTIFIED BY 'user_password';

If you want to allow this user to connect from a different host, just replace ‘localhost’ with the remote IP machine. If you want to allow any remote IP, use the ‘%’ wildcard.

mysql> CREATE USER 'newuser'@'%' IDENTIFIED BY 'user_password';

However, MySQL Server default configuration does not allow remote connections. Therefore, you need to edit the “/etc/mysql/mysql.conf.d/mysqld.cnf” file, either commenting (to allow any remote host) or modifying the line starting with “bind-address” (do not forget to restart the MySQL service after modifying it).

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
# bind-address           = 127.0.0.1

sudo service mysql restart

Finally, you will need to provide access rights to the newly created user to the database. The following command will allow to the new user to access the new database from any host (if the user you created is limited to localhost, you must change it in the command below).

mysql> GRANT ALL PRIVILEGES ON newdb.* TO 'newuser'@'%';

Grant remote access to root user

Although I DO NOT RECOMMEND you to allow remote root access to your MySQL Server, it is possible to allow it with the correct access rights.

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

This statement will grant remote access to root user to any database and use any command.