# mysql - cant be this hard surely

## windood

i've done a new install of gentoo, installed apache2 and mod_php 

they seem to work ok.

have got to installing mysql. have installed mysql cant seem to get it running properly. have read tonnes of messages on these forums. am perhaps more confused than when i started.

have tried deleting /var/db/pkg/dev-db/mysel etc and rerunning the ebuild script. 

it comes down to this:

run the ebuild

start the server with /etc/init.d/mysql start

as soon as i go anywhere near mysqladmin

 *Quote:*   

> 
> 
> root@mydatabase andy # /usr/bin/mysqladmin -u root -h mydatabase password 'mypasswd'
> 
> /usr/bin/mysqladmin: connect to server at 'mydatabase' failed
> ...

 

mysql -u root mysql  works and lets me in

assuming if i'd done everything else right and this stage should just work then i can only assume there's something wrong somewhere thats causing this problem.

but what?

my hosts file is now *

127.0.0.1 localhost

192.168.1.101 mydatabase.homenetwork.net mydatabase

any other ideas? ta.

* it wasnt when i compiled mysql

----------

## vega

Mysql binds your login to the server for security reasons.

In other words, the account you have that is working has in the "HOST" column "localhost".

But you need "mydatabase" also if you're accessing from "mydatabase".

I don't know if I fully understand what you're trying to do... It seems that you could only set in your /etc/hosts something like:

```
127.0.0.1 mydatabase.homenetwork.net mydatabase
```

Anyway, if yoiu want to login from a different IP number, try to login using the default and create another user with:

```
grant all privileges on yourdatase.* to youruser@mydatabase identified by "yourpass" with grant option
```

or 

```
grant all privileges on yourdatase.* to youruser@% identified by "yourpass" with grant option (dangerous) 
```

----------

## windood

the final aim is to have a mysql odbc connection on my winxp machine connect to the database 

even with no root password it's still erroring 

with 

[Mysql][ODBC 3.51 Driver] Host '192.168.1.1' is not allowed to connect to this MySQL server

the trouble i have with these privilege tables is that they make no sense to the uninitiated.  Even reading up on them it seems a lot overcomplicated for what i want. 

ah just remembered to restart the server and it works now but i'm not sure how i did it. i've added more than one row to more than one table.

The adage of turning all security on by default suddenly is not as attractive  :Wink: 

gave root a password with mysqladmin -u root password 'password' 

copied the encrypted password from the default  root row to the 192.168.1.1  row i added before. and it works. ta for helping me go in the right direction.

----------

## Chris W

On the machine hosting MySQL run:

```
$ mysql -u root -p mysql

> select host, user, password from user;
```

 to see a list of defined users and the hosts that they can connect from.  You'll see that the 'root' user cannot connect from any machine other than the host of MySQL by default.   You can either modify this table directly or, more safely, use the GRANT command to add a user or change privileges: 

```
$ mysql -u root -p mysql

> grant all on *.* to root@192.168.1.1;
```

Give it a try and let us know how it goes.

----------

## rogue

also, to make sure that you can access it over the network, comment out the line

```
bind-address    = 127.0.0.1
```

in the /etc/mysql/my.cnf file

----------

## vega

You didn't need to restart the application. Next time just do:

```
FLUSH PRIVILEGES;
```

 I forgot to say...  :Smile: 

----------

## Edweirdo

I am having this same problem.

I have just installed mysql and I am getting the error:

```

ERROR 1045: Access denied for user: 'root@localhost' (Using password: NO)

```

when trying to run a script (mysql < script).

When I do the following:

```

$ mysql -u root -p mysql

select host, user, password from user;

```

I get:

```

+-----------+------+----------+

| host      | user | password |

+-----------+-----+-----------+

| localhost | root | [hash]   |

| norman    | root | [hash]   |

| localhost |      |          |

| norman    |      |          |

+-----------+------+----------+

```

My /etc/hosts file looks like this:

```

127.0.0.1   localhost norman

```

Any idea what I've done wrong?  It is a fresh install.

----------

## afabco

I'm having troubles also.  Did a fresh emerge (listed below) and am stuck.  Is mysql really this difficult to get up and going?  if there's a rtfm somewhere, I can't find it.  any help is appreciated.

---------------------------------------------

<snipped all the emerge stuff>

>>> /usr/lib/mysql/libmysqlclient.so -> ../libmysqlclient.so

