Connect MySQL over network? SOLVED

[JanPihlgren]

I bought my Bubba TWO last week.
I have some experiences with MySQL at Mandriva.

Ping to Bubba works OK.

I try to connect to MySQL from a desktop.
First I tried with MySQL Browser.
This give me the following error:

Could not connect to host '192.168.0.82'.
MySQL Error Nr. 2003
Can't connect to MySQL server on '192.168.0.82' (111)

Second I tried with the following command from the shell:

Code: Select all

[jan@Zebbie ~]$ mysql -h 192.168.0.82 -u root -p

At the second try I get the following error message:

Code: Select all

ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.0.82' (111)

Is there anyone that can explain what I have to do to get this work?

[lelle]

http://dev.mysql.com/doc/refman/5.1/en/ ... enied.html might help you investigating the problem.

[JanPihlgren]

Sorry. It doesn't help.

Code: Select all

jan@bubba:~$ netstat -ln | grep mysql                             
unix  2      [ ACC ]     STREAM     LISTENING     3634     /var/run/mysqld/mysqld.sock

mysqladmin variables
Resultat:

Code: Select all

| skip_networking                 | OFF                         |

My thought is that this may have to do with bubba, or .....?

[pa]

The MySQL user root is not allowed to log in from an external host for security reasons, only from localhost.

You need to log in to Bubba first and from Bubba use mysql -u root

/PA

[JanPihlgren]

But how do I access MySQL from a different host then?
I have tried to connect both as admin and as jan. No succsess!
Is it possible to change the roles and allow connection to MySQL from different server?

[pa]

I think that the easiest way is use a ssh program such as putty to forward port 3306 from your windows machine to bubba.

This is described here using (up until step 3) using putty:
http://souptonuts.sourceforge.net/sshtips.htm

Or here SecureCRT:
http://dev.mysql.com/doc/refman/5.1/en/ ... d-ssh.html


Another way is to log into bubba and change the access rights for either the root user or a new user.

A good place to start is here:
http://dev.mysql.com/doc/refman/5.1/en/ ... ation.html

Please note that changeing the default empty password for the root user will cause problems when using the upgrade system in the web-ui since many functions depend on the database and need access to this.

/PA

[JanPihlgren]

1. I'm not running a windows host.
2. My desktop is a Linux, Mandriva Linux 2009.0 PWP.
3. This is th comand I use:

Code: Select all

[jan@Zebbie ~]$ mysql -h bubba -u admin -p

This give the error:

Code: Select all

Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on 'bubba' (111)
[jan@Zebbie ~]$

4. Where do I have to change the accessrights for root or admin?
5. MySQL documentation does not solve my problem.

[pa]

Connect to bubba as your regular user using ssh.
Then on bubba run
mysql -u root

The mysql user "root" is for security reasons only allowed to access from localhost, ie from the commandline of bubba.

/PA

[Cohan]

Du you really need administrative rights? Create a mysql-user and give it access to the database in question. Non-root users should not have problems with remote access.

[JanPihlgren]

Nothing of suggested ideas will work. I give up!
Cohan:
Yes, I need administrativ rights to administrate the databases.
Its very awsame to use terminal konsol to administrate MySQL.

I also tried to install phpMyAdmin but I failed. No such package!
I think that this package should have been included by default.

For now I have to write my own php-script to access the databases.

[pa]

Can you help us with what is failing for access using the terminal to bubba?

Can you not nog in using ssh to bubba or can you not connect to mysql once logged in on bubba?

/PA

[Xet]

As some posts indicate, using the root user for external access isn't recomended. If you *really* got to have admin right on the database from an external address there are a few things you got to do:

Create a user that have the correct privileges and is allowed access from an external addrress. From the mysql-promt issue:

Code: Select all

CREATE USER 'MyUser'@'%' IDENTIFIED BY 'MyPassword';
GRANT ALL PRIVILEGES ON *.* TO 'MyUser'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

The "%" sign let "MyUser" connect from any host. Perhaps you can narrow that a bit and ,needless to say, pick a better username/password combo....

Change the "bind-address" option in /etc/mysql/my.conf from "127.0.0.1" to "0.0.0.0" and restart MySql.

After that you should be able to connect to mysql from an external address with the user MyUser using phpMyAdmin, Mysql Control Center or whatever you want...

Edited:
The path to the config file should be /etc/mysql/my.conf

[JanPihlgren]

That did the work.
Especially changing in /etc/mysql/my.cnf from 127.0.0.1 to 0.0.0.0
Now I have the possibility to work easily.

[winzhangout]

In my my.cnf i have only port and socket just that 2 lines. My skills in mysql is not big and i NEED my homesite to run on the bubba two. I have putted all my php site in web folder and from my old webhotel i have taken the sql file. how the hell do i get the sql file in mysql easiest? i suppose i can forget some gui mode in windows to play with mysql?

[winzhangout]

now i find that lines but cant change file because i dont know the root password?