How to log into a MySQL db on another server

fabiuz

Hi,

I am migrating to a new server where I have a new installation of MySQL with the last 3.x stable version.

I have installed successfully phpmyadmin but I am unable to connect to the DB using PHP or any MySQL remote client :-(

Anybody could help me understanding why ?

The user for the db in the new mysql server has been created, the privileges are the same as in the old server... but I am still unable to connect :-(

Is there any configuration parameter for MySQL that I could check?

Thanks!

Fabi

VaTech

Are you using ODBC?

fabiuz

no, I dont'...

michael

Let's see the code...

Maybe the ipchains ... firewall... herm.

fabiuz

Some more informations about the problem, ipchains and firewall are ok:

[root@ns1 root]# mysqladmin version

mysqladmin Ver 8.23 Distrib 3.23.52, for pc-linux-gnu on i686

Server version 3.23.52
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/lib/mysql/mysql.sock

Threads: 1 Questions: 52 Slow queries: 0 Opens: 8 Flush tables: 1 Open tables: 2 Queries per second avg: 0.043

Could this help to understand the problem ?

Zimm

I had a problem like this before, this is how I fixed it.

If the script you want to execute is on the host machine (the machine mySQL is installed on) then you should be able to connect putting > localhost < in the connection string. Of course, you'll have to make sure the database user you have for mySQL is able to connect through localhost. The user should be something like mysqluser@localhost <user@host>

If the script you want to execute is NOT on the host machine, then you'll have to use the IP address in the connection string and you'll have to make sure the database user has access from the IP. The user should be something like mysqluser@127.0.0.1 <user@host>

If you're unsure about what I'm talking about, don't know if you added the correct user privledges, or want to know how to add the correct users, mySQL.org has a document one how to do so.

http://www.mysql.com/doc/en/Adding_users.html

fabiuz

Thanks for the answer, I am already using localhost to use db but I need to connect from remote hosts too...

Hope to find a solution soon...

Fabi

jerdo

First you have to add a user to the MySQL user db that is allowed to access from the remote server. Each user you setup on the MySQL server should have the option of adding a host, I believe the default is localhost. So if you create a user sqluser with a server of localhost, sqluser will only be allowed to access the db from the localhost. However, if you create the user with a server of www.yourserver.com sqluser that user will be allowed to access the server from www.yourserver.com. If you have phpMyAdmin(and the correct privileges to the server) on the sql server this is easily accomplished using the Users link on the first page you get when accessing phpMyAdmin.

Zimm

Originally posted by fabiuz
... I am already using localhost to use db but I need to connect from remote hosts too...

whats the remote host? If im getting you right, all you have to do is add a new mysql user to access from the remote host, which isnt hard...

Use the same user you have added to access from the local host and do this...

Login to mySQL as root, then type the following exactly changing <MYSQLUSER> with the user name of your choosing and make_a_pass (keep the ' around the password)...

GRANT ALL PRIVILEGES ON . TO <MYSQLUSER>@"%" IDENTIFIED BY 'make_a_pass' WITH GRANT OPTION;

This will allow the user you specified (<MYSQLUSER>) to login and access your mysql database (with password make_a_pass) from any remote host.

From there, youd just put <MYSQLUSER> in the dbuser section of your php mysql connect statement or in the phpmyadmin config. Make sure you put your server IP/hostname in the host 🙂