Search This Blog

Saturday, September 8, 2012

How to access MySQL Server from outside




MySQL Server Remote Access

For accessing MySQL Server remotely from external network you to do port forwarding of SSH port 22 i.e. default port for SSH.

Step1: Edit /etc/my.cnf  
            # vi /etc/my.cnf
OR
# vi /opt/lamp/lamp/etc/my.cnf 
            Now locate file that read as :
            [mysqld]
            Make sure line skip-networking is commented and add the following line.
            bind-address= your-MySQlServer-IP (i.e. your system’s IP )

Step2: Save and close my.cnf file and restart mysql services.
            #service mysqld restart
OR
#/opt/lamp/lamp restart

Step3: Grant access to remote IP address:
            # mysql –u root –p “password for mysql”
            mysql> GRANT ALL ON *.* TO root@’Your network public IP’ IDENTIFIED BY  ‘PASSWORD’;        

Note: In the above mysql query “*.*’”, first * is indicates all databases and second * indicates all tables. So, if you want give access to a particular database all tables then write this query.
mysql> GRANT ALL ON <Database 1>.* TO root@’Your network public IP’ IDENTIFIED BY  ‘PASSWORD’;
Similarly for giving access to a particular table write the below query.
mysql> GRANT ALL ON <Database 1>.<table1> TO root@’Your network public IP’ IDENTIFIED BY  ‘PASSWORD’; 
mysql> exit

From your remote system or your desktop type the following command:

$ mysql -u root –h 10.10.10.1 –p

Where,
§  -u is MySQL username
§  -h IP or hostname is MySQL server IP address or hostname
§  -p : Prompt for password

OR

  You can use SQLYog software to connect from remote location.





No comments:

Post a Comment