Wednesday, September 11

How to connect MYSQL Database from remote machine in Linux command line


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.




15 comments:

  1. u can also do this by using port redirect, apply this setting to iptables, so then used like this:

    ex: $ssh root@host_ip:port

    ReplyDelete
  2. how do config redirect with iptables?

    ReplyDelete
  3. MySQL is an RDBMS. Therefore, it generally contains no GUI tools to administer MySQL databases or to manage data contained within. DB Designer

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete
  5. Linux 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

    ReplyDelete
  6. Since 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

    ReplyDelete
  7. You 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

    ReplyDelete
  8. There are numerous product merchants in the market which click here furnish such recuperation arrangements with free demo/preliminary variant.

    ReplyDelete
  9. It'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

    ReplyDelete
  10. These are really amazing and valuable websites you have shared with us. Thanks for the informative post.
    WordPress development company in Chennai

    ReplyDelete
  11. From your remote Machine or your desktop(Local machine) type the following command
    Do I have to buy rdp for this?

    ReplyDelete
  12. 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.
    bioresonantie den bosch

    ReplyDelete
  13. 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.
    bioresonantie amsterdam

    ReplyDelete
  14. What's more, organizations need to perceive and gain by the open doors these improvements present.machine learning course in pune

    ReplyDelete
  15. You 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.

    ReplyDelete

:: Linux - Legends ::