Remote Access to Mysql on PI

[andrewstillie]

Hi there,

I am relatively new to all this.

I have set up a LAMP installation on my PI and can remotely SHH + FTP from my ubuntu laptop using the ip address 192.168.1.249.

Also, I have installed mysql on my PI and can access it locally over SSH.

I have been trying to access the Mysql using a mysql client called mysql workbench.
When I try to connect using the ip address of the pi I keep getting a unable to connect

Failed to Connect to MySQL at 192.168.1.249:3306 with user root

Can't connect to MySQL server on '192.168.1.249' (111)

Is this some kind of permissions issue?
Any other ideas?

[joan]

I think by default mysql only accepts local connections.

Have a look in /etc/mysql for a file called my.cnf

See if it has a line like

bind-address = 127.0.0.1

if it does then comment it out by putting a # at the start of the line and restart mysql

sudo /etc/init.d/mysql restart

Those instructions work on my laptop which may or may not be simialr to your Pi.

[andrewstillie]

Cheers Joan...


now I get the following error
Failed to Connect to MySQL at 192.168.1.249:3306 with user root
Host '192.168.1.248' is not allowed to connect to this MySQL server

[daveg]

This is a permissions thing in MySQL.
By defaul root probably only has access rights from local host

You will have to allow root from all addresses. Bad idea for security.

Or allow access from the ip you connect from or the subnet

More info here
https://dev.mysql.com/doc/refman/5.5/en ... users.html

[rwycuff]

Depending on what os your on.
There is mysql clients that will create ssh tunnel then open mysql

Windows im not sure if there is any good free clients that will do this.

Linux i would almost thing there has to be something

Mac i use sequal pro

[Boezelman]

Not sure if it is the same thing, but I use phpMyAdmin to edit Mysql on my Pi (also accessible from outside the local network).

[andrewstillie]

Hello again - Is it possible to build my database on my host Ubuntu laptop running mySql, then copy a database file to my Pi using FTP?

[gnomepi_j]

I managed to sort the mysql connection out. It is a permissions problem.

connect to mysql : mysql -u root -p

CREATE USER 'root'@'localhost' IDENTIFIED BY 'some_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost'
WITH GRANT OPTION

try this or create a new user called admin with full permissions.
try connecting again.

[daddymacdaddy]

I had issues connecting to mysql via mysql workbench and thanks to gnomepi_j it worked!! U da Man!

[jandi100]

I had some problems in connecting MySQL Work bench with my Raspberry pi. This is how I got it running.

I created a server connection type: Standard TCP/IP over SSH. That way I can use the normal Raspberry Pi user name and password to connect via SSH.

SSH Hostname should be my local IP address on the network or the global web address, i this case: www.somthing.dk:22

username and password defined for the pi..

Then the MySQL hostname should be: localhost or 127.0.0.1
Server port: 3306
User name: root (or what ever username you have defined)
password: also as you defined for the root user in MySQL.

That's it.

I am very surprised how fast the Raspberry Pi respond to the MySQL Workbench. Great tool.

Regards
Jan

[Amaredues]

Thanks Jan this worked exactly as described!

Please do not change permissions as gnomepi_j instructs. This is a security concern just as daveg stated.

[femindharamshi10]

What will be the mysql host for raspberry pi mysql ? because i used to use it with hostinger.in and there it was mysql.hostinger.in

[jimva]

This is what I did to get Mysql Workbench 6.3ce to work remotely with my Raspberry Pi 3 Model B

Look in /etc/mysql for a file called my.cnf

Find the line like this.

bind-address = 127.0.0.1

change the line to "bind-address = 0.0.0.0"
by logging in as root then change directory to /etc/mysql
and then using nano to alter file.

this will change it so you can access mysql server from anywhere.

next create a mysql user with super privileges that can also access mysql server from anywhere.

mysql> CREATE USER 'monty'@'%' IDENTIFIED BY 'some_pass';
mysql> GRANT ALL PRIVILEGES ON *.* TO 'monty'@'%' WITH GRANT OPTION;

next restart mysql server
[email protected]: ~$ sudo /etc/init.d/mysql restart

"monty" with password "some_pass" will be able to access the raspberry pi mysql server from anywhere and will have the same privileges as root.

[torbeta]

Preamble
There seems to be little mention here of PhpMyAdmin , which is a browser based way of accessing MySQL databases. It can be used on the host computer browser by ‘localhost/phpmyadmin’ and from other computers on the same network using the local IP address of the computer with the database followed by’/phpmyadmin’. This method of access allows rapid construction of databases, modification and editing of data.
With reference to the present discussion it can access the MySQL ‘internal’ database and allow a user to be added to facilitate remote access by program or command line. A small modification to /etc/mysql/my.cnf is the only other requirement.
Several other bits are needed to get started with phpmyadmin as follows:-
Installation of apache2 webserver
Using root terminal type in these 5 commands. (or prefix with sudo)
If you do not use PERL, enter only the first line.
apt-get install apache2
apt-get update
apt-get install libapache2-mod-perl2
a2enmod cgi
service apache2 restart

