0

I was wondering if someone could help showing detailed steps (if possible) to make MySQL accessible remotely. Example: PC 1 has MySQL installed (DB server) on it and PC 2 can now ask PC 1 for data using either PHP, Python etc.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Andile
  • 384
  • 2
  • 3
  • 12
  • You do it like you normally would, just change the HOST in the db connection to that mysql server IP – Marko Paju Apr 26 '18 at 09:16
  • @MarkoPaju how? do I use the IP address of the machine containing MySQL? please explain maybe with examples if possible as I have never done this before! Thanks – Andile Apr 26 '18 at 09:17
  • First of you need to check that can you telnet pc1 ip address if you can then you can connect with mysql – rowmoin Apr 26 '18 at 09:19
  • What exactly is not working? Shouldn't there be tons of tutorials about this topic? – Nico Haase Apr 26 '18 at 09:20
  • @NicoHaase there are but for novice developers like me people assume a lot. That’s why I have asked for examples. – Andile Apr 26 '18 at 09:23
  • @gumedean Instead of 'localhost' you put the mysql server IP – Marko Paju Apr 26 '18 at 09:23
  • @MarkoPaju how to get the IP address of MySQL, if you don’t mind explaining to me. – Andile Apr 26 '18 at 09:24
  • 1
    @gumedean it's the same as you PC 1 IP – Marko Paju Apr 26 '18 at 09:25
  • @MarkoPaju let me try – Andile Apr 26 '18 at 09:26
  • You connect to the computer that hosts MySQL and then, depending on operating system, you check what its IP is. For windows, it's right click on an icon and then you get a ton of buttons - press one I guess, you'll get the info. For `*nix` you can use the terminal and type `ip a`, `eth0` is what you're usually after. The problem that you probably will have is that your MySQL won't allow remote connections. You need to edit `my.cnf` and change `bind-address` to `0.0.0.0`, save, restart MySQL. Make sure that user you're connecting with is allowed to connect remotely. – N.B. Apr 26 '18 at 09:27
  • Possible duplicate of [How to allow remote connection to mysql](https://stackoverflow.com/questions/14779104/how-to-allow-remote-connection-to-mysql) – Mark Rotteveel Apr 26 '18 at 17:04

1 Answers1

1

First, I would like to give credits to the people who assisted me when doing this. I will outline how I managed to get this to work. This was done on a machine running Windows 10. The steps are outlined below:

  • Installing MySQL and Location my.ini file

After installing MySQL server and workbench. Windows store my.ini file on a hidden location other than that of the MySQL installation location.

C:\ProgramData\MySQL\MySQL Server 5.7

This location can be view only when you have enabled windows to show hidden files. This is achieved with the following steps:

  1. Navigate to Control Panel
  2. Click on Appearance and Personalization
  3. Under File Explore Options, click on show hidden files and folders
  4. Under the view tab, Advanced settings, look for the option hidden files and folder and select the show hidden files, folders and drives and click ok.
  5. Navigate to C:\ and you should see the ProgramData folder
  6. Copy this file onto MySQL installation folder
  7. Navigate to

C:\ProgramData\MySQL\MySQL Server 5.7 and copy the file to C:\Program Files\MySQL\MySQL Server 5.7

  • Configure MySQL for remote access

open my.in file using your preferred editor and look for the line and remove the # in front of it to uncomment it:

bind-address=0.0.0.0

if the line is not there then add it manually save and restart MySQL or the machine.

  • Activating Telnet

The computer must have the telnet activity-activated, to achieve this the do the following steps:

  1. Navigate to Control Panel -> Programs -> Programs and Features and under this section click on Turn Windows features on or off
  2. Look for Telnet Client and check it
  3. Click Ok, and done now telnet is active.

On the MySQL, workbench creates a new SQL connection and use the IP address you used on my.in file and you should be set to go.

Now test this by writing a simple PHP or Python script to connect to your MySQL server.

Andile
  • 384
  • 2
  • 3
  • 12