Connect MYSQL Database from remote machine
MySQL Pronounced "my ess cue el" (each letter separately) and not "my SEE kwill." MySQL is an open source RDBMS that relies on SQL for processing the data in the database. MySQLprovides APIs for the languages C, C++, Eiffel, Java, Perl, PHP and Python. In addition, OLE DB and ODBC providers exist for MySQL data connection in the Microsoft environment. A MySQL .NET Native Provider is also available, which allows native MySQL to .NET access without the need for OLE DB.
MySQL is most commonly used for Web applications and for embedded applications and
has become a popular alternative to proprietary database systems because of its speed and reliability. MySQL can run on UNIX, Windows and Mac OS.
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. This post will explain how to setup a user account and access mysql server remotely.
Step1: Connect ur Server using ssh
ssh user@bsrtech.net
Login as Root user using su
$su - root
enter passwd
OR directly login as root user
ssh root@bsrtech.net
Step2: Edit MYSQL server configuration file my.cnf
#vim /etc/my.cnf
Once file open add the following line
# bind-address = 127.0.0.1 # comment this line out
If you cannot locate “bind-address” in my.conf, you can add the following.
bind-address = <public_ip_address_of_your_machine>
Make sure line "skip-networking" is commented or remove line
:Save & quit the File (:wq!)
Output:-
Note : "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 be removed from my.cnf or put it in comment state.
Step3 : Restart Mysql service
#service mysqld restart
or
#/etc/init.d/mysqld restart
Output:-
#chkconfig mysqld on
Step4: Grant remote access to the MySQL user(mahesh) within MySQL server as below.
Add MYSQL USER, First connect mysql root user
# mysql -u root -p
mysql> CREATE USER 'mahesh'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'mahesh'@'%';
In the above command, I allow the MySQL user “mahesh” to access all MySQL databases from any remote host.
mysql> FLUSH PRIVILEGES;
Your changes will now be in effect.
Exit Mysql, using exit command
mysql>exit
Output:-
Step 5: Checking Remote Access
From your remote Machine or your desktop(Local machine) type the following command
$mysql -u mahesh -h "remote-server-ipaddress" -p
Ask Mysql user Password (enter password)
Output:-
Where -u mahesh: mahesh is MySQL username
-h IP or hostname: MySQL server IP address or hostname (FQDN)
-p : password of mysql user
In the above Post , I allow the MySQL user “mahesh” to access all MySQL databases from any remote host.
MySQL Pronounced "my ess cue el" (each letter separately) and not "my SEE kwill." MySQL is an open source RDBMS that relies on SQL for processing the data in the database. MySQLprovides APIs for the languages C, C++, Eiffel, Java, Perl, PHP and Python. In addition, OLE DB and ODBC providers exist for MySQL data connection in the Microsoft environment. A MySQL .NET Native Provider is also available, which allows native MySQL to .NET access without the need for OLE DB.
MySQL is most commonly used for Web applications and for embedded applications and
has become a popular alternative to proprietary database systems because of its speed and reliability. MySQL can run on UNIX, Windows and Mac OS.
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. This post will explain how to setup a user account and access mysql server remotely.
Step1: Connect ur Server using ssh
ssh user@bsrtech.net
Login as Root user using su
$su - root
enter passwd
OR directly login as root user
ssh root@bsrtech.net
Step2: Edit MYSQL server configuration file my.cnf
#vim /etc/my.cnf
Once file open add the following line
# bind-address = 127.0.0.1 # comment this line out
If you cannot locate “bind-address” in my.conf, you can add the following.
bind-address = <public_ip_address_of_your_machine>
Make sure line "skip-networking" is commented or remove line
:Save & quit the File (:wq!)
Output:-
Note : "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 be removed from my.cnf or put it in comment state.
Step3 : Restart Mysql service
#service mysqld restart
or
#/etc/init.d/mysqld restart
Output:-
#chkconfig mysqld on
Step4: Grant remote access to the MySQL user(mahesh) within MySQL server as below.
Add MYSQL USER, First connect mysql root user
# mysql -u root -p
mysql> CREATE USER 'mahesh'@'%' IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON * . * TO 'mahesh'@'%';
In the above command, I allow the MySQL user “mahesh” to access all MySQL databases from any remote host.
mysql> FLUSH PRIVILEGES;
Your changes will now be in effect.
Exit Mysql, using exit command
mysql>exit
Output:-
Step 5: Checking Remote Access
From your remote Machine or your desktop(Local machine) type the following command
$mysql -u mahesh -h "remote-server-ipaddress" -p
Ask Mysql user Password (enter password)
Output:-
Where -u mahesh: mahesh is MySQL username
-h IP or hostname: MySQL server IP address or hostname (FQDN)
-p : password of mysql user
In the above Post , I allow the MySQL user “mahesh” to access all MySQL databases from any remote host.
u can also do this by using port redirect, apply this setting to iptables, so then used like this:
ReplyDeleteex: $ssh root@host_ip:port
how do config redirect with iptables?
ReplyDeleteMySQL is an RDBMS. Therefore, it generally contains no GUI tools to administer MySQL databases or to manage data contained within. DB Designer
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteLinux clients won't flutter an eyelid before they state an accentuation originates from a profound aversion of Microsoft's routine with regards to changing the earth for programming. Check this site
ReplyDeleteSince a devoted server web facilitating does not impart its server to different sites (like shared web facilitating), you will find that you have much more order over your server task and its data transmission. One of the favorable highlights of devoted server web facilitating is that it can likewise be remotely designed and worked. esxi dedicated server hosting
ReplyDeleteYou there, this is really good post here. Thanks for taking the time to post such valuable information. Quality content is what always gets the visitors coming. Managed IT Services Mississauga
ReplyDeleteThere are numerous product merchants in the market which click here furnish such recuperation arrangements with free demo/preliminary variant.
ReplyDeleteIt's simply a question of having the correct devices and the learning important to realize what to do, much like a specialist performing heart by-pass medical procedure. tape data recovery
ReplyDeleteThese are really amazing and valuable websites you have shared with us. Thanks for the informative post.
ReplyDeleteWordPress development company in Chennai
From your remote Machine or your desktop(Local machine) type the following command
ReplyDeleteDo I have to buy rdp for this?
Sometimes we have to face a lot of confusion when we found different opinion of different hundreds of articles related to same question. But I think now I am close to resolve my doubts after reading this blog.
ReplyDeletebioresonantie den bosch
I am glad that I saw this post. It is informative blog for us and we need this type of blog thanks for share this blog, Keep posting such instructional blogs and I am looking forward for your future posts.
ReplyDeletebioresonantie amsterdam
What's more, organizations need to perceive and gain by the open doors these improvements present.machine learning course in pune
ReplyDeleteYou should continue writing such blogs, it was informative. Also, within your appreciation, I just want to add that you can buy World Zip Code Database from World Cities Database at very reasonable prices.
ReplyDeleteSometime few educational blogs become very helpful while getting relevant and new information related to your targeted area. As I found this blog and appreciate the information delivered to my database.
ReplyDeletepersoonlijke ontwikkeling utrecht