Remote access to mysql database

Step 1 : vi  /etc/my.cnf
———————————————
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    = mysql server ip

# skip-networking
———————————————

bind-address : IP address to bind to.

skip-networking : Don’t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed. Since you need to allow remote connection this line should removed from file or put it in comment state.

Step 2 : /etc/init.d/mysql restart

Step 3 : Grant access to remote IP address

mysql -u root -p mysqlGrant access to new database

Step 4 : If you want to add new database aaa for user bbb and remote IP xxx.xxx.xxx.xxx then type following commands at mysql prompt:

mysql> CREATE DATABASE aaa;
mysql> GRANT ALL ON aaa.* TO bbb@’xxx.xxx.xxx.xxx’ IDENTIFIED BY ‘PASSWORD’;

Step 5 : Grant access to existing database

mysql> update db set Host=’xxx.xxx.xxx.xxx’ where Db=’webdb’;
mysql> update user set Host=’xxx.xxx.xxx.xxx’ where user=’webadmin’;

mysql> exit

Step 6 : Test it From remote system

mysql -u webadmin –h 65.55.55.2 –p

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s