How to install MySql on Ubuntu 18 server

MySql 5.7 is an open source free RDBMS for small and midsize data. This support connectivity with many different programming languages and platforms via drivers. Om Linux system is part of  LAMP(Linux, Apache, MySQL, PHP/Python/Perl) stack.

In this post, i  am writing methods to install MySQL 5.7 on Ubuntu 18 via the apt repository.

Simply you can install MySql using these three commands

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

Installing MySql

To install MySQL on Ubuntu 18, just update your apt repository first using

$ sudo apt-get update

After this On Ubuntu 18, only the latest version of MySQL is included in the APT package repository by default. At the time of writing, that’s MySQL 5.7. Now another version is not maintained by communities. You can try MariaDB instead of MySQL.

$ sudo apt-get install mysql-server

Above command will ask you sudo user password and some inputs and install MySQL 5.7 on your Ubuntu system.

Configuring MySql

On a fresh installation of MySQL, you need to initialize data directories and security settings of MySQL. But now installation comes with a script which automatically does all configurations for you.

$ sudo mysql_secure_installation

Using the above command it will ask to setup root user password and other basic security settings. It will ask several questions and you need to select Y or other options as given below.

$ sheel@linux-18:~$ sudo mysql_secure_installation

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?

Press y|Y for Yes, any other key for No: Y

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: 0
Please set the password for root here.

New password:

Re-enter new password:

Estimated strength of the password: 100 
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : Y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.

Remove anonymous users? (Press y|Y for Yes, any other key for No) :

... skipping.


Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.

Disallow root login remotely? (Press y|Y for Yes, any other key for No) :

... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.


Remove test database and access to it? (Press y|Y for Yes, any other key for No) :

... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.

Reload privilege tables now? (Press y|Y for Yes, any other key for No) : Y
Success.

All done!

To initialize data directory on Ubuntu you can use a scriptmysqld --initialize to initialize data directories.

Verify and Test MySql Installation

Tverify MqSql installation on Ubuntu you can check MySQL service status.

$ sudo service mysql status

If MySQL service is running correctly then it should display output like below

● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
Active: active (running) since Sun 2019-02-24 01:50:52 UTC; 29min ago
Main PID: 16921 (mysqld)
Tasks: 28 (limit: 1111)
CGroup: /system.slice/mysql.service
└─16921 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid

After this, you can test MySQL connectivity

sudo mysql -u root -p
It will ask you root password and open you sql terminal.

Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.7.25-0ubuntu0.18.04.2 (Ubuntu)

Copyright (c) 2000, 2019, 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.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

Enable MySql Remote Connection on Ubuntu 18

For MySQL version 5.7 and above

uncomment in /etc/mysql/mysql.conf.d/mysqld.cnf and assigned to your computers IP address and not loopback

#Replace xxx with your IP Address 
bind-address = xxx.xxx.xxx.xxx

Or add a bind-address = 0.0.0.0 if you don’t want to specify the IP

Then stop and restart MySQL with the new entrymysqld.cnf.

$ sudo service mysql restart

Once running, go to the terminal and enter the following command.

$ netstat -nlt | grep 3306

sample result will like below :

tcp 0 0 xxx.xxx.xxx.xxx:3306 0.0.0.0:* LISTEN

Now create a user in MySQL with privileges to connect from a remote client

$ sudo mysql -u root -p
mysql> CREATE USER 'myuser'@'%' IDENTIFIED BY 'Password$4';
mysql> GRANT ALL ON *.* TO 'myuser'@'%' IDENTIFIED BY 'Password$4' WITH GRANT OPTION;
mysql> FLUSH PRIVILEGES;
mysql> exit

Open MySql port 3306 on Ubuntu 18

Enable ufw and open ports on ufw

$ sudo enable ufw
$ sudo ufw allow 22
$ sudo ufw allow 3306

Now test connection from another remote client.

$ sudo mysql -u myuser -h <remote host ip> -p

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.