DEV Community

Cover image for Install MYSQL on Ubuntu server 18.04
ilhamsabir
ilhamsabir

Posted on

Install MYSQL on Ubuntu server 18.04

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
Enter fullscreen mode Exit fullscreen mode

Then install the default package:

sudo apt install mysql-server
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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
. . .
Enter fullscreen mode Exit fullscreen mode

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
. . .
Enter fullscreen mode Exit fullscreen mode

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

sudo systemctl restart mysql
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

Create user for remotely server:

CREATE USER 'user_name'@'%' IDENTIFIED BY 'password';
Enter fullscreen mode Exit fullscreen mode
GRANT ALL PRIVILEGES ON *.* TO 'user_name'@'%' WITH GRANT OPTION;
Enter fullscreen mode Exit fullscreen mode
FLUSH PRIVILEGES;
Enter fullscreen mode Exit fullscreen mode
exit
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode
sudo ufw allow 3306
Enter fullscreen mode Exit fullscreen mode

And now access your mysql server

mysql -u user -h database_server_ip -p
Enter fullscreen mode Exit fullscreen mode

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

Discussion (5)

Collapse
8bitsouvik profile image
Souvik Mandal

Hii, can you tell me if there's any free remote SQL server for free (no expiration like mongoDB) for development purpose?

Collapse
cess11 profile image
PNS11

MySQL from Oracle (subject of the article), MariaDB and Percona are all free.

If you're learning MySQL for professional use I'd recommend setting up a small Percona-based Galera-cluster with ProxySQL in front.

Collapse
8bitsouvik profile image
Souvik Mandal

Thanks! This will help me a lot now!

Collapse
ilhamsabir profile image
ilhamsabir Author • Edited on

You may try MySQL Workbench or HeidiSQL

Collapse
8bitsouvik profile image
Souvik Mandal

Yeah lemme see...