Topic: Connect to MySQL

[Henrik Andreasen]

Hi all

I’ve been thinking on making an online version of my databases made in Access.
I’ve found a program (Access2MySQL) that can convert the Access databases to a SQL databases, but it want to connect to the MySQL server, before it converts the database.
Some of the databases I have are huge, so I don’t want to start from scratch again, if I can avoid it.

Does anyone know if it’s possible to connect to the MySQL server on SME (5.5 running as server only btw) from a machine on the LAN?
If it is possible to connect to it, what port do I use then?
If it’s not possible, can it then be made possible (just for the machines on the LAN)?

Anyone knows about a program (Windoze), that can convert Access (2000) databases to an MySQL sql file (with tables and data)?


TIA

[Nathan Fowler]

Henrick, I use some software called "MySQL Front", you may download this software here:

http://www.anse.de/mysqlfront/

You should be able to connect to the database server from a machine behind the E-Smith box.

Hope this helped,
Nathan

[Henrik Andreasen]

Thanx for the URL Nathan, it looks like a nice and easy way to work with MySQL (I'm using PHPMyAdmin ATM btw).

But MySQL Front can't connect to the server.
When I try to it, I get the error message '1045 - Access denied for user root@pc-00069 (using password YES)'
I’ve tried to add a user to MySQL user database, with pc-00069 and the name this PC has on the LAN, but still no luck.

I'm not behind the SME server btw, It is running as server only on the LAN, not as server/gateway, maybe that's why I can't connect to MySQL on it?

[Dan Brown]

Are you using the correct mysql root password?

[Henrik Andreasen]

Dan Brown wrote:

> Are you using the correct mysql root password?

Yep, when I need it, I just copy it from the  .my.cnf file in the home directory for root, to be sure that it’s the right password I’m using.
It’s the same one, that I’ve used to get PHPMyAdmin working aprox a week ago and PHPMyAdmin is working with it, so the password hasn’t been changed.

I get the same error, if I try to connect (after a reboot of the server) with the new user I made in the MySQL database btw and that user has all the rights to create, add, del and so on as root has.

Don’t know if it means anything, but it is not a fresh install of 5.5 I’m running, I just updated the 5.1.2 I had installed before.

[Charlie Brady]

Dan Brown wrote:

> Are you using the correct mysql root password?

I don't believe that that's the problem. By default root only has permission to connect from localhost. The grant tables will have to be changed to allow access from the LAN. MySQL documentation will tell you how to do that.

Charlie

[Dan Brown]

Yeah, Charlie, I realized that after I posted that message.  Probably a better option than changing the root user would be to add another user that could access from the machine in question.

[Henrik Andreasen]

Thanx for the answers Dan and Charlie.
I thought that root could as default log into MySQL from other machines on the LAN, but just couldn't find the needed info to do it.

Anyway, I've looked on the docs for MySQL and if I write:
GRANT ALL PRIVILEGES ON *.* TO lanroot@"pc-000%"' -> IDENTIFIED BY 'password' WITH GRANT OPTION; into the MySQL console., then the user lanroot can connect to MySQL from the LAN only (or from domains starting with pc-000) and can do all the things root can do when used in the console, right?

Thanx again, for taking the time to answer all my questions.

[Dan Brown]

Henrik, yes, it looks like that should do what you're looking for (though I'm not 100% sure on the host syntax, I think that should do it).

[ClaudioG]

Hi:

> GRANT ALL PRIVILEGES ON *.* TO lanroot@"pc-000%"' -> IDENTIFIED BY 'password' WITH GRANT OPTION;

this way is very dangerous: user lanroot can do EVERYTHING in your MySQL.

More conservative way:

Suppose that you are interesting to work with db named 'foo'.
First, create the db with:
(from mysql console):

> create database foo;

than think to the user that can connect and use this db, i.e. 'user':
(from mysql console):

>GRANT ALL ON foo.* TO user IDENTIFIED BY 'password';
>FLUSH PRIVILEGES;

In this way you allow user to connect only to foo db.

When you grant the privileges, you can:
...TO user@localhost    <- only from the server
...TO user                    <- both server and lan
...TO user@pc-002       <- from single lan host

This way works for me.

Sorry for broken english

Regards
ClaudioG

[Henrik Andreasen]

There were a couple of typos in the grant line, but after thy ware changed it worked.
I can now connect to MySQL on SME from machines on the LAN.
The only thing that’s missing now, is to convert the database and the PHP front ends for them

Thanx Nathan, Dan and Charlie for answering my questions.

[Henrik Andreasen]

ClaudioG wrote:

> this way is very dangerous: user lanroot can do EVERYTHING in your MySQL.

Yeah, lanroot can change/delete anything in MySQL, with the grant I posted earlier, but the lanroot user will only be there when I need it, e.g. when converting Access databases to MySQL, so the security problem isn’t that big.

> More conservative way:

 ..........

> In this way you allow user to connect only to foo db.

Your right, but I have aprox 20 different Access databases, I want to convert to MySQL and the easy way to get all the databases converted, is to let the convert program do all the work
When they are converted to MySQL I kill the lanroot user again and create a user for the databases, with only the need rights.

Thanx for taking the time to answer btw.

[jasper]

To convert your access databases you might try the MySQL ODBC driver to connect via Access to your MySQL Server and transfer the data using Access queries.

hope that helps.

[Henrik Andreasen]

jasper wrote:

> To convert your access databases you might try the MySQL ODBC driver to
> connect via Access to your MySQL Server and transfer the data using Access
> queries.

I can't get Access to connect to MySQL, if I use the port Access want to use as default, I get this error http://themixer.adsl.dk/miscstuff/Images/AccessError1.jpg
The settings is at http://themixer.adsl.dk/miscstuff/Images/AccessSettings1.jpg btw.
If I change the port to 3306 (other programs can connect to MySQL on this port), I get this error http://themixer.adsl.dk/miscstuff/Images/AccessError2.jpg
The settings is at http://themixer.adsl.dk/miscstuff/Images/AccessSettings2.jpg

[jasper]

It looks like you are using the MS SQL driver - don't.

Go to www.mysql.com and get the current stable windows ODBC driver for MySQL. Install, setup connection details via ODBC Manager in windows control panel. Start Access and link tables of type ODBC, select your previously defined connection.

Good luck

jasper