How Do I Allow Remote Access/Connections To MySQL Database Server?

By default remote access to the MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server.

Step 1: Login into the server using os user which user under istalled mysql.

Ex: If you are installed os user under mysql

Login putty if you are using Linux or Unix-like systems.

Login as mysql


Step 2: Verify if you are able to access database using MySQL Connectors/MySQL drivers like JDBC etc..

If you getting error or not able to connect database follow below steps.

Step 3: By default MySQL only listens to localhost, if we want to enable the remote access to it, then we need to made some changes in my.cnf file as like below:

a. Stop services:

sudo service mysql stop

b. Edit my.cnf file and comment out the bind-address and skip-external-locking lines:

sudo su - root

vi /etc/mysql/my.cnf

[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/English
#bind-address = 127.0..1

# skip-networking
....
..
....


c. sudo service mysql start

d. Verify port listening or not

   netsta -na|grep 3306

e.Grant permission to root from any host:

  mysql -u root -p

  GRANT ALL PRIVILEGES ON *.* TO root@'%' IDENTIFIED BY 'password';

  Put % instead of your IP to allow access from any IP
  root is mysql database user

  FLUSH PRIVILEGES;

Step 4: Now you are able to access database using MySQL Connectors/MySQL drivers like JDBC etc..