Official Power Up Hosting Blog

Everything about Linux, Windows, and hosting ;)

Selvakumar
Author

I am an Online Marketer and technology lover. I like to learn new things and share that with people.

Share


Our Newsletter


Lorem ipsum dolor sit amet, consectetur adipisicing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.

Tags


Twitter


Official Power Up Hosting Blog

How to install and Secure MySQL on Ubuntu 14.04 and 16.04

SelvakumarSelvakumar

Do you have the problem in installing MySQL on your Ubuntu server or Don't know how to do it?

Of course, the difficulty comes when you use the command interface with a lot of commands.

Don't worry about that.

Here is the article which shows you how to install MySQL in Ubuntu using the terminal. This article gives you clear steps involved in installing MySQL.

install mysql ubuntu

What is MySQL?

MySQL is the open source relational database management system. It uses the structured query language to handle the database.

Using MySQL you can create and manage the large database. World's many large sites are running using the MySQL.

MySQL is used by Google, Facebook, Twitter, Paypal, Amazon, Ebay and more large companies.

See, How powerful the MySQL is.

Since it is open source and can be used by anyone, you can also have that in your system.

I am going to show you the simple basic steps involved in the installation of MySQL.

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

First, you have to update the package index and then you have to install the MySQL server.

Then run the security scripts to change the sample users and remote root logins.

After that, run the database initialization scripts to initiate the data directory.

Here in this tutorial, we are going to see, how to install MySQL 5.5, 5.6 and 5.7 on Ubuntu 14.04 server. Also, we will see how to install MySQL 5.7 on Ubuntu 16.04 server.

First, Let us start with Ubuntu 14.04.

How to install MySQL on Ubuntu 14.04 using terminal

Prerequisites:

You need the following to install MySQL.

  • Ubuntu 14.04 Droplet with sudo non-root user.

Installing the MySQL

There are two ways available to install MySQL. If you are looking to install MySQL 5.5 and 5.6, you can directly install them from the APT package.

But:

If you decided to have the MySQL version 5.7, you have to download the package and install it manually.

Let us see how to install MySQL 5.5 and 5.6.

As mentioned above, just update the package index by executing the command.

$ sudo apt-get update

Now, let us install MySQL 5.5. To do that use the install mysql-server-5.5 command.

$ sudo apt-get install mysql-server-5.5

The command is same for the installation process of MySQL 5.6. But you will replace the mysql-server-5.5 with install mysql-server-5.6 Ubuntu command.

$ sudo apt-get install mysql-server-5.6

While executing the installation command, you will be asked to create a root password.

Make sure to make the password strong and also make sure to remember it.

Because you will need the password later.

How to install MySQL 5.7 in Ubuntu 14.04

As you don't have the MySQL 5.7 by default in the APT Package repository, you have to download the new APT package repository from MySQL APT repository page.

You will be asked to either signup or login. But just skip and see below. There will be line

"No Thanks, just start my download."

Just, right click on the download button and copy the link using copy link menu.

Now:

let us download the .db package from the MySQL Repository page.

$ wget http://dev.mysql.com/get/mysql-apt-config_0.6.0-1_all.deb

Then, install the package using dbkg.

$ sudo dpkg -i mysql-apt-config_0.6.0-1_all.deb

Now the prompt will ask you select the MySQL Product to install. The highlighted MySQL server option should show mysql-5.7.

If not, press enter and scroll down to mysql-5.7 and press enter again.

Now the MySQL server option shows mysql-5.7.

Again scroll down and go to the menu "Apply" and press enter.

Now, it is the time to update the package.

$ sudo apt-get update

Then install the MySQL server package using sudo apt-get install mysql-server.

$ sudo apt-get install mysql-server

Now the prompt will ask you to create a root password. Make sure to create the strong password and remember it.

How to Configure MySQL

Now, you have to run the security script which is included in the package.

It will change the options and sample users to secure MySQL.

$ sudo mysql_secure_installation

Now:

The prompt will ask you to enter the root password you created at step 1.

Enter the password and you will be asked a question regarding changes.

Press enter for all and keep the default settings except one question where the prompt will ask you to change the root password.

We have set the root password initially. So we don't need to change that now.

Then, we have to initialize the MySQL directory. MySQL Directory stores the data for MySQL.

The process differs based on the MySQL version you use. First, you have to find which version of MySQL you are using.

Here is the command to find the version of MySQL.

$ mysql --version

The output will give you the version of MySQL

Output
mysql  Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using  EditLine wrapper

If your version of MySQL is earlier than 5.7.6, then you should initialize the Data directory by running the below command.

$ sudo mysql_install_db.

Here the mysql_install-db initialize the database.

Note: In MySQL 5.6 you will have the chance to get the following error ".

