Install and Configure MySQL on Ubuntu 16.04

I have been working on a project for some time. This tutorial is part of that project. I will talk about the project in another post.

Let's get back to the topic.

MySql is a general purpose free RDBMS. This is a very popular database in the opensource world.

I am using Ubuntu 16.04 for this tutorial.

You can check your OS by using below command.

lsb_release -a

The stable MySql package is available under Ubuntu repository.

Let's update the OS before we install MySql package. 

sudo apt-get update
sudo apt-get upgrade
sudo apt-get dist-upgrade

All the above commands will update the OS.
Once that is done you might need to restart the system, based on the what packages you are installing. If you are prompt to restart, then reboot your system.

In the next step, we will install MySql package using the below command.

sudo apt-get install mysql-server

During the installation, you will be prompt to set root password. Please remember the password. You will need it in other steps.
Once the installation is done, you can check the status of the MySql server and the version of MySql server.

To know the status: mysql -V

To know the process status: sudo netstat -tap | grep mysql

Here are my outputs for those commands.

If you would like to secure the server then you can use below script. This will run on already installed MySql server and change few policies. As I am installing for development purposes, not for production, I will leave it alone.

The script for secure installation: sudo mysql_secure_installation

Now let's connect to the MySql instance. Currently, MySql is installed as localhost.
code to connect: mysql -u root -p

I also got a Perl script to optimize the MySql server.
To use that script, you need to have git. Please install git using below command, if you don't have it already.

Install git: sudo apt-get install git

Now let's clone the git repo, and run the optimization code.

cd ~
git clone https://github.com/major/MySQLTuner-perl.git
cd MySQLTuner-perl
perl mysqltuner.pl --user root --pass='root_password'

After the tuner script install, you need to restart the MySQL.
sudo systemctl restart mysql.service

Now verify if everything running fine, by checking the mySql status and login to the MySQL server.

To know the status: mysql -V
To know the process status: sudo netstat -tap | grep mysql

You also can change the config file in MySql, if you want to connect to this instance from outside(not from localhost) world.

The config file present in "/etc/mysql/my.cnf"

Below screen print shoes how my config file looks like. Please note the starting line with "[mysqld]" and at the end with 

port = 3306

bind-address = 0.0.0.0

bind-address = 0.0.0.0, means I would like to connect to this instance, from any system, outside of localhost.

You also need to give privileges to the host you are connecting from.
Use below command to make it ready.

mysql -u root -p
Enter password: <enter password>
mysql>GRANT ALL ON *.* to root@'host ip' IDENTIFIED BY 'put-your-password';
mysql>FLUSH PRIVILEGES;
mysql>exit

then restart the service "sudo systemctl restart mysql.service"

If you are having a problem with log in to a local MySql server:

You can try as follows it works for me.

Start server:

sudo service mysql start

Now, Go to sock folder:

cd /var/run

Back up the sock:

sudo cp -rp ./mysqld ./mysqld.bak

Stop server:

sudo service mysql stop

Restore the sock:

sudo mv ./mysqld.bak ./mysqld

Start mysqld_safe:

 sudo mysqld_safe --skip-grant-tables --skip-networking &

Init mysql shell:

 mysql -u root

Change password:

Hence, First choose the database

mysql> use mysql;

Now enter below two queries:

mysql> update user set authentication_string=password('123456') where user='root';
mysql> update user set plugin="mysql_native_password" where User='root'; 

Now, everything will be ok.

mysql> flush privileges;
mysql> quit;

For checking:

mysql -u root -p