This sets up apache2 with PERL support. If you are a python programmer, I am sure you can find the equivalent bindings via Google.
Test by typing localhost in your pi browser or your pi's ip address in a browser on a computer or tablet on the same network.
Put html files in /var/www/html/ and perl files in /usr/lib/cgi-bin/.
Remember to set execute permissions for perl files to 'anyone'
Installation of MySQL and phpmyadmin
The following loads mysql, but as you are at this post you probably have it already installed.
Using root terminal
(Set a password when requested)
apt-get install mysql-server
Install PHP5 and phpmyadmin
(Set a password when requested)
apt-get install php5 libapache2-mod-php5
apt-get install phpmyadmin
Now add the following line (as root) to file /etc/apache2/apache2.conf

Include /etc/phpmyadmin/apache.conf

Set up new user for mysql
Type http://localhost/phpmyadmin into your browser on the Pi with the databases or http://ipaddress/phpmyadmin from a remote pc on the same network. Log in with the password created earlier.
On the left you will see a list of databases. Click on the ‘+’ by mysql, then on the word ‘user’ at the bottom of the list. In the other pane the current list of users is displayed. Note that the password is encrypted. Choose ‘copy’ on one of the rows. The insert page appears and you just need to change some entries as follows:-
In ‘Host’ replace with something like ‘192.168.1.%’ where the 192.168.1 are the numbers used in your network. ‘%’ is a wildcard which allows any computer on your network access to the databases.
In ‘User’ give a username . I use pi.
If you want to change the password, select ‘PASSWORD’ from the function box on the ‘Password’ row and enter your new password in the ‘Value’ box.
At the bottom of the page click the ‘Go’ button.
Then click ‘Browse’ on the top menu and the list of users will include your addition.
Finally
Edit /etc/mysql/my.cnf as root.
Find ‘bind_address=’ and change the value to 0.0.0.0
If you do not have the latest mysql you may need to comment out this line (by adding the # at the front)
#skip-networking
Conclusion
You should now be able to access the databases on the host computer either by program or command line from a remote pc.
The code for PERL for example is
my $dbh = DBI->connect("DBI:mysql:database=databasename;host=ipaddress of host computer","username","password") or die " No connection to database \n";

There is more information on Raspberry Pi set up with Jessie and information on my Pi projects at http://www.torbeta.com.

[clint020]

answer from
by jimva » Fri Apr 22, 2016 12:09 am

thank You very much !

I had need to connect windows Workbench from public network to raspbian Mysql in into my home.
Jan answer maybe works for someone but didnt work when I tried it that way.

Bind address should be like said by jimva : 0.0.0.0
and second step is to create mysql user and grant privileges.

[greykes]

Hi guys, I've tried all that was said in this thread and got no success to the problem as stated by the original thread message. I did get success though after re-setting my wlan IP to dhcp; because I had previously set it to static to connect via ssh. I really hope this helps someone else.

[richardespiri2]

YOU CAN FIND THE CONFIG HERE (FOR MARIADB)

nano /etc/mysql/mariadb.conf.d/50-server.cnf

#REPLACE 127.0.0.1 with 0.0.0.0
bind-address = 0.0.0.0

This works for me...

[Matha]

Pardon me for butting in here. While the responses on the connection strings (for Python, etc.) are fine in an earlier section of this thread, my recommendation is to use the MySQL command line interface on the client machine to check the connectivity after permissions have been granted on the host MariaDB/MySQL server.

Code: Select all

mysql -u root -h x.x.x.x -p

with x.x.x.x representing the IP address in the event DNS lookup is impractical.

If this connection is unsuccessful then perhaps the following command within the MySQL command line on the host could be the next step in debugging:

Code: Select all

mysql -u root -p
SELECT user, host FROM mysql.user GROUP BY host ORDER BY user;

Of course, the assumption is that the "sysadmin" for the MariaDB/MySQL is "root" which is generally the default.

Regards.

[oliverusselldev]

Check whether you have established the remote mysql connection correctly or not. The proper method is to first whitelist the IP and then apply remote access configs

Code: Select all

<?php

function getdb(){

$servername = "46.101.5.233"; // put your cloudways server IP here
$username = "qxxfumxxxbd";
$password = "xxxxbQxxmM";
$db = "qxxfumxxxbd";

try {

   $conn = mysqli_connect($servername, $username, $password, $db);

    //echo "Connected successfully";

   }

catch(exception $e)

   {

   echo "Connection failed: " . $e->getMessage();

   }

   return $conn;

}

Then you can use mysql clients like SQLYOG and Mysql workbench to connect to the database.


Source: Enabling Remote MySQL Connection

[johnathansmith69]

I am getting the following error:

MariaDB [(none)]> CREATE USER 'root'@'localhost' IDENTIFIED BY ‘Regan123’;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '‘Regan123’' at line 1

[fbe]

This is a syntax error. ‘Regan123’ is not 'Regan123'.

[rpiuser2016]

YOU CAN FIND THE CONFIG HERE (FOR MARIADB)

nano /etc/mysql/mariadb.conf.d/50-server.cnf

#REPLACE 127.0.0.1 with 0.0.0.0
bind-address = 0.0.0.0

This works for me...

Thanks!