If you get the error, you have to move the /usr/share/my.cnf to /usr/share/mysql/my-default.cnf.

Execute the below command to move the configuration file.

$ sudo cp /etc/mysql/my.cnf /usr/share/mysql/my-default.cnf
$ sudo mysql_install_db

It happens due to some error in the APT package.( Of course, No product is perfect).

Look:

mysql_install_db is used for earlier versions of MySQL version 5.7.6. If you are using the 5.6.7 or later version, then you have to use the mysqld --initialize.

But:

If you installed the MySQL version 5.7 from the Debian distribution, the data directory will be initialized automatically.

You don't need to initialize it. However, if you run the Initialize command, you will get the following error.

Output:

2016-03-07T20:11:15.998193Z 0 [ERROR] --initialize specified but the data directory has files in it. Aborting.

Testing the MySQL

You might have installed MySQL either of the ways as mentioned above. But the MySQL should run automatically.

Check whether MySQL is running or not by executing the below command.

$ service mysql status

You will see the output like this

**Output**

mysql start/running, process 2699

How to start MySQL on Ubuntu

If your MySQL is not running, you can start that by sudo service mysql start.

$ sudo service mysql start

You can do additional checks with your MySQL by connecting it to the database using the mysqladmin tool.

It is a client and you can run the administrative using it.

Here is the command, which connects to the MySQL as root(-u root) and asks for the password(-p).

It returns the version of MySQL.

$ mysqladmin -p -u root version

Now you will get an output which should be similar to the below one.

Output
mysqladmin  Ver 8.42 Distrib 5.5.47, for debian-linux-
gnu on x86_64
Copyright (c) 2000, 2015, 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.

Server version      5.5.47-0ubuntu0.14.04.1
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         4 min 15 sec

Threads: 1  Questions: 602  Slow queries: 0  Opens: 189  
Flush tables: 1  Open tables: 41  Queries per second 
avg: 2.360

If you get output like this, then it means that the MySQL is running.

We have seen how to install MySQL on Ubuntu 14.04. Now let us see the MySQL installation process for Ubuntu 16.04.

How to Install MySQL on Ubuntu 16.04

Prequisites

  • Ubuntu 16.04 server with sudo non-root user access and firewall.

Installing MySQL

Ubuntu 16.04 version has the latest of MySQL version included in its APT package repository.

To install the package, just update the package index on the server.

Then install the default package using apt-get

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

The prompt will ask you to set the root password.

Create a secure password. You should remember the password because you will need it later.

Configuring MySQL

Since it is the fresh installation, you have to run the security script.

It will look up and solve some initial security issues like remote root logins and sample users.

In the older version of MySQL you have to initialize the data directory manually, but here the data directory will be initialized automatically.

Execute the below command to run the security script.

$ sudo mysql_secure_installation

Now the prompt will ask you for the root password which you created while installing the MySQL.

Enter the password and now accept all the default options. But when the prompt ask you to change the root password, Enter N.

As you have set the root password at step one, you don't need to set it again.

You have to use mysql_install_db to initialize the database directory for the versions before 5.7.6. If you use MySQL version 5.7.6 or later, you have to use mysqld --initialize.

Now you have installed the MySQL from Debian distribution so that the data directory will be initialized automatically.

You don't need to initialize the data directory manually. If you try to initialize it by yourself, you will get following error.

Output
2016-03-07T20:11:15.998193Z 0 [ERROR] --initialize specified, but the data directory has files in it. Aborting.

Now, the next step is to test the MySQL installation.

Test the MySQL

You could have installed MySQL in any way, but the MySQL should start running automatically.

Now, Let us test that by checking the status.

$ sudo service mysql status

You should get the output similar to the following one.

Output
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
Active: active (running) since Wed 2016-11-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

If you get the output, then it means the MySQL is running.

How to Install the latest version of MySQL on Ubuntu

I have shown you how to install the MySQL in Ubuntu 16.04 and 14.04.

But:

Here, I would like to show you How to install the latest version of MySQL on your Ubuntu 16.04 server.

It is always necessary to update your applications to get the bugs fixed and featured added software.

MySQL is the widely used open source database management system which uses the relational database model to store, manage and retrieve the data.

It is used in Linux with Apache Web server and PHP(LAMP).

If you like to install the latest version, you have to download the .db packages on their official website.

Prerequisites:

  • Ubuntu 16.04 with sudo non-root user access.

How to add the MySQL Software Repository?

MySQL developers maintain their own software repository with updated software.

The .db package helps you to configure and install the MySQL repositories.

Once you setup the repository you can install the software.

