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
