Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.

Raspberry PI and IP address.

marsheng's profile image marsheng posted 6 months ago in General Permalink

I have run WAMP on mp PC and all worked fine with connecting to local host at 127.0.0.1.

I've installed a LAMP server on a Raspberry PI and when I point my browser to the IP address, I have to click on the PHPMYADMIN folder to find the DB login.

From what I see when I try and connect to XXX.XXX.XXX.XXX/phpmyadmin/ I get an error. Do I change Heidi's settings or change the way the Raspberry Pi is setup.

I'm new to this.

Thanks Wallace.

ansgar's profile image ansgar posted 6 months ago Permalink

In HeidiSQL you need to enter the IP address of your Raspberry PI server, not the url to your Apache document root.

marsheng's profile image marsheng posted 6 months ago Permalink

Like most things, its easy when you have done it before. (Maybe not exercise)

I have had this running some 2 years ago but the PI crashed and I'm setting the system up again.

My Pi is at address XX.XX.XX.06 on my network.

The message I get is Can't connect to MySQl server on 'xx.xx.xx.6' (10061)

If I go to my browser on the remote PC, the address is valid and I can see the PHPMYAdmin folder. I can login with PHPMyAdmin on the remote machine.

The problem I have is I'm not sure what the key words are to do a web search for a solution.

The host machine is Linux so I cant test Heidi on that machine.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
ansgar's profile image ansgar posted 6 months ago Permalink

I would first check your Windows Firewall, because HeidiSQL needs the MySQL port open (normally 3306). While port 80 and 443 for http/s is open by default, others need to be opened manually.

marsheng's profile image marsheng posted 6 months ago Permalink

I think you are correct in that 3306 is not open. The MYSQL.cnf file has no lines of code in it. From what I have read, it should have some parameters for the ports. I have posted the question on the Linux forum.

Thanks for the help.

ansgar's profile image ansgar posted 6 months ago Permalink

If there is "Port=xyz" line in your mysql.cnf, then the default of 3306 applies.

marsheng's profile image marsheng posted 6 months ago Permalink

Although not strictly MYSQL, can you please help me understand connecting to one. The difficulty is that I cant connect but there are many reasons for that and each one is a whole subject on its own.

I did have this running a few years back, but the install program back then must have set up everything automatically as it worked out the box.

Here are my thoughts.

127.0.0.1 is the local host address.

1: Is the 3306 a port that connects the DB to 127.0.0.1 (Settings in mysql.cnf or similar)

2: I presume that the server needs to open this address to be accessed externally. (Settings in mysql.cnf or similar)

3: Is remote access to DBs on the server related to each DBs or the complete MYSQL system. If each db then sql commands to set it, if not in mysql.cnf.

4: Do I need to allow remotes IP address to be known to the server. Something like 0.0.0.0:*

Judging by the number of hits you get googling Connect to MYSQL, this is real issues with "many" solutions but each only seems to address on aspect of the whole process.

Cheers Wallace.

ansgar's profile image ansgar posted 6 months ago Permalink

some things which might help you:

  • Ensure your (Windows) firewall lets your heidisql.exe connect through port 3306 to any ip address. This was quite often the issue for others and me, so you should really check that if you still get "Can't connect.."
  • The ip on the server to which you connect is not 127.0.0.1 - this is a special local ip address, only valid when you connect to a service within the same computer. You said your Raspi is at address XX.XX.XX.06 - use that in HeidiSQL's "Hostname/IP" field.
  • You can restrict access of each DB on the server differently, by creating one user per DB, but you don't need to. If this is some small home server, I recommend using one user with a good password and access to everything.
  • You should create that user once using phpMyAdmin on your webbrowser, and fire such a batch of commands:
CREATE USER 'root'@'192.168.%' IDENTIFIED BY 'yOurPassW0rd';
GRANT EVENT, DROP, DELETE, CREATE VIEW, CREATE TEMPORARY TABLES, CREATE TABLESPACE,
  CREATE ROUTINE, CREATE, ALTER ROUTINE, ALTER, SHOW VIEW, SHOW DATABASES, SELECT,
  PROCESS, EXECUTE  ON *.* TO 'root'@'192.168.%';
FLUSH PRIVILEGES;
  • note the ip after the @ is yours on the client computer, not on the server.

There may be one or two additional things to set up, but you could just do this first and report back whether you can connect through HeidiSQL afterwards.

marsheng's profile image marsheng posted 6 months ago Permalink

I use a DB on Crazydomains daily with my Delphi program and Heidi - myregister.co.nz Port 3306. Heid has no issues so I assume the firewall is ok.

I use XX.XX.XX.6 in Heidi and Port 3306 - error Cant connect 10061

I can login into the PI SQL on my PC using the web and PHPMYADMIN. So I do have access.

Ran your code, no errors.

Same error Cant connect 10061.

See attached screen. Top heading says Server localhost:3306. Is that correct? My Win7 pc is connected to the PI.

The install is from here.

link text

I'm loosing faith the the LAMP installation. I have already had to make some changes to the MYSQL files to fix import and export errors. Followed instruction on the web.

What beer do you drink ??

1 attachment(s):
  • Screen
marsheng's profile image marsheng posted 6 months ago Permalink

This may help identify the problem. From the web - Stackoverflow

For anyone else who is hosting the MySQL on a Raspberry Pi 3 the file you are looking for is in etc/mysql/mariadb.conf.d/50-server.cnf Comment out 127.0.0.1 '#'bind-address = 127.0.0.1 (have to use quotes as web sees this as a bold directive)

If I do that and restart the PI, I get "my IP address is not allowed to connect to this MariaDB server" with Heidi. However, I can still access the db from my machine via a web interface.

So my machine is definitely trying to connect but the Pi is refusing the connection using Heidi.

Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.
marsheng's profile image marsheng posted 6 months ago Permalink

I can connet with putty xx.xx.xx.6 port 80 and have full access so that means firewall eyc ok.

marsheng's profile image marsheng posted 6 months ago Permalink

Oops not port 80 should be 22

marsheng's profile image marsheng posted 6 months ago Permalink

Tried this and added a new user.

CREATE USER 'username'@'localhost' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON . TO 'username'@'localhost' WITH GRANT OPTION; CREATE USER 'username'@'%' IDENTIFIED BY 'password'; GRANT ALL PRIVILEGES ON . TO 'username'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES;

Finally can login and have access to all the tables with my new user name.

ansgar's profile image ansgar posted 6 months ago Permalink

Wasn't that what I wrote above already?

Anyway, glad you can connect now.

marsheng's profile image marsheng posted 6 months ago Permalink

Thanks for the reply.

Not quite, I created a new user (not root) and then it worked. Still cant long in with root and the password, but can through the browser.

Somethings wrong with root.

Please login to leave a reply, or register at first.




Ads were blocked - no problem. But keep in mind that developing HeidiSQL, user support and hosting takes time and money. You may want to send a donation instead.