# MYSQL

## RCMN

i use webmine to connect MYSQL40: i have this error

DBI connect failed : Access denied for user: '@localhost' to database 'mysql'

my host look like this:

127.0.0.1               localhost

192.168.0.10            mycomp.mydomain.net mycomp

anyway if i change my host and restart mysql nothing happen!!

when i try to do one of those commande:

GRANT ALL PRIVILEGES ON *.* to root@localhost IDENTIFIED BY 'rootpass' WITH GRANT OPTION;

GRANT ALL PRIVILEGES ON *.* to root@"%" IDENTIFIED BY 'rootpass' WITH GRANT OPTION;

i have this error:

ERROR 1064: You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'to root@localhost IDENTIFIED BY 'mypass' WITH GRANT OPTION' a

i don't have this file:

my.cnf

when i reinstall i keep having the same probleme.

it was working ,until i change under webmin (for test) in mysql-->host: mycomputer to mycomputer.domaine.net 

since i add '.domaine.net' i have this error. how can i fixe that , and where .I read the manual and try things but i'm new to mysql so i need a little help.

----------

## destiney

There is no user defined in '@localhost', that's the first problem to solve.

----------

## thecooptoo

thaks to MgicMonkey this is how I sorted mine out 

By the way, the MySQL root user is not the same as the local root user (MySQL uses it's own authentication method); the database will be running as a user "mysql" which will need access to the actual database files.

If things have got screwed up, here's a bit of a "role-back-and-start-again" recovery procedure:

1) Stop the database daemon (/etc/init.d/mysql stop)

2) Delete everything from the data directory (rm -r /var/lib/mysql)

3) Re-run mysql_install_db

4) Start the database daemon again (/etc/init.d/mysql start)

5) Login as root with no password (mysql -u root)

6) See if it'll let you at the mysql database (use mysql)

Then you should be able to use Webmin; please make sure that the first thing you do is set the MySQL root password...

----------

## RCMN

ok i did everything that you say : now i try to change the password and :

# /usr/local/bin/mysqladmin -u root -h localhost password 'mypwd'

/usr/local/bin/mysqladmin: unable to change password; error: 'Can't find file: './mysql/user.frm' (errno: 13)'

and i can only see test in webmin and all others option are locked.i don,t see mysql

----------

## thecooptoo

did you run mysql_install_db?

AFAIK this sets up the default DB in /var/lib/mysql/mysql

----------

## RCMN

yes i did

and i have everything in /var/lib/mysql/mysqlLast edited by RCMN on Sun Dec 21, 2003 10:58 pm; edited 1 time in total

----------

## thecooptoo

what is in /var/lib/mysql ?

----------

## RCMN

# ls /var/lib/mysql/mysql/

columns_priv.MYD        db.frm                  host.MYI                user.MYD

columns_priv.MYI        func.MYD                host.frm                user.MYI

columns_priv.frm        func.MYI                tables_priv.MYD         user.frm

db.MYD                  func.frm                tables_priv.MYI

db.MYI                  host.MYD                tables_priv.frm#l

----------

## thecooptoo

cant fin dthe reference , but I 'think' localhost has to be in quotes 'localhost' - somethign to dowith how mysql parses the command. i think if you run the setp without a host it assumes its localhost

----------

## RCMN

/usr/local/bin/mysqladmin -u root -h 'localhost' password 'password'

/usr/local/bin/mysqladmin: unable to change password; error: 'Can't find file: './mysql/user.frm' (errno: 13)'

same problem

why it try to look for './mysql/user.frm'  anyway    why not /var/lib/etc...Last edited by RCMN on Sun Dec 21, 2003 11:06 pm; edited 1 time in total

----------

## thecooptoo

did you delete all the stuff first adn runt he setup from the beginning?

----------

## RCMN

i delete  everthing  and do it again ...now i have this message in /var/lib/mysql/mycomputer.err

031221 17:02:14  mysqld started

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

031221 17:02:15  InnoDB: Setting file ./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait...

031221 17:02:16  InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait...

031221 17:02:16  InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait...

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

031221 17:02:18  InnoDB: Started

031221 17:02:18  Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' (errno: 13)

031221 17:02:18  Aborting

031221 17:02:18  InnoDB: Starting shutdown...

031221 17:02:20  InnoDB: Shutdown completed

031221 17:02:20  /usr/local/libexec/mysqld: Shutdown Complete

031221 17:02:20  mysqld ended

 :Shocked: 

----------

## thecooptoo

sorry this is beyond me - remove mysql and re-emerge it?

----------

## RCMN

well i already did it .I 'll do it again .thx anyway .

----------

## punter

I want to reset my whole mysql.

If I unemerge mysql, then rm -rf /var/lib/mysql and then emerge mysql again.

would that do ??

Is /var/lib/mysql the only place it holds its settings/database/password/etc or do I need to delete other stuff as well ???

----------

## gigatexal

If a fix hasn't been found already try this:

In a Unix environment, the procedure is as follows: 

You must log onto your system as either the Unix root user or the same user as mysqld server. 

You must first locate the `.pid' which contains the server's process ID. The exact location and name of this file depends on your distribution, hostname, and configuration. Common locations are: `/var/lib/mysql/', `/var/run/mysqld/' and `/usr/local/mysql/data/'. Generally the base filename is either `mysqld' or your system's hostname. Now you can take down the MySQL server by sending a normal kill (not kill -9) to the mysqld process: 

shell> kill `cat /mysql-data-directory/hostname.pid`

Restart the MySQL server with the special --skip-grant-tables option: 

shell> mysqld_safe --skip-grant-tables &

Set a new password for the root@localhost MySQL user: 

shell> mysqladmin -u root flush-privileges password "newpwd"

Replace ``newpwd'' with the actual root password that you want to use. 

You should now be able to connect using the new password. 

Alternatively, you can set the new password using the mysql client: 

Take down and restart mysqld with the --skip-grant-tables option as described above. 

Connect to the mysqld server with: 

shell> mysql -u root mysql

Issue the following commands in the mysql client: 

mysql> UPDATE mysql.user SET Password=PASSWORD('newpwd')

    ->                   WHERE User='root';

mysql> FLUSH PRIVILEGES;

Replace ``newpwd'' with the actual root password that you want to use. 

You should now be able to connect using the new password.

----------

