Wednesday, April 12, 2017

MySQL Server Installation

1. INTRODUCTION

Installation of mySQL Server in linux system is not that difficult. We just need to run a couple of commands and thats it!

2. INSTALLATION
 
Here are the required commands to install and prepare mysql server:

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

Check the status of mysql server

sudo systemstl status mysql.service

This above command should show Active: active (running)  as output which means it is running.

The administration of mysql is carried out with mysqladmin command which can be run from terminal as follows:

mysqladmin -p -u root version

This will show the version information along with other information. 

3. LOCAL CONNECTION

We can use mysql to connect to the server as follows:

mysql -u root -p -h localhost

After connection, we need some basic commands as follows:

show databases;
use db_name;  
show tables; 
select * from [TABLE_NAME];
exit

We can see all the command by typing \h. 

 4. REMOTE CONNECTION

Local connection is not the problem we can directly use local server. But for security reasons, we can not connect if server is located remotely. We have to define some configuration settings to carry out remote connection. 

cd /etc/mysql/mysql.conf.d
sudo vi /mysql.conf.d

Check the section [mysqld] and modify the bind-address with the ip address of the server as follows:
bind-address =10.8.102.62

After restarting the server, we can now connect remotely.

Note: mysql uses port 3306, so this port should not be blocked from firewall. 

5. REMOTE USER CONFIGURATION 

Yes, the default root user is so configured that, only can connect locally. So, next task is to define this user so that it can connect remotely. That we do after carrying out local connection.

mysql -u root -p localhost
 
SELECT host FROM mysql.user WHERE User='root';

It shows localhost or 127.0.0.1 which means this user can not connect remotely. 

CREATE USER 'root'@'%' IDENTIFIED BY '[PASSORD]’;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%';

Reload permissions:

FLUSH PRIVIOLEGES;


Now, we can connect remotely with root user. % signifies the acceptance of connection from any external sources.