First Let us download the .db package with curl and install it using dpkg command.

  1. Go to the MySQL download page

  2. Scroll down to the bottom and look at the right end of the page before the footer.

  3. You could see the download button. Just right click on it and grab the link using copy link option.

  4. Now Go to the command prompt and change the directory where you can write.

    $ cd /tmp

  5. Download the file using the curl command. Paste the URL that you have copied after curl

    $ curl -OL https://dev.mysql.com/get/mysql-apt-config_0.8.3-1_all.deb

Here the L flag instructs the curl command to follow HTTP redirects.

The package should be downloaded now.

  1. Just to check it, list the files.

    $ ls
    Now:

The file name should be displayed.

Output
mysql-apt-config_0.8.3-1_all.deb

Let us install the MySQL by executing the following command.

$ sudo dpkg -i mysql-apt-config*

Here the dpkg is used to install, inspect and remove .db packages.

With** "i"**, we are asking dpkg to install the software.

The installation process will be initiated and it will ask you to choose the version of MySQL along with an option to install repositories for MySQL database.

The default option will install the repositories for the latest stable version of MySQL.

Now, Press the down arrow and move to the ok Menu and Hit Enter.

The repository will be added now and you have to refresh the apt package cache to make the new software available.

$ sudo apt-get update

Let us remove the downloaded file.

$ rm mysql-apt-config*

We have successfully added the MySQL repository and our next task is to install the MySQL software.

If you like to update the configuration of the repositories, run the below command.

$ pkg-reconfigure mysql-apt-config

Choose the new options and then refresh the package cache.

$ sudo apt-get update

Installing the MySQL

Once you setup the repository, the next task is to install the MySQL Server Package.

To install MySQL Server Package in Ubuntu install mysql server command is used.

, we will use the apt-get command.

$ sudo apt-get install mysql-server

Here, apt-get will look at the mysql-server packages and checks whether it is latest one and best suit.

After that, it will check the package dependencies. Then it will ask you to approve the installation. Here Type Y and then hit Enter.

The software will be installed and the prompt will ask for you to set the root password.

Enter a secure password and enter it once again. Then hit enter.

The process is completed. Now the MySQL should run.

Check that by executing the following command.

$ systemctl status mysql

Output
● mysql.service - MySQL Community Server
 Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Wed 2017-04-05 19:28:37 UTC; 3min 42s ago
 Main PID: 8760 (mysqld)
 CGroup: /system.slice/mysql.service
       └─8760 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

You can see status at Active: active (running). It means MySQL is running.

Our next step is to make the installation secure.

Securing MySQL

The MySQL has a command to check the security issues in newly installed software.

$ mysql_secure_installation

Now, the prompt will ask you for the root password. Remember the password you set during the installation process. Type the password and hit enter.

Now you have to answer Yes or No to some questions.

First the prompt will ask for the validate password plugin.

This plugin will ask the MySQL user to enter a strong password. You have take decision based on the individual user security requirement.

If you like to enable it, type Y and Hit Enter. If not, Just enter to skip it.

If you choose yes, then you will be asked to enter the number 0-2 to fix the password strength.

Enter any number and Press Enter.

After that, the prompt will ask for you to change the root password. We set the root password already, just press the enter to skip it.

After that, you will be asked the following

  • Removing the Anonymus MySQL user
  • Disallowing remote root login
  • Removing the test database
  • Reloading the privilege table

Just Press Yes for all of them.

Once you finished answering all the questions, the prompt will exit.

Now, the MySQL is secured. Our next step is to test the MySQL by accessing it from the client.

Testing MySQL

First, let us connect to the server using the mysqladmin the command line administrative client of MySQL.

$ mysqladmin -u root -p version

Here u root tells the MySQL Admin to log in as the root user and p tells MYSQL Admin to ask for root password.

The version will return the MySQL version.

The output contains the MySQL version, Uptime and some other status.

Output
mysqladmin  Ver 8.42 Distrib 5.7.17, for Linux on x86_64
Copyright (c) 2000, 2016, 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.

Server version      5.7.17
Protocol version    10
Connection      Localhost via UNIX socket
UNIX socket     /var/run/mysqld/mysqld.sock
Uptime:         58 min 28 sec

Threads: 1  Questions: 10  Slow queries: 0  Opens: 113      Flush tables: 1  Open tables: 106  Queries per second avg: 0.002

If you got the similar output like the above, it means that you have got the latest version installed in your system.

Conclusion

Now you have learned how to install MySQL on Ubuntu 14.04 and 16.04.

Also, you now know how to get the latest version of MySQL installed on your system.

You have learned how to secure the newly installed MySQL.

If you still have any doubts regarding the installation of MySQL..

please let us know that by leaving your comment below.

Selvakumar
Author

Selvakumar

I am an Online Marketer and technology lover. I like to learn new things and share that with people.

Comments