How to install and configure mysql server on Linux

Prerequisites

Assuming you have a vanilla installation of Ubuntu 14.04.1 LTS Server... and are connected to the internet... Ensure your repository is up to date by entering the following at the terminal.

sudo apt-get update

Set a static IP address on the machine hosting the database server. You don't have to do this, however if using DHCP your IP address may change. To configure the static IP address enter the following at the terminal to open the network configuration file for editing.

sudo vi /etc/network/interfaces

And change the following

auto eth0
iface eth0 inet dhcp

to

auto eth0
iface eth0 inet static
address <ip address>
netmask <subnet mask>
gateway <gateway>

Replace <ip address> and alike with your database IP address and network details accordingly. Then save the file (:w) and quit vi (:q).

Installation

Install MySQL Server

To install mysql server simply enter the following at the terminal.

sudo apt-get install mysql-server

During the installation you will need to enter a password for the 'root' user - record / recall this.

Enable remote listening

To enable remote listening enter the following at the terminal to open the mysql configuration file for editing.

sudo vi /etc/mysql.mysql.cnf

Ensure skip-networking is commented / removed ( although this is not applicable in ubuntu 14.04.1 ) and changethe bind-address from 127.0.0.1 to <ip address>
Then save the file (:w) and quit vi (:q).

Create a new database

Firstly login to your database server.

mysql -uroot -p<password>

Then the mysql prompt will appear e.g. 'mysql>'

Next create the new database called 'mydb'

mysql>create database mydb;

Important to remember to include the trailing semi-colon, as mysql uses this to delimit the end of the instruction.
If successful you will see something like 'Query OK, 1 row affected'
Exit database server.

mysql>quit;

Create user and set permissions

Now login to the new database 'mydb' on the database server.

mysql -uroot -p<password> mydb

Enable remote user. Replace <myuser> and <passowrd> with your 'mydb' database user name and database passwords respectively.

mysql>create user '<myuser>'@'localhost' identified by '<passowrd>';
mysql>create user '<myuser>'@'%' identified by '<passowrd>';

Configure permissions.

mysql>grant all on *.* to '<myuser>'@'localhost';
mysql>grant all on *.* to '<myuser>'@'%';

That's it!.

Now restart the network service so the new IP address and new database settings take effect.

sudo /etc/init.d/networking restart
sudo service mysql restart

Or simply reboot the database server to restart the network and mysql service.

sudo reboot