Install MYSQL on Ubuntu server 18.04

MySQL is an open-source database management system, commonly installed as part of the popular LAMP (Linux, Apache, MySQL, PHP/Python/Perl) stack. It uses a relational database and SQL (Structured Query Language) to manage its data.

The short version of the installation is simple: update your package index, install the mysql-server package, and then run the included security script.

Prerequisites

To follow this tutorial you will need:
- One Ubuntu 18.04 server set up by following this initial server setup guide, including a non-root user with sudo privileges and a firewall.

Installing MySQL

To install it, update the package index on your server with apt:

sudo apt update

Then install the default package:

sudo apt install mysql-server

This will install MySQL, but will not prompt you to set a password or make any other configuration changes. Because this leaves your installation of MySQL insecure, we will address this next.

Configuring MySQL

For fresh installations, you’ll want to run the included security script. This changes some of the less secure default options for things like remote root logins and sample users. On older versions of MySQL, you needed to initialize the data directory manually as well, but this is done automatically now.

Run the security script:

sudo mysql_secure_installation

This will take you through a series of prompts where you can make some changes to your MySQL installation’s security options. The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the strength of your MySQL password. Regardless of your choice, the next prompt will be to set a password for the MySQL root user. Enter and then confirm a secure password of your choice.

Note that even though you’ve set a password for the root MySQL user, this user is not configured to authenticate with a password when connecting to the MySQL shell. If you’d like, you can adjust this setting by following Step 3.

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

systemctl status mysql.service

you’ll see output similar to the following:

● mysql.service - MySQL Community Server
   Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
   Active: active (running) since Wed 2018-04-23 21:21:25 UTC; 30min ago
 Main PID: 3754 (mysqld)
    Tasks: 28
   Memory: 142.3M
      CPU: 1.994s
   CGroup: /system.slice/mysql.service
           └─3754 /usr/sbin/mysqld

How To Allow Remote Access to MySQL

One of the more common problems that users run into when trying to set up a remote MySQL database is that their MySQL instance is only configured to listen for local connections. This is MySQL’s default setting, but it won’t work for a remote database setup since MySQL must be able to listen for an external IP address where the server can be reached. To enable this, open up your mysqld.cnf file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

Navigate to the line that begins with the bind-address directive. It will look like this:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# 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
. . .

By default, this value is set to 127.0.0.1, meaning that the server will only look for local connections. You will need to change this directive to reference an external IP address. For the purposes of troubleshooting, you could set this directive to a wildcard IP address, either *, ::, or 0.0.0.0:

. . .
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address            = 0.0.0.0
. . .

Then restart the MySQL service to put the changes you made to mysqld.cnf into effect:

sudo systemctl restart mysql

If you have an existing MySQL user account which you plan to use to connect to the database from your remote host, you’ll need to reconfigure that account to connect from the remote server instead of localhost. To do so, open up the MySQL client as your root MySQL user or with another privileged user account:

sudo mysql

Create user for remotely server:

CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';

GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%' WITH GRANT OPTION;

FLUSH PRIVILEGES;

exit

Now user for accessing remotely db server is on.

Allow UFW

Lastly, assuming you’ve configured a firewall on your database server, you will also need to open port 3306 — MySQL’s default port — to allow traffic to MySQL.
If you only plan to access the database server from one specific machine, you can grant that machine exclusive permission to connect to the database remotely with the following command. Make sure to replace remote_IP_address with the actual IP address of the machine you plan to connect with:

sudo ufw allow from remote_IP_address to any port 3306

sudo ufw allow 3306

And now access your mysql server

mysql -u user -h database_server_ip -p

Or use apps like HeidiSQL, SequelPro, Navicat , Workbeanch , etc.