How do I Enable Remote Access To MySQL Database?

Suppose you want to change and enable the remote access to MySQL DB on your linux server (Debian). By default, you don’t get such privileges so you will have to work your way through this. I have a very simple article for you so that you can enable the remote access. This is a pretty useful trick for all the database admins and controllers.

1. Find my.cnf file. If you don’t know about it, go and search in this path

• If you are using Debian Linux file is located at /etc/mysql/my.cnf location
• If you are using Red Hat Linux/Fedora/Centos Linux file is located at /etc/my.cnf location
• If you are using FreeBSD you need to create a file /var/db/mysql/my.cnf

The my.cnf file will look like this

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
[mysql.server]
user=mysql
basedir=/var/lib
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Now comment the bind adress as “bind-address = 127.0.0.1″

For example, if your MySQL server IP is 65.55.55.2 then entire block should be look like as follows:

[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.0.1
# skip-networking
….
..
….

[Note: this skip networking is very essential. It says, don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. a must have for local requests serving servers.]

2. Restart your mysql server:

/etc/init.d/mysql restart

3. Now type:

mysql -u root -p

4. Now carefully type the following code

GRANT ALL ON *.* TO root@’%’ IDENTIFIED BY ‘PASSWORD’;

[Note: change PASSWORD by your original password]

5. Restart your mysql server and you are done.

/etc/init.d/mysql restart

After this, you can use telnet and check.