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
# vi /opt/lamp/lamp/etc/my.cnf
Now locate file that read as :
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
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’;
From your remote system or your desktop type the following command:
§ -u is MySQL username
§ -h IP or hostname is MySQL server IP address or hostname
§ -p : Prompt for password
You can use SQLYog software to connect from remote location.