>>> /usr/lib/mysql/libmysqlclient_r.so -> ../libmysqlclient_r.so

 *

 * You might want to run:

 * "ebuild /var/db/pkg/dev-db/mysql-4.0.18-r1/mysql-4.0.18-r1.ebuild config"

 * if this is a new install.

 *

 *

 * If you're upgrading from MySQL-3.x, you must recompile the other

 * packages on your system that link with libmysqlclient after the

 * upgrade completes.  To obtain such a list of packages for your

 * system, you may use 'revdep-rebuild' from app-portage/gentoolkit.

 *

>>> Regenerating /etc/ld.so.cache...

 * Caching service dependencies...

>>> dev-db/mysql-4.0.18-r1 merged.

>>> Recording dev-db/mysql in "world" favorites file...

>>> clean: No packages selected for removal.

>>> Auto-cleaning packages ...

>>> No outdated packages were found on your system.

 * Regenerating GNU info directory index...

 * Processed 245 info files.

root@amlp / # rc-update add mysql default

 * mysql added to runlevel default

 * Caching service dependencies...

 * rc-update complete.

root@amlp / # ebuild /var/db/pkg/dev-db/mysql-4.0.18-r1/mysql-4.0.18-r1.ebuild config

 * Press ENTER to create the mysql database and set proper

 * permissions on it, or Control-C to abort now...

Preparing db table

Preparing host table

Preparing user table

Preparing func table

Preparing tables_priv table

Preparing columns_priv table

Installing all prepared tables

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/etc/init.d/mysql start (you have to start the server first!)

/usr/bin/mysqladmin -u root -h amlp password 'new-password'

/usr/bin/mysqladmin -u root password 'new-password'

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at

http://www.mysql.com

Support MySQL by buying support/licenses at https://order.mysql.com

root@amlp / # /etc/init.d/mysql start

 * Starting mysqld...                                                                         [ ok ]

root@amlp / # /usr/bin/mysqladmin -u root -h amlp password 'test'

/usr/bin/mysqladmin: connect to server at 'amlp' failed

error: 'Lost connection to MySQL server during query'

root@amlp / # /usr/bin/mysqladmin -u root -h amlp password test

/usr/bin/mysqladmin: connect to server at 'amlp' failed

error: 'Lost connection to MySQL server during query'

root@amlp / # /usr/bin/mysqladmin -u root password 'test'

root@amlp / # exit

exit

bozo@amlp bozo $ mysql -u root -p test

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 2 to server version: 4.0.18

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select host, user, password from user;

ERROR 1146: Table 'test.user' doesn't exist

mysql>

----------

## pakman

Edwierdo: you are getting that error because you are trying to log into MySQL as root user without using a password when you have set a password (hence the "Access denied for user: 'root@localhost' (Using password: NO) "). When you load the script do it something like this (note the -p) and type the password you set when it asks you:

mysql -u root -p < script.sql

afabco: iirc the default install of MySQL doesn't listen on any network interfaces so you can't connect to it by using the hostname "amlp". Just leave out the -h bit. If you want it listening on the network comment out "skip-networking" in /etc/mysql/my.cnf as the other poster mentions above. Your final error occurs because the "test" database is empty, theres no tables in it. To find out whats where do something like:

USE test;

SHOW tables;

----------

## afabco

Hi, thanks for the reply!

the skip-networking was already commented out, but the bind-address was set to 127.0.0.1.  Elsewhere I googled that this could only be one ip, so I set it to the ip I wanted (this is a 2-nic machine).  this cleared up the 'Lost connection' error when using -h

Question: is it possible to use the nic designator (eg eth1) instead of the ip?

>afabco: iirc the default install of MySQL doesn't listen on any network

> interfaces so you can't connect to it by using the hostname "amlp". 

> Just leave out the -h bit. If you want it listening on the network 

>comment out "skip-networking" in /etc/mysql/my.cnf as the other 

> poster mentions above. 

As far as setting up a 'test' database, I haven't gotten that far yet.  All I was trying to do was change the mysql root password to 'test'.

Question: the mysql user 'root' has no relation to the system user 'root', right?  

Thanks!

> Your final error occurs because the "test" database is empty, theres no

> tables in it. To find out whats where do something like:

>

>USE test;

>SHOW tables;

----------

## pakman

 *afabco wrote:*   

> Question: is it possible to use the nic designator (eg eth1) instead of the ip?
> 
> 

 

I don't think so, however removing all of the bind-address statements from my.cnf should make it listen on all interfaces if thats any help, you could then use iptables to block any interfaces you don't want mysql connections on (iptables can work with interface names).

The root user on mysql isn't anything to do with the system root you're right, I assume they chose that name for the admin to fit in with their preferred platform  :Smile: 

----------

