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.




60 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. 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
  9. These are really amazing and valuable websites you have shared with us. Thanks for the informative post.
    WordPress development company in Chennai

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

    ReplyDelete
  11. 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
  12. 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
  13. What's more, organizations need to perceive and gain by the open doors these improvements present.machine learning course in pune

    ReplyDelete
  14. 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
  15. Sometime 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.
    persoonlijke ontwikkeling utrecht

    ReplyDelete
  16. This blog is really helpful regarding all educational knowledge I earned. It covered a great area of subject which can assist a lot of needy people. Everything mentioned here is clear and very useful.bioresonantie amsterdam

    ReplyDelete
  17. That’s what i used to be looking for. i am speaking approximately all subjects bundled in this weblog. they all are truly very beneficial for me similarly to for my institution. we are certainly going to use its highlighted records. Elephant volunteers

    ReplyDelete
  18. Now day, everything is going to find a new but well settled and successful stream for their career. When I came to this blog, I really impressed by all the knowledge points mentioned here. Thank you for this assistance.bioresonantie amsterdam

    ReplyDelete
  19. written content. I added new knowledge to my database for essay writing skill.bioresonantie veendam

    ReplyDelete
  20. thanks for article, I use root privileges for remote MySQL, is that good or I change username for this ??

    ReplyDelete
  21. Your writing style says a lot about who you are and in my opinion I'd have to say you're insightful. This article reflects many of my own thoughts on this subject. You are truly unique.
    Best Data Science training in Mumbai

    Data Science training in Mumbai

    ReplyDelete
  22. The best article I came across a number of years, write something about it on this page. northshore university connect

    ReplyDelete
  23. The most interesting text on this interesting topic that can be found on the net ... northshore connect

    ReplyDelete
  24. I exploit solely premium quality products -- you will observe these individuals on: Organic ghee

    ReplyDelete
  25. In this article understand the most important thing, the item will give you a keyword rich link a great useful website page: Website Manialiga

    ReplyDelete
  26. Such a very useful article. Very interesting to read this article.I would like to thank you for the efforts you had made for writing this awesome article.
    Data Science Training Institute in Bangalore

    ReplyDelete
  27. I am really enjoying reading your well written articles. It looks like you spend a lot of effort and time on your blog. I have bookmarked it and I am looking forward to reading new articles. Keep up the good work.
    Best Data Science Courses in Bangalore

    ReplyDelete
  28. Excellent Blog! I would like to thank for the efforts you have made in writing this post. I am hoping the same best work from you in the future as well. I wanted to thank you for this websites! Thanks for sharing. Great websites!
    Data Science Course in Bangalore

    ReplyDelete
  29. I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!
    Data Science Training in Bangalore

    ReplyDelete
  30. I invite you to the page where see how much we have in common. เล่นคาสิโนฟรี

    ReplyDelete
  31. I'd love to thank you for the efforts you've made in composing this post. I hope the same best work out of you later on too. I wished to thank you with this particular sites! Thank you for sharing. Fantastic sites!
    Data Science Course in Bangalore

    ReplyDelete
  32. This is a great post. This post gives a truly quality information. I am certainly going to look into it. Really very helpful tips are supplied here. Thank you so much. Keep up the great works
    Data Science Training in Bangalore

    ReplyDelete
  33. I recommend only good and reliable information, so see it: finance

    ReplyDelete
  34. This is very useful, although it will be important to help simply click that web page link: Finance

    ReplyDelete
  35. I am glad to read this post, it's a good one. I am always looking for quality posts and articles and this is what I found here, admin rdp for sale

    ReplyDelete
  36. Really wonderful blog completely enjoyed reading and learning to gain the vast knowledge. Eventually, this blog helps in developing certain skills which in turn helpful in implementing those skills. Thanking the blogger for delivering such a beautiful content and keep posting the contents in upcoming days.

    360DigiTMG Cyber Security Course

    ReplyDelete
  37. Fantastic post found to be very impressive to come across such an awesome blog. I really felt enthusiast while reading and enjoyed every bit of your content. Certainly, since this blog is being more informative it is an added advantage for the users who are going through this blog. Once again nice blog keep it up.

    360DigiTMG IoT Course

    ReplyDelete
  38.  Being a travel blogger and lover, it was appealing and helpful for me. It is a strong article to portray this topic limits the readers to widen their horizon of accepted wisdom and power as they go through the article.
    Data Science training in Mumbai
    Data Science course in Mumbai
    SAP training in Mumbai

    ReplyDelete
  39. You have performed a great job on this article. It’s very precise and highly qualitative. You have even managed to make it readable and easy to read. You have some real writing talent. Thank you so much. τοιχογραφιες σε Καφε

    ReplyDelete
  40. This excellent is undoubtedly fantastic. Most of these minuscule truth is generated applying broad range connected with accreditation know-how. When i benefit taking that approach lots. נערות ליווי בצפון

    ReplyDelete

  41. Top quality article with very informative information and found very knowledgeable thanks for sharing waiting for next blog.
    Data Analytics Course Online

    ReplyDelete
  42. Fantastic blog extremely good well enjoyed with the incredible informative content which surely activates the learners to gain the enough knowledge. Which in turn makes the readers to explore themselves and involve deeply in to the subject. Wish you to dispatch the similar content successively in future as well.

    artificial intelligence course in bhilai

    ReplyDelete
  43. Really wonderful blog completely enjoyed reading and learning to gain the vast knowledge. Eventually, this blog helps in developing certain skills which in turn helpful in implementing those skills. Thanking the blogger for delivering such a beautiful content and keep posting the contents in upcoming days.

    Data Science certification in Bhilai

    ReplyDelete
  44. Truly incredible blog found to be very impressive due to which the learners who ever go through it will try to explore themselves with the content to develop the skills to an extreme level. Eventually, thanking the blogger to come up with such an phenomenal content. Hope you aarrive with the similar content in future as well.

    Digital Marketing training in Bhilai

    ReplyDelete
  45. Tremendous blog quite easy to grasp the subject since the content is very simple to understand. Obviously, this helps the participants to engage themselves in to the subject without much difficulty. Hope you further educate the readers in the same manner and keep sharing the content as always you do.

    Data Science training in Raipur

    ReplyDelete
  46. Stupendous blog huge applause to the blogger and hoping you to come up with such an extraordinary content in future. Surely, this post will inspire many aspirants who are very keen in gaining the knowledge. Expecting many more contents with lot more curiosity further.

    Digital Marketing training in Raipur

    ReplyDelete
  47. Fantastic blog extremely good well enjoyed with the incredible informative content which surely activates the learners to gain the enough knowledge. Which in turn makes the readers to explore themselves and involve deeply in to the subject. Wish you to dispatch the similar content successively in future as well.

    ai course in raipur

    ReplyDelete
  48. Nice Information Your first-class knowledge of this great job can become a suitable foundation for these people. I did some research on the subject and found that almost everyone will agree with your blog.
    Cyber Security Course in Bangalore

    ReplyDelete
  49. Writing in style and getting good compliments on the article is hard enough, to be honest, but you did it so calmly and with such a great feeling and got the job done. This item is owned with style and I give it a nice compliment. Better!
    Cyber Security Training in Bangalore

    ReplyDelete

:: Linux - Legends ::