Change MySQL server port (Ubuntu)

Following are the steps to change mysql server port :

sudo apt­get install mysql­server­5.6

sudo nano /etc/mysql/my.cnf

port = 3306 to port = 6606

port =3306 to port =6606

bind­address = 127.0.0.1 to bind­address =0.0.0.0

• Create database :

mysql> create database esa_project;

 

mysql> use esa_project;

 

mysql> create table SICSR (sid int(10) PRIMARY KEY, sfname varchar(200), slname varchar(200),city varchar(50),fees varchar(10));

• Insert into SICSR :

mysql>insert into SICSR values ('1','bhargav','amin','vadodara','500000');

• Grant privileges to client:

mysql>GRANT ALL ON esa_project.* TO sicsradmin@'10.10.24.32' IDENTIFIED BY 'mysql';

Client side

• Install MySQL client :

$sudo apt­get install mysql­client

• Try to access MySQL server at 10.10.24.36 :

$mysql ­u sicsradmin ­p ­h 10.10.24.36

Incase you get error such as :

1. ERROR 2003 (HY000): Can’t connect to MySQL server on ‘10.10.24.36’ (111)

Change bind­address in /etc/mysql/my.cnf file :

bind­address = 127.0.0.1 to bind­address =0.0.0.0

Open port 6606 in iptables if not opened already:

sudo iptables ­A INPUT ­i eth0 ­p tcp ­m tcp ­­dport 6606 ­j ACCEPT

OR

You can also allow access to specific client by provide rules such as :

2. Write a rule to allow all the client from any port connect to MySQL server ip and port

for input if not given already:

sudo iptables ­A INPUT ­p tcp ­s 0/0 ­­sport 1024:65535 ­d 10.10.24.36 ­­dport 6606 ­m state state NEW,ESTABLISHED ­j ACCEPT

Write another rule to allow output from MySQL server to any client if not given already

sudo iptables ­A OUTPUT ­p tcp ­s 10.10.24.36 ­­sport 6606 ­d 0/0 ­­dport 1024:65535 ­m state ­­state ESTABLISHED ­j ACCEPT

3. ERROR 1130 (HY000): Host '10.10.24.32‘ is not allowed to connect to this MySQL server

Check for privileges of client on MySQL server:

show grants for 'sicsradmin'@'10.10.24.32';

GRANT ALL ON esa_project.* TO sicsradmin@'10.10.24.32' IDENTIFIED BY 'mysql';

• Try to access MySQL server at 10.10.24.36 :

$mysql ­u sicsradmin ­p ­h 10.10.24.36

Note : If connection is successful you will be able to login